时间戳

首页 > 解决方案 > Pandas merge, right file has more than one instance of the same key

python - Pandas merge, right file has more than one instance of the same key

问题描述

Context: working with a medical database (CSV)

So I have a set of data that looks like this. This will be the 'left' file during the merge.

The 'right' file looks like this.

I need to merge the two CSVs on 'INC_KEY' (unique 9-digit number assigned to each patient), however the 'right' file may contain more than one instance of the same INC_KEY (i.e. more than one TMODE for the same patient).

How do I merge the files such that all of the TMODEs for each patient will be in the resulting merged file? The column names would be: TMODE1, TMODE2, TMODE3, etc.

标签： pythonpandasdatabasedataframemerge

解决方案

您需要将 pandas 的两个表都读取为数据框，然后根据您的标准将它们合并，该标准匹配两个表并返回在右表中具有多种模式的患者：

import pandas as pd
table1 = pd.read_csv('left.csv')
table2 = pd.read_csv('right.csv')
table = pd.merge(table1,table2,on='INC_KEY',how='outer')
table.pivot(index='INC_KEY', columns='TMODE', values=['TRANS_BLOOD_24HOURS','TRANS_BLOOD_MEASURE_DESC','TRANS_BLOOD_CONV','TRANTYPE'])

要了解有关how合并函数中参数的更多信息，请阅读以下内容：here

推荐阅读