Pandas: Add columns to DataFrame based off existing column

Question

I have 2 pandas DataFrames that I am manipulating in Python, and I would like to combine them based on a certain column (col_1):

#df1
    col_1   col_2          
0       3       7            
1       4       8            
2       5       5    

#df2
  col_1    col_3 
0     4       78
1     5       89

and the output should look like this:

    col_1   col_2   col_3
0   3       7       0
1   4       8       78
2   5       5       89

I'm not sure about how to go about this using 'best practices'.

Thank you.

Allen Qin · Accepted Answer · 2018-03-06 03:29:27Z

4

Suppose you have:

df1=pd.DataFrame({'col_1': {0: 3, 1: 4, 2: 5}, 'col_2': {0: 7, 1: 8, 2: 5}})
df2=pd.DataFrame({'col_1': {0: 4, 1: 5}, 'col_3': {0: 78, 1: 89}})

You can use merge:

pd.merge(df1,df2,on='col_1',how='left').fillna(0)
Out[22]: 
   col_1  col_2  col_3
0      3      7    0.0
1      4      8   78.0
2      5      5   89.0

If you need col_3 as int (thanks to piRSquared for his suggestion):

pd.merge(df1,df2,on='col_1',how='left').fillna(0, downcast='infer')

Out[25]: 
   col_1  col_2  col_3
0      3      7      0
1      4      8     78
2      5      5     89

edited Mar 6, 2018 at 3:29

answered Mar 6, 2018 at 0:42

Allen Qin

20k9 gold badges55 silver badges68 bronze badges

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

2 Comments

piRSquared Over a year ago

downcast parameter will do the trick... pd.merge(df1,df2,on='col_1',how='left').fillna(0, downcast='infer')

piRSquared Over a year ago

Or pd.merge(df1,df2,on='col_1',how='left').fillna(0).astype(dict(col_3=int))

piRSquared · Accepted Answer · 2018-03-06 01:26:11Z

4

You can use join after using set_index.

df1.join(df2.set_index('col_1'), on='col_1').fillna(0, downcast='infer')

   col_1  col_2  col_3
0      3      7      0
1      4      8     78
2      5      5     89

answered Mar 6, 2018 at 1:26

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

jpp · Accepted Answer · 2018-03-06 00:42:43Z

2

Mapping a series if often efficient for a single column.

df1 = pd.DataFrame({'col_1': [3, 4, 5],
                    'col_2': [7, 8, 5]})

df2 = pd.DataFrame({'col_1': [4, 5],
                    'col_3': [78, 89]})

df1['col_3'] = df1['col_1'].map(df2.set_index('col_1')['col_3']).fillna(0)

#    col_1  col_2  col_3
# 0      3      7    0.0
# 1      4      8   78.0
# 2      5      5   89.0

answered Mar 6, 2018 at 0:42

jpp

166k37 gold badges301 silver badges363 bronze badges

1 Comment

piRSquared Over a year ago

Mind you, this works because unique values of df1col_1 are a super set of values in df2.col_2

BENY · Accepted Answer · 2018-03-06 02:47:40Z

2

Maybe combine_first

df1.set_index('col_1').combine_first(df2.set_index('col_1')).fillna(0).reset_index().astype(int)
Out[237]: 
   col_1  col_2  col_3
0      3      7      0
1      4      8     78
2      5      5     89

answered Mar 6, 2018 at 2:47

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Pandas: Add columns to DataFrame based off existing column

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related