Pandas将年/月Int列转换为日期时间和季度平均值

Question

我在df中有数据，分为年和月列，我试图找到观察到的数据列的平均值。我无法在网上找到如何将"年"one_answers"月"列转换为日期时间，然后找到Q1, Q2, Q3等平均值。

year    month   data
0   2021    1   7.100427005789888
1   2021    2   7.22523237179488
2   2021    3   8.301528122415217
3   2021    4   6.843885683760697
4   2021    5   6.12365177832918
5   2021    6   6.049659188034206
6   2021    7   5.271174524400343
7   2021    8   5.098493589743587
8   2021    9   6.260155982906011

我需要最终数据看起来像-

year    Quarter Q data
2021    1       7.542395833
2021    2       6.33906555
2021    3       5.543274699

我已经尝试了这种变化，将'year'和'month'列更改为datetime，但它给出了一个以year = 1970开始的长日期

df.iloc[:, 1:2] = df.iloc[:, 1:2].apply(pd.to_datetime)
year                         month  wind_speed_ms
0  2021 1970-01-01 00:00:00.000000001       7.100427
1  2021 1970-01-01 00:00:00.000000002       7.225232
2  2021 1970-01-01 00:00:00.000000003       8.301528
3  2021 1970-01-01 00:00:00.000000004       6.843886
4  2021 1970-01-01 00:00:00.000000005       6.123652
5  2021 1970-01-01 00:00:00.000000006       6.049659
6  2021 1970-01-01 00:00:00.000000007       5.271175
7  2021 1970-01-01 00:00:00.000000008       5.098494
8  2021 1970-01-01 00:00:00.000000009       6.260156

谢谢你,

Answer 1

我希望这对你有用

# I created period column combining year and month column
df["period"]=df.apply(lambda x:f"{int(x.year)}-{int(x.month)}",axis=1).apply(pd.to_datetime).dt.to_period('Q')
# I applied groupby to period
df=df.groupby("period").mean().reset_index()
df["Quarter"] = df.period.astype(str).str[-2:]
df = df[["year","Quarter","data"]]
df.rename(columns={"data":"Q data"})

year    Quarter Q data
0   2021.0  Q1     7.542396
1   2021.0  Q2     6.339066
2   2021.0  Q3     5.543275