旋转和熔化，多个标题列到行 [英] Pivoting and melting, multiple header column to row

问题描述

我有一个csv文件，带有这样的表

I have a csv file, with a table like this

                             1/1/2017           3/4/2017
                             2/2/2018           5/6/2018
Group   ID    SecID  ISIN    a        b   c     a         b   c  ... 
xxxx    xx    xxxx   xxxx    x        x   x     x         x  x

如上表所示，前四个是可以的，但是a，b，c列有两个上部标题.我想添加两个新列start和end以及列a，b，c，并将前两个头数据放入开始和结束列.

As you can see in the above table, the first four are okay, but the columns a, b, c have a two upper header. I want to add two new columns start and end along with the columns a, b, c and put the first two header data in to start and end column.

请注意，前两行的日期与标题中的"a"列相关.

Please note the first two rows dates are associated with the column 'a' in header.

所以它应该看起来像这样:

So it should look like this :

 Group   ID    SecID  ISIN  start        end       a  b  c
 xxxx    xxx    xxx    xxx   1/1/2017    2/2/2018  x  x  x 
 xxxx    xxx    xxx    xxx   3/4/2017    5/6/2018  x  x  x 

是的，将创建重复的记录，我可以为此解决方案吗? 我的导入代码:

yes duplicate records will be created, can I get a solution for this? my import code :

import pandas as pd
import numpy as np
df=pd.read_csv('test.csv',header=[0,1,2])

我尝试将其融化，

df = pd.melt(df, id_vars=["Group", "Id","ISIN","SecId"],
                  var_name="Others", value_name="Value") 

但是没有用.

推荐答案

您可以使用stack，reset_index和rename:

df = df.set_index(['Group','ID','SecID','ISIN'])
df.stack([0,1]).reset_index().rename(columns={'level_4':'start','level_5':'end'})

这篇关于旋转和熔化，多个标题列到行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！