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