Python Pandas: Group BY function on categorical variables

user_2293224 0
Viewed 363

I have dataframe which looks like this:

     DATE                       Cause                 Value
0        2019-04-01     Foreign Interference          0.52680
1        2019-04-01     Foreign Interference          0.91260
2        2019-04-02          Equipment                0.03129
3        2019-04-02          Equipment                0.74794
4        2019-04-02  Vegetation - Touching / Burning  0.12511
5        2019-04-02     Incorrect Installation        0.05635
6        2019-04-02     Incorrect Installation        0.03070
7        2019-04-03     Incorrect Installation        0.02296

Now I want to groupby the data frame by date and sum up the values. However, I want Cause column to be appear as collection of causes if it happend on the same date. So my desired output would be something like that:

Desired Output:

          DATE                       Cause            Value
0        2019-04-01     Foreign Interference          1.438
1        2019-04-02     Equipment,                    0.9915
                        Vegetation - Touching / Burning, 
                        Incorrect Installation                      
2        2019-04-03     Incorrect Installation        0.02296

I tried to do with groupby(['Date','Cause']), but it did not produce the desired output. Any help would be really appreciated.

pandas-groupby pandas python-3.x python
1 Answers

Let us do agg with unique 

s=df.groupby('DATE',as_index=False).agg({'Cause': lambda x : ','.join(x.unique()), 'Value':'sum'})
user_79645270
Related