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

Question

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]]

score 2 · Accepted Answer · answered Jan 24 '22 at 11:47

2

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])

answered Jan 24 '22 at 11:47

sagi

40,026
6
59
84

score 1 · Answer 2 · answered Jan 24 '22 at 11:50

1

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.

answered Jan 24 '22 at 11:50

White_Sirilo

264
1
11

score 1 · Answer 3 · answered Jan 24 '22 at 11:51

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)

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

3 Answers3