Pandas Groupby count on multiple columns for specific string values only

user_8388707 0
Viewed 618

I have a data frame like this

dummy = pd.DataFrame([
('01/09/2020', 'TRUE', 'FALSE'),
('01/09/2020', 'TRUE', 'TRUE'),
('02/09/2020', 'FALSE', 'TRUE'),
('02/09/2020', 'TRUE', 'FALSE'),
('03/09/2020', 'FALSE', 'FALSE'),
('03/09/2020', 'TRUE', 'TRUE'),
('03/09/2020', 'TRUE', 'FALSE')], columns=['date', 'Action1', 'Action2'])

enter image description here

Now I want an aggregation of 'TRUE' action per day, which should look like
enter image description here

I applied group by, sum and count etc but nothing is working for me as it i have to aggegate multiple columns and I don't want to split the table into multiple dataframes and resolve it indivisually and merge into one, can someone please suggest any smart way to do it.

pandas python
6 Answers

True and False in your dummy df are strings, you can convert them to int and sum

dummy.replace({'TRUE':1,'FALSE':0}).groupby('date',as_index = False).sum()

    date        Action1 Action2
0   01/09/2020  2       1
1   02/09/2020  1       1
2   03/09/2020  2       1
user_62873080

You can also try:

dummy.set_index(['date']).eq('TRUE').sum(level='date')

Output:

            Action1  Action2
date                        
01/09/2020        2        1
02/09/2020        1        1
03/09/2020        2        1
user_42384080

Anyone seeing this answer should look at the answers by @QuangHoang or @Vaishali
They are much better answers. I can't control what the OP chooses, but you should go upvote those answers.

Inspired by @QuangHoang

dummy.iloc[:, 1:].eq('TRUE').groupby(dummy.date).sum()

            Action1  Action2
date                        
01/09/2020        2        1
02/09/2020        1        1
03/09/2020        2        1

OLD ANSWER

Fix your dataframe such that it has actual True/False values

from ast import literal_eval

dummy = dummy.assign(**dummy[['Action1', 'Action2']].applymap(str.title).applymap(literal_eval))

Then use groupby

dummy.groupby('date').sum()

            Action1  Action2
date                        
01/09/2020        2        1
02/09/2020        1        1
03/09/2020        2        1
user_23366540 
In [7]: dummy
Out[7]:
         date Action1 Action2
0  01/09/2020    TRUE   FALSE
1  01/09/2020    TRUE    TRUE
2  02/09/2020   FALSE    TRUE
3  02/09/2020    TRUE   FALSE
4  03/09/2020   FALSE   FALSE
5  03/09/2020    TRUE    TRUE
6  03/09/2020    TRUE   FALSE


In [9]: dummy.groupby(['date'], as_index=False).agg(lambda x: x.eq('TRUE').sum())
Out[9]:
         date  Action1  Action2
0  01/09/2020        2        1
1  02/09/2020        1        1
2  03/09/2020        2        1
user_138966670

You can also use pivot table:

dummy.pivot_table(index='date', values=['Action1', 'Action2'], 
                  aggfunc=lambda x: (x=='TRUE').sum()).reset_index()

Output:

          date  Action1 Action2
0   01/09/2020        2       1
1   02/09/2020        1       1
2   03/09/2020        2       1
user_133401430

On the similar path using .resample

...
dummy['date'] = pd.to_datetime(dummy['date'], dayfirst=True)
dummy[['Action1', 'Action2']] = dummy[['Action1', 'Action2']].replace({'TRUE':True, 'FALSE': False})

# set date to index
dummy.set_index('date', inplace=True)

dummy.resample('1D').sum()

See resample documentation

user_68582440
Related