Merging several dataframes into one without creating duplicate columns

Question

I have three dataframes:

array = {'name': ['Trevor', 'John', 'Trevor', 'John', 'Trevor', 'Trevor', 'John'], 'day': ['2020-10-11 08:00:00', '2020-10-11 08:00:00', '2020-10-11 08:00:00', '2020-10-11 08:00:00', '2020-10-11 08:00:00'
        , '2020-10-11 12:34:00', '2020-10-11 09:12:00'], 'number': [5,0,3,5,1,8,6]}
df = pd.DataFrame(array)
array1 = {'name': ['Trevor'], 'color': ['red']}
df1 = pd.DataFrame(array1)
array2 = {'name': ['John'], 'color': ['blue']}
df2 = pd.DataFrame(array2)
df

I want to merge df1 & df2 into df in the real world I have hundreds of df1, df2, df3...), so the output would be:

    name    day              number color
0   Trevor  2020-10-11 08:00:00 5   red
1   John    2020-10-11 08:00:00 0   blue
2   Trevor  2020-10-11 08:00:00 3   red
3   John    2020-10-11 08:00:00 5   blue
4   Trevor  2020-10-11 08:00:00 1   red
5   Trevor  2020-10-11 12:34:00 8   red
6   John    2020-10-11 09:12:00 6   blue

Have you looked through this post? stackoverflow.com/questions/19125091/… — ddejohn
– ddejohn, Commented Feb 17, 2021 at 21:29

Quang Hoang · Accepted Answer · 2021-02-17 21:39:16Z

1

Looks like merge and concat:

df.merge(pd.concat([df1,df2]), on='name', how='left')

Output:

     name                  day  number color
0  Trevor  2020-10-11 08:00:00       5   red
1    John  2020-10-11 08:00:00       0  blue
2  Trevor  2020-10-11 08:00:00       3   red
3    John  2020-10-11 08:00:00       5  blue
4  Trevor  2020-10-11 08:00:00       1   red
5  Trevor  2020-10-11 12:34:00       8   red
6    John  2020-10-11 09:12:00       6  blue

Update: Try loc update every time you get a new df#:

df.loc[df['name'].isin(df1['name']), 'color'] = df1['color'].iloc[0]

df.loc[df['name'].isin(df2['name']), 'color'] = df2['color'].iloc[0]

edited Feb 17, 2021 at 21:39

answered Feb 17, 2021 at 21:33

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

gtomer Over a year ago

Thanks, BUT I have hundreds of df# - not just two

Quang Hoang Over a year ago

How do you store that hundreds of df#? you can easily pass the list pd.concat(list_of_dfs_)?

gtomer Over a year ago

Problem is I get them one at a time and need to "merge" them one at a time to df

Quang Hoang Over a year ago

@gtomer you should mention that in your question. See updated answer if it helps.

ddejohn Over a year ago

current_df = current_df.merge(pd.concat([current_df, new_df]), ....

|

wwnde · Accepted Answer · 2021-02-17 21:34:25Z

1

pd.merge(df,df1.append(df2), how='left', on='name')

answered Feb 17, 2021 at 21:34

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

2 Comments

gtomer Over a year ago

Thanks, BUT I have hundreds of df# - not just two

wwnde Over a year ago

Parse them as a list. Concat become more useful then

Collectives™ on Stack Overflow

Merging several dataframes into one without creating duplicate columns

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related