I have a Python pandas dataframe that looks like this:
JavaScript
x
34
34
1
print(dataframe1.head(30))2
Date Close* month_initial month day year3
date_final 4
2022-09-23 Sep 23, 2022 3693.23 Sep 9.0 23 20225
2022-09-22 Sep 22, 2022 3757.99 Sep 9.0 22 20226
2022-09-21 Sep 21, 2022 3789.93 Sep 9.0 21 20227
2022-09-20 Sep 20, 2022 3855.93 Sep 9.0 20 20228
2022-09-19 Sep 19, 2022 3899.89 Sep 9.0 19 20229
2022-09-16 Sep 16, 2022 3873.33 Sep 9.0 16 202210
2022-09-15 Sep 15, 2022 3901.35 Sep 9.0 15 202211
2022-09-14 Sep 14, 2022 3946.01 Sep 9.0 14 202212
2022-09-13 Sep 13, 2022 3932.69 Sep 9.0 13 202213
2022-09-12 Sep 12, 2022 4110.41 Sep 9.0 12 202214
2022-09-09 Sep 09, 2022 4067.36 Sep 9.0 09 202215
2022-09-08 Sep 08, 2022 4006.18 Sep 9.0 08 202216
2022-09-07 Sep 07, 2022 3979.87 Sep 9.0 07 202217
2022-09-06 Sep 06, 2022 3908.19 Sep 9.0 06 202218
2022-09-02 Sep 02, 2022 3924.26 Sep 9.0 02 202219
2022-09-01 Sep 01, 2022 3966.85 Sep 9.0 01 202220
2022-08-31 Aug 31, 2022 3955.00 Aug 8.0 31 202221
2022-08-30 Aug 30, 2022 3986.16 Aug 8.0 30 202222
2022-08-29 Aug 29, 2022 4030.61 Aug 8.0 29 202223
2022-08-26 Aug 26, 2022 4057.66 Aug 8.0 26 202224
2022-08-25 Aug 25, 2022 4199.12 Aug 8.0 25 202225
2022-08-24 Aug 24, 2022 4140.77 Aug 8.0 24 202226
2022-08-23 Aug 23, 2022 4128.73 Aug 8.0 23 202227
2022-08-22 Aug 22, 2022 4137.99 Aug 8.0 22 202228
2022-08-19 Aug 19, 2022 4228.48 Aug 8.0 19 202229
2022-08-18 Aug 18, 2022 4283.74 Aug 8.0 18 202230
2022-08-17 Aug 17, 2022 4274.04 Aug 8.0 17 202231
2022-08-16 Aug 16, 2022 4305.20 Aug 8.0 16 202232
2022-08-15 Aug 15, 2022 4297.14 Aug 8.0 15 202233
2022-08-12 Aug 12, 2022 4280.15 Aug 8.0 12 202234
I want to keep the first and the last row per month. How can I do that? I tried using the following code:
JavaScript
1
7
1
import pandas as pd2
dataframe1.set_index("date_final", inplace=True)3
resultDf = dataframe1.groupby([dataframe1.index.year, dataframe1.index.month]).agg(["first", "last"])4
resultDf.index.rename(["year", "month"], inplace=True)5
resultDf.reset_index(inplace=True)6
resultDf7
but I don’t get the results I want.
Advertisement
Answer
pandas groupby operations don’t sort each group prior to aggregation, which is why 'first' and 'last' are not selecting the correct rows for you.
Additionally, you can use .resample('M') instead of a groupby on year & month.
JavaScript
1
14
14
1
out = (2
df.set_index(df.index.astype('datetime64[ns]')) # copying in the data, I lost the datetime index3
.sort_index() # sort ensures first and last work as expected4
.resample('M') # resample for a shorthand year/month grouping5
.agg(['first', 'last'])6
)7
8
print(out)9
Date Close* month_initial month day year 10
first last first last first last first last first last first last11
date_final 12
2022-08-31 Aug 12, 2022 Aug 31, 2022 4280.15 3955.00 Aug Aug 8.0 8.0 12 31 2022 202213
2022-09-30 Sep 01, 2022 Sep 23, 2022 3966.85 3693.23 Sep Sep 9.0 9.0 1 23 2022 202214
This output doesn’t have the most usable format, so we can use a quick .stack to remedy it:
JavaScript
1
10
10
1
out = out.stack()2
3
print(out)4
Date Close* month_initial month day year5
date_final 6
2022-08-31 first Aug 12, 2022 4280.15 Aug 8.0 12 20227
last Aug 31, 2022 3955.00 Aug 8.0 31 20228
2022-09-30 first Sep 01, 2022 3966.85 Sep 9.0 1 20229
last Sep 23, 2022 3693.23 Sep 9.0 23 202210