python - 使用两个数据帧之间的 "VLOOKUP"创建新数据帧

Question

与 Excel 的 VLOOKUP 函数有些类似，我想使用一个数据帧(下面的portfolio)中的值来查找第二个数据帧(下面的returns)中的关联值)并用这些返回值填充第三个数据帧(现在我们称之为数据帧3)。我发现了几篇基于左合并和 map 的帖子，但我原来的两个数据帧具有不同的结构，因此这些方法似乎不适合(至少对我来说)。

我没有取得太大进展，但这是我的代码:

代码

import pandas as pd

portfolios = pd.read_csv('portstst5_1.csv')
returns = pd.read_csv('Example_Returns.csv')

total_cols = len(portfolios.columns)
headers = list(portfolios)

concat = returns['PERMNO'].map(str) + returns['FROMDATE'].map(str)
idx = 2
returns.insert(loc=idx, column="concat", value=concat)

for i in range(total_cols):
    col_len = portfolios.iloc[:,i].count()
    for j in range(col_len):
        print(portfolios.iat[j,i].astype('int').astype('str') + headers[i])

数据

如果我首先描述我的数据，这段代码会更有意义:portfolios 是一个包含 13 列不同长度的数据框。列标题是 YYYYMMDD 格式的日期。每个日期标题下方都有标识符，它们是五位数字代码。 portfolio 的片段如下所示(某些列中的某些元素包含 NaN):

    20131231  20131130  20131031  20130930  20130831  20130731  20130630  \
0    93044.0   93044.0   13264.0   13264.0   89169.0   82486.0   91274.0   
1    79702.0   91515.0   90710.0   81148.0   47387.0   88359.0   93353.0   
2    85751.0   85724.0   88810.0   11513.0   85576.0   47387.0   85576.0

returns data 中的数据最初由三列和 799 行组成，如下所示(所有元素都填充了值):

     PERMNO  FROMDATE     MORET
0     93044  20131231 -0.022304
1     79702  20131231  0.012283
2     85751  20131231 -0.016453
3     85576  20131231  0.038766

所需输出

我想制作第三个数据框，其结构与投资组合相同。也就是说，它将具有与 portfolio 相同的列标题日期和每列中相同的行数，但它不会包含标识符，而是包含用于适当的标识符/日期组合。这就是上面代码中串联的原因 - 我正在尝试(也许不必要)创建唯一的查找值，以便我可以在portfolios和returns之间进行通信。例如，要填充 dataframe3[0,0]，我会查找 portfolios[0,0] 和 headers[0] 中的串联值code>(即 9304420131231)在 returns['concat'] 中，并在 returns['MORET'] 中返回关联值(即 -0.022304)。我被困在这里如何使用连接的值来返回我想要的数据。

非常感谢任何想法。

Answer 1

IIUC:

使用 melt 的组合所以我们可以merge按所需列返回的值。然后使用pivot重新调整数据形状，如下所示。

portfolios.columns = portfolios.columns.astype(int)
newdf = portfolios.reset_index().melt(id_vars='index',var_name=['FROMDATE'],value_name='PERMNO').merge(returns,on=['FROMDATE','PERMNO'],how='left').pivot(index='index',columns='FROMDATE',values='MORET')

返回下面的数据框

FROMDATE  20130630  20130731  20130831  20130930  20131031  20131130  20131231
index
0              NaN       NaN       NaN       NaN       NaN       NaN -0.022304
1              NaN       NaN       NaN       NaN       NaN       NaN  0.012283
2              NaN       NaN       NaN       NaN       NaN       NaN -0.016453

<小时/>

对列进行排序

newdf.loc[:,newdf.columns.sort_values(ascending=False)]