Python长格式:减去所选行

2024-03-29 01:52:44 发布

您现在位置:Python中文网/ 问答频道 /正文

全部,

我有以下长格式数据帧:

df = pd.DataFrame({'date': ["2020-01-01","2020-01-01","2020-01-02","2020-01-02","2020-01-01","2020-01-01","2020-01-02","2020-01-02"], 'asset': ["x", "x","x", "x","y","y","y","y"], 'type': ["price", "spread","price","spread","price", "spread","price","spread"], 'value': ["1.5", "0.01","1.6", "0.01","1","0.08","1.2","0.09"]})

看起来是这样的:

         date asset    type value
0  2020-01-01     x   price   1.5
1  2020-01-01     x  spread  0.01
2  2020-01-02     x   price   1.6
3  2020-01-02     x  spread  0.01
4  2020-01-01     y   price     1
5  2020-01-01     y  spread  0.08
6  2020-01-02     y   price   1.2
7  2020-01-02     y  spread  0.09

我想从x的价格中减去y的价格,并保持相同的数据结构,结果应该如下所示:

         date    asset       type value
0  2020-01-01        x      price   1.5
1  2020-01-01        x     spread  0.01
2  2020-01-02        x      price   1.6
3  2020-01-02        x     spread  0.01
4  2020-01-01        y      price     1
5  2020-01-01        y     spread  0.08
6  2020-01-02        y      price   1.2
7  2020-01-02        y     spread  0.09
8  2020-01-01  x_min_y  pricediff   0.5
9  2020-01-02  x_min_y  pricediff   0.4

我想使用pandas的assign()函数来创建它,但我不确定如何做到这一点

提前谢谢


Tags: 数据数据结构dataframedfdatevalue格式type
2条回答

假设不需要匹配日期,并且数据集的定义如示例所示,则可以执行以下操作:

df2 = pd.DataFrame(df1[df1["asset"] == "x" & df1["type"] == "price"]["value"].reset_index()["value"].astype(float)  - df1[df1["asset"] == "y" & df1["type"] == "price"]["value"].reset_index()["value"].astype(float))
df2["date"] = df1[df1["asset"] == "x"]["date"]
df2["type"] = df1[df1["asset"] == "x"]["type"]
df2["asset"] = "x_min_y"
pd.concat([df1,df2])

基本上,执行计算并随后连接

使用:

m = df['type'].eq('price') & df['asset'].isin(['x', 'y'])
d = df[m].pivot('date', 'asset', 'value').astype(float)

d = pd.concat(
    [df, d['x'].sub(d['y']).reset_index(name='value').assign(
        asset='x_min_y', type='pricediff')],
    ignore_index=True)

详细信息:

创建一个布尔掩码m以过滤typepriceassetx, y的行,并使用^{}重新塑造数据帧:

print(d) # pivoted dataframe
asset         x    y
date                
2020-01-01  1.5  1.0
2020-01-02  1.6  1.2

使用^{}从数据透视帧中的y中减去列x,并分配列assettype,然后使用^{}将该数据透视帧与原始数据帧df连接

print(d)
         date    asset       type value
0  2020-01-01        x      price   1.5
1  2020-01-01        x     spread  0.01
2  2020-01-02        x      price   1.6
3  2020-01-02        x     spread  0.01
4  2020-01-01        y      price     1
5  2020-01-01        y     spread  0.08
6  2020-01-02        y      price   1.2
7  2020-01-02        y     spread  0.09
8  2020-01-01  x_min_y  pricediff   0.5
9  2020-01-02  x_min_y  pricediff   0.4

相关问题 更多 >