CODEWITHSUNDEEP
pythonpandasdataframe
Tarun K
Tarun K

Reputation: 499

How to Concate 2 dataframes horizontally (row and col wise)?

I have 2 dataframes

df1

  Cols/Rows   A    B    C
0         A   50  150  200
1         B  200  250  300
2         C  350  400  450

df2

  Cols/Rows    A    B    C
0         A   50  150  200
1         B  200  300  300
2         C  370  400  450

My expected output

  Cols/Rows    A    A2    B     B2    C    C2
0         A   50    50   150    150  200   200
1         B  200    200  250    300  300   300
2         C  350    370  400    400  450   450

I want to create new dataframe merging both with col and row wise. I tried to use merge() but it didn't worked

print(df2.merge(df1, how='left'))

Upvotes: 4

Views: 88

Answers (3)

jezrael
jezrael

Reputation: 863301

First use merge with left join and parameter suffixes and then for columns names for change order is used this perfect solution:

df = df2.merge(df1, how='left', on='Cols/Rows', suffixes=['','2'])
print (df)
  Cols/Rows    A    B    C   A2   B2   C2
0         A   50  150  200   50  150  200
1         B  200  300  300  200  250  300
2         C  370  400  450  350  400  450

def mygen(lst):
    for item in lst:
        yield item
        yield item + '2'

#first column removed by indexing
cols = ['Cols/Rows'] + list(mygen(df1.columns[1:]))
df = df[cols]
print (df)
  Cols/Rows    A   A2    B   B2    C   C2
0         A   50   50  150  150  200  200
1         B  200  200  300  250  300  300
2         C  370  350  400  400  450  450

Last if need add difference new columns is better use changed jpp solution, because need subtract columns aligned by first col:

df1 = df1.set_index('Cols/Rows')
df2 = df2.set_index('Cols/Rows')
df3 = df2.sub(df1)

df = df2.join(df1.add_suffix(2)).join(df3.add_suffix(3))
print (df)
             A    B    C   A2   B2   C2  A3  B3  C3
Cols/Rows                                          
A           50  150  200   50  150  200   0   0   0
B          200  300  300  200  250  300   0  50   0
C          370  400  450  350  400  450  20   0   0

def mygen(lst):
    for item in lst:
        yield item
        yield item + '2'
        yield item + '3'

df = df[list(mygen(df1.columns))].reset_index()
print (df)
  Cols/Rows    A   A2  A3    B   B2  B3    C   C2  C3
0         A   50   50   0  150  150   0  200  200   0
1         B  200  200   0  300  250  50  300  300   0
2         C  370  350  20  400  400   0  450  450   0

Upvotes: 4

BENY
BENY

Reputation: 323356

merge have suffixes 

df1.merge(df2,on='Cols/Rows',suffixes =['','2'],how='left')
Out[225]: 
  Cols/Rows    A    B    C   A2   B2   C2
0         A   50  150  200   50  150  200
1         B  200  250  300  200  300  300
2         C  350  400  450  370  400  450

Upvotes: 5

jpp
jpp

Reputation: 164783

You can align indices and use pd.DataFrame.join:

res = df1.set_index('Cols/Rows')\
         .join(df2.set_index('Cols/Rows').add_suffix(2))

print(res)

             A    B    C   A2   B2   C2
Cols/Rows                              
A           50  150  200   50  150  200
B          200  250  300  200  300  300
C          350  400  450  370  400  450

Use reset_index as a final step to elevate your index to a series.

Upvotes: 4

Related Questions