1

I have the following DF1 with 100 columns (col_1 to col_100), and I want to put a condition to count only the columns (col_1 to col_100) with value > 50 against every item in column Item. In Excel, it's fairly easy - use the function COUNTIF(). How can we achieve the same in Python?

Item    col_1   col_2   col_3   col_4   col_5   col_6   col_7   …
item_1  10  5   6   9   4   6   77  …
item_2  3   5   66  76  7   89  33  …

In above table, outcome should be:

Item    Result
item_1  1
item_2  3
busybear
  • 10,194
  • 1
  • 25
  • 42
fna
  • 23
  • 1
  • 5
  • `DF1` - can I suppose that you use [pandas.DataFrame](https://pandas.pydata.org)? – Alex Yu Feb 11 '19 at 23:16
  • Sorry I forgot to mention, I do use pandas DF. I've tried a solution available at a previous post pasted below - couldn't really adjust it into my script: https://stackoverflow.com/questions/2643850/what-is-a-good-way-to-do-countif-in-python – fna Feb 13 '19 at 00:07
  • OK. Some clarifications: a) did you saw https://stackoverflow.com/questions/24810526/countif-in-pandas-python-over-multiple-columns-with-multiple-conditions/24811478 ? b) can you share some code you tried? – Alex Yu Feb 13 '19 at 00:40

1 Answers1

0

I would do it with DataFrame.transpose()

>>>df
Item    col_1   col_2   col_3   col_4   col_5   col_6   col_7   …
item_1  10  5   6   9   4   6   77  …
item_2  3   5   66  76  7   89  33  …

df1 = df.T
tcol = []

for col in df1.columns:
    tcol.append(len(df1.index[df1[col] > 50].tolist()))

df_total = pd.DataFrame(tcol, columns=['total'])
df = df.join(df_total)

P.S. There may be a way to do a conditional sum by row but I do not know it off the back of my head. Would be very much appreciated if anyone in the know can point out how!

kerwei
  • 1,822
  • 1
  • 13
  • 22