Python: How to filter row with latest date for each category and fill in the value from row with oldest date to row with latest date

user_14829039 0
Viewed 915

This is an example dataset:

df = pd.DataFrame({'Date': ['11-9-2019', '11-9-2020', '11-8-2019', '15-5-2020'],

                   'name': ['Allen', 'Allen', 'David', 'David'],

                   'Grade': [50, np.nan, 60, np.nan],

                   'code': [3352326, np.nan, 22233467, np.nan]})

df['Date'] = pd.to_datetime(df['Date'])

df

What I want to do is to filter the latest date by column 'name' (ex: Allen). However, all info is in the row with the oldest date (ex: Grade, code), the row with the latest date all have missing data. I want the result to only show the rows with the latest date but also want to move the info from the rows with the oldest date to the rows with the latest date. Like the result below.

df1 = pd.DataFrame({'Date': ['11-9-2020', '15-5-2020'],

                   'name': ['Allen', 'David'],

                   'Grade': [50, 60],

                   'code': [3352326, 22233467]})

df1['Date'] = pd.to_datetime(df1['Date'])

df1

I'm not sure if it's possible, and I couldn't find related results. Thanks in advance!

pandas filter numpy date python
2 Answers

If the actual values are always the first row of the group, you could try:

>>> df.groupby('name').first().reset_index()
    name       Date  Grade        code
0  Allen 2019-11-09   50.0   3352326.0
1  David 2019-11-08   60.0  22233467.0
>>>

If they are not always the first one, you could try:

>>> df.groupby('name').apply(lambda x: x.apply(lambda y: y.dropna().tolist()).iloc[0]).rename_axis('').reset_index().drop('', axis=1)
        Date  Grade        code   name
0 2019-11-09   50.0   3352326.0  Allen
1 2019-11-08   60.0  22233467.0  David
>>>
user_87083640

Try:

df[['Grade','code']]=df[['Grade','code']].fillna(method='ffill')
df_out=df.groupby('name').agg('last').reset_index()

output:

df_out
Out[32]: 
    name       Date  Grade        code
0  Allen 2020-11-09   50.0   3352326.0
1  David 2020-05-15   60.0  22233467.0
user_109535920
Related