Pandas/Numpy - Fill Missing Values Per Another Column

Question

Good afternoon all i have a dataframe

dataframe = df

NPI       Fa Name
1100      
1100      Johnson
1100  
5555 
2222      Meghan
2222    
4444
4444

My goal is to fill in the Missing FA name per the corresponding NPI if it is present

    NPI       Fa Name
    1100      Johnson
    1100      Johnson
    1100      Johnson
    5555 
    2222      Meghan
    2222      Meghan
    4444
    4444

i know this might be a simple task, however coding wise i am having trouble figuring it out. Please help!

jezrael · Accepted Answer · 2019-07-07 17:40:43Z

2

Use GroupBy.apply with forward and back filling missing values if order is important:

#if necessary
df['Fa Name'] = df['Fa Name'].replace('', np.nan)

df['Fa Name'] = df.groupby('NPI')['Fa Name'].apply(lambda x: x.ffill().bfill())
print (df)
    NPI  Fa Name
0  1100  Johnson
1  1100  Johnson
2  1100  Johnson
3  5555      NaN
4  2222   Meghan
5  2222   Meghan
6  4444      NaN
7  4444      NaN

If not, fist sorting per both columns and then use GroupBy.ffill:

df = df.sort_values(['NPI','Fa Name'])

df['Fa Name'] = df.groupby('NPI')['Fa Name'].ffill()
print (df)
    NPI  Fa Name
1  1100  Johnson
0  1100  Johnson
2  1100  Johnson
4  2222   Meghan
5  2222   Meghan
6  4444      NaN
7  4444      NaN
3  5555      NaN

answered Jul 7, 2019 at 17:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Pandas/Numpy - Fill Missing Values Per Another Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related