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.

I will post the article as a memorandum.

environment

• Python 3.8.1
• pandas 1.0.3

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