- Обработка Excel файлов в Python
- Чтение таблиц из Excel файлов
- Запись таблиц в Excel файл
- Чтение таблиц из Excel файлов с несколькими листами
- Форматирование таблиц
- Выводы
Для работы с табличными данными часто используют продукт Microsoft Excel. В таблицы Excel помещают как списки покупок, так и отчетности компаний. Благодаря распространенности данного формата разработчики создали инструменты для автоматизации обработки данных.
Pandas является средством работы с табличными данными и умеет работать с файлами формата Excel-таблиц: .xls и .xlsx. И каждый разработчик должен уметь работать с такими форматами наравне с текстовыми файлами и файлами формата json и html.
В этом уроке мы познакомимся с основными методами библиотеки Pandas для работы с табличными данными в формате Microsoft Excel: .xls и .xlsx. Мы научимся их читать и записывать. Также мы разберем работу с файлами, в которых есть несколько листов, а также форматированию данных при записи.
Обработка Excel файлов в Python
Среди форматов файлов Excel наиболее популярными являются:
- .xls — использовался в версиях Microsoft Excel до 2007
- .xlsx — используется во всех версиях после 2007
Для работы с обоими типами в Python есть ряд открытых библиотек:
xlwtopenpyxlXlsxWriterxlrd
В библиотеке 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.
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
Для полного доступа к курсу нужен базовый план
Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.
