如何合并或连接Pandas中的堆叠数据帧？

Question

我在别处找不到这个问题的答案；我想在pandas中做一个类似SQL的连接，但稍微有点扭曲，一个数据帧是堆叠的。 我已经从pandas中的csv文件创建了一个带有堆叠列索引的dataframe a，如下所示：

|           |      | 2013-01-04 | 2013-01-07 |
|----------:|-----:|-----------:|-----------:|
| Adj Close |  OWW | NaN        | NaN        |
|   Close   | OXLC | 4.155157   | 4.147217   |
|           |  OXM | 40.318089  | 42.988800  |
|           |  OXY | 50.416079  | 62.934800  |

原始csv对每个条目重复了第1列中的内容，如下所示：

|           |      | 2013-01-04 | 2013-01-07 |
|----------:|-----:|-----------:|-----------:|
| Adj Close |  OWW | NaN        | NaN        |
|   Close   | OXLC | 4.155157   | 4.147217   |
|   Close   |  OXM | 40.318089  | 42.988800  |
|   Close   |  OXY | 50.416079  | 62.934800  |

最初的csv是此版本的转置版本。熊猫选择在转换为数据帧时堆叠它。（我使用了以下代码：pd.read\u csv（文件，头=[0,1]，索引=0.T）

在另一个csv/dataframe B中，对于所有这些所谓的股票代码，我有另一个我更愿意使用的ID：CIK

| CIK     | Ticker | Name                                           |
|---------|--------|------------------------------------------------|
| 1090872 | A      | Agilent Technologies Inc                       |
| 4281    | AA     | Alcoa Inc                                      |
| 1332552 | AAACU  | Asia Automotive Acquisition Corp               |
| 1287145 | AABB   | Asia Broadband Inc                             |
| 1024015 | AABC   | Access Anytime Bancorp Inc                     |
| 1099290 | AAC    | Sinocoking Coal & Coke Chemical Industries Inc |
| 1264707 | AACC   | Asset Acceptance Capital Corp                  |
| 849116  | AACE   | Ace Cash Express Inc                           |
| 1409430 | AAGC   | All American Gold Corp                         |
| 948846  | AAI    | Airtran Holdings Inc                           |

期望输出：我希望在一个新的数据帧中使用CIK而不是ticker，否则与a相同

现在在SQL中，我可以很容易地加入到A.name\u of_2nd\u column=b.Ticker中，因为表的每一行都会重复第1列中的条目（如原始csv），并且该列会有一个名称，但在pandas中我不能。我尝试了以下代码：

result = pd.merge(data, tix, how='left', left_on=[1] right_on=['Ticker'])

我如何告诉熊猫使用第二列作为键和/或将第一列解释为重复值

Answer 1

您想要的是从一组标识符（代码）转换到另一组标识符（我想SEC Edgar数据库中使用的CIK）

我会的

1. 将索引列转换为普通列，特别是当这些是多索引时，可能在重命名索引列之后

A.index.names=('Data','Ticker')
A = A.reset_index()

1. 使用map方法将股票代码转换为CIK

transco = B.set_index('Ticker').CIK
A['CIK'] = A.Ticker.map(transco)

1. 最终根据需要重新编制索引，删除未使用的索引

A = A.drop('Ticker', axis=1).set_index(['Data','CIK'])

作为步骤2.5，您可能希望删除您没有任何CIK的条目，例如，执行以下操作：

A = A[A.CIK.notnull()]
A.CIK = A.CIK.astype(int)

您也可以在执行reset_index()之后进行合并，但我会避免这样做，因为您可能最终会得到无用的大数据帧，因为合并的结果将有一个names列。如果您有许多不同类型的数据（Adj Close、Close等），这可能会增加

Answer 2

我最终能够通过以下方式做到这一点：

df = A
tix = B 

ticker_2_CIK = dict(zip(tix.Ticker,tix.CIK))  # create a dict

tmp = df.reset_index().assign(CIK=lambda x: x['ticker'].map(ticker_2_CIK)) # use dict to find the correct value for colum 

# data was unclean, some ticker symbols were created after the period my data is from 
# and data was incomplete with some tickers missing
solution = tmp.dropna(subset=['CIK']).astype({'CIK':int})