Mapping Rows of Two Pandas DataFrames

Question

I'm working with two DataFrames,

df1

altitude
132 16.324794
133 16.027025
134 15.738367
135 15.462613
136 15.195307
137 14.934009
138 14.682448
139 14.440509
140 14.207593
141 14.070644

df2

altitude    density east_wind   north_wind
0   5   0.020567    39.714397   6.795392
1   7   0.016871    41.171996   6.852655
2   9   0.013839    42.629594   6.909918
3   11  0.011351    44.087193   6.967182
4   13  0.009311    45.544791   7.024445
5   15  0.007638    47.003028   7.079618
6   17  0.006263    48.303168   7.340789
7   19  0.005129    48.942837   8.478684
8   21  0.004201    49.588021   9.587021
9   23  0.003433    50.797853   11.256209

I'd like to map the altitude in df1 to the closest altitude in df2 and eventually merging the density east_wind and north_wind values of that row to a new Dataframe.

Expected Result

altitude    density east_wind   north_wind
132 16.324794   0.006263   48.303168   7.340789
136 15.195307   0.007638   47.003028   7.079618
137 14.934009   0.007638   47.003028   7.079618

Please Advise

How 132 (value of altitude in df1) became closest to 17 (value of altitude in df2) as per your expected output? — ThePyGuy
– ThePyGuy, Commented Mar 22, 2021 at 7:01
132 is the index value, 16.324794 is the altitude and it is mapped to 17 — The Singularity
– The Singularity, Commented Mar 22, 2021 at 7:05

tdy · Accepted Answer · 2021-03-22 08:00:54Z

1

You could get the closest df2.altitude index using idxmin:

df1['df2_idx'] = df1.altitude.apply(lambda x: df2.altitude.sub(x).abs().idxmin())

#      altitude   df2_idx
# 132  16.324794  6
# 133  16.027025  6
# 134  15.738367  5
# 135  15.462613  5
# 136  15.195307  5
# 137  14.934009  5
# 138  14.682448  5
# 139  14.440509  5
# 140  14.207593  5
# 141  14.070644  5

And then merge on df1.df2_idx and df2.index:

df1.merge(df2.drop('altitude', axis=1), left_on='df2_idx', right_index=True).drop('df2_idx', axis=1)

#      altitude   density   east_wind  north_wind
# 132  16.324794  0.006263  48.303168  7.340789
# 133  16.027025  0.006263  48.303168  7.340789
# 134  15.738367  0.007638  47.003028  7.079618
# 135  15.462613  0.007638  47.003028  7.079618
# 136  15.195307  0.007638  47.003028  7.079618
# 137  14.934009  0.007638  47.003028  7.079618
# 138  14.682448  0.007638  47.003028  7.079618
# 139  14.440509  0.007638  47.003028  7.079618
# 140  14.207593  0.007638  47.003028  7.079618
# 141  14.070644  0.007638  47.003028  7.079618

edited Mar 22, 2021 at 8:00

answered Mar 22, 2021 at 7:52

tdy

42k42 gold badges124 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Singularity Over a year ago

Just the answer I had in mind! Please tweak the second part of your answer there seem to be an extra set of parentheses

tdy Over a year ago

Sure, the outer parentheses were only there to allow line wrapping. I went ahead and removed them.

Collectives™ on Stack Overflow

Mapping Rows of Two Pandas DataFrames

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related