Python Dataframe - replace NaN value with an existing value from Dataframe

Question

I am trying to concatenate two dataframes together which expectedly results in NaN values being created for columns X and Y.

d1 = {'Name':['X1','X2','X3','X4','X5'],
     'Value':[1,2,3,4,5],
     'X':[10,10,20,20,30],
     'Y':[10,20,10,20,10]} 

df1 = pd.DataFrame(d1)

d2 = {'Name':['X1','X2','X2','X3'],    
     'Value':[8,6,5,12]}

df2 = pd.DataFrame(d2)

df1.append(df2).reset_index(drop=True)

I would like to update the NaN values in cols X and Y with the corresponding values already populated in df1.

For example any instance of a row containing df1['Name'] == 'X1' would have the same df1['X'] and df1['Y'] values as those associated with df1['Name'] == 'X1'.

In this example the values would be: Name = X1, X = 10, Y = 10.

Many thanks for the help.

Henry Ecker · Accepted Answer · 2021-06-05 12:52:33Z

2

Try groupby ffill:

new_df = df1.append(df2).reset_index(drop=True)
new_df[['X', 'Y']] = new_df.groupby('Name')[['X', 'Y']].ffill()

new_df:

  Name  Value     X     Y
0   X1      1  10.0  10.0
1   X2      2  10.0  20.0
2   X3      3  20.0  10.0
3   X4      4  20.0  20.0
4   X5      5  30.0  10.0
5   X1      8  10.0  10.0
6   X2      6  10.0  20.0
7   X2      5  10.0  20.0
8   X3     12  20.0  10.0

answered Jun 5, 2021 at 12:52

Henry Ecker♦

35.9k19 gold badges48 silver badges67 bronze badges

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

Comments

Anurag Dabas · Accepted Answer · 2021-06-05 13:02:49Z

1

out = df1.append(df2,ignore_index=True)

Another way via fillna() and groupby():

out.fillna(out.groupby('Name')[['X','Y']].transform('first'),inplace=True)

OR

out[['X', 'Y']] = out.groupby('Name')[['X', 'Y']].fillna(method='ffill')

output of out:

  Name  Value     X     Y
0   X1      1  10.0  10.0
1   X2      2  10.0  20.0
2   X3      3  20.0  10.0
3   X4      4  20.0  20.0
4   X5      5  30.0  10.0
5   X1      8  10.0  10.0
6   X2      6  10.0  20.0
7   X2      5  10.0  20.0
8   X3     12  20.0  10.0

edited Jun 5, 2021 at 13:02

answered Jun 5, 2021 at 12:56

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Collectives™ on Stack Overflow

Python Dataframe - replace NaN value with an existing value from Dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related