Bdtjtk

I am trying to concatenate some of the columns in my data frame in python pandas. Say, I have the following data frames:

df1['Head','Body','feat1','feat2']

df2['Head','Body','feat3','feat4']

I want to merge the dataframes into:

merged_df['Head','Body','feat1','feat2','feat3',feat4']

Intuitively, I did this:

merged_df = pd.concat([df1, df2['feat3','feat4'],axis=1)

It did not work. I did my research and did this:

merged_df = 

df1[['Head','Body','feat1','feat2']].merge(df2[['Head','feat3','feat4']], 

on='Head', how='left')

It worked but caused some discrepancies on my data. Turns out some of my 'Head' data are not unique. So now I am just looking for the most straight forward way to concatenate the selected columns from DF2 into my DF1. Note that both data frames follow the same order, so the row 1 in DF1 is directly related to row 1 in DF2, so is the row 8120th and so on..

Thanks

taking an example, lets suppose we have two DataFrame's as df1 and df2, so, if the values are of the columns are same or unique across then you simple do merge which will align the columns as you desired.

$ df1

   Head  Body  feat1  feat2

0     1     1      1      1

1     2     2      2      2

2     3     3      3      3





$ df2

   Head  Body  feat3  feat4

0     1     1      1      1

1     2     2      2      2

2     3     3      3      3

Step 1 solution:

>>> pd.merge(df1, df2, on=['Head',  'Body'])

   Head  Body  feat1  feat2  feat3  feat4

0     1     1      1      1      1      1

1     2     2      2      2      2      2

2     3     3      3      3      3      3

Secondly, if you have the columns values are different as follows then you can use pd.concat or pd.merge:

$ df1

   Head  Body  feat1  feat2

0     1     1      1      1

1     2     2      2      2

2     3     3      3      3



$ df2

   Head  Body  feat3  feat4

0     4     1      1      1

1     5     2      2      2

2     6     3      3      3

Step 2 solution:

If you want to use union of keys from both frames, then you can do it both with concat and merge as follows:

>>> pd.concat([df1,df2], join="outer", sort=False)

   Head  Body  feat1  feat2  feat3  feat4

0     1     1    1.0    1.0    NaN    NaN

1     2     2    2.0    2.0    NaN    NaN

2     3     3    3.0    3.0    NaN    NaN

0     4     1    NaN    NaN    1.0    1.0

1     5     2    NaN    NaN    2.0    2.0

2     6     3    NaN    NaN    3.0    3.0





>>> pd.merge(df1, df2, on=['Head',  'Body'], how='outer')

   Head  Body  feat1  feat2  feat3  feat4

0     1     1    1.0    1.0    NaN    NaN

1     2     2    2.0    2.0    NaN    NaN

2     3     3    3.0    3.0    NaN    NaN

3     4     1    NaN    NaN    1.0    1.0

4     5     2    NaN    NaN    2.0    2.0

5     6     3    NaN    NaN    3.0    3.0

Or you can opt to have :

a) if you want to use keys from left frame

pd.merge(df1, df2, on=['Head',  'Body'], how='left')

b) if you want to use keys from right frame

pd.merge(df1, df2, on=['Head',  'Body'], how='right')

Default it takes 'inner'.

inner: use intersection of keys from both frames, similar to a SQL
inner join; preserve the order of the left keys

You Can see DataFrame.merge for detail options..

After looking at your workaround, you want to use the keys from left frame

>>> pd.merge(df1, df2, on=['Head',  'Body'], how='left')

   Head  Body  feat1  feat2  feat3  feat4

0     1     1      1      1    NaN    NaN

1     2     2      2      2    NaN    NaN

2     3     3      3      3    NaN    NaN

I think you need value assign , and it will ignore index

df1['feat3']=df2['feat3'].values

df1['feat4']=df2['feat4'].values