假设我有下表
import pandas as pd, datetime
table = [[datetime.datetime(2015, 1, 1), 1],
[datetime.datetime(2015, 1, 27), 1],
[datetime.datetime(2015, 1, 31), 1],
[datetime.datetime(2015, 2, 1), 1],
[datetime.datetime(2015, 2, 3), 1],
[datetime.datetime(2015, 2, 15), 1],
[datetime.datetime(2015, 2, 28), 1],
[datetime.datetime(2015, 3, 1), 1],
[datetime.datetime(2015, 3, 17), 1],
[datetime.datetime(2015, 3, 28), 1],
[datetime.datetime(2015, 4, 12), 1],
[datetime.datetime(2015, 4, 28), 1]]
df1 = pd.DataFrame(table, columns=['Date', 'Id'])
df2 = df1.copy()
df2['Id'] = 2
df = df1.append(df2)
table2 = [[1, datetime.datetime(1900, 1, 1), datetime.datetime(2015, 2, 28), 2, 20],
[1, datetime.datetime(2015, 3, 1), datetime.datetime(3000, 1, 1), 4, 25],
[2, datetime.datetime(1900, 1, 1), datetime.datetime(3000, 1, 1), 2, 20]]
df3 = pd.DataFrame(table2, columns=['Id', 'Start', 'End', 'Fix', 'Performance'])
编辑 在 df3 中,表格根据 Id 分组。 IE。前两行对 Id = 1 有效,最后两行对 Id = 2 有效。
我现在的问题是;有没有一种方法可以将 Fix 和 Performance 作为列附加到 df 以便各个列的元素位于 所在的行上Start 和 End 是否有效,由 Date 确定?这意味着我的 table 看起来像
Date Id Fix Performance
0 2015-01-01 1 2 20
1 2015-01-27 1 2 20
2 2015-01-31 1 2 20
3 2015-02-01 1 2 20
4 2015-02-03 1 2 20
5 2015-02-15 1 2 20
6 2015-02-28 1 2 20
7 2015-03-01 1 4 25
8 2015-03-17 1 4 25
9 2015-03-28 1 4 25
10 2015-04-12 1 4 25
11 2015-04-28 1 4 25
0 2015-01-01 2 2 20
1 2015-01-27 2 2 20
2 2015-01-31 2 2 20
3 2015-02-01 2 2 20
4 2015-02-03 2 2 20
5 2015-02-15 2 2 20
6 2015-02-28 2 2 20
7 2015-03-01 2 2 20
8 2015-03-17 2 2 20
9 2015-03-28 2 2 20
10 2015-04-12 2 2 20
11 2015-04-28 2 2 20
谢谢,丁吉斯
请您参考如下方法:
这是一种方法,您可以逐行应用一个函数来生成两个想要的列:
import pandas as pd
import numpy as np
def search(x):
df_ = df3[df3.Id==x['Id']]
mask = np.logical_and(df_.Start<=x['Date'], df_.End>=x['Date'])
return pd.Series([df_.loc[mask].Fix.tolist()[0], df_.loc[mask].Performance.tolist()[0]])
df[['Fix','Performance']] = df.apply(search, axis=1)
In [423]: df
Out[423]:
Date Id Fix Performance
0 2015-01-01 1 2 20
1 2015-01-27 1 2 20
2 2015-01-31 1 2 20
3 2015-02-01 1 2 20
4 2015-02-03 1 2 20
5 2015-02-15 1 2 20
6 2015-02-28 1 2 20
7 2015-03-01 1 4 25
8 2015-03-17 1 4 25
9 2015-03-28 1 4 25
10 2015-04-12 1 4 25
11 2015-04-28 1 4 25
0 2015-01-01 2 2 20
1 2015-01-27 2 2 20
2 2015-01-31 2 2 20
3 2015-02-01 2 2 20
4 2015-02-03 2 2 20
5 2015-02-15 2 2 20
6 2015-02-28 2 2 20
7 2015-03-01 2 2 20
8 2015-03-17 2 2 20
9 2015-03-28 2 2 20
10 2015-04-12 2 2 20
11 2015-04-28 2 2 20
