数据帧合并重复值以对齐

Question

以下是指向原始数据集源的链接： dataset for capacity和dataset for type

或修改版本dataset modified1和dataset modified2

我有两个数据帧要合并：

  first_df=pd.DataFrame([['2001','Abu Dhabi','100-','462'],['2001','Abu Dhabi','100','44'],['2001','Abu Dhabi','200','462'],['2001','Dubai','100-','40'],['2001','Dubai','100','30'],['2001','Dubai','200','51'],['2002','Abu Dhabi','100-','300'],['2002','Abu Dhabi','100','220'],['2002','Abu Dhabi','200','56'],['2002','Dubai','100-','55'],['2002','Dubai','100','67'],['2002','Dubai','200','89']],columns=['Year','Emirate','Capacity','Number'])

  second_df=pd.DataFrame([['2001','Abu Dhabi','Performed','45'],['2001','Abu Dhabi','Not Performed','76'],['2001','Dubai','Performed','90'],['2001','Dubai','Not Performed','50'],['2002','Abu Dhabi','Performed','78'],['2002','Abu Dhabi','Not Performed','45'],['2002','Dubai','Performed','76'],['2002','Dubai','Not Performed','58']],columns=['Year','Emirate','Type','Value'])

所以我为两个数据帧设置了多索引：

first=first_df.set_index(['Year','Emirate'])
second=second_df.set_index(['Year','Emirate'])

合并后：

merged=first.merge(second,how='outer',right_index=True,left_index=True)

结果如下：

合并

| Year , Emirate | Capacity | count | friday | count | |:----------------------|:-----------|--------:|:--------------|--------:| | ('2001', 'Abu Dhabi') | 100- | 462 | Performed | 45 | | ('2001', 'Abu Dhabi') | 100- | 462 | Not Performed | 76 | | ('2001', 'Abu Dhabi') | 100 | 44 | Performed | 45 | | ('2001', 'Abu Dhabi') | 100 | 44 | Not Performed | 76 | | ('2001', 'Abu Dhabi') | 200 | 657 | Performed | 45 | | ('2001', 'Abu Dhabi') | 200 | 657 | Not Performed | 76 | | ('2001', 'Dubai') | 100- | 40 | Performed | 90 | | ('2001', 'Dubai') | 100- | 40 | Not Performed | 50 | | ('2001', 'Dubai') | 100 | 30 | Performed | 90 | | ('2001', 'Dubai') | 100 | 30 | Not Performed | 50 | | ('2001', 'Dubai') | 200 | 51 | Performed | 90 | | ('2001', 'Dubai') | 200 | 51 | Not Performed | 50 | | ('2002', 'Abu Dhabi') | 100- | 300 | Performed | 78 | | ('2002', 'Abu Dhabi') | 100- | 300 | Not Performed | 45 | | ('2002', 'Abu Dhabi') | 100 | 220 | Performed | 78 | | ('2002', 'Abu Dhabi') | 100 | 220 | Not Performed | 45 | | ('2002', 'Abu Dhabi') | 200 | 56 | Performed | 78 | | ('2002', 'Abu Dhabi') | 200 | 56 | Not Performed | 45 | | ('2002', 'Dubai') | 100- | 55 | Performed | 76 | | ('2002', 'Dubai') | 100- | 55 | Not Performed | 58 | | ('2002', 'Dubai') | 100 | 67 | Performed | 76 | | ('2002', 'Dubai') | 100 | 67 | Not Performed | 58 | | ('2002', 'Dubai') | 200 | 89 | Performed | 76 | | ('2002', 'Dubai') | 200 | 89 | Not Performed | 58 |

并试图得出以下结果：

joined=pd.concat([first,second])

加入

| Year , Emirate | Capacity | Number | Type | Value | |:----------------------|:-----------|---------:|:--------------|--------:| | ('2001', 'Abu Dhabi') | 100- | 462 | nan | nan | | ('2001', 'Abu Dhabi') | 100 | 44 | nan | nan | | ('2001', 'Abu Dhabi') | 200 | 657 | nan | nan | | ('2001', 'Dubai') | 100- | 40 | nan | nan | | ('2001', 'Dubai') | 100 | 30 | nan | nan | | ('2001', 'Dubai') | 200 | 51 | nan | nan | | ('2002', 'Abu Dhabi') | 100- | 300 | nan | nan | | ('2002', 'Abu Dhabi') | 100 | 220 | nan | nan | | ('2002', 'Abu Dhabi') | 200 | 56 | nan | nan | | ('2002', 'Dubai') | 100- | 55 | nan | nan | | ('2002', 'Dubai') | 100 | 67 | nan | nan | | ('2002', 'Dubai') | 200 | 89 | nan | nan | | ('2001', 'Abu Dhabi') | nan | nan | Performed | 45 | | ('2001', 'Abu Dhabi') | nan | nan | Not Performed | 76 | | ('2001', 'Dubai') | nan | nan | Performed | 90 | | ('2001', 'Dubai') | nan | nan | Not Performed | 50 | | ('2002', 'Abu Dhabi') | nan | nan | Performed | 78 | | ('2002', 'Abu Dhabi') | nan | nan | Not Performed | 45 | | ('2002', 'Dubai') | nan | nan | Performed | 76 | | ('2002', 'Dubai') | nan | nan | Not Performed | 58 |

所以连接在一起的两个数据帧不应该有重复（比如第一次合并）或者下移（比如concat变量）。 有什么解决方案可以使两个数据帧很好地对齐？你知道吗

下面是所需输出的样子：

| | Year | Emirate | Capacity | Number | Type | Value | |---:|-------:|:----------|:-----------|---------:|:--------------|--------:| | 0 | | | 100- | 462 | Performed | 45 | | 1 | | Abu Dhabi | 100 | 44 | Not Performed | 76 | | 2 | | | 200 | 657 | NaN | nan | | 3 | 2001 | | 100- | 40 | Performed | 90 | | 4 | | Dubai | 100 | 30 | Not Performed | 50 | | 5 | | | 200 | 51 | NaN | nan | | 6 | | | 100- | 300 | Performed | 78 | | 7 | | Abu Dhabi | 100 | 220 | Not Performed | 45 | | 8 | 2002 | | 200 | 56 | NaN | nan | | 9 | | | 100- | 55 | Performed | 76 | | 10 | | Dubai | 100 | 67 | Not Performed | 58 | | 11 | | | 200 | 89 | NaN | nan |

enter code here

Answer 1

我在这里看到了问题所在，当您在['year','Emirate']上连接数据时，它会导致交叉连接。e、 g 2001年阿布扎比与2001年阿布扎比在“已执行和未执行”两个数据框中加入。基本上这是m x n关系连接的数据集。除非指定一个可以唯一标识每一行的主键，否则最终会得到相同的结果。你知道吗