pandas 如何读取子标题 [英] Pandas how to read sub headers

问题描述

我正在使用python + pandas处理csv文件.

I'm using python+pandas to process a csv file.

csv文件具有多个标头，例如

The csv file has multiple headers, like

       Header1                     Header2
Date   Subheader1-1 Subheader1-2   Subheader2-1 Subheader2-2

以原始文本格式，csv文件内容看起来像

And in raw text format, the csv file content looks like

,Header1,,Header2,,...
Date,Subheader1-1,Subheader1-2,Subheader2-1,Subheader2-2,...
...

我的问题是

熊猫是否支持此子标题格式?如果没有，是否有办法将此csv读入pandas数据帧并对其进行一些计算?

Does Pandas support this sub-header format? If not, is there a way to read this csv into pandas dataframe and do some calculation on it?

(计算就像提取Header1的Subheader1-2列，计算平均值和STD，然后使用matplotlib绘制所有内容.)

(The calculation is like extracting Header1's Subheader1-2 column, calculate average and STD, and plot everything using matplotlib.)

推荐答案

使用参数 header = [0,1] ，但是接下来的处理是必要的-替换 Unnamed 列为 NaN ，然后向前填充:

Use parameter header=[0,1], but then next processing is necessary - replace Unnamed columns to NaN and then by forward filling:

import pandas as pd

temp=u''',Header1,,Header2,
Date,Subheader1-1,Subheader1-2,Subheader2-1,Subheader2-2
2018-01-02,10,2,5,6'''
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), header=[0,1])
print (df) 
  Unnamed: 0_level_0      Header1 Unnamed: 2_level_0      Header2  \
                Date Subheader1-1       Subheader1-2 Subheader2-1   
0         2018-01-02           10                  2            5   

  Unnamed: 4_level_0  
        Subheader2-2  
0                  6 

a = df.columns.get_level_values(0).to_series()
b = a.mask(a.str.startswith('Unnamed')).ffill().fillna('')
df.columns = [b, df.columns.get_level_values(1)]
print (df)
                   Header1                   Header2             
         Date Subheader1-1 Subheader1-2 Subheader2-1 Subheader2-2
0  2018-01-02           10            2            5            6

另一个更好的解决方案是通过第一列创建索引:

Another better solution is create index by first column:

import pandas as pd

temp=u''',Header1,,Header2,
Date,Subheader1-1,Subheader1-2,Subheader2-1,Subheader2-2
2018-01-02,10,2,5,6'''
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), header=[0,1], index_col=[0])
print (df) 
                Header1 Unnamed: 2_level_0      Header2 Unnamed: 4_level_0
Date       Subheader1-1       Subheader1-2 Subheader2-1       Subheader2-2
2018-01-02           10                  2            5                  6

a = df.columns.get_level_values(0).to_series()
b = a.mask(a.str.startswith('Unnamed')).ffill().fillna('')
df.columns = [b, df.columns.get_level_values(1)]
print (df)
                Header1                   Header2             
Date       Subheader1-1 Subheader1-2 Subheader2-1 Subheader2-2
2018-01-02           10            2            5            6

这篇关于 pandas 如何读取子标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！