I am trying to implement incremental data import using pandas.
I have two dataframes: df_old (original data, loaded before) and df_new (new data, to be merged with df_old).
data in df_old/df_new are unique on multiple columns (for simplicity, lets say just 2: key1 and key2). other columns are data to be merged and lets say, they are only 2 of them too: val1 and val2.
beside these, there is one more column to be taken care of: change_id - it increases for each new entry overwriting the old one
the logic of import is pretty straightforward:
- if there is new key pair in df_new, it should be added (with corresponding val1/val2 values) to df_old
if there is a key pair in df_new which exists in df_old, then:
2a) if corresponding values in df_old and df_new are same, the old ones should be kept
2b) if corresponding values in df_old and df_new are different, the values in df_new should replace the old ones in df_old
there's no need to care about dala deletion (if some data in df_old exist, which are not present in df_new)
so far, I came up with 2 different solutions:
>>> df_old = pd.DataFrame([['A1','B2',1,2,1],['A1','A2',1,3,1],['B1','A2',1,3,1],['B1','B2',1,4,1],], columns=['key1','key2','val1','val2','change_id'])
>>> df_old.set_index(['key1','key2'], inplace=True)
>>> df_old
val1 val2 change_id
key1 key2
A1 B2 1 2 1
A2 1 3 1
B1 A2 1 3 1
B2 1 4 1
>>> df_new = pd.DataFrame([['A1','B2',2,1,2],['A1','A2',1,3,2],['C1','B2',2,1,2]], columns=['key1','key2','val1','val2','change_id'])
>>> df_new.set_index(['key1','key2'], inplace=True)
>>> df_new
val1 val2 change_id
key1 key2
A1 B2 2 1 2
A2 1 3 2
C1 B2 2 1 2
solution 1
# this solution groups concatenated old data with new ones, group them by keys and for each group evaluates if new data are different
def merge_new(x):
if x.shape[0] == 1:
return x.iloc[0]
else:
if x.iloc[0].loc[['val1','val2']].equals(x.iloc[1].loc[['val1','val2']]):
return x.iloc[0]
else:
return x.iloc[1]
def solution1(df_old, df_new):
merged = pd.concat([df_old, df_new])
return merged.groupby(level=['key1','key2']).apply(merge_new).reset_index()
solution 2
# this solution uses pd.merge to merge data + additional logic to compare merged rows and select new data
>>> def solution2(df_old, df_new):
>>> merged = pd.merge(df_old, df_new, left_index=True, right_index=True, how='outer', suffixes=('_old','_new'), indicator='ind')
>>> merged['isold'] = (merged.loc[merged['ind'] == 'both',['val1_old','val2_old']].rename(columns=lambda x: x[:-4]) == merged.loc[merged['ind'] == 'both',['val1_new','val2_new']].rename(columns=lambda x: x[:-4])).all(axis=1)
>>> merged.loc[merged['ind'] == 'right_only','isold'] = False
>>> merged['isold'] = merged['isold'].fillna(True)
>>> return pd.concat([merged[merged['isold'] == True][['val1_old','val2_old','change_id_old']].rename(columns=lambda x: x[:-4]), merged[merged['isold'] == False][['val1_new','val2_new','change_id_new']].rename(columns=lambda x: x[:-4])])
>>> solution1(df_old, df_new)
key1 key2 val1 val2 change_id
0 A1 A2 1 3 1
1 A1 B2 2 1 2
2 B1 A2 1 3 1
3 B1 B2 1 4 1
4 C1 B2 2 1 2
>>> solution2(df_old, df_new)
val1 val2 change_id
key1 key2
A1 A2 1.0 3.0 1.0
B1 A2 1.0 3.0 1.0
B2 1.0 4.0 1.0
A1 B2 2.0 1.0 2.0
C1 B2 2.0 1.0 2.0
Both of the work, however, I am still quite dissapointed with the performance on huge dataframes. the question is: is there some better way to do this? any hint for decent speed improvement will be more than welcome...
>>> %timeit solution1(df_old, df_new)
100 loops, best of 3: 10.6 ms per loop
>>> %timeit solution2(df_old, df_new)
100 loops, best of 3: 14.7 ms per loop