CODEWITHSUNDEEP
python-3.xpandas
ManInMoon
ManInMoon

Reputation: 7005

How to gather and rename the same columns from multiple Pandas dataframes

I have many dataframes with the same structure - number of rows and names of columns. How can I gather all the columns with same name, but with name replaced, into a single new dataframe?

df1 = pd.DataFrame({'Name':['Tom', 'nick', 'krish', 'jack'], 'Age':[20, 21, 19, 18]})
df2 = pd.DataFrame({'Name':['Wendy', 'Frank', 'krish', 'Lucy'], 'Age':[20, 21, 19, 18]})

print(df1)
print(df2)

I want:

df3 = pd.DataFrame({'Name1':['Wendy', 'Frank', 'krish', 'Lucy'], 'Name2':['Tom', 'nick', 'krish', 'jack']})
print(df3)

Output:

df1:
    Name  Age
0    Tom   20
1   nick   21
2  krish   19
3   jack   18

df2:
    Name  Age
0  Wendy   20
1  Frank   21
2  krish   19
3   Lucy   18

df3:
   Name1  Name2
0  Wendy    Tom
1  Frank   nick
2  krish  krish
3   Lucy   jack

Upvotes: 1

Views: 63

Answers (2)

ALollz
ALollz

Reputation: 59579

You can concat the two DataFrames together along axis=1 in a list comprehension. Use .add_suffix with enumerate to get the numbers appended to the column names.

pd.concat([df[['Name']].add_suffix(i+1) for i,df in enumerate([df2, df1])], axis=1)

   Name1  Name2
0  Wendy    Tom
1  Frank   nick
2  krish  krish
3   Lucy   jack

Or if you want to do this for many similar columns at once concat with keys to create a MultiIndex on the columns and then collapse the MultiIndex and join the column names in a list comprehension.

l = [df2, df1]
df3 = pd.concat(l, axis=1, keys=np.arange(len(l))+1)
df3.columns = [f'{y}{x}' for x,y in df3.columns]
#   Name1  Age1  Name2  Age2
#0  Wendy    20    Tom    20
#1  Frank    21   nick    21
#2  krish    19  krish    19
#3   Lucy    18   jack    18

df3.filter(like='Name')

   Name1  Name2
0  Wendy    Tom
1  Frank   nick
2  krish  krish
3   Lucy   jack

Upvotes: 1

Paul Brennan
Paul Brennan

Reputation: 2706

df1 = df1.drop(column='Age')
df2 = df2.drop(column='Age')

df3 = df1.join(df2)

Upvotes: 1

Related Questions