使用python和pandas使用2个现有列的函数填充新列

Question

基于其他两行值创建和填充新列时遇到一些问题。 我创建了一个函数，用于查找（在一个大型CSV文件（1GB）中）源和目标邮政编码，并返回指定行。你知道吗

我想生成距离和时间，并将其作为新列添加到orders文件中。你知道吗

我试过了订单.应用（）比我得到这个错误

  File "pandas\_libs\index.pyx", line 88, in pandas._libs.index.IndexEngine.get_value
  File "pandas\_libs\index.pyx", line 128, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index_class_helper.pxi", line 91, in pandas._libs.index.Int64Engine._check_type
KeyError: ('customer_address', 'occurred at index datetime') 

我遇到的另一个问题是，执行calculateInstance的执行时间是20秒。我想知道是否有任何性能改进，我可以做。你知道吗

import pandas as pd

orders = pd.read_csv('ordersModified.csv', delimiter=';', encoding="ISO-8859-1")
distance_chunks = pd.read_csv('PostcodeDistances.csv', chunksize=100000)

def calculateDistance(src, dest):
    result = pd.concat([chunk[(chunk['src'] == src) & (chunk['dest'] == dest)] for chunk in distance_chunks])
    return result


orders['distance_meters'] = orders.apply(lambda row: calculateDistance(row['customer_address'], row['restaurant_address']).meters)

distance = calculateDistance("9727KE", "9742PA")
print(distance.meters)
print(distance.seconds)

你知道吗订单修改.csv看起来像这样：

datetime;restaurant;customer_address;amount;restaurant_address
2018-01-01 09:01:48;Name;9728AC;59.93;9717BB
2018-01-01 09:02:13;Name;9712AN;110.73;9727KE
2018-01-01 09:02:52;Name;9732MC;22.30;9726BD
2018-01-01 09:03:21;Name;9743KX;63.98;9718CS
2018-01-01 09:03:59;Name;9721BJ;37.93;9726BD
2018-01-01 09:04:38;Name;9713JL;37.87;9728VJ
2018-01-01 09:05:03;Name;9728VD;70.07;9718CB
2018-01-01 09:05:45;Name;9721VW;75.32;9718CP

你知道吗邮政编码.csv如下所示（29.003.611行）：

src,dest,meters,seconds
9728AC,9717BB,22.5,5.5
9711AA,9711AC,55.1,13.2
9711AA,9711AD,93.6,22.5
9711AA,9711AE,135.5,32.6

Answer 1

我认为最简单和最快的方法是merge（读：join）在depreture邮政编码和arrival邮政编码上的两个数据帧。这样，您可以一次性获得meters+seconds数据帧中的所有order信息。你知道吗

提供的试验数据代码：

orders.merge(distance_chunks, 
             left_on=['customer_address', 'restaurant_address'],
             right_on=['src', 'dest']).drop(['src', 'dest'], axis=1)

输出

             datetime restaurant customer_address  amount restaurant_address  meters  seconds
0 2018-01-01 09:01:48       Name           9728AC   59.93             9717BB    22.5      5.5

---

对于你的chunks，它看起来是这样的（我自己无法测试）：

dfs = []
for chunk in distance_chunk:
    dfs.append(
    orders.merge(chunk, 
             left_on=['customer_address', 'restaurant_address'],
             right_on=['src', 'dest']).drop(['src', 'dest'], axis=1)
    )

final_df = pd.concat(dfs, ignore_index=True)

print(final_df.head())