CODEWITHSUNDEEP
python-3.xpandas
Martien Lubberink
Martien Lubberink

Reputation: 2735

Pandas fill of NA values horizontally, but limited to one forward fill value only

I want to fill values forward horizontally, but limited to one fill value only.

See the frames below: dfa has some gaps that need filling. I want the results as shown in dfb.

(Note the .T at the end of the lines, the transpose the data horizontally.)

However, dfa.fillna(0, limit=1, axis=1) fills all cells in the Name row, whereas columns 5 and 6 (i.e. the two columns to the left of 7 in the Name row) should remain NaN.

import pandas as pd
import numpy as np
dfa = pd.DataFrame({'Name':[1,      np.nan,      3, np.nan, np.nan, np.nan,      7, np.nan], 
                    'Age': [np.nan,      2, np.nan,      4, np.nan,      6, np.nan,      8]}).T
dfb = pd.DataFrame({'Name':[1,           0,      3,      0, np.nan, np.nan,      7,      0],                
                    'Age': [np.nan,      2,      0,      4,      0,      6,      0,      8]}).T
dfc = dfa.fillna(0, limit=1, axis=1)

Upvotes: 2

Views: 934

Answers (1)

jezrael
jezrael

Reputation: 862921

One idea is use forward filling for mask and then replace with DataFrame.mask chained conditions with &:

m = dfa.ffill(limit=1, axis=1).isna()
print (m)
          0      1      2      3      4      5      6      7
Name  False  False  False  False   True   True  False  False
Age    True  False  False  False  False  False  False  False

dfc = dfa.mask(dfa.isna() & ~m, 0)

Or first replace all NaNs and then create NaNs by condition:

dfc = dfa.fillna(0).mask(m)

print (dfc)
        0    1    2    3    4    5    6    7
Name  1.0  0.0  3.0  0.0  NaN  NaN  7.0  0.0
Age   NaN  2.0  0.0  4.0  0.0  6.0  0.0  8.0

Upvotes: 2

Related Questions