How to output CSV of multi-line header with pandas

Thing you want to do

I want to output a table with multiple rows of headers like the one below in CSV format.

image.png

I will post the article as a memorandum.

environment

Realization method

  1. Create a DataFrame with columns MultiIndex
  2. Output with the to_csv method of DataFrame

How to create a DataFrame

Pass dict to the data argument of the DataFrame constructor.

df = pandas.DataFrame({
    ("Alice","Math"): [80,81],
    ("Alice","English"): [90,91],
    ("Bob","Math"): [70,71]
})

print(df)
#   Alice          Bob
#    Math English Math
# 0    80      90   70
# 1    81      91   71

print(df.columns)
# MultiIndex([('Alice',    'Math'),
#             ('Alice', 'English'),
#             (  'Bob',    'Math')],
#            )

Pass MultiIndex to the columns argument of the DataFrame constructor

index = pandas.MultiIndex.from_tuples([
    ("Alice","Math"),
    ("Alice","English"),
    ("Bob","Math")
])

df2 = pandas.DataFrame([[80,90,70],[81,91,71]], columns=index)

print(df2)
#   Alice          Bob
#    Math English Math
# 0    80      90   70
# 1    81      91   71

print(df2.columns)
# MultiIndex([('Alice',    'Math'),
#             ('Alice', 'English'),
#             (  'Bob',    'Math')],
#            )

Pass a list of dicts in the data argument of the DataFrame constructor

If the constructor argument columns is None, the type of the columns property will be ʻIndex` and it will not be possible to output with a multi-line header.

df3 = pandas.DataFrame(
    [
        {("Alice","Math"):80, ("Alice","English"):90,("Bob","Math"):70},
        {("Alice","Math"):81, ("Alice","English"):91,("Bob","Math"):71},
    ]
)

print(df3)
#    (Alice, Math)  (Alice, English)  (Bob, Math)
# 0             80                90           70
# 1             81                91           71

print(df3.columns)
# Index([('Alice', 'Math'), ('Alice', 'English'), ('Bob', 'Math')], dtype='object')

You can output multi-line headers by passing MultiIndex as the columns argument.

index = pandas.MultiIndex.from_tuples([
    ("Alice","Math"),
    ("Alice","English"),
    ("Bob","Math")
])

df3 = pandas.DataFrame(
    [
        {("Alice","Math"):80, ("Alice","English"):90,("Bob","Math"):70},
        {("Alice","Math"):81, ("Alice","English"):91,("Bob","Math"):71},
    ]
    ,columns=index
)

print(df3)
#   Alice          Bob
#    Math English Math
# 0    80      90   70
# 1    81      91   71

Output with the to_csv method of DataFrame

df.to_csv("foo.csv", index=False)

foo.csv


Alice,Alice,Bob
Math,English,Math
80,90,70
81,91,71

Recommended Posts

How to output CSV of multi-line header with pandas
How to convert JSON file to CSV file with Python Pandas
Output to csv file with Python
[Python] Summary of how to use pandas
How to read CSV files in Pandas
How to change multiple columns of csv in Pandas (Unixtime-> Japan Time)
[Python] How to read a csv file (read_csv method of pandas module)
How to create sample CSV data with hypothesis
How to read a CSV file with Python 2/3
[Python] How to read excel file with pandas
How to specify attributes with Mock of python
How to implement "named_scope" of RubyOnRails with Django
CSV output of pulse data with Raspberry Pi (CSV output)
How to use Pandas 2
Convert 202003 to 2020-03 with pandas
Summary of how to read numerical data with python [CSV, NetCDF, Fortran binary]
How to convert horizontally held data to vertically held data with pandas
How to extract null values and non-null values with pandas
How to extract non-missing value nan data with pandas
[Python] How to deal with pandas read_html read error
[Python] A memo to write CSV vertically with Pandas
How to infer MAP estimate of HMM with PyStruct
[Python-pptx] Output PowerPoint font information to csv with python
How to infer MAP estimate of HMM with OpenGM
How to learn structured SVM of ChainCRF with PyStruct
How to extract non-missing value nan data with pandas
Summary of how to share state with multiple functions
[Memo] Load csv of s3 into pandas with boto3
Read csv with python pandas
Output to syslog with Loguru
How to update with SQLAlchemy?
How to use Pandas Rolling
How to cast with Theano
How to Alter with SQLAlchemy?
Write to csv with Python
How to separate strings with','
How to RDP with Fedora31
How to Delete with SQLAlchemy?
How to output a document in pdf format with Sphinx
How to enable Read / Write of net.Conn with context with golang
How to display a list of installable versions with pyenv
[Python] How to output a pandas table to an excel file
How to get an overview of your data in Pandas
How to read an Excel file (.xlsx) with Pandas [Python]
How to create dataframes and mess with elements in pandas
How to output additional information when logging with python's logging module
How to output the number of VIEWs, likes, and stocks of articles posted on Qiita to CSV (created with "Python + Qiita API v2")
How to cancel RT with tweepy
How to paste a CSV file into an Excel file using Pandas
How to extract features of time series data with PySpark Basics
Python: How to use async with
[Hugo] Summary of how to add pages to sites built with Learn
[Python] Write to csv file with Python
Summary of how to use pandas.DataFrame.loc
How to write soberly in pandas
How to get the ID of Type2Tag NXP NTAG213 with nfcpy
How to use virtualenv with PowerShell
Etosetra related to read_csv of Pandas
How to deal with imbalanced data
I want to output the beginning of the next month with Python
Output the contents of ~ .xlsx in the folder to HTML with Python