Update pandas dataframe value(s) from list

Question

Imagine that we have the following dataframe:

Name    ID  Phone   Email
Paul    10  000001  [email protected]
Sarah   20          [email protected]
John    30  000003  
Will    40  
Evelyn  50  000005  [email protected]

And also the following lists:

['Sarah', '20', '000002', '[email protected]']
['John', '30', '000003', '[email protected]']
['Will', '40', '000004', '[email protected]']

Is there any pythonic pandas way to update the values that are None in the Dataframe from the lists than not having to loop and look field by field?

The result should be:

Name    ID  Phone   Email
Paul    10  000001  [email protected]
Sarah   20  000002  [email protected]
John    30  000003  [email protected]
Will    40  000004  [email protected]
Evelyn  50  000005  [email protected]

Thank you in advance!!

jezrael · Accepted Answer · 2020-11-04 11:50:55Z

You can create DataFrame from lists, set Name to index in both DataFrames and use DataFrame.combine_first, for same order is converted index to column, processing and last sorting by this column:

L = [['Sarah', '20', '000002', '[email protected]'],
['John', '30', '000003', '[email protected]'],
['Will', '40', '000004', '[email protected]']]

df1 = pd.DataFrame(L, columns=['Name','ID','Phone','Email']).set_index('Name')
print (df1)
       ID   Phone          Email
Name                            
Sarah  20  000002  [email protected]
John   30  000003  [email protected]
Will   40  000004  [email protected]

df = (df.reset_index()
       .set_index('Name')
       .combine_first(df1)
       .reset_index()
       .sort_values('index', ignore_index=True)
       .reindex(df.columns, axis=1))
print (df)
     Name  ID   Phone            Email
0    Paul  10  000001    [email protected]
1   Sarah  20  000002    [email protected]
2    John  30  000003    [email protected]
3    Will  40  000004    [email protected]
4  Evelyn  50  000005  [email protected]

Anoter idea is use DataFrame.update, but all values are overwitten, not only NaNs:

df = df.set_index('Name')
df.update(df1)
df = df.reset_index()
print (df)
     Name  ID   Phone            Email
0    Paul  10  000001    [email protected]
1   Sarah  20  000002    [email protected]
2    John  30  000003    [email protected]
3    Will  40  000004    [email protected]
4  Evelyn  50  000005  [email protected]

Collectives™ on Stack Overflow

Update pandas dataframe value(s) from list

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