给定公共列的两个不同大小 Dataframe 之间的Pandas算法

wkyowqbh  于 2022-12-28  发布在  其他
关注(0)|答案(1)|浏览(149)

东风1

| ColA     | Colb           | Stock    | Date       |
| -------- | -------------- | -------- | ---------- |
| A        | 1              | 3        | 2022-26-12 |
| B        | 2              | 3        | 2022-26-12 |
| C        | 3              | 3        | 2022-26-12 |

东风2

| ColA     | Colb           | Sales    | Date       |
| -------- | -------------- | -------- | ---------- |
| A        | 1              | 1        | 2022-26-12 |
| B        | 2              | 1        | 2022-26-12 |
| C        | 3              | 1        | 2022-26-12 |

给定要连接的任意数量的列,如何在panda中执行Dataframe算术,例如,如果我想减去上面的两个Dataframe,得到如下结果
一天结束时的库存

| ColA     | Colb           | Stock    | Date       |
| -------- | -------------- | -------- | ---------- |
| A        | 1              | 2        | 2022-26-12 |
| B        | 2              | 2        | 2022-26-12 |
| C        | 3              | 2        | 2022-26-12 |

在本例中,给定所有公共列的股票销售额
编辑:与我的问题等价的SQL代码是:

SELECT
    DF1.ColA,
    DF1.Colb,
    DF1.Date,
    DF1.Stock - coalesce(DF2.Sales, 0)
FROM
    DF1
    LEFT JOIN DF2
        on
            DF1.ColA = DF2.ColA and
            DF1.Colb = DF2.Colb and
            DF1.Date = DF2.Date
z8dt9xmd

z8dt9xmd1#

如果它们有相同的行数和列数,那么就这样做:

df3 = df1[['ColA', 'Colb','Date']]
df3['Stock'] = df1.Stock - df2.Sales

但是,如果他们是不同的merge他们然后做你想要的:

df3= pd.merge(df1, df2, on='ColA', how='inner')
df3['Stock'] = df3.Stock - df3.Sales

在您的情况下,根据您编辑的问题:

df3 = pd.merge(df1, df2,  how='left', left_on=['ColA','Colb','Date'], right_on = ['ColA','Colb','Date'])
#rename the columns as you want
df3.columns=['col1','col2']
#only select columns you want
df3=df3[['col1','col2']]
#then do your subtraction
df3['Stock'] = df3.col1 - df3.col2

相关问题