Is there a way to find the value for the last day of each month in a dataframe

user_7889906 0
Viewed 46

I have a dataframe which gives me the daily quantity levels of various articles. I want to get a dataframe which gives me the quantity levels on the last day of every month of each article.

OriginaL df:

item Date Quantity
apple 23/09/21 2143
bat 21/09/2021 2444
cola 15/09/21 1512
apple 21/10/21 2906
bat 4/10/21 2730
cola 16/10/21 2449
cola 31/12/2021 0
apple 27/12/2021 1086
bat 25/12/2021 1186
apple 26/12/2021 1377

Target df:

item Date Quantity
cola 31/12/2021 0
apple 27/12/2021 1086
bat 25/12/2021 1186

Is there any way to obtain it?

I tried group by item and date with tail() but it didn't work.

pandas dataframe group-by python
1 Answers

IIUC need last values per years by Grouper with GroupBy.tail:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

df = df.groupby(['item', pd.Grouper(freq='Y', key='Date')]).tail(1)
print (df)
    item       Date  Quantity
6   cola 2021-12-31         0
8    bat 2021-12-25      1186
9  apple 2021-12-26      1377

because per monhts output is different:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)

df = df.groupby(['item', pd.Grouper(freq='m', key='Date')]).tail(1)
print (df)
    item       Date  Quantity
0  apple 2021-09-23      2143
1    bat 2021-09-21      2444
2   cola 2021-09-15      1512
3  apple 2021-10-21      2906
4    bat 2021-10-04      2730
5   cola 2021-10-16      2449
6   cola 2021-12-31         0
8    bat 2021-12-25      1186
9  apple 2021-12-26      1377
user_29010020
Related