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

- davidjhp

---

嗨，伙计。数据集是你可以更改的吗？还是你必须坚持当前的格式（指邮政编码和城市/州查找列表）？ - Danail Petrov

它们可以被改变。 - davidjhp

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

- Stef