Set dataframe column using values from matching indices in another dataframe

Question

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 try df1[["col2"]] = df2[["col2"]]

Shadab Faiz
– Shadab Faiz

2017-10-23 18:34:05 +00:00
Commented Oct 23, 2017 at 18:34 — Shadab Faiz
– Shadab Faiz, Commented Oct 23, 2017 at 18:34

jezrael · Accepted Answer · 2017-10-23 18:43:09Z

9

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 NaNs 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

edited Oct 23, 2017 at 18:43

answered Oct 23, 2017 at 18:30

jezrael

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

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

Comments

Vaishali · Accepted Answer · 2017-10-23 18:33:31Z

1

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

answered Oct 23, 2017 at 18:33

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Collectives™ on Stack Overflow

Set dataframe column using values from matching indices in another dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related