加快pandas数据帧查找

Question

我有一个熊猫数据框，有邮政编码、城市、州和国家大约60万个地点。我们称之为我的

我想查找这些地点对应的经度和纬度。谢天谢地，这里有一个this的数据库。我们将此数据帧称为zipdb。在

zipdb除其他外，还有邮政编码、城市、州和国家的列。 所以，我想查找zipdb中的所有位置（邮政编码、城市、州和国家）。在

def zipdb_lookup(zipcode, city, state, country):

   countries_mapping = { "UNITED STATES":"US"
                     , "CANADA":"CA"
                     , "KOREA REP OF":"KR"
                     , "ITALY":"IT"
                     , "AUSTRALIA":"AU"
                     , "CHILE":"CL"
                     , "UNITED KINGDOM":"GB"
                     , "BERMUDA":"BM"
    }

    try:
        slc = zipdb[ (zipdb.Zipcode == str(zipcode)) &
                     (zipdb.City == str(city).upper()) &
                     (zipdb.State == str(state).upper()) &
                     (zipdb.Country == countries_mapping[country].upper()) ]

        if slc.shape[0] == 1:
            return np.array(slc["Lat"])[0], np.array(slc["Long"])[0]
        else:
            return None
    except:
         return None

我尝试过pandas的.apply以及for循环来实现这一点。 两者都很慢。我知道有很多行，但我还是忍不住想，一定有更快的办法。在

---

^{pr2}$

注意：我还对zibdb执行了此转换：

zipdb["Zipcode"] = zipdb["Zipcode"].astype(str)

---

函数调用：

#Defined a wrapper function:
def lookup(row):
    """

    :param row:
    :return:
    """

    lnglat = zipdb_lookup(
                  zipcode = my_df["organization_zip"][row]
                , city    = my_df["organization_city"][row]
                , state   = my_df["organization_state"][row]
                , country = my_df["organization_country"][row]
    )

    return lnglat

lnglat = list()
for l in range(0, my_df.shape[0]):
    # if l % 5000 == 0: print(round((float(l) / my_df.shape[0])*100, 2), "%")
    lnglat.append(lookup(row = l))

---

来自my_df的示例数据：

       organization_zip organization_city organization_state  organization_country
0                 60208          EVANSTON                 IL   United Sates
1                 77555         GALVESTON                 TX   United Sates
2                 23284          RICHMOND                 VA   United Sates
3                 53233         MILWAUKEE                 WI   United Sates
4                 10036          NEW YORK                 NY   United Sates
5                 33620             TAMPA                 FL   United Sates
6                 10029          NEW YORK                 NY   United Sates
7                 97201          PORTLAND                 OR   United Sates
8                 97201          PORTLAND                 OR   United Sates
9                 53715           MADISON                 WI   United Sates

Answer 1

使用^{}将比在每一行上调用一个函数快得多。确保字段类型匹配并且字符串被剥离：

# prepare your dataframe
data['organization_zip'] = data.organization_zip.astype(str)
data['organization_city'] = data.organization_city.apply(lambda v: v.strip())
# get the zips database
zips = pd.read_csv('/path/to/free-zipcode-database.csv')
zips['Zipcode'] = zips.Zipcode.astype(str)
# left join
#   prepare common join columns
zips.rename(columns=dict(Zipcode='organization_zip',
                         City='organization_city'), 
            inplace=True)  
# specify join columns along with zips' columns to copy
cols = ['organization_zip', 'organization_city', 'Lat', 'Long']
data.merge(zips[cols], how='left')
=> 

注意您可能需要扩展合并列和/或添加更多列以从zips dataframe复制。在