python - 使用groupby重新格式化excel数据并在python中的数据框中添加空行

Question

我有一个很长的 Excel 文件，其中包含一年中 60 分钟的降雨量。我希望读入 excel 文件，聚合每日总降雨量的降雨量值(group.by 效果很好)，然后将这些值放入一个新的数据框中，其中一年中的每一天都是如果当天没有下雨，则使用 0 单独一行；如果下雨，则使用每日总降雨量的 Value。我已经概述了我要采取的步骤以及我对下面代码的尝试。如果我尝试编写的代码很糟糕，我愿意接受其他建议。 Excel 文件的第一行如下所示:

60 Minute Counts, []            
Time Stamp  Latitude    Longitude   Value ()
Dec 27 2015 01:30:00 AM 0.297   36.900  0.25
Dec 25 2015 01:00:00 PM 0.297   36.900  0.51
Dec 25 2015 10:30:00 AM 0.297   36.900  0.25
Dec 25 2015 07:30:00 AM 0.297   36.900  0.25
Dec 25 2015 05:00:00 AM 0.297   36.900  0.25
Dec 25 2015 04:30:00 AM 0.297   36.900  0.25
Dec 17 2015 02:30:00 AM 0.297   36.900  0.25
Dec 16 2015 02:30:00 PM 0.297   36.900  0.25
Dec 16 2015 02:00:00 PM 0.297   36.900  0.76
Dec 16 2015 12:30:00 PM 0.297   36.900  0.25
Dec 16 2015 12:00:00 PM 0.297   36.900  0.76
Dec 16 2015 11:30:00 AM 0.297   36.900  5.08
Dec 16 2015 11:00:00 AM 0.297   36.900  0.51
Dec 15 2015 03:30:00 PM 0.297   36.900  0.25

然后我需要读取我已经使用过的 Excel 文件:

from openpyxl import load_workbook

wb = load_workbook(filename = 'filename.xlsx')
sheet_ranges = wb['60 minute counts']

但我不确定如何在第 3 行以上读取实际值。

为 Time Stamp 和 Value () 列定义数据帧 df0 后，我需要转换 Time Stamp 转换为 YYYY-MM-DD 等格式，可以使用以下代码:

import pandas as pd
df0["time"] = pd.to_datetime(df0["time"])
df0["day"] = df0['time'].map(lambda x: x.day)
df0["month"] = df0['time'].map(lambda x: x.month)
df0["year"] = df0['time'].map(lambda x: x.year)

然后我需要将 60 分钟计数的降雨量合并为每日总降雨量，方法是:

df1 = df0.groupby(['Value ()', 'day', 'month', 'year'], as_index=False).sum()

最终我需要制作一个数据框，其中包含一年中每一天的行，然后是每日总降雨量。它看起来像这样:

Date    Value
2015-12-31  0
2015-12-30  0
2015-12-29  0
2015-12-28  0
2015-12-27  0.25
2015-12-26  0
2015-12-25  1.52
2015-12-24  0
2015-12-23  0
2015-12-22  0
2015-12-21  0
2015-12-20  0
2015-12-19  0
2015-12-18  0
2015-12-17  0.25
2015-12-16  7.62

...等等

请告诉我发布整个文件是否有帮助，我可以添加一个保管箱链接。

Answer 1

看来你需要 resample :

df0.index = pd.to_datetime(df0["Time Stamp"])

df1 = df0.resample('D')['Value ()'].sum().fillna(0).reset_index()
print (df1)
   Time Stamp  Value ()
0  2015-12-15      0.25
1  2015-12-16      7.61
2  2015-12-17      0.25
3  2015-12-18      0.00
4  2015-12-19      0.00
5  2015-12-20      0.00
6  2015-12-21      0.00
7  2015-12-22      0.00
8  2015-12-23      0.00
9  2015-12-24      0.00
10 2015-12-25      1.51
11 2015-12-26      0.00
12 2015-12-27      0.25

或 groupby 为 Grouper :

df0.index = pd.to_datetime(df0["Time Stamp"])

df1 = df0.groupby(pd.Grouper(freq='D'))['Value ()'].sum().fillna(0).reset_index()
print (df1)
   Time Stamp  Value ()
0  2015-12-15      0.25
1  2015-12-16      7.61
2  2015-12-17      0.25
3  2015-12-18      0.00
4  2015-12-19      0.00
5  2015-12-20      0.00
6  2015-12-21      0.00
7  2015-12-22      0.00
8  2015-12-23      0.00
9  2015-12-24      0.00
10 2015-12-25      1.51
11 2015-12-26      0.00
12 2015-12-27      0.25

如有必要，请添加 sort_index :

df1 = df0.resample('D')['Value ()'].sum().sort_index(ascending=False).fillna(0).reset_index()
print (df1)
   Time Stamp  Value ()
0  2015-12-27      0.25
1  2015-12-26      0.00
2  2015-12-25      1.51
3  2015-12-24      0.00
4  2015-12-23      0.00
5  2015-12-22      0.00
6  2015-12-21      0.00
7  2015-12-20      0.00
8  2015-12-19      0.00
9  2015-12-18      0.00
10 2015-12-17      0.25
11 2015-12-16      7.61
12 2015-12-15      0.25


df1 = df0.groupby(pd.Grouper(freq='D'))['Value ()'].sum()
         .sort_index(ascending=False).fillna(0).reset_index()
print (df1)
   Time Stamp  Value ()
0  2015-12-27      0.25
1  2015-12-26      0.00
2  2015-12-25      1.51
3  2015-12-24      0.00
4  2015-12-23      0.00
5  2015-12-22      0.00
6  2015-12-21      0.00
7  2015-12-20      0.00
8  2015-12-19      0.00
9  2015-12-18      0.00
10 2015-12-17      0.25
11 2015-12-16      7.61
12 2015-12-15      0.25