Python Pandas:按日期索引和公共列值组合两个数据框 [英] Python pandas: Combine two dataframes by date index and a common column value

问题描述

有两个日期框架，一个是df1，另一个是df2，如下所示:

There are two dateframes, one is df1, another is df2 as follows:

df1:

               a   b    id
2010-01-01     1   4    21
2010-01-01     2   5    22
2010-01-01     3   6    23
2010-01-01     4   7    24
2010-01-02     1   4    21
2010-01-02     2   5    22
2010-01-02     3   6    23
2010-01-02     4   7    24
2010-01-03     1   4    21
2010-01-03     2   5    22
2010-01-03     3   6    23
2010-01-03     4   7    24
...........................

df2:

               c   d    id
2010-01-02     1   4    21
2010-01-02     2   5    22
2010-01-02     3   6    23
2010-01-02     4   7    24
2010-01-03     1   4    21
2010-01-03     2   5    22
2010-01-03     3   6    23
2010-01-03     4   7    24
...........................

我想通过通用索引(请注意df1中的某些索引不在df2中)和id合并或联接两个数据框，我期望如下联接数据框

I want to merge or join two dataframes by common index (please notice that some index in df1 is not in df2) and id, and I expected a joined dataframe as following

               c   d    a   b   id
2010-01-02     1   4    1   4   21
2010-01-02     2   5    2   5   22
2010-01-02     3   6    3   6   23
2010-01-02     4   7    4   7   24
2010-01-03     1   4    1   4   21
2010-01-03     2   5    2   5   22
2010-01-03     3   6    3   6   23
2010-01-03     4   7    4    7  24

我使用了以下代码

 df = df1.join(df2, on = ['id'], how='inner')

但是那没用

推荐答案

IIUC:

In [388]: df2.set_index('id', append=True).join(df1.set_index('id', append=True)) \
             .reset_index(level='id')
Out[388]:
            id  c  d  a  b
2010-01-02  21  1  4  1  4
2010-01-02  22  2  5  2  5
2010-01-02  23  3  6  3  6
2010-01-02  24  4  7  4  7
2010-01-03  21  1  4  1  4
2010-01-03  22  2  5  2  5
2010-01-03  23  3  6  3  6
2010-01-03  24  4  7  4  7

这篇关于Python Pandas:按日期索引和公共列值组合两个数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！