backfill a column based on a group pandas

Question

I am working with the following dataframe:

df = pd.DataFrame({"id": ['A', 'A', 'A', 'B', 'B', 'B', 'C','C'  ], 
            "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']})

What I want to achieve is: To backfill "other_col" entries for each corresponding group and to delete "other_col" when it is equal to 'NA' in "other_col_1".

I have tried groupby bfill() and ffill() df.groupby('id')['other_col'].bfill() but it does't work.

The resulting dataframe should look like this:

df_new = pd.DataFrame({"id": ['A', 'A', 'B', 'B', 'C' ], 
            "date": [pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2015, 12, 30), pd.Timestamp(2016, 12, 30), pd.Timestamp(2016, 12, 30)], 
            "other_col": ['A444', 'A444', 'B666', 'B666', 'C999'],
            "other_col_1": [123, 123, 0.765, 0.555, 0.324]})

Yes, you are right, in fact each group is defined by A, B and C, while each element of the group is represented by adding a number to the group, like A1 for first element of the group A, and B1 corresponds to the first element of group B, and so on... — Researcher, Mar 15 '22 at 15:38

score 2 · Accepted Answer · answered Mar 15 '22 at 16:06

First, replace 'NA' with a real NaN value, then bfill:

df = df.replace('NA', np.nan)
df = df.bfill()[df['other_col_1'].notna()]

Output:

>>> df
  id       date other_col  other_col_1
0  A 2015-12-30      A444      123.000
1  A 2016-12-30      A444      123.000
3  B 2015-12-30      B666        0.765
4  B 2016-12-30      B666        0.555
6  C 2016-12-30      C999        0.324

mozway · Answer 2 · 2022-03-15T16:29:46.987

1

IIUC, you could do:

out = (
 df.replace('NA', pd.NA) # ensure real NA
   .assign(other_col=lambda d: d['other_col'].bfill()) # backfill other_col
   .dropna(subset=['other_col_1']) # drop rows based on other_col_1
)

or, to bfill per group:

(df.replace('NA', pd.NA)
   .assign(other_col=lambda d: d.groupby(d['id'].str.replace('\d+', '', regex=True))
                                ['other_col'].bfill())
   .dropna(subset=['other_col_1'])
)

output:

   id       date other_col other_col_1
0  A1 2015-12-30      A444         123
1  A2 2016-12-30      A444         123
3  B1 2015-12-30      B666       0.765
4  B2 2016-12-30      B666       0.555
6  C1 2016-12-30      C999       0.324

edited Mar 15 '22 at 16:29

answered Mar 15 '22 at 15:34

mozway

194,879
13
39
75

Thank you @mozway for your answer, I am able to backfill the column by modifying your code like this out = ( df.replace('NA', pd.NA) # ensure real NA .assign(other_col=lambda d: d['other_col'].bfill()) # backfill other_col ) but it doesn't work when i add third line for subsetting to dropnas. – Researcher Mar 15 '22 at 15:52
@Researcher it does work on the provided dataset, can you provide a example for which it fails? – mozway Mar 15 '22 at 15:58
perhaps i've an issue of any package in my installation, because on the provided dataset this code throws the following error TypeError: Index(...) must be called with a collection of some kind, 'other_col_1' was passed – Researcher Mar 15 '22 at 16:18
@Researcher check the update – mozway Mar 15 '22 at 16:23

backfill a column based on a group pandas

2 Answers2