CODEWITHSUNDEEP
pythonpandaspandas-groupby
DjangoRulz
DjangoRulz

Reputation: 95

Unstack Groupby does not group the data in proper dataset using Pandas

Hello Data Scientist and Pandas Experts,

I need some help as I can’t get my data organized properly.

When using unstack in groupby it does not group the data in properly. Here is my dataframe:

data = [
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'aemp', 'Department': 'dep1'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'aemp', 'Department': 'dep1'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'bemp', 'Department': 'dep1'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'bemp', 'Department': 'dep1'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'cemp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'cemp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store1', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'eemp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'eemp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'femp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'eemp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'femp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'femp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'aemp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'aemp', 'Department': 'dep1'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'gemp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-05 00:00:00'), 'Employee': 'gemp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'gemp', 'Department': 'dep2'},\
{'Store': 'Store2', 'Date': pd.Timestamp('2020-08-09 00:00:00'), 'Employee': 'cemp', 'Department': 'dep2'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'eemp', 'Department': 'dep1'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-05 00:00:00'), 'Employee': 'eemp', 'Department': 'dep1'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'bemp', 'Department': 'dep1'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-05 00:00:00'), 'Employee': 'bemp', 'Department': 'dep1'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'bemp', 'Department': 'dep1'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-07 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'},\
{'Store': 'Store3', 'Date': pd.Timestamp('2020-08-01 00:00:00'), 'Employee': 'demp', 'Department': 'dep2'}]
df = pd.DataFrame(data)

I want to organize my output as follow:

 Store        Store1                   Store2                            Store3           
 Department   dep1          dep2       dep1           dep2             dep1      dep2   
 Employee      aemp  bemp  cemp demp   aemp eemp femp cemp demp gemp   bemp eemp demp
 Date
 2020-08-03    1.0   1.0   2.0  3.0    1.0  1.0  2.0   0.0  1.0 1.0    2.0  1.0   1.0
 2020-08-10    1.0   1.0   0.0  4.0    1.0  2.0  1.0   1.0  2.0 1.0    1.0  1.0   1.0

I used following groupby expression (I don't know how to sort the frame by its level):

df = df.groupby([pd.Grouper(key='Date', freq='W-MON'), 'Store', 'Department', 'Employee'])\
       .size().unstack(['Store', 'Department', 'Employee']).fillna(0)

Here is what I got the result when I used above groupby expression:

Store      Store1                Store2                     Store3           Store2
Department   dep1      dep2        dep1           dep2        dep1      dep2   dep2
Employee     aemp bemp cemp demp   aemp eemp femp demp gemp   bemp eemp demp   cemp
Date
2020-08-03    1.0  1.0  2.0  3.0    1.0  1.0  2.0  1.0  1.0    2.0  1.0  1.0    0.0
2020-08-10    1.0  1.0  0.0  4.0    1.0  2.0  1.0  1.0  2.0    1.0  1.0  1.0    1.0

Please provide me your expert help on how I can solve and fix my output so everything group properly.

Thank You and really appreciate your help.

This is continuous of my previous blog: How to show only column with Values in Pandas Groupby

Upvotes: 1

Views: 97

Answers (1)

David Erickson
David Erickson

Reputation: 16683

Almost there, you just need to:

  1. Change the order of your .groupby columns as it will unstack in order and date needs to be at the end rather than the beginning OR
  2. You can sort by the index, but rearranging correctly in step 1 prevents you from having to do this extra step.

Rearranging .groupby columns:

df = (df.groupby(['Store', 'Department', 'Employee', pd.Grouper(key='Date', freq='W-MON'), ])
        .size()
        .unstack(['Store', 'Department', 'Employee']).fillna(0))

OR sort by the index, prior to unstacking with sort_index():

df = (df.groupby([pd.Grouper(key='Date', freq='W-MON'), 'Store', 'Department', 'Employee'])
        .size()
        .sort_index(level=['Store', 'Department', 'Employee', 'Date'])
        .unstack(['Store', 'Department', 'Employee']).fillna(0))
Out[1]: 
Store      Store1                Store2                          Store3       \
Department   dep1      dep2        dep1           dep2             dep1        
Employee     aemp bemp cemp demp   aemp eemp femp cemp demp gemp   bemp eemp   
Date                                                                           
2020-08-03    1.0  1.0  2.0  3.0    1.0  1.0  2.0  0.0  1.0  1.0    2.0  1.0   
2020-08-10    1.0  1.0  0.0  4.0    1.0  2.0  1.0  1.0  1.0  2.0    1.0  1.0   

Store            
Department dep2  
Employee   demp  
Date             
2020-08-03  1.0  
2020-08-10  1.0

Upvotes: 1

Related Questions