how to pass first column value column value has filter in pandas data frame [duplicate]

This is my below data frame "df" sorted by date

item  price    date
  A    11.2   2021-12-31
  B    32.4   2021-12-31
  C    75.5   2021-12-31 
  A    89.3   2021-12-30
  B    12.2   2021-12-30 
  C    14.2   2021-12-30

basically I just need the Data at the last available date

i.e

 item  price    date
  A    11.2   2021-12-31
  B    32.4   2021-12-31
  C    75.5   2021-12-31 

I tried the same with the below code , its not work as expected. I am new to pandas kindly help.

df = df.set_index(['date'])
df = df.loc[df['date'][0]]

You can extract the indices of the maximum dates for each item, and then select them:

idxs = df.groupby(['item'])['date'].transform(max) == df['date']
print(df[idxs])

Don't forget that you can always use dict = df.to_dict() or list = df.toList() and use a dict or a list that you are probably more experienced with.


You can sort your dataframe based on your item and date columns (in ascending order which is the default) and return the last row using tail:

df.sort_values(['item','date']).groupby(['item']).tail(1)

  item  price       date
0    A   11.2 2021-12-31
1    B   32.4 2021-12-31
2    C   75.5 2021-12-31

The pd.to_datetime part will ensure that your date is of datetime type. So essentially you need:

df.assign(date = pd.to_datetime(df['date'])).sort_values(['item','date']).groupby(['item']).tail(1)