使用pandas DataFrame进行数据透视表操作并排序

Question

我有一个关于数据展示的问题。我的数据集已经格式化并按'Site ID'排序。我需要按Site ID分类呈现所有日期实例。

使用透视表，我已经完成了90%的目标。

df_pivot = pd.pivot_table(df, index=['Site Ref','Site Name', 'Date'])

然而，日期列并未排序。（虽然这个小例子的输出看起来是有序的，但是 ****Thu Jan 11 2018 10:43:20 entry**** 展示了我在大数据集上面遇到的问题）

我无法想出如何呈现类似下面这样的内容，同时每个站点ID下的日期都是有序的。

非常感谢任何帮助。

df = pd.DataFrame.from_dict([{'Site Ref': '1234567', 'Site Name': 'Building A', 'Date': 'Mon Jan 08 2018 10:43:20', 'Duration': 120}, {'Site Ref': '1245678', 'Site Name':'Building B', 'Date': 'Mon Jan 08 2018 10:43:20', 'Duration': 120}, {'Site Ref': '1245678', 'Site Name':'Building B', 'Date': 'Tue Jan 09 2018 10:43:20', 'Duration': 70}, {'Site Ref': '1245678', 'Site Name':'Building B', 'Date': 'Wed Jan 10 2018 10:43:20', 'Duration': 120}, {'Site Ref': '1212345', 'Site Name':'Building C', 'Date': 'Fri Jan 12 2018 10:43:20', 'Duration': 100}, {'Site Ref': '1123456', 'Site Name':'Building D', 'Date': 'Thu Jan 11 2018 10:43:20', 'Duration': 80}, {'Site Ref': '1123456', 'Site Name':'Building D', 'Date': 'Fri Jan 12 2018 12:22:20', 'Duration': 80}, {'Site Ref': '1123456', 'Site Name':'Building D', 'Date': 'Mon Jan 15 2018 11:43:20', 'Duration': 90}, {'Site Ref': '1123456', 'Site Name':'Building D', 'Date': 'Wed Jan 17 2018 10:43:20', 'Duration': 220}])

df = DataFrame(df, columns=['Site Ref', 'Site Name', 'Date', 'Duration'])
df = df.sort_values(by=['Site Ref'])
df

    Site Ref    Site Name   Date                        Duration
5   1123456     Building D  Thu Jan 11 2018 10:43:20    80
6   1123456     Building D  Fri Jan 12 2018 12:22:20    80
7   1123456     Building D  Mon Jan 15 2018 11:43:20    90
8   1123456     Building D  Wed Jan 17 2018 10:43:20    220
4   1212345     Building C  Fri Jan 12 2018 10:43:20    100
0   1234567     Building A  Mon Jan 08 2018 10:43:20    120
1   1245678     Building B  Mon Jan 08 2018 10:43:20    120
2   1245678     Building B  Tue Jan 09 2018 10:43:20    70
3   1245678     Building B  Wed Jan 10 2018 10:43:20    120

df_pivot = pd.pivot_table(df, index=['Site Ref','Site Name', 'Date'])
df_pivot

Site Ref    Site Name   Date    
1123456     Building D  Fri Jan 12 2018 12:22:20    80
                        Mon Jan 15 2018 11:43:20    90
                        ****Thu Jan 11 2018 10:43:20    80****
                        Wed Jan 17 2018 10:43:20    220
1212345     Building C  Fri Jan 12 2018 10:43:20    100
1234567     Building A  Mon Jan 08 2018 10:43:20    120
1245678     Building B  Mon Jan 08 2018 10:43:20    120
                        Tue Jan 09 2018 10:43:20    70
                        Wed Jan 10 2018 10:43:20    120

- user9373483

---

它按字典顺序排序，因为“date”具有“object”（字符串）数据类型... - MaxU - stand with Ukraine

谢谢MaxU，这个完美地解决了我的问题。在此之前，我使用了.dt布尔运算符来过滤数据集，但完全忽略了使用datetime dtype进行排序！眼前的森林，非常感谢。 - user9373483

Answer 1

这里使用字典序排序，因为Date的数据类型是字符串。

解决方法-添加一个新的列，并将其数据类型设置为datetime，在pivot_table中使用它，在Date之前使用，然后再删除它：

In [74]: (df.assign(x=pd.to_datetime(df['Date']))
            .pivot_table(df, index=['Site Ref','Site Name', 'x', 'Date'])
            .reset_index(level='x', drop=True))
Out[74]:
                                              Duration
Site Ref Site Name  Date
1123456  Building D Thu Jan 11 2018 10:43:20        80
                    Fri Jan 12 2018 12:22:20        80
                    Mon Jan 15 2018 11:43:20        90
                    Wed Jan 17 2018 10:43:20       220
1212345  Building C Fri Jan 12 2018 10:43:20       100
1234567  Building A Mon Jan 08 2018 10:43:20       120
1245678  Building B Mon Jan 08 2018 10:43:20       120
                    Tue Jan 09 2018 10:43:20        70
                    Wed Jan 10 2018 10:43:20       120

- MaxU - stand with Ukraine

---

也许我们可以在groupby中使用sort=False。 - Bharath M Shetty

Answer 2

您需要将日期转换为日期时间值，而不是字符串。以下类似的方法可以在您当前的数据透视表上使用：

df_pivot.reset_index(inplace=True)
df_pivot['Date'] = pd.to_datetime(df_pivot['Date'])
df_pivot.sort_values(by=['Site Ref', 'Date'], inplace=True)

- sjw

Answer 3

按照站点参考值排序，使用sort = False进行平均分组。

df.sort_values('Site Ref').groupby(['Site Ref','Site Name','Date'],sort=False).mean()

                                            Duration
Site Ref Site Name  Date                              
1123456  Building D Thu Jan 11 2018 10:43:20        80
                    Fri Jan 12 2018 12:22:20        80
                    Mon Jan 15 2018 11:43:20        90
                    Wed Jan 17 2018 10:43:20       220
1212345  Building C Fri Jan 12 2018 10:43:20       100
1234567  Building A Mon Jan 08 2018 10:43:20       120
1245678  Building B Mon Jan 08 2018 10:43:20       120
                    Tue Jan 09 2018 10:43:20        70
                    Wed Jan 10 2018 10:43:20       120

- Bharath M Shetty