合并 pandas DataFrames时如何保持列MultiIndex值 [英] How to keep column MultiIndex values when merging pandas DataFrames

问题描述

我有两个熊猫DataFrame，如下所示:

I have two pandas DataFrames, as below:

df1 = pd.DataFrame({('Q1', 'SubQ1'):[1, 2, 3], ('Q1', 'SubQ2'):[1, 2, 3], ('Q2', 'SubQ1'):[1, 2, 3]})
df1['ID'] = ['a', 'b', 'c']

df2 = pd.DataFrame({'item_id': ['a', 'b', 'c'], 'url':['a.com', 'blah.com', 'company.com']})

df1:

     Q1          Q2 ID
  SubQ1 SubQ2 SubQ1   
0     1     1     1  a
1     2     2     2  b
2     3     3     3  c

df2:

  item_id          url
0       a        a.com
1       b     blah.com
2       c  company.com

请注意，df1的某些列具有层次结构索引(例如('Q1', 'SubQ1'))，而有些列仅具有常规索引(例如ID).

Note that df1 has some columns with hierarchical indexing (eg. ('Q1', 'SubQ1')) and some with just normal indexing (eg. ID).

我想在ID和item_id字段上合并这两个数据帧.使用:

I want to merge these two data frames on the ID and item_id fields. Using:

result = pd.merge(df1, df2, left_on='ID', right_on='item_id')

给予:

   (Q1, SubQ1)  (Q1, SubQ2)  (Q2, SubQ1) (ID, ) item_id          url
0            1            1            1      a       a        a.com
1            2            2            2      b       b     blah.com
2            3            3            3      c       c  company.com

如您所见，合并本身可以正常工作，但是MultiIndex已丢失，并已还原为元组.我尝试使用pd.MultiIndex.from_tuples重新创建MultiIndex，如下所示:

As you can see, the merge itself works fine, but the MultiIndex has been lost and has reverted to tuples. I've tried to recreate the MultiIndex by using pd.MultiIndex.from_tuples, as in:

result.columns = pd.MultiIndex.from_tuples(result)

但这会导致item_id和url列出现问题，仅使用其名称的前两个字符:

but this causes problems with the item_id and url columns, taking just the first two characters of their names:

     Q1          Q2 ID  i            u
  SubQ1 SubQ2 SubQ1     t            r
0     1     1     1  a  a        a.com
1     2     2     2  b  b     blah.com
2     3     3     3  c  c  company.com

将df2中的列转换为一个元素元组(即('item_id',)而不只是'item_id')没有区别.

Converting the columns in df2 to be one-element tuples (ie. ('item_id',) rather than just 'item_id') makes no difference.

如何合并这两个DataFrame并正确保留MultiIndex?或者，如何获取合并结果并返回具有适当MultiIndex的列，而又不弄乱item_id和url列的名称?

How can I merge these two DataFrames and keep the MultiIndex properly? Or alternatively, how can I take the result of the merge and get back to columns with a proper MultiIndex without mucking up the names of the item_id and url columns?

推荐答案

如果您无法击败'em，请加入'em. (在合并之前，使两个DataFrame具有相同数量的索引级别):

If you can't beat 'em, join 'em. (Make both DataFrames have the same number of index levels before merging):

import pandas as pd

df1 = pd.DataFrame({('Q1', 'SubQ1'):[1, 2, 3], ('Q1', 'SubQ2'):[1, 2, 3], ('Q2', 'SubQ1'):[1, 2, 3]})
df1['ID'] = ['a', 'b', 'c']

df2 = pd.DataFrame({'item_id': ['a', 'b', 'c'], 'url':['a.com', 'blah.com', 'company.com']})

df2.columns = pd.MultiIndex.from_product([df2.columns, ['']])
result = pd.merge(df1, df2, left_on='ID', right_on='item_id')
print(result)

收益

     Q1          Q2 ID item_id          url
  SubQ1 SubQ2 SubQ1                        
0     1     1     1  a       a        a.com
1     2     2     2  b       b     blah.com
2     3     3     3  c       c  company.com

这也避免了UserWarning:

pandas/core/reshape/merge.py:551:UserWarning:在不同级别之间合并会产生意想不到的结果(左侧2级，右侧1级)

pandas/core/reshape/merge.py:551: UserWarning: merging between different levels can give an unintended result (2 levels on the left, 1 on the right)

这篇关于合并 pandas DataFrames时如何保持列MultiIndex值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！