replace a column in a dataframe by inserting another dataframe

Question

I have two dataframes df1 and df2:

data1 = {'A':[1,3,2,1.4,2,1,2,4], 'B':[10,30,20,1.4,2,78,2,78],'C':[200,340,20,180,2,201,2,100]}
df1 = pd.DataFrame(data1)
print(df1)

     A     B    C
0  1.0  10.0  200
1  3.0  30.0  340
2  2.0  20.0   20
3  1.4   1.4  180
4  2.0   2.0    2
5  1.0  78.0  201
6  2.0   2.0    2
7  4.0  78.0  100

data2 = {'D':['a1','a2','a3','a4',2,1,'a3',4], 'E':['b1','b2',20,1.4,2,78,2,78],'F':[200,340,'c1',180,2,'c2',2,100]}
df2 = pd.DataFrame(data2)
print(df2) 

    D    E    F
0  a1   b1  200
1  a2   b2  340
2  a3   20   c1
3  a4  1.4  180
4   2    2    2
5   1   78   c2
6  a3    2    2
7   4   78  100

I want to insert df2 in df1 by replacing column B in df1. How can a dataframe be inserted by reaplcing column in another dataframe.

desired result:

     A   D    E    F    C
0  1.0  a1   b1  200  200
1  3.0  a2   b2  340  340
2  2.0  a3   20   c1   20
3  1.4  a4  1.4  180  180
4  2.0   2    2    2    2
5  1.0   1   78   c2  201
6  2.0  a3    2    2    2
7  4.0   4   78  100  100

jezrael · Accepted Answer · 2020-03-21 15:04:03Z

Idea is use concat with select columns by positions by DataFrame.iloc and Index.get_loc, last remove original column by DataFrame.drop:

c = 'B'
pos = df1.columns.get_loc(c)

df = pd.concat([df1.iloc[:, :pos], df2, df1.iloc[:, pos:]], axis=1).drop(c, axis=1)
print (df)
     A   D    E    F    C
0  1.0  a1   b1  200  200
1  3.0  a2   b2  340  340
2  2.0  a3   20   c1   20
3  1.4  a4  1.4  180  180
4  2.0   2    2    2    2
5  1.0   1   78   c2  201
6  2.0  a3    2    2    2
7  4.0   4   78  100  100

Tested another columns:

c = 'A'
pos = df1.columns.get_loc(c)

df = pd.concat([df1.iloc[:, :pos], df2, df1.iloc[:, pos:]], axis=1).drop(c, axis=1)
print (df)
    D    E    F     B    C
0  a1   b1  200  10.0  200
1  a2   b2  340  30.0  340
2  a3   20   c1  20.0   20
3  a4  1.4  180   1.4  180
4   2    2    2   2.0    2
5   1   78   c2  78.0  201
6  a3    2    2   2.0    2
7   4   78  100  78.0  100

c = 'C'
pos = df1.columns.get_loc(c)

df = pd.concat([df1.iloc[:, :pos], df2, df1.iloc[:, pos:]], axis=1).drop(c, axis=1)
print (df)
     A     B   D    E    F
0  1.0  10.0  a1   b1  200
1  3.0  30.0  a2   b2  340
2  2.0  20.0  a3   20   c1
3  1.4   1.4  a4  1.4  180
4  2.0   2.0   2    2    2
5  1.0  78.0   1   78   c2
6  2.0   2.0  a3    2    2
7  4.0  78.0   4   78  100

BENY · Accepted Answer · 2020-03-21 15:09:24Z

0

IIUC we use itertools with reindex

import itertools
l=list(itertools.chain.from_iterable(list(df2) if item == 'B' else [item] for item in list(df1)))

pd.concat([df1,df2], axis=1).reindex(columns=l)
     A   D    E    F    C
0  1.0  a1   b1  200  200
1  3.0  a2   b2  340  340
2  2.0  a3   20   c1   20
3  1.4  a4  1.4  180  180
4  2.0   2    2    2    2
5  1.0   1   78   c2  201
6  2.0  a3    2    2    2
7  4.0   4   78  100  100

answered Mar 21, 2020 at 15:09

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

replace a column in a dataframe by inserting another dataframe

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related