Как читать Excel таблицы с составным заголовком?

Question

Есть таблица Excel, в которой заголовок размещается на 3 строках:

Я хочу преобразовать данную таблицу в плоский вид (скрин неполный):

Я смог решить эту задачу следующим способом:

df = pd.read_excel(filepath, header=[0, 1, 2], engine='openpyxl')
df_melt = df.melt(id_vars=[('Товар', 'Unnamed: 0_level_1', 'Категория'),
            ('Товар', 'Unnamed: 1_level_1',     'Бренд'),
            ('Товар', 'Unnamed: 2_level_1',   'Артикул'),
            ('Поставщик', 'Unnamed: 3_level_1', 'Поставщик'),
            ('Поставщик', 'Unnamed: 4_level_1',    'Статус')])
df_melt.rename(columns={('Товар', 'Unnamed: 0_level_1', 'Категория'): 'Категория',
('Товар', 'Unnamed: 1_level_1', 'Бренд'): 'Бренд',
('Товар', 'Unnamed: 2_level_1', 'Артикул'): 'Артикул',
('Поставщик', 'Unnamed: 3_level_1', 'Поставщик'): 'Поставщик',
('Поставщик', 'Unnamed: 4_level_1', 'Статус'): 'Статус',
'variable_0': 'Торговая точка', 'variable_1': 'Месяц', 'variable_2': 'Тип документа', 'value': 'Значение'}, inplace=True)

Загвоздка в том, что мне нужно решение, которое будет срабатывать в случае, если будет добавлен или удален столбец в группе "Товар" или "Поставщик", при этом структура заголовков с Торговыми точками справа остается неизменной (но могут добавиться новые точки).

Как решить данную задачу? Также буду рад любым комментариям по упрощению решения.

Ссылку на файл прилагаю.

Answer 1

Обрабатываем все столбцы до первого столбца с наименованием "Торговая точка ..." динамически:

i = df.columns.get_level_values(0).str.contains(r"^Торговая точка").argmax()
cols = pd.MultiIndex.from_tuples(
    df.columns[:i].map(lambda tup: (tup[2],"","")).to_list() + df.columns[i:].to_list()
)
col_map = dict(
    variable_0="Торговая точка",
    variable_1="Месяц",
    variable_2="Тип документа",
    value="Значение"
)
res = df.melt(id_vars=df.columns.to_list()[:i])
res.columns = res.columns.map(lambda c: c[0] if isinstance(c, tuple) else col_map.get(c))

результат:

In [226]: res
Out[226]:
          Товар    Товар  Товар Поставщик Поставщик    Торговая точка      Месяц Тип документа  Значение
0   Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 1 2020-01-01        Приход       100
1   Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 1 2020-01-01        Приход       100
2   Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 1 2020-01-01        Приход       100
3   Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 1 2020-01-01        Приход       100
4   Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 1 2020-01-01       Продажа       200
5   Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 1 2020-01-01       Продажа       200
6   Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 1 2020-01-01       Продажа       200
7   Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 1 2020-01-01       Продажа       200
8   Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 1 2020-01-01          Сток       150
9   Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 1 2020-01-01          Сток       150
10  Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 1 2020-01-01          Сток       150
11  Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 1 2020-01-01          Сток       150
12  Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 2 2020-01-01        Приход       200
13  Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 2 2020-01-01        Приход       200
14  Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 2 2020-01-01        Приход       200
15  Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 2 2020-01-01        Приход       200
16  Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 2 2020-01-01       Продажа       300
17  Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 2 2020-01-01       Продажа       300
18  Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 2 2020-01-01       Продажа       300
19  Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 2 2020-01-01       Продажа       300
20  Категория 1  Бренд 1      1   Ромашка  Активный  Торговая точка 2 2020-01-01          Сток       100
21  Категория 1  Бренд 2      2   Ромашка  Активный  Торговая точка 2 2020-01-01          Сток       100
22  Категория 1  Бренд 3      3   Ромашка  Активный  Торговая точка 2 2020-01-01          Сток       100
23  Категория 1  Бренд 4      4   Ромашка  Активный  Торговая точка 2 2020-01-01          Сток       100

БЛОГ НА HUSL

Как читать Excel таблицы с составным заголовком?

Ответы (1 шт):