时间戳

首页 > 解决方案 > Unstack Groupby 没有使用 Pandas 将数据分组到正确的数据集中

python - Unstack Groupby 没有使用 Pandas 将数据分组到正确的数据集中

问题描述

您好，数据科学家和 Pandas 专家，

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

在 groupby 中使用 unstack 时，它不会正确地将数据分组。这是我的数据框：

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)

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

 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

我使用了以下 groupby 表达式（我不知道如何按级别对框架进行排序）：

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

这是我使用上面的 groupby 表达式时得到的结果：

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

请向我提供您的专家帮助，了解如何解决和修复我的输出，以便所有内容都正确分组。

谢谢你，非常感谢你的帮助。

这是我之前博客的延续：如何在 Pandas Groupby 中仅显示带有值的列

标签： pythonpandaspandas-groupby

解决方案

快到了，您只需要：

  1. 更改.groupby列的顺序，因为它将按顺序取消堆叠，并且date需要位于末尾而不是开头或
  2. 您可以按索引排序，但在步骤 1 中正确重新排列可以避免您执行此额外步骤。

重新排列.groupby列：

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

或按索引排序，然后使用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

推荐阅读