CODEWITHSUNDEEP
pythonpandas
Vixare
Vixare

Reputation: 93

Merge two DataFrames on ranges intersections

I am trying to merge two DataFrames based on the intersection of min-max values. Does anyone have a nice way to do it with Pandas?

##  min  max  x1       ##  min  max   x2
##0  1    20   0.5     ##0  1    12   1.2
##1  20   30   1.5     ##1  12   30   2.2

Desired output:

##   min  max  x1   x2
##0  1    12   0.5  1.2
##1  12   20   0.5  2.2
##2  20   30   1.5  2.2

Thx!

Upvotes: 1

Views: 99

Answers (1)

elPastor
elPastor

Reputation: 8996

This gives you what you're looking for based on your data set above, but I have the feeling it may not work in more complex situations.

Code:

# Simple data frame append - since it looks like you want it ordered, you can order it here, and then reset index.
df = df1.append(df2).sort_values(by = 'max')[['min','max','x1','x2']].reset_index(drop = True)

# Here, set 'min' for all but the first row to the 'max' of the previous row
df.loc[1:, 'min'] = df['max'].shift()

# Fill NaNs 
df.fillna(method = 'bfill', inplace = True)

# Filter out rows where min == max
df = df.loc[df['min'] != df['max']]

Output:

    min  max   x1   x2
0   1.0   12  0.5  1.2
1  12.0   20  0.5  2.2
2  20.0   30  1.5  2.2

Upvotes: 1

Related Questions