CODEWITHSUNDEEP
pythonpandasgroup-bypivot-table
DjangoRulz
DjangoRulz

Reputation: 95

How to show only column with Values in Pandas Groupby

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

Here is my data frame:

df_dict = [ {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store1', 'employee': 'emp1', 'duties': 'opening'}, \
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'deli'}, \
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store1', 'employee': 'emp3', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store2', 'employee': 'emp1', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store2', 'employee': 'emp5', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store3', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store3', 'employee': 'emp7', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-03 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store1', 'employee': 'emp1', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store1', 'employee': 'emp3', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store2', 'employee': 'emp1', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store2', 'employee': 'emp5', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store3', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store3', 'employee': 'emp7', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-04 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store1', 'employee': 'emp1', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store1', 'employee': 'emp3', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store1', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store2', 'employee': 'emp1', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store2', 'employee': 'emp4', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store2', 'employee': 'emp5', 'duties': 'deli'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store3', 'employee': 'emp2', 'duties': 'closing'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'opening'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store3', 'employee': 'emp7', 'duties': 'cashier'},\
            {'Date': Timestamp('2014-01-10 00:00:00'), 'Store': 'store3', 'employee': 'emp6', 'duties': 'deli'}]

I want to organize my output as follow:

                     Store 1               Store 2          store3      
    Week          emp1  emp2  emp3     emp1 emp4 emp5   emp2 emp6 emp7
    2013-12-30     2    4       2        2    4   2      2    4    2
    2014-01-06     1    1       1        1    1   1      2    1    1

So I have tried following Group by expression:

df_group = dict_df.groupby([pd.Grouper(key='Date', freq='W-MON'), 'Store', 'employee'])\
                            ['duties'].count().unstack(level=1).unstack(level=1).reset_index()

However it shows all employee instead of showing employees work in that particular store example:

                      Store 1                            
Week          emp1  emp2  emp3 emp4 emp5 emp6  emp7 
2013-12-30     2    4       2   NaN NaN  NaN   NaN 
2014-01-06     1    1       1   NaN NaN  NaN   NaN

So how can I get my desire outcome. Basically I want to filter out the employees who are not working in that store.

Is it better to use Groupby for this need or should I consider some other method?

Upvotes: 3

Views: 342

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150785

You can unstack both levels at once:

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

Output:

Store      store1           store2           store3          
employee     emp1 emp2 emp3   emp1 emp4 emp5   emp2 emp6 emp7
Date                                                         
2014-01-06      2    4    2      2    4    2      2    4    2
2014-01-13      1    2    1      1    2    1      1    2    1

Upvotes: 2

Andrej Kesely
Andrej Kesely

Reputation: 195553

Try to unstack multiple levels [1, 2]:

df_out = (df.groupby([pd.Grouper(key='Date', freq='W-MON'), 'Store', 'employee'])['duties']
            .count()
            .unstack(level=[1, 2])
        )
print(df_out)

Prints:

Store      store1           store2           store3          
employee     emp1 emp2 emp3   emp1 emp4 emp5   emp2 emp6 emp7
Date                                                         
2014-01-06      2    4    2      2    4    2      2    4    2
2014-01-13      1    2    1      1    2    1      1    2    1

Upvotes: 2

Related Questions