我有一个股票每天开盘价和收盘价的数据框架。
ticker Date Open Close
0 CAMT 2021-01-14 24.150000 24.740000
1 CAMT 2021-01-15 24.540001 24.400000
2 F 2021-01-14 9.810000 10.170000
3 F 2021-01-15 10.040000 9.830000
4 GKOS 2021-01-14 84.050003 86.709999
5 GKOS 2021-01-15 86.250000 86.339996
我从这个数据框创建了一个新的数据框,其中每行包含一个股票和每天的打开/关闭列。我使用了以下代码:
df.pivot(index='ticker', values=['Open', 'Close'], columns='Date').reset_index(drop=False)
它返回这个数据帧:
ticker Open Close
Date 2021-01-14 00:00:00 2021-01-15 00:00:00 2021-01-14 00:00:00 2021-01-15 00:00:00
0 CAMT 24.150000 24.540001 24.740000 24.400000
1 F 9.810000 10.040000 10.170000 9.830000
2 GKOS 84.050003 86.250000 86.709999 86.339996
3 IGBH 25.180000 24.959999 25.110001 24.950001
我想重置列上的索引,如下所示:
ticker Open_14-01-2021 Close_14-01-2021 Open_15-01-2021 Close_15-01-2021
0 CAMT 24.150000 24.540001 24.740000 24.400000
1 F 9.810000 10.040000 10.170000 9.830000
2 GKOS 84.050003 86.250000 86.709999 86.339996
3 IGBH 25.180000 24.959999 25.110001 24.950001
我试过了,但没有成功:
df.columns = df.columns.droplevel(0)
首先进行透视,然后使用简单的列表推导,通过连接每个级别的标签,将列上的MultiIndex折叠为一个简单的Index。然后我们重置索引,将'ticker'
移回列中。
df = df.pivot(index='ticker', values=['Open', 'Close'], columns='Date')
df.columns = [f'{x}_{y.strftime("%d-%m-%Y")}' for x,y in df.columns]
df = df.reset_index()
ticker Open_14-01-2021 Open_15-01-2021 Close_14-01-2021 Close_15-01-2021
0 CAMT 24.150000 24.540001 24.740000 24.400000
1 F 9.810000 10.040000 10.170000 9.830000
2 GKOS 84.050003 86.250000 86.709999 86.339996