Fill nan rows of multiple columns' contents with previous values in Pandas

Question

For an excel file, after reading with pandas, I get a dataframe as follows:

   type sub_type  num
0     a       a1    1
1   NaN      NaN    2
2   NaN       a2    3
3     b       b1    4
4   NaN      NaN    5
5   NaN       b2    6
6   NaN      NaN    7
7     c       c1    8
8   NaN      NaN    9
9   NaN      NaN   10
10  NaN       c2   11

How can I get an expected result like this? Thanks.

   type sub_type  num
0     a       a1    1
1     a       a2    2
2     a       a2    3
3     b       b1    4
4     b       b2    5
5     b       b2    6
6     b       b3    7
7     c       c1    8
8     c       c2    9
9     c       c3   10
10    c       c2   11

SM Abu Taher Asif · Accepted Answer · 2019-07-16 04:14:11Z

2

you can use forward fill from fillna in a fixed column:

df['ColumnNameORIndex'] = df['ColumnNameORIndex'].fillna(method='ffill')

or in full dataFrame:

df = df.fillna(method='ffill')

edited Jul 16, 2019 at 4:14

answered Jul 16, 2019 at 4:08

SM Abu Taher Asif

2,3111 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ah bon Over a year ago

Thanks. If I want forward fill specifc columns by names, it should be like this: df.loc[:, ['type', 'sub_type']] = df[['type', 'sub_type']].fillna(method='ffill'), right?

BENY · Accepted Answer · 2019-07-16 04:13:55Z

2

Yes we can do it , but not that easy

df.type=df.type.ffill()# first ffill with type 
s=df.groupby([df.type,df.sub_type.notnull().cumsum()]).cumcount().add(df.sub_type.str[1:].astype(float).ffill(),fill_value=0).astype(int).astype(str).radd(df.type)
# then we create the sub group with the notnull value to find the sub id
# and get the number of values within each subgroup add the first value sub_id
df.sub_type.fillna(s,inplace=True)
df
   type sub_type  num
0     a       a1    1
1     a       a2    2
2     a       a2    3
3     b       b1    4
4     b       b2    5
5     b       b2    6
6     b       b3    7
7     c       c1    8
8     c       c2    9
9     c       c3   10
10    c       c2   11

answered Jul 16, 2019 at 4:13

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

ah bon Over a year ago

Thanks, your solution is quite difficult to understand. What do you think about this one: df.loc[:, ['type', 'sub_type']] = df[['type', 'sub_type']].fillna(method='ffill')?

Collectives™ on Stack Overflow

Fill nan rows of multiple columns' contents with previous values in Pandas

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related