CODEWITHSUNDEEP
pythonpandasdataframepandas-groupbypivot-table
Tiago Emanuel Pratas
Tiago Emanuel Pratas

Reputation: 463

Pivot table with count if

I want to count if the number is higher than 0.1 and then group them by month-year to see which month-year has the most days with more than 0.1 variations.

I have a df like these with daily data but only showing month-year index.

enter image description here

table = df.pivot_table(df, columns=['btc','bnb','eth','xmr','xrp'], aggfunc=df[df > 0.1].count())

print(table)

Why not working?

The result needs to be something like this

The result will be something like this

Upvotes: 1

Views: 206

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

You can stack the dataframe then compare the stacked frame with 0.1 to create a booolean mask then take sum on level=0 to count the values which are greater than 0.1 per month-year:

df.stack().gt(0.1).sum(level=0)

Alternate approach:

df[df > 0.1].stack().count(level=0)

EDIT: If you want to count the values which are greater than 0.1 in each of the column per month-year:

df.gt(0.1).sum(level=0)

Upvotes: 3

Related Questions