0

How to add three columns from one data frame to another at a certain position? I want to add these columns after a specific column? DF1=['C','D'] after columns A and B in DF2. So how to join columns in between other columns in another dataframe.

df1=pd.read_csv(csvfile)
df2=pd.read_csv(csvfile)

df1['C','D','E'] to df2['K','L','A','B','F']

so it looks like df3= ['K','L','A','B','C','D','F']
keenQuestions
  • 35
  • 1
  • 8

3 Answers3

1

Use concat with DataFrame.reindex for change order of columns:

df3 = pd.concat([df1, df2], axis=1).reindex(['K','L','A','B','C','D'], axis=1)

More general solution:

df1 = pd.DataFrame(columns=['H','G','C','D','E'])
df2 = pd.DataFrame(columns=['K','L','A','B','F'])
    
df3 = pd.concat([df1, df2], axis=1)
c = df3.columns.difference(['C', 'D'], sort=False)
pos = c.get_loc('B')  + 1
c = list(c)
#https://stackoverflow.com/a/3748092/2901002
c[pos:pos] = ['C', 'D']
df3 = df3.reindex(c, axis=1)
print (df3)
Empty DataFrame
Columns: [H, G, E, K, L, A, B, C, D, F]
Index: []
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • what do if a value error, duplicated values in index – keenQuestions May 19 '21 at 13:34
  • @keenQuestions What are duplicates columns? Need merge by them, so instead `pd.concat([df1, df2], axis=1)` need `pd.merge(df1, df2, on=['col1' , 'col2'], how='left')` if duplicated columns, it means in both Dataframes are `col1, col2`? – jezrael May 19 '21 at 13:49
0

Try:

df3=pd.DataFrame()
df3[['K','L','A','B']]=df2[['K','L','A','B']]
df3[['C','D','E']]=df1[['C','D','E']]

Finally:

df3=df3[['K','L','A','B','C','D']]

OR

df3=df3.loc[:,['K','L','A','B','C','D']]
David
  • 8,113
  • 2
  • 17
  • 36
Anurag Dabas
  • 23,866
  • 9
  • 21
  • 41
0

This should work

pd.merge([df1, df2, left_index=True, right_index=True]).[['K','L','A','B','C','D']]

or simply use join which is left by deafult

df1.join(df2)[['K','L','A','B','C','D']]
David
  • 8,113
  • 2
  • 17
  • 36
Pawan Jain
  • 815
  • 3
  • 15