使用熊猫合并时如何保持索引

问题:使用熊猫合并时如何保持索引

我想合并两个DataFrames,并保留第一帧的索引作为合并数据集中的索引。但是,当我进行合并时,所得的DataFrame具有整数索引。如何指定要保留左侧数据框中的索引?

In [4]: a = pd.DataFrame({'col1': {'a': 1, 'b': 2, 'c': 3}, 
                          'to_merge_on': {'a': 1, 'b': 3, 'c': 4}})

In [5]: b = pd.DataFrame({'col2': {0: 1, 1: 2, 2: 3}, 
                          'to_merge_on': {0: 1, 1: 3, 2: 5}})

In [6]: a
Out[6]:
   col1  to_merge_on
a     1            1
b     2            3
c     3            4

In [7]: b
Out[7]:
   col2  to_merge_on
0     1            1
1     2            3
2     3            5

In [8]: a.merge(b, how='left')
Out[8]:
   col1  to_merge_on  col2
0     1            1   1.0
1     2            3   2.0
2     3            4   NaN

In [9]: _.index
Out[9]: Int64Index([0, 1, 2], dtype='int64')

编辑:切换到示例代码,可以轻松地复制

I would like to merge two DataFrames, and keep the index from the first frame as the index on the merged dataset. However, when I do the merge, the resulting DataFrame has integer index. How can I specify that I want to keep the index from the left data frame?

In [4]: a = pd.DataFrame({'col1': {'a': 1, 'b': 2, 'c': 3}, 
                          'to_merge_on': {'a': 1, 'b': 3, 'c': 4}})

In [5]: b = pd.DataFrame({'col2': {0: 1, 1: 2, 2: 3}, 
                          'to_merge_on': {0: 1, 1: 3, 2: 5}})

In [6]: a
Out[6]:
   col1  to_merge_on
a     1            1
b     2            3
c     3            4

In [7]: b
Out[7]:
   col2  to_merge_on
0     1            1
1     2            3
2     3            5

In [8]: a.merge(b, how='left')
Out[8]:
   col1  to_merge_on  col2
0     1            1   1.0
1     2            3   2.0
2     3            4   NaN

In [9]: _.index
Out[9]: Int64Index([0, 1, 2], dtype='int64')

EDIT: Switched to example code that can be easily reproduced


回答 0

In [5]: a.reset_index().merge(b, how="left").set_index('index')
Out[5]:
       col1  to_merge_on  col2
index
a         1            1     1
b         2            3     2
c         3            4   NaN

注意:对于某些左合并操作,如果和之间存在多个匹配项,则最终可能会出现更多行ab并且需要进行重复数据删除(有关重复数据删除的文档)。这就是为什么熊猫不为您保留索引的原因。

In [5]: a.reset_index().merge(b, how="left").set_index('index')
Out[5]:
       col1  to_merge_on  col2
index
a         1            1     1
b         2            3     2
c         3            4   NaN

Note that for some left merge operations, you may end up with more rows than in a when there are multiple matches between a and b. In this case, you may need to drop duplicates.


回答 1

您可以在左侧数据框上复制索引并进行合并。

a['copy_index'] = a.index
a.merge(b, how='left')

我发现在处理大型数据框和使用pd.merge_asof()(或dd.merge_asof())时,此简单方法非常有用。

当重置索引很昂贵(大数据帧)时,这种方法会更好。

You can make a copy of index on left dataframe and do merge.

a['copy_index'] = a.index
a.merge(b, how='left')

I found this simple method very useful while working with large dataframe and using pd.merge_asof() (or dd.merge_asof()).

This approach would be superior when resetting index is expensive (large dataframe).


回答 2

有一个非pd.merge解决方案。使用mapset_index

In [1744]: a.assign(col2=a['to_merge_on'].map(b.set_index('to_merge_on')['col2']))
Out[1744]:
   col1  to_merge_on  col2
a     1            1   1.0
b     2            3   2.0
c     3            4   NaN

并且,不要index为索引引入虚拟名称。

There is a non-pd.merge solution using Series.map and DataFrame.set_index.

In: a['col2'] = a['to_merge_on'].map(b.set_index('to_merge_on')['col2']))
In: a['col2']
Out:
   col1  to_merge_on  col2
a     1            1   1.0
b     2            3   2.0
c     3            4   NaN

This doesn’t introduce a dummy index name for the index.

Note however that there is no DataFrame.map method, and so this approach is not for multiple columns.


回答 3

df1 = df1.merge(
        df2, how="inner", left_index=True, right_index=True
    )

这样可以保留df1的索引

df1 = df1.merge(df2, how="inner", left_index=True, right_index=True)

This allows to preserve the index of df1


回答 4

认为我想出了一个不同的解决方案。我是根据左表的索引将左表与索引值连接在一起,将右表与列值连接在一起。我所做的是普通合并:

First10ReviewsJoined = pd.merge(First10Reviews, df, left_index=True, right_on='Line Number')

然后,我从合并表中检索了新的索引号,并将它们放在名为“情感行号”的新列中:

First10ReviewsJoined['Sentiment Line Number']= First10ReviewsJoined.index.tolist()

然后,我基于称为行号(我从左表索引加入的列值)的现有列,将索引手动设置回原始的左表索引:

First10ReviewsJoined.set_index('Line Number', inplace=True)

然后删除行号的索引名称,使其保持空白:

First10ReviewsJoined.index.name = None

也许有点破解,但似乎运行良好且相对简单。另外,猜测它会减少重复/混乱数据的风险。希望一切都有意义。

Think I’ve come up with a different solution. I was joining the left table on index value and the right table on a column value based off index of left table. What I did was a normal merge:

First10ReviewsJoined = pd.merge(First10Reviews, df, left_index=True, right_on='Line Number')

Then I retrieved the new index numbers from the merged table and put them in a new column named Sentiment Line Number:

First10ReviewsJoined['Sentiment Line Number']= First10ReviewsJoined.index.tolist()

Then I manually set the index back to the original, left table index based off pre-existing column called Line Number (the column value I joined on from left table index):

First10ReviewsJoined.set_index('Line Number', inplace=True)

Then removed the index name of Line Number so that it remains blank:

First10ReviewsJoined.index.name = None

Maybe a bit of a hack but seems to work well and relatively simple. Also, guess it reduces risk of duplicates/messing up your data. Hopefully that all makes sense.


回答 5

另一个简单的选择是将索引重命名为之前的名称:

a.merge(b, how="left").set_axis(a.index)

合并保留数据帧“ a”的顺序,但只是重置索引,因此可以保存以使用set_axis

another simple option is to rename the index to what was before:

a.merge(b, how="left").set_axis(a.index)

merge preserves the order at dataframe ‘a’, but just resets the index so it’s save to use set_axis