Pandas: How to remove selected rows from each group and keep only the recent one-CodePudding

I have the following dataframe:

df1 = pd.DataFrame({"id": ['A1', 'A2', 'A3', 'A4', 'B1', 'B2', 'B3', 'B4', 'C1','C2','C3','C4'  ], 
                "date": [pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30),  pd.Timestamp(2017, 12, 30), pd.Timestamp(2018, 12, 30),pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2017, 12, 30), pd.Timestamp(2018, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2017, 12, 30), pd.Timestamp(2018, 12, 30), pd.Timestamp(2019, 12, 30)], 
                "other_col": ['NA', 'NA', 'A333', 'A444', 'NA', 'NA', 'B555', 'B666', 'NA', 'C777', 'C888', 'C999'],
                "other_col_1": [123, 123, 'NA', 'NA', 0.765, 0.555, 'NA', 'NA', 0.324, 'NA', 'NA','NA']})

I want to delete rows where id column corresponds to the value twice in "other_col" and to keep only the recent row for each group. The resulting data-frame should be:

df_new = pd.DataFrame({"id": ['A1', 'A2', 'A4', 'B1', 'B2', 'B4', 'C1','C4'  ], 
                "date": [pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2018, 12, 30),pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2018, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2019, 12, 30)], 
                "other_col": ['NA', 'NA', 'A444', 'NA', 'NA', 'B666', 'NA', 'C999'],
                "other_col_1": [123, 123, 'NA', 0.765, 0.555, 'NA', 0.324, 'NA']})

CodePudding user response：

IIUC, you can groupby the letter and NA status and get the last:

df2 = df1.groupby([df1['id'].str[0], df1['other_col'].eq('NA')],
                  sort=False, as_index=False).last()

output:

   id       date other_col
0  A1 2016-12-30        NA
1  A3 2018-12-30       444
2  B1 2016-12-30        NA
3  B3 2018-12-30       222
4  C1 2016-12-30        NA
5  C4 2019-12-30       888

For a more generic way to get the id: df1['id'].str.extract('^(\D)', expand=False)

If you have real NaNs in other_col, use df1['other_col'].isna()

CodePudding user response：

First convert values NA to missing values in other_col and if necessary sorting values per id and dates, so possible get last non missing value per other_col by GroupBy.last per groups created id without numbers, last filter match rows with missing values in other_col:

df1['other_col'] = df1['other_col'].replace('NA', np.nan)
df1 = df1.sort_values(['id','date'])

s = df1.groupby(df1['id'].str.replace('\d',''))['other_col'].transform('last')
df_new = df1[df1['other_col'].eq(s) | df1['other_col'].isna()]
print (df_new)
    id       date other_col other_col_1
0   A1 2015-12-30       NaN         123
1   A2 2016-12-30       NaN         123
3   A4 2018-12-30      A444          NA
4   B1 2015-12-30       NaN       0.765
5   B2 2016-12-30       NaN       0.555
7   B4 2018-12-30      B666          NA
8   C1 2016-12-30       NaN       0.324
11  C4 2019-12-30      C999          NA