Pandas根据条件进行合并和更新，而不重命名列

Question

Pandas 1.0.5

我有一个交易文件，我想增强纬度和经度。

如果事务文件有一个邮政编码，那么我想使用该邮政编码来查找其纬度和经度，并将其添加到文件中。

如果事务文件有一个城市/州，没有邮政编码，那么我想使用该城市/州来查找其纬度和经度，并更新文件中的。只有在没有邮政编码的情况下。

代码的问题在于它添加了一个"_x〃；到列名。第二个问题是城市查找覆盖了邮政编码查找。

import pandas as pd
import numpy as np
#The transaction file
data = [
['MCDONALDS RESTAURANT STORE 100', '94521', '', ''],
['MCDONALDS RESTAURANT STORE 200', '94521', 'CLAYTON', 'CA'],  #zipcode is present so do not lookup with city
['BURGER KING RESTAURANT STORE 100', '', 'CONCORD', 'CA'],
['BURGER KING RESTAURANT STORE 200', '', 'CONCORD', 'CA'],
['TACO BELL RESTAURANT STORE 100', '', '', ''],
]
t = pd.DataFrame(data, columns = ['merchant', 'zipcode', 'city', 'state'])
#Step 1. Use zipcodes to lookup latitudes
data = [
['94521', '37.9780', '-121.0311'],
['94522', '40.1234', '-200.1234'],
]
z = pd.DataFrame(data, columns = ['zipcode', 'latitude', 'longitude'])
t = pd.merge(t, z[['zipcode', 'latitude', 'longitude']], how='left', on='zipcode') #works perfectly
#Step 2. Use city/states to lookup latitudes, if there was no zipcode
data = [
['CA', 'CONCORD', '37.9780', '-121.0311'],
['CA', 'CLAYTON', '40.1234', '-200.1234'],
]
c = pd.DataFrame(data, columns = ['state', 'city', 'latitude', 'longitude'])
t = pd.merge(t, c[['state', 'city', 'latitude', 'longitude']], how='left', on=['state', 'city']) #this line is the problem

Answer 1

不是很优雅，但您可以只对剩下的(lon/lat是NA(行进行第二次合并，然后连接两个部分：

m = t.latitude.notna()
t = pd.concat([t.loc[m],
pd.merge(t.loc[~m, ['merchant', 'zipcode', 'city', 'state']], c[['state', 'city', 'latitude', 'longitude']], how='left', on=['state', 'city'])])

结果：

merchant zipcode     city state latitude  longitude
0    MCDONALDS RESTAURANT STORE 100   94521                  37.978  -121.0311
1    MCDONALDS RESTAURANT STORE 200   94521  CLAYTON    CA   37.978  -121.0311
0  BURGER KING RESTAURANT STORE 100          CONCORD    CA   37.978  -121.0311
1  BURGER KING RESTAURANT STORE 200          CONCORD    CA   37.978  -121.0311
2    TACO BELL RESTAURANT STORE 100                             NaN        NaN