How to merge two dataframes for updating older one with new one?

user_18947420 0
Viewed 26

I am sorry for being a noob but I can't find a solution for my problem with hours of search.

import pandas as pd

df1 = pd.read_excel('df1.xlsx')
df1.set_index('time')
print(df1)

df2 = pd.read_excel('df2.xlsx')
df2.set_index('time')
print(df2)

new_df = pd.merge(df1, df2,how='outer')
print(new_df)

df1
   time  bought
0     1       0
1     2       0
2     3       0
3     4       0
4     5       1

df2
   time  bought
0     3       0
1     4       0
2     5       0
3     6       0
4     7       0

new_df
   time  bought
0     1       0
1     2       0
2     3       0
3     4       0
4     5       1
5     5       0
6     6       0
7     7       0

What I want is

  1. updating df1(existing data) with df2(new data feed). when it comes to bought value, df1 data should comes first
  2. the new_df should have all unique time values from df1, df2 without duplicates

I tried every method I found but no one made my desired outcome or created unnecessary duplicates as above.(two rows with time value of 5) merge method created _x _y suffixes or duplicates

join() didn't work as well.

What I desire should look like:

new_df
   time  bought
0     1       0
1     2       0
2     3       0
3     4       0
4     5       1
5     6       0
6     7       0

Thank you in advance

pandas dataframe merge python
1 Answers

if you perform the join as you have done all you need to do is remove the duplicate rows keeping only the more resent data, drop_duplicates() take the kwarg subset which takes a list of columns and keep which sets which row to keep if there are duplicates in this case we only need to check for duplicates in the time column and wee keep the first column

import pandas as pd

df1 = pd.read_excel('df1.xlsx')
df1.set_index('time')
print(df1)

df2 = pd.read_excel('df2.xlsx')
df2.set_index('time')
print(df2)

new_df = pd.merge(df1, df2,how='outer')
new_df = new_df.drop_duplicates(subset=['time'], keep='first')
print(new_df)

Output:

   time  bought
0     1       0
1     2       0
2     3       0
3     4       0
4     5       1
5     6       0
6     7       0
user_155832490
Related