Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Работа с Excel-файлами в Pandas Python: Pandas

Для работы с табличными данными часто используют продукт Microsoft Excel. В таблицы Excel помещают как списки покупок, так и отчетности компаний. Благодаря распространенности данного формата разработчики создали инструменты для автоматизации обработки данных.

Pandas является средством работы с табличными данными и умеет работать с файлами формата Excel-таблиц: .xls и .xlsx. И каждый разработчик должен уметь работать с такими форматами наравне с текстовыми файлами и файлами формата json и html.

В этом уроке мы познакомимся с основными методами библиотеки Pandas для работы с табличными данными в формате Microsoft Excel: .xls и .xlsx. Мы научимся их читать и записывать. Также мы разберем работу с файлами, в которых есть несколько листов, а также форматированию данных при записи.

Обработка Excel файлов в Python

Среди форматов файлов Excel наиболее популярными являются:

  • .xls — использовался в версиях Microsoft Excel до 2007
  • .xlsx — используется во всех версиях после 2007

Для работы с обоими типами в Python есть ряд открытых библиотек:

  • xlwt
  • openpyxl
  • XlsxWriter
  • xlrd

В библиотеке Pandas не реализован свой функционал работы с Excel-файлами, но есть единый интерфейс для работы с каждой из указанных выше библиотек.

Чтобы использовать этот функционал, нужно установить указанные библиотеки в окружение, в котором установлена библиотека Pandas. Библиотеки не являются взаимозаменяемыми и дополняют друг друга — лучше установить их все.

Чтение таблиц из Excel файлов

Чтобы читать файлы в Pandas, используется метод read_excel(). Ему на вход подается путь к читаемому файлу:

import pandas as pd
df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx')
print(df_orders.head())
# =>    weekday shop_1  shop_2  shop_3  shop_4
#     0     mon     7       1       7       8
#     1     tue     4       2       4       5
#     2     wed     3       5       2       3
#     3     thu     8       12      8       7
#     4     fri     15      11      13      9
#     5     sat     21      18      17      21
#     6     sun     25      16      25      17

В примере выше прочитан файл продаж четырех магазинов за неделю и размещен в объекте DataFrame. Pandas по умолчанию добавил столбец индексов — последовательность целых чисел от 0 до 6.

Чтобы указать, какой из столбцов является столбцом индексов, необходимо указать его номер в параметре index_col. В нашем случае это первый столбец, в котором указаны дни недели:

df_orders = pd.read_excel('data_read/Shop_orders_one_week.xlsx', index_col=0)
print(df_orders.head())
# =>    shop_1  shop_2  shop_3  shop_4
# weekday
# mon       7   1   7   8
# tue       4   2   4   5
# wed       3   5   2   3
# thu       8   12  8   7
# fri       15  11  13  9
# sat       21  18  17  21
# sun       25  16  25  17

Если перед таблицей некоторые строки содержали записи, то попытка прочтения не приведет к ожидаемому результату. Pandas будет стараться положить данные в строках до таблицы в качестве индексов столбцов:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx')
print(df_orders.head())
# => Orders by shop Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4
# 0            NaN        NaN        NaN        NaN        NaN
# 1        weekday     shop_1     shop_2     shop_3     shop_4
# 2            mon          7          1          7          8
# 3            tue          4          2          4          5
# 4            wed          3          5          2          3

Для корректного прочтения необходимо пропустить некоторое количество строк при прочтении. Для этого нужно использовать параметр skiprows и указать количество пропускаемых строк:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xlsx', skiprows=2)
print(df_orders.head())
# => weekday  shop_1  shop_2  shop_3  shop_4
#  0     mon       7       1       7       8
#  1     tue       4       2       4       5
#  2     wed       3       5       2       3
#  3     thu       8      12       8       7
#  4     fri      15      11      13       9

Итоговый вариант корректного чтения, где пропущены две строки и использован один столбец в качестве столбца индексов, выглядит следующим образом:

df_orders = pd.read_excel('data_read/Shop_orders_one_week_with_head.xls', skiprows=2, index_col=0)
print(df_orders.head())
# =>       shop_1  shop_2  shop_3  shop_4
# weekday
# mon           7       1       7       8
# tue           4       2       4       5
# wed           3       5       2       3
# thu           8      12       8       7
# fri          15      11      13       9

Запись таблиц в Excel файл

Также в Excel-файл можно записывать результаты работы программы. Эту задачу можно разделить на два типа по сложности используемого синтаксиса:

  • Быстрая запись на один лист — записывается одна таблица, которая будет размещена на одном листе файла Excel
  • Создание файла с несколькими листами — если результаты работы программы располагаются в нескольких итоговых таблицах, то для формирования единого файла Excel с несколькими листами потребуется применить определенные правила создания

Быстрая запись на один лист

В качестве результатов работы программы используем среднее по магазинам за неделю:

df_orders_mean = pd.DataFrame(df_orders.mean()).T.round(1)
df_orders_mean.index = ['mean']
print(df_orders_mean)
# =>    shop_1  shop_2  shop_3  shop_4
# mean    11.9     9.3    10.9    10.0

Сформируем итоговую таблицу на основе исходной и добавим аналитические результаты:

df_analitic_results = pd.concat([
    df_orders,
    df_orders_mean
])
print(df_analitic_results)
# =>    shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0     8.0
# tue      4.0     2.0     4.0     5.0
# wed      3.0     5.0     2.0     3.0
# thu      8.0    12.0     8.0     7.0
# fri     15.0    11.0    13.0     9.0
# sat     21.0    18.0    17.0    21.0
# sun     25.0    16.0    25.0    17.0
# mean    11.9     9.3    10.9    10.0

Чтобы быстро записать данную таблицу, достаточно воспользоваться методом to_excel(). Формат файла .xls или .xlsx необходимо указать в расширении файла. Pandas автоматически определит, какой библиотекой воспользоваться для конкретного формата:

df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xlsx')
df_analitic_results.to_excel('data_read/Shop_orders_one_week_analitics.xls')

Создание файла с несколькими листами

Чтобы задать имя листа, на котором располагается таблица, необходимо указать его в параметре sheet_name. В данном примере получится лист Total:

path_for_analitic_results = 'data_read/Shop_orders_one_week_analitics.xlsx'
df_analitic_results.to_excel(
    path_for_analitic_results,
    sheet_name='Total'
)

Попробуем добавить к сформированному файлу лист итогов только для первого магазина:

df_analitic_results[['shop_1']].to_excel(
    path_for_analitic_results,
    sheet_name='shop_1',
)

Все выполнено без ошибок, но в итоговом файле листа Total нет. Чтобы перезаписать файл и удалить предыдущий, вызовем функцию to_excel().

Для корректной записи или дозаписи нужно использовать следующую конструкцию. В одном файле запишем итоговую таблицу на один лист, а для каждого магазина создадим отдельный лист только с его итогами:

with pd.ExcelWriter(
        path_for_analitic_results,
        engine="xlsxwriter",
        mode='w') as excel_writer:
    # Add total df
    df_analitic_results.to_excel(excel_writer, sheet_name='Total')
    # Add all shop df results
    for shop_name in df_analitic_results.columns.to_list():
        df_analitic_results[[shop_name]].to_excel(excel_writer, sheet_name=shop_name)

В коде выше создается экземпляр класса ExcelWriter на "движке" библиотеки xlsxwriter. Далее мы используем инициализированный экземпляр excel_writer в качестве первого параметра метода to_excel(). Конструкция with...as... позволяет безопасно работать с потоком данных и закрыть файл, даже когда возникают ошибки записи.

Чтение таблиц из Excel файлов с несколькими листами

Чтобы прочитать файл с несколькими листами, не хватит метода read_excel(), поскольку будет прочитан только первый лист из файла:

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0)
print(df_analitic_results_from_file)
# =>    shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0       8
# tue      4.0     2.0     4.0       5
# wed      3.0     5.0     2.0       3
# thu      8.0    12.0     8.0       7
# fri     15.0    11.0    13.0       9
# sat     21.0    18.0    17.0      21
# sun     25.0    16.0    25.0      17
# mean    11.9     9.3    10.9      10

При этом можно прочитать конкретный лист, если указать его название в параметре sheet_name:

df_analitic_results_from_file = pd.read_excel(path_for_analitic_results, index_col=0, sheet_name='shop_1')
print(df_analitic_results_from_file)
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9

Чтобы прочитать несколько листов и не переоткрывать файл, достаточно использовать экземпляр класса ExcelFile и его метод parse(). В последнем указывается имя нужного листа и дополнительные параметры чтения, аналогичные методу read_excel().

excel_reader = pd.ExcelFile(path_for_analitic_results)
df_shop_1 = excel_reader.parse('shop_1', index_col=0)
df_shop_2 = excel_reader.parse('shop_2', index_col=0)
print(df_shop_1)
print(df_shop_2)
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9
#       shop_2
# mon      1.0
# tue      2.0
# wed      5.0
# thu     12.0
# fri     11.0
# sat     18.0
# sun     16.0
# mean     9.3

Данный подход для чтения файла Excel удобен, чтобы получить список всех листов. Для этого нужно посмотреть на атрибут sheet_names:

print(excel_reader.sheet_names)
# => ['Total', 'shop_1', 'shop_2', 'shop_3', 'shop_4']

Если использовать наработки выше, можно собрать словарь из датафреймов, в которых будут располагаться все таблицы файла. Чтобы получить нужный датафрейм, нужно обратиться к словарю по ключу с соответствующим названием листа:

sheet_to_df_map = {}
for sheet_name in excel_reader.sheet_names:
    sheet_to_df_map[sheet_name] = excel_reader.parse(sheet_name, index_col=0)
print(sheet_to_df_map['shop_1'])
print(sheet_to_df_map['Total'])
# =>    shop_1
# mon      7.0
# tue      4.0
# wed      3.0
# thu      8.0
# fri     15.0
# sat     21.0
# sun     25.0
# mean    11.9
#       shop_1  shop_2  shop_3  shop_4
# mon      7.0     1.0     7.0       8
# tue      4.0     2.0     4.0       5
# wed      3.0     5.0     2.0       3
# thu      8.0    12.0     8.0       7
# fri     15.0    11.0    13.0       9
# sat     21.0    18.0    17.0      21
# sun     25.0    16.0    25.0      17
# mean    11.9     9.3    10.9      10

Форматирование таблиц

За время своего развития Excel накопил довольно мощный функционал, чтобы анализировать и презентовать данные: создание графиков, цветовая подсветка результатов по условию, настройка шрифтов и многое другое.

В примере ниже мы форматируем итоговые аналитические данные: если значения в таблице превышают порог в одиннадцать заказов, то они раскрашиваются в один цвет, иначе — в другой. Цветовая дифференциация данных удобна, чтобы быстро оценивать результаты и искать закономерности в данных:

with pd.ExcelWriter(
        path_for_analitic_results,
        engine="xlsxwriter",
        mode='w') as excel_writer:

    # Add total df
    df_analitic_results.to_excel(excel_writer, sheet_name='Total')
    # Formatting total df
    threshold = 11
    workbook = excel_writer.book
    worksheet = excel_writer.sheets['Total']
    format1 = workbook.add_format({'bg_color': '#FFC7CD',
                                'font_color': '#9C0006'})
    format2 = workbook.add_format({'bg_color': '#C6EFCD',
                                'font_color': '#006100'})

    worksheet.conditional_format('B2:E9', {
            'type' : 'cell',
            'criteria' : '>=',
            'value' : threshold,
            'format' : format1}
    )
    worksheet.conditional_format('B2:E9', {
            'type' : 'cell',
            'criteria' : '<',
            'value' : threshold,
            'format' : format2}
    )

В примере выше используются методы движка xlsxwriter. Разбор всех возможностей форматирования данных при записи выходит за рамки данного урока. Можно глубже погрузиться в данную тему через документацию с примерами по следующей ссылке.

Выводы

В этом уроке мы познакомились с основными методами библиотеки Pandas для работы с табличными данными в формате Microsoft Excel: .xls, .xlsx. Мы научились их читать и записывать.

Также мы уделили внимание работе с файлами, в которых есть несколько листов, а также форматированию данных при записи. Набор этих навыков позволяет строить процессы по автоматизации обработки и анализу табличных данных, которые представляются в формате Excel.


Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
от 24 542 ₸ в месяц
новый
Сбор, анализ и интерпретация данных
9 месяцев
с нуля
Старт 28 ноября

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»