pandas 到Excel(合并标题栏) [英] Pandas to Excel (Merged Header Column)

问题描述

我想将df转换为Excel工作表，但也想添加标题列以对所有列进行分类.

I want to convert my df to an excel sheet, but also want to add a header column to categorize all the columns.

为了复制:

import pandas as pd
 # Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

推荐答案

您可以创建MultiIndex:

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})

使用开始和结束列名称指定级别的新名称:

Specified new name of level with start and end column name:

L = [('OBS','A','C'), ('FIN', 'D','F')]

然后在列表理解中为 MultiIndex.from_tuples :

And then in list comprehension create tuples for MultiIndex.from_tuples:

cols = [(new, c) for new, start, end in L for c in df.loc[:, start:end].columns]

print (cols)
[('OBS', 'A'), ('OBS', 'B'), ('OBS', 'C'), ('FIN', 'D'), ('FIN', 'E'), ('FIN', 'F')]

df.columns = pd.MultiIndex.from_tuples(cols)
print (df)
  OBS       FIN      
    A  B  C   D  E  F
0   a  4  7   1  5  a
1   b  5  8   3  3  a
2   c  4  9   5  6  a
3   d  5  4   7  9  b
4   e  5  2   1  2  b
5   f  4  3   0  4  b

这篇关于 pandas 到Excel(合并标题栏)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！