Pandas Convert Year/Month Int Columns to Datetime and Quarter Average-CodePudding

I have data in a df that is separated into a year and month column and I'm trying to find the average of observed data columns. I cannot find online how to convert the 'year' and 'month' columns to datetime and then to find the Q1, Q2, Q3, etc. averages.

    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

I need the final data to look like -

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

I've tried variations of this to change the 'year' and 'month' columns to datetime but it gives a long date starting with 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

Thank you,

CodePudding user response：

I hope this will work for you

# 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