Update pandas column with another dataframe without overriding existing

Question

Been stuck on this the past hour, and couldn't find a thread here that applies..

Assuming a dataframe:

sample_id | value
0            NAN
1            NAN
2            NAN
3            NAN
...
19990        NAN

I have many other dataframe which are very small subsets of the above. eg:

sample_id | value
0            2
1            4

and

sample_id | value
194            2
200            4

How would I update the values in the first dataframe with the second dataframe but leaving everything else untouched? Using map() overrides the values so that subsequent updates remove previously written values..

Intended outcome:

df = df.(df2) df = df.(df3)

final df:

sample_id | value
0            2
1            4
..            
194          2
200          4
..
19990        NAN

I know I can use loops, but I'm sure theres a faster solution thats on the brink on the horizon that I havent figured out..

Thank you! :)

dataframe.copy to make a copy of the first dataframe, and then operate on that when you want to change it? As you'd noticed, Pandas doesn't make copies unless it needs to. — bouteillebleu
– bouteillebleu, Commented May 18, 2017 at 14:01

Scott Boston · Accepted Answer · 2017-05-18 14:08:24Z

1

Use combine_first

df = pd.DataFrame({'Sample_id':pd.np.arange(0,10000),'value':pd.np.nan})

df1 = pd.DataFrame({'Sample_id':[3,4],'value':[2,4]})

df.set_index('Sample_id', inplace=True)

df1.set_index('Sample_id', inplace=True)

df_out = df1.combine_first(df)
print(df_out.head(10)

Output:

           value
Sample_id       
0            NaN
1            NaN
2            NaN
3            2.0
4            4.0
5            NaN
6            NaN
7            NaN
8            NaN
9            NaN

edited May 18, 2017 at 14:08

answered May 18, 2017 at 14:03

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

3 Comments

Scott Boston Over a year ago

Do you have some null sample_ids?

Wboy Over a year ago

no, sample_id is a column not the index column im afraid

Scott Boston Over a year ago

You use set_index to get it into the index the use combine_first and reset_index afterwards.

Collectives™ on Stack Overflow

Update pandas column with another dataframe without overriding existing

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related