CODEWITHSUNDEEP
pythonpandasdataframemergerename
Tony
Tony

Reputation: 121

Merging 3 databases with same names, and renaming them in python

I have 3 df with 25 columns each. All the columns are same in the 3 df.

I want to merge the 3 df, and change the column name to "_a" for 25 columns of df1, change to "_b" for 25 columns of df2, and change to "_c" for 25 columns of df3.

I am using the below code:

pd.merge(pd.merge(df1,df2,'left',on='year',suffixes=['_a','_b']),df3,'left',on='year')

How do I use a rename or some other function, to change all the 25 columns of df3 in the code above?

Thanks.

Upvotes: 2

Views: 721

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210932

pd.merge(pd.merge(df1,df2,'left',on='year',suffixes=['_a','_b']),
         df3,'left',on='year',suffixes=['','_c'])

Another approach:

Source DFs:

In [68]: d1
Out[68]:
   col1  col2  col3
0     1     2     3
1     4     5     6

In [69]: d2
Out[69]:
   col1  col2  col3
0    11    12    13
1    14    15    16

In [70]: d3
Out[70]:
   col1  col2  col3
0    21    22    23
1    24    25    26

Let's create a list of DFs:

In [71]: dfs = [d1,d2,d3]

and a list of suffixes:

In [73]: suffixes = ['_a','_b','_c']

Now we can merge them in one step like as follows:

In [74]: pd.concat([df.add_suffix(suffixes[i]) for i,df in enumerate(dfs)], axis=1)
Out[74]:
   col1_a  col2_a  col3_a  col1_b  col2_b  col3_b  col1_c  col2_c  col3_c
0       1       2       3      11      12      13      21      22      23
1       4       5       6      14      15      16      24      25      26

Short explanation: in the list comprehension we are generating a list of DFs with already renamed columns:

In [75]: [suffixes[i] for i,df in enumerate(dfs)]
Out[75]: ['_a', '_b', '_c']

In [76]: [df.add_suffix(suffixes[i]) for i,df in enumerate(dfs)]
Out[76]:
[   col1_a  col2_a  col3_a
 0       1       2       3
 1       4       5       6,    col1_b  col2_b  col3_b
 0      11      12      13
 1      14      15      16,    col1_c  col2_c  col3_c
 0      21      22      23
 1      24      25      26]

Upvotes: 3

Related Questions