python - 如何在 Pandas Groupby 中仅显示具有值的列

Question

我需要一些帮助，因为我无法正确组织我的数据。

这是我的数据框:

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'}]

我想按如下方式组织我的输出:

                     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

所以我尝试按照表达式分组:

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

但是它显示所有员工而不是显示员工在该特定商店示例中的工作:

                      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

那么我怎样才能得到我想要的结果。基本上我想过滤掉不在那家商店工作的员工。

对于这种需求，使用 Groupby 更好还是我应该考虑其他方法？

Answer 1

尝试拆分多层[1, 2]:

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

打印:

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