在两个数据帧之间使用“VLOOKUP”创建新的数据帧

Question

与Excel的VLOOKUP函数有些类似，我希望使用一个dataframe（portfolios）中的一个值来在第二个dataframe（returns下面）中找到一个关联的值，并用这些返回值填充第三个dataframe（现在我们称之为dataframe3）。我发现了一些基于左合并和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格式的日期。在每个日期标题下面是五位数字代码的标识符。portfolios的片段如下（某些列中的某些元素包含NaN）：

^{pr2}$

returns数据中的数据最初由三列和799行组成，如下所示（所有元素都用值填充）：

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

期望输出

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

任何想法都非常感谢。在

Answer 1

你想做的事情比你试着做的要简单得多。您可以先将portfolios融化，将所有日期列作为行收集到一列中，然后将其与returns连接，最后旋转以获得所需的结果。这基本上是@djk47463在一个复合行中所做的，我编辑的答案作为他的一步一步的分解。在

让我们创建你的数据帧，让答案重现。在

import pandas as pd
import sys
if sys.version_info[0] < 3:
    from StringIO import StringIO
else:
    from io import StringIO

# Create df
rawText = StringIO("""
     PERMNO  FROMDATE     MORET
0     93044  20131231 -0.022304
1     79702  20131231  0.012283
2     85751  20131231 -0.016453
3     85576  20131231  0.038766
4     93044  20131010 -0.02
5     79702  20131010  0.01
6     85751  20131010 -0.01
7     85576  20131010  0.03
""")
returns = pd.read_csv(rawText, sep = "\s+")
portfolios = pd.DataFrame({'20131010':[93044, 85751],
                       '20131231':[85576, 79702]})

注意，returns的FROMDATE列由数字组成，但是在portfolios中，日期列是字符串。我们必须使它们保持一致：

^{pr2}$

让我们通过melt}（即unpivot）portfolios开始解决方案：

^{3}$

现在您希望保持这个pm常量，并在PERMNO和{}匹配时将returns合并到其行：

merged = pm.merge(df, how='left', on=['PERMNO', 'FROMDATE'])
# merged: 
   FROMDATE  PERMNO     MORET
0  20131010   93044 -0.020000
1  20131010   85751 -0.010000
2  20131231   85576  0.038766
3  20131231   79702  0.012283

还记得我们在开始的时候melt编辑了portfolios吗？我们应该pivot这个结果可以得到portfolios的形状：

final = merged.pivot(index='PERMNO', columns='FROMDATE', values='MORET').reset_index()
# final: 
FROMDATE  PERMNO  20131010  20131231
0          79702       NaN  0.012283
1          85576       NaN  0.038766
2          85751     -0.01       NaN
3          93044     -0.02       NaN

Answer 2

IIUC:

使用^{}的组合，这样我们就可以根据所需的列从returns得到{a2}值。然后使用^{}重新调整数据的形状，如下所示。在

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')

返回下面的数据帧

^{pr2}$

---

对列排序

^{3}$

Answer 3

在python中执行vlookup的典型方法是用索引中的左列创建一个序列，然后按查找值对该序列进行切片。南岛有点复杂。我们将从returns生成一个序列，方法是使用set_index方法将PERMNO设置为数据帧的索引，然后按列名进行切片，将MORET列隔离为一个序列。在

lookupseries = returns.set_index('PERMNO')['MORET']
def lookup(x):
    try: 
        return lookupseries[x]
    except: 
        return np.nan
newdf = portfolios.copy()
for c in newdf.columns:
    newdf[c] = newdf[c].apply(lookup)