CODEWITHSUNDEEP
pandasdataframe
user9715239
user9715239

Reputation: 23

adding a new column to data frame

I'm trying do something that should be really simple in pandas, but it seems anything but. I have two large dataframes

df1 has 243 columns which include:

   ID2  K.   C  type
1  123  1.   2.   T
2  132  3.   1.   N
3  111  2.   1.   U

df2 has 121 columns which include:

   ID3  A    B   
1  123  0.   3.   
2  111  2.   3.   
3  132  1.   2.

df2 contains different information about the same ID (ID2=ID3) but in different order

I wanted to create a new column in df2 named (type) and match the type column in df1. If it's the same ID to the one in df1, it should copy the same type (T, N or U) from df1. In another word, I need it to look like the following data frame butwith all 121 columns from df2+type

ID3   A    B  type
123  0.   3.   T
111  2.   3.   U
132  1.   2.   N

I tried

pd.merge and pd.join.

I also tried 

df2['type'] = df1['ID2'].map(df2.set_index('ID3')['type'])

but none of them is working. it shows KeyError: 'ID3'

Upvotes: 2

Views: 89

Answers (2)

piRSquared
piRSquared

Reputation: 294198

join

df2.join(df1.set_index('ID2')['type'], on='ID3')

   ID3    A    B type
1  123  0.0  3.0    T
2  111  2.0  3.0    U
3  132  1.0  2.0    N

merge (take 1)

df2.merge(df1[['ID2', 'type']].rename(columns={'ID2': 'ID3'}))

   ID3    A    B type
0  123  0.0  3.0    T
1  111  2.0  3.0    U
2  132  1.0  2.0    N

merge (take 2)

df2.merge(df1[['ID2', 'type']], left_on='ID3', right_on='ID2').drop('ID2', 1)

   ID3    A    B type
0  123  0.0  3.0    T
1  111  2.0  3.0    U
2  132  1.0  2.0    N

map and assign

df2.assign(type=df2.ID3.map(dict(zip(df1.ID2, df1['type']))))

   ID3    A    B type
0  123  0.0  3.0    T
1  111  2.0  3.0    U
2  132  1.0  2.0    N

Upvotes: 0

Peter Leimbigler
Peter Leimbigler

Reputation: 11105

As far as I can see, your last command is almost correct. Try this:

df2['type'] = df2['ID3'].map(df1.set_index('ID2')['type'])

Upvotes: 1

Related Questions