python - 如何最好地使用来自另一个用于检索多个值的 df 的索引值创建新的 df？

Question

nn_idx_df 包含与 xyz_df 的索引匹配的索引值。如何从 xyz_df 中的 H 列获取值并在 nn_idx_df 中创建新列以匹配 output_df 中所示的结果。我可以解决这个问题，但希望看到一个可爱的解决方案。

nn_idx_df = pd.DataFrame({'nn_1_idx': {0: 65, 1: 7, 2: 18},
 'nn_2_idx': {0: 64, 1: 9, 2: 64},
 'nn_3_idx': {0: 69, 1: 67, 2: 68},
 'nn_4_idx': {0: 75, 1: 13, 2: 65},
 'nn_5_idx': {0: 70, 1: 66, 2: 1}})

print(nn_idx_df)

    nn_1_idx    nn_2_idx    nn_3_idx    nn_4_idx    nn_5_idx
0   65  64  69  75  70
1   7   9   67  13  66
2   18  64  68  65  1

xyz_df = pd.DataFrame({'X': {1: 6401652.35,
  7: 6401845.46,
  9: 6401671.93,
  13: 6401868.98,
  18: 6401889.78,
  64: 6401725.71,
  65: 6401663.04,
  66: 6401655.89,
  67: 6401726.33,
  68: 6401755.92,
  69: 6401755.23,
  70: 6401766.23,
  75: 6401825.9},
 'Y': {1: 1858548.15,
  7: 1858375.68,
  9: 1858490.83,
  13: 1858403.79,
  18: 1858423.25,
  64: 1858579.25,
  65: 1858570.3,
  66: 1858569.97,
  67: 1858607.8,
  68: 1858581.58,
  69: 1858591.46,
  70: 1858517.48,
  75: 1858420.72},
 'Z': {1: 467.62,
  7: 482.22,
  9: 459.15,
  13: 485.17,
  18: 488.35,
  64: 488.88,
  65: 465.75,
  66: 467.35,
  67: 486.12,
  68: 490.12,
  69: 490.68,
  70: 483.96,
  75: 467.39},
 'H': {1: 47.8791,
  7: 45.5502,
  9: 46.0995,
  13: 41.9554,
  18: 41.0537,
  64: 47.1215,
  65: 46.0047,
  66: 45.936,
  67: 40.5807,
  68: 37.8478,
  69: 37.1639,
  70: 37.2314,
  75: 25.8446}})


print(xyz_df)

X   Y   Z   H
1   6401652.35  1858548.15  467.62  47.8791
7   6401845.46  1858375.68  482.22  45.5502
9   6401671.93  1858490.83  459.15  46.0995
13  6401868.98  1858403.79  485.17  41.9554
18  6401889.78  1858423.25  488.35  41.0537
64  6401725.71  1858579.25  488.88  47.1215
65  6401663.04  1858570.30  465.75  46.0047
66  6401655.89  1858569.97  467.35  45.9360
67  6401726.33  1858607.80  486.12  40.5807
68  6401755.92  1858581.58  490.12  37.8478
69  6401755.23  1858591.46  490.68  37.1639
70  6401766.23  1858517.48  483.96  37.2314
75  6401825.90  1858420.72  467.39  25.8446

output_df = pd.DataFrame( 
  {'nn_1_idx': {0: 65, 1: 7, 2: 18},
 'nn_2_idx': {0: 64, 1: 9, 2: 64},
 'nn_3_idx': {0: 69, 1: 67, 2: 68},
 'nn_4_idx': {0: 75, 1: 13, 2: 65},
 'nn_5_idx': {0: 70, 1: 66, 2: 1},                                    
 'nn_1_idx_h': {0: 46.0047, 1: 45.5502, 2: 41.0537},
 'nn_2_idx_h': {0: 47.1215, 1: 46.0995, 2: 47.1215},
 'nn_3_idx_h': {0: 37.1639, 1:40.5807, 2: 37.8478},
 'nn_4_idx_h': {0: 25.8446, 1: 41.9554, 2: 46.0047},
 'nn_5_idx_h': {0: 37.2314, 1: 45.9360, 2: 47.8791}})

print(output_df)

nn_1_idx    nn_2_idx    nn_3_idx    nn_4_idx    nn_5_idx    nn_1_idx_h  nn_2_idx_h  nn_3_idx_h  nn_4_idx_h  nn_5_idx_h
0   65  64  69  75  70  46.0047 47.1215 37.1639 25.8446 37.2314
1   7   9   67  13  66  45.5502 46.0995 40.5807 41.9554 45.9360
2   18  64  68  65  1   41.0537 47.1215 37.8478 46.0047 47.8791

Answer 1

让我们做 replace与 join

df=nn_idx_df.join(nn_idx_df.replace(xyz_df.H).add_suffix('_h'))
df
   nn_1_idx  nn_2_idx  nn_3_idx  ...  nn_3_idx_h  nn_4_idx_h  nn_5_idx_h
0        65        64        69  ...     37.1639     25.8446     37.2314
1         7         9        67  ...     40.5807     41.9554     45.9360
2        18        64        68  ...     37.8478     46.0047     47.8791
[3 rows x 10 columns]