CODEWITHSUNDEEP
pythonpandas
Yorian
Yorian

Reputation: 2062

compare dataframes by values

I have two files with geometrical measurements: each file contains a large number of points (defined by x, y and z)

I would like to compare the depth (z-value) where the horizontal coordinates are the same (df1['x'] == df2['x']) & (df1['y'] == df2['y']). The order of the horizontal coordinates can however be different in both files. Example data:

x1   y1   z1
1    1    1.5
1    2    0.5
2    1    0.2
2    2    2.2
3    1    0.6
3    2    0.7

x2   y2   z2
2    1    0.5
2    2    0.2
2    3    0.5
1    1    0.1
1    2    2.3
1    3    1.7

I would like a resulting dataframe like such (z1 - z2 where x and y match):

x    y    z
1    1    1.4
1    2    -1.8
2    1    -0.3
2    2    2.0

How would I accomplish this?

Upvotes: 1

Views: 56

Answers (1)

EdChum
EdChum

Reputation: 394041

You can merge and then subtract:

In [67]:
merged = df.merge(df1, left_on=['x1','y1'], right_on=['x2','y2'])
merged['z'] = merged['z1'] - merged['z2']
merged.drop(['x2','y2','z1','z2'],axis=1,inplace=True)
merged = merged.rename(columns={'x1':'x','y1':'y'})
merged

Out[67]:
   x  y    z
0  1  1  1.4
1  1  2 -1.8
2  2  1 -0.3
3  2  2  2.0

if you merge it will perform an inner type merge so only where the values match and drop all extraneous rows, you can perform the subtraction and then drop/rename the columns to get the desired result

Upvotes: 1

Related Questions