How to group data by rows in pandas

user_11679697 0
Viewed 72

I have a dataset which looks like this

        Fault ID    REPORT_DATE INITIAL_MFL
0       ID000001    2014-01-01  CE
1       ID000002    2014-01-01  CA
2       ID000003    2014-01-01  OTHER
3       ID000004    2014-01-01  OK
4       ID000005    2014-01-01  LN
...       ...        ...    ...
275586  ID275581    2017-03-31  CE
275587  ID275586    2017-03-31  CE
275588  ID275589    2017-03-31  CA
275589  ID275590    2017-03-31  CA
275590  ID275591    2017-03-31  OK

I want to group them by date. For example

REPORT_DATE INITIAL_MFL                 CE CA OK LN OTHERS
2014-01-01  [CE, CA, OTHERS, OK, ...]    2  1  5  3      6
2014-01-02  [CE, CA, OTHERS, OK, ...]    2  1  5  3      6

Many faults could occur in a single day. so, I need to group them and create a new column on its basis. I am unable to figure out how to do it.

I would be grateful to you for helping with this.

Thank you.

pandas data-analysis data-manipulation dataframe python
1 Answers

Suppose you have this dataframe:

   Fault ID REPORT_DATE INITIAL_MFL
0  ID000001  2014-01-01          CE
1  ID000002  2014-01-01          CA
2  ID000003  2014-01-01       OTHER
3  ID000004  2014-01-01          OK
4  ID000005  2014-01-01          LN
5  ID275581  2017-03-31          CE
6  ID275586  2017-03-31          CE
7  ID275589  2017-03-31          CA
8  ID275590  2017-03-31          CA
9  ID275591  2017-03-31          OK

Then you can do:

from collections import Counter

g = df.groupby("REPORT_DATE")["INITIAL_MFL"].agg(list)
c = pd.DataFrame(g.apply(Counter).tolist(), index=g.index)
df_out = g.to_frame().join(c)
print(df_out)

Prints:

                         INITIAL_MFL  CE  CA  OTHER  OK   LN
REPORT_DATE                                                 
2014-01-01   [CE, CA, OTHER, OK, LN]   1   1    1.0   1  1.0
2017-03-31      [CE, CE, CA, CA, OK]   2   2    NaN   1  NaN
user_100359850
Related