I would like to set values in col2
of DF1
using the value held at the matching index of col2
in DF2
:
DF1
:
col1 col2
index
0 a
1 b
2 c
3 d
4 e
5 f
DF2
:
col1 col2
index
2 a x
3 d y
5 f z
DF3
:
col1 col2
index
0 a NaN
1 b NaN
2 c x
3 d y
4 e NaN
5 f z
If I just try and set DF1['col2'] = DF2['col2']
then col2
comes out as all NaN
values in DF3
- I take it this is because the indices are different. However when I try and use map()
to do something like:
DF1.index.to_series().map(DF2['col2'])
then I still get the same NaN
column, but I thought it would map the values over where the index matches...
What am I not getting?
You need join
or assign
:
df = df1.join(df2['col2'])
print (df)
col1 col2
index
0 a NaN
1 b NaN
2 c x
3 d y
4 e NaN
5 f z
Or:
df1 = df1.assign(col2=df2['col2'])
#same like
#df1['col2'] = df2['col2']
print (df1)
col1 col2
index
0 a NaN
1 b NaN
2 c x
3 d y
4 e NaN
5 f z
If no match and all values are NaN
s check if indices have same dtype in both df
:
print (df1.index.dtype)
print (df2.index.dtype)
If not, then use astype:
df1.index = df1.index.astype(int)
df2.index = df2.index.astype(int)
Bad solution (check index 2):
df = df2.combine_first(df1)
print (df)
col1 col2
index
0 a NaN
1 b NaN
2 a x
3 d y
4 e NaN
5 f z
You can simply concat as you are combining based on index
df = pd.concat([df1['col1'], df2['col2']],axis = 1)
col1 col2
index
0 a NaN
1 b NaN
2 c x
3 d y
4 e NaN
5 f z
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With