Pandas merge two df's where df1.column is = or between df2.column1 and df2.column2

user_10426490 0
Viewed 575

I can't seem to find this example anywhere. Having trouble determining if I need to use indexes, boolean mask, or if a direct merge is possible. I've tried variations of .isin and .between each to no success.

Scenario:

  • Two dataFrames with no common index:

    df1 = pd.DataFrame({'printId': ['x','y', 'z', 'a'],'locCode': [0.9, 1.5, 4.0, 7.8]})
df2 = pd.DataFrame({'assetId': ['1','1a', '2', '2a', '3', '4'], 'locStart': [0.9, 0.9, 1, 1, 4, 8], 'locEnd': [0.9, 0.9, 3, 3, 5, 13]})

df1:

enter image description here

df2:

enter image description here

Need this:

df3 = pd.DataFrame({'printId': ['x','x', 'y', 'y', 'z', 'a', 'NaN'], 'locCode': ['0.9', '0.9', '1.5', '1.5', '4.0', '7.8', 'NaN'], 'assetID': ['1', '1a', '2', '2a', '3', 'NaN', '4'], 'locStart': ['0.9', '0.9', '1.0', '1.0', '4.0', 'NaN', '8.0'], 'locEnd':['0.9', '0.9', '3.0', '3.0', '4.0', 'NaN', '13.0']})

df3

enter image description here

How do professionals attack this problem?

EDITED: Original answer did not work after closer examination.

  • Where df2 has duplicated locStart/End records, but unique assetID (row 0, 1 and row 2 and 3), df1 will not merge.
pandas between dataframe merge
3 Answers

Try this.

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'printId': ['01','1A', '2B', '3C'],'locCode': [0.9, 1.5, 4.0, 7.8], 'a1': ['foo', 'foo', 'foo', 'foo']})
df2 = pd.DataFrame({'assetId': ['oo', 'aa', 'zz', 'xx'], 'locCodeStart': [0, 1, 4, 8], 'locCodeEnd': [0.4, 3, 5, 13]})

df1['assetId'] = np.nan
for ind, row in df1.iterrows():
    loc_code = row['locCode']
    temp = (df2['locCodeStart'] <= loc_code) & (loc_code < df2['locCodeEnd'])
    df2_index = temp[temp == True].index
    if len(df2_index) == 1:
        df1['assetId'].loc[ind] = df2['assetId'].loc[df2_index[0]]

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

This works also if there are duplicates of locStart, locEnd in df2 (with unique assetId's)

It also handles the case if in df1 there duplicate in printId or locCode 

df1 = pd.DataFrame({'printId': ['x','y', 'z', 'a'],'locCode': [0.9, 1.5, 4.0, 7.8]})
df2 = pd.DataFrame({'assetId': ['1','1a', '2', '2a', '3', '4'], 'locStart': [0.9, 0.9, 1, 1, 4, 8], 'locEnd': [0.9, 0.9, 3, 3, 5, 13]})


merge_id = []

for i,code in df1.locCode.iteritems():
    filled = True
    partial = []
    for j,row in df2.iterrows():
        if code>=row.locStart and code<=row.locEnd:
            filled = False
            partial.append(j)
    if filled:
        partial.append(-1)
    merge_id.append(partial)

df1['merge_id'] = merge_id

df = df1.explode('merge_id').merge(df2, right_index=True, left_on='merge_id', how='outer')
df = df.reset_index(drop=True).drop('merge_id', axis=1)


print(df)
  printId  locCode assetId  locStart  locEnd
0       x      0.9       1       0.9     0.9
1       x      0.9      1a       0.9     0.9
2       z      4.0       3       4.0     5.0
3       y      1.5       2       1.0     3.0
4       y      1.5      2a       1.0     3.0
5       a      7.8     NaN       NaN     NaN
6     NaN      NaN       4       8.0    13.0
user_103750490

you can do it handle two cases differently. first case is simple merge where df1.locCode exist in df2.locStart, the second case by creating bins with all the values locStart and locEnd in both df1 and df2, then merge them after removing the cases already handled in the first merge:

## handle the case where locCode of df1 is equal to locStart of df2

# get the rows in this case
mask_merge = df1['locCode'].isin(df2['locStart'].unique())

# handle them with a direct
m1 = df1[mask_merge].merge(df2, right_on='locStart', left_on='locCode', how='inner')

## handle the other cases

# get unique values from df2 both columns loc
l_unique = pd.concat([df2['locStart'], df2['locEnd']]).sort_values().unique()

# add cat columns with pd.cut in both dataframe with all unique values
df2['cat'] = pd.cut(df2['locEnd'], bins=[-np.inf]+l_unique.tolist() + [+np.inf],
                    labels=range(len(l_unique)+1))
df1['cat'] = pd.cut(df1['locCode'], bins=[-np.inf]+l_unique.tolist() + [+np.inf],
                    labels=range(len(l_unique)+1))

# mask to get which asser does not have same start and end
mask_startEnd = df2['locStart'].ne(df2['locEnd'])
# mask assert already merged above
mask_df2merged = ~df2['locStart'].isin(df1['locCode'])

# merge the rows needed
m2 = df1[~mask_merge].merge(df2[mask_startEnd&mask_df2merged], on='cat', how='outer')

#concat both and drop column cat
res = pd.concat([m1, m2], axis=0, ignore_index=True).drop('cat', axis=1)

print (res)
  printId  locCode assetId  locStart  locEnd
0       x      0.9       1       0.9     0.9
1       x      0.9      1a       0.9     0.9
2       z      4.0       3       4.0     5.0
3       y      1.5       2       1.0     3.0
4       y      1.5      2a       1.0     3.0
5       a      7.8     NaN       NaN     NaN
6     NaN      NaN       4       8.0    13.0
user_92747320
Related