Вопрос или проблема
Рассмотрите следующую таблицу в Excel:
h1 h1_1 h1_2 h1_3
h2 h2_1 h2_2 h2_3
h3 h3_1 h3_2 h3_3
h4 h4_1 h4_2 h4_3
h5 h5_1 h5_2 h5_2
i1 i2 i3 h6 h6_1 h6_2 h6_2
i1_2 i1_3 foo
i2_2 i2_3 foo
i3_2 i3_3 foo 1
Эта таблица имеет 7 строк заголовков и 4 столбца индексов. Кроме того, индексы и заголовки имеют названия, и название заголовка h6 перекрывается с тем, что могло бы быть названием индекса i4.
Я разбираю это с помощью pandas следующим образом:
import pandas as pd
df_scratch = pd.read_excel('scratch2.xlsx', index_col=[0,1,2,3], header=[0,1,2,3,4,5,6])
Однако тогда, похоже, что первая строка пропускается:
df_scratch
h1 h1_1 h1_2 h1_3
h2 h2_1 h2_2 h2_3
h3 h3_1 h3_2 h3_3
Unnamed: 4_level_3 Unnamed: 5_level_3 Unnamed: 6_level_3
h4 h4_1 h4_2 h4_3
h5 h5_1 h5_2 h5_2
h6 h6_1 h6_2 h6_2
i2_1 i3_1 foo
NaN i2_2 i3_2 foo NaN NaN NaN
i2_3 i3_3 foo NaN NaN 1.0
Чтобы подтвердить, df_scratch.loc[pd.IndexSlice[:,'i1_2',:]] возвращает KeyError: 'i1_2', в то время как df_scratch.loc[pd.IndexSlice[:,'i2_2',:]] возвращает:
h1 h1_1 h1_2 h1_3
h2 h2_1 h2_2 h2_3
h3 h3_1 h3_2 h3_3
Unnamed: 4_level_3 Unnamed: 5_level_3 Unnamed: 6_level_3
h4 h4_1 h4_2 h4_3
h5 h5_1 h5_2 h5_2
h6 h6_1 h6_2 h6_2
i1_3 foo
NaN i2_3 foo NaN NaN NaN
Если я разбираю с одной строкой заголовка меньше, то последняя строка заголовка интерпретируется как данные:
import pandas as pd
df_scratch = pd.read_excel('scratch2.xlsx', index_col=[0,1,2,3], header=[0,1,2,3,4,5])
h1 h1_1 h1_2 h1_3
h2 h2_1 h2_2 h2_3
h3 h3_1 h3_2 h3_3
Unnamed: 4_level_3 Unnamed: 5_level_3 Unnamed: 6_level_3
h4 h4_1 h4_2 h4_3
h5 h5_1 h5_2 h5_2
i1 i2 i3 h6 h6_1 h6_2 h6_2
i1_2 i1_3 foo NaN NaN NaN
i2_2 i2_3 foo NaN NaN NaN
i3_2 i3_3 foo NaN NaN 1
Можно ли сразу прочитать правильное расположение, или мне нужно сначала прочитать файл Excel без каких-либо заголовков/индексов и добавить их после того, как я их прочитаю?
Ответ или решение
Непоследовательный разбор заголовков Excel в DataFrame pandas
Вопрос о корректном разборе заголовков в Excel-файлах с помощью библиотеки pandas часто возникает из-за сложной структуры данных. В данном случае у нас есть таблица с несколькими уровнями заголовков и индексами. Давайте подробно разберем, почему вы сталкиваетесь с проблемами и как эффективно решить эту задачу.
Структура таблицы
Вы представили таблицу Excel, которая имеет 7 уровень заголовков и 4 индекса:
h1 h1_1 h1_2 h1_3
h2 h2_1 h2_2 h2_3
h3 h3_1 h3_2 h3_3
h4 h4_1 h4_2 h4_3
h5 h5_1 h5_2 h5_2
i1 i2 i3 h6 h6_1 h6_2 h6_2
i1_2 i1_3 foo
i2_2 i2_3 foo
i3_2 i3_3 foo 1Проблемы с разбором
Когда вы вызываете pd.read_excel() с массивом заголовков [0, 1, 2, 3, 4, 5, 6], первый уровень заголовка пропускается, что приводит к возникновению строк с именем 'Unnamed: 4_level_3'. Это происходит из-за нехватки уникальности в заголовках и индексации.
При попытке использовать одну строку заголовков меньше (например, [0, 1, 2, 3, 4, 5]), вы замечаете, что один из уровней заголовка интерпретируется как строки данных. Это происходит из-за того, что отбирается слишком много строк заголовка с других уровней, и pandas не может правильно сопоставить их с индексами.
Рекомендации по решению
Для корректного разбора этой таблицы, вам нужно будет использовать более контрольный подход:
-
Сначала загрузите данные без указания заголовков и индексов.
import pandas as pd # Чтение данных без указания заголовков и индексов df_raw = pd.read_excel('scratch2.xlsx', header=None) -
Определите структуру заголовков и индексов вручную.
После загрузки данных, вы можете вручную создать многоуровневые индексы и заголовки.# Определение заголовков и индексов new_header = df_raw.iloc[0:7] # первые 7 строк как заголовки df_raw = df_raw[7:] # удаление заголовков из основной таблицы df_raw.columns = pd.MultiIndex.from_frame(new_header) # Установка индексов df_raw.set_index([0, 1, 2, 3], inplace=True) -
Проверьте корректность полученного DataFrame.
После этого, вы можете проверить, правильно ли загружены данные и как выглядят индексы.print(df_raw)
Заключение
Таким образом, учитывая сложность структуры вашего Excel-файла, рекомендуется сначала загружать данные без разборки заголовков. После этого вы сможете точно настраивать заголовки и индексы в соответствии с вашими потребностями. Убедитесь, что все заголовки уникальны, чтобы избежать путаницы между уровнями и индексами. Это позволит вам избежать проблем с разбросом данных при работе с многоуровневыми структурами.
Если у вас есть дополнительные вопросы о работе с pandas или обработки данных в Excel, не стесняйтесь задавать их!