Pandas question

[deanhystad]

Is your question that you have multi-indexed columns and you don't know how to use them? To index multi-indexed columns, provide a value for each index. Thes example has two index values per column.

import pandas as pd
import numpy as np

col = pd.MultiIndex.from_arrays(
    [["one", "one", "one", "two", "two", "two"], ["a", "b", "c", "a", "b", "c"]]
)
data = np.array(range(2 * 3 * 4)).reshape((4, 2 * 3))

df = pd.DataFrame(data, columns=col)
print(df)

print(df[("two", "a")])

Output:
  one         two
    a   b   c   a   b   c
0   0   1   2   3   4   5
1   6   7   8   9  10  11
2  12  13  14  15  16  17
3  18  19  20  21  22  23
0     3
1     9
2    15
3    21
Name: (two, a), dtype: int32

To verify this works when reading from an excel file, I wrote the dataframe to excel and read the file using this progrram.

import pandas as pd

df = pd.read_excel("test.xlsx", header=(0, 1))
print(df)

print(df[("two", "a")])

Output:
  one         two
    a   b   c   a   b   c
0   0   1   2   3   4   5
1   6   7   8   9  10  11
2  12  13  14  15  16  17
3  18  19  20  21  22  23
0     3
1     9
2    15
3    21
Name: (two, a), dtype: int64

You could also collapse the columns to be single-indexed.

def collapse_header(columns):
    """Collapse multi-index column indices to a list of strings."""
    return [" ".join(column).strip() for column in columns]


df = pd.read_excel("test.xlsx", header=(0, 1))
print(df)

df.columns = collapse_header(df.columns)
print(df)

print(df["two a"])

Output:
  one         two
    a   b   c   a   b   c
0   0   1   2   3   4   5
1   6   7   8   9  10  11
2  12  13  14  15  16  17
3  18  19  20  21  22  23
   one a  one b  one c  two a  two b  two c
0      0      1      2      3      4      5
1      6      7      8      9     10     11
2     12     13     14     15     16     17
3     18     19     20     21     22     23
0     3
1     9
2    15
3    21
Name: two a, dtype: int64