Импорт данных в Python – чтение и запись файлов Excel
Всем привет! Уверен, что подавляющее большинство читателей этой статьи интересует анализ данных. Иначе вас бы здесь не было! Но для начала данные необходимо импортировать в Python. Помимо баз данных, средством решения этой задачи являются файлы. В сфере науки о данных чаще всего используются файлы в формате csv (значения, разделённые запятыми), но нередко можно встретить и формат xlsx, потому что почти во всех компаниях Excel по-прежнему является самой популярной программой для редактирования таблиц.
Я покажу вам, как читать файлы Excel с помощью Python. При правильном выборе библиотек это совсем не сложно.
Python: чтение файлов Excel
Вариантов довольно много. То есть, добавить файлы xlsx в Python можно несколькими способами. Если вам нужно просто и быстро достать данные из файла Excel, то лучше всего подойдёт библиотека pandas. Если требуется более высокий уровень контроля, или вы собираетесь считывать формулы, стоит воспользоваться пакетами xlrd или openpyxl. Фактически, для доступа к данным Excel библиотека pandas использует xlrd в фоновом режиме.
Файл Jupyter Notebook с кодом для всех трёх вариантов можно найти по этой ссылке.
Импорт файлов xlsx с помощью Pandas
Одним из популярных способов чтения файлов Excel в Python является функция read_excel из pandas. Если вы занимаетесь “наукой о данных” и работаете с Python, библиотека pandas в любом случае незаменима. Функция read_excel способна обрабатывать файлы xls (старый формат) и xlsx, а также записывать содержимое в DataFrame.
import pandas as pd
df = pd.read_excel("Имя файла")У этой функции есть целый ряд параметров, наиболее важные из которых перечислены ниже:
sheet_name = 0- Здесь можно ввести числовое значение (начиная с 0) или название электронной таблицы. По умолчанию считывается первая таблица в файле. Если в качестве значения указать список, то будет возвращён словарь датафреймов (см. ниже).
skiprows = None- Если вам требуется пропустить первые строки (обычное дело с таблицами Excel, поскольку очень часто сверху в них находится заголовок и описание), сделать это можно с помощью данного параметра, просто указав, сколько строк вы хотите пропустить. Как обычно в Python, отсчёт начинается с нуля, т.е. если вам нужно начать с 4-й строки, используйте skiprows=3.
header = 0- Строка, содержащая “хедер”, т.е. заголовки столбцов. При установленном значении “0” используется первая строка. Если у столбцов не должно быть заголовков, установите значение “None”.
names = None- Этот параметр позволяет передать в функцию список с именами столбцов
usecols = None- Если вы хотите вывести только конкретные столбцы, их нужно указать здесь. Лучший вариант в этом случае – передать список с номерами или названиями столбцов.
dtype = None- Если вам надо использовать типы данных, которые могут отличаться от автоматически распознанных, подставьте к этому параметру словарь, где указаны конкретные типы данных для конкретных столбцов.
nrows = None- Если не хотите считывать все строки, можете ограничить их число с помощью параметра nrows.
Перечисленные параметры должны охватить большинство возможных сценариев использования. Если вам потребуется более специализированная информация, посмотрите справочные материалы по API read_excel.
Импорт одновременно нескольких листов файла Excel
Как указано в описании параметра sheet_name, он позволяет указать список номеров или названий рабочих листов. В этом случае вы получите не датафрейм, а словарь датафреймов.
Map = pd.read_excel("Example-Excel.xlsx", sheet_name=['Таблица1','Таблица2','Таблица3'], skiprows=3)
print(type(Map))
print(Map.keys())
print(Map['Таблица3'].head())Импорт данных Excel с помощью пакета XLRD
При использовании пакета xlrd вы получаете гораздо больше контроля над таблицами Excel, но и разобраться с ним немного сложнее. Однако в настоящее время разработка прекращена. На его странице в GitHub говорится, что лучше использовать openpyxl.
Помимо xlrd, предназначенной для чтения, есть ещё библиотеки xlwt для записи файлов xls (Excel 97 – 2003) и xlutils для редактирования современных файлов Excel.
Первым делом надо открыть файл
wb = xlrd.open_workbook("Example-Excel.xlsx")С помощью функции sheet_names вы получаете названия таблиц
sheet_names = wb.sheet_names()После этого к таблице можно обратиться по её названию, например:
Tab1 = wb.sheet_by_name('Таблица1')API xlrd имеет очень логичную структуру и людям, обладающим некоторым опытом взаимодействия с API, несложно будет в нём разобраться.
- В модуль book входят все инструменты, которые нужны для работы со всей книгой. Класс xlrd.book.Book хранит её содержимое.
- В модуль sheet входят все инструменты для работы с отдельными рабочими листами. И соответствующий класс xlrd.sheet.Sheet хранит содержимое рабочего листа
- Стоит также упомянуть модуль formatting, который позволяет считать форматирование файла
- Модуль formula (обратите внимание) работает не с формулами каждой ячейки, а с менеджером имен. Если вы хотите прочитать формулы из отдельных ячеек, это делается с помощью openpyxl, а
- xldate помогает разобраться с датой и временем
Пакет Openpyxl: чтение файлов Excel
Если вам нужно не просто прочитать данные, а сделать что-то ещё, можете воспользоваться пакетом openpyxl. Хотя лично мне больше по душе pandas.
Здесь открыть и просмотреть книгу Excel тоже очень легко.
from openpyxl import load_workbook
wb2 = load_workbook("Example-Excel.xlsx")Флаг data_only позволяет указать, что будет считываться – формулы или вычисляемые значения. По умолчанию стоит data_only = False, поэтому считываются формулы.
К сожалению, пакет не поддерживает изображения и диаграммы, поэтому с помощью openpyxl не получится модифицировать шаблоны или вставлять данные в существующие дэшборды на основе Excel. Подходящим инструментом для этого является xlwings, разговор о котором будет чуть позже.
Но давайте вернёмся к openpyxl. Каждая книга Excel содержит рабочие листы, к которым можно легко обращаться по имени
print(wb2.sheetnames)
ws = wb2['Таблица1']
for i in ws.values:
print(i)Если надо преобразовать данные в датафрейм pandas, это делается следующим образом. В приведённом примере таблица начинается с четвёртой строки.
import pandas as pd
df = pd.DataFrame(ws.values)
df.columns = df.iloc[[3]].values.tolist()[0]
df = df[4:]
df.head()Опять же, вот ещё ссылка на документацию openpyxl. Также вас может заинтересовать глава 12 в бесплатной книге Automate the boring stuff, там речь идёт как раз о применении пакета openpyxl.
Python: сохранение файлов Excel
Логично предположить, что помимо чтения файлов Excel нам в какой-то момент понадобится возможность их сохранять. Файл Jupyter Notebook с кодом для всех трёх вариантов можно найти по этой ссылке.
Запись датафрейма в файл XLSX с помощью Pandas
Опять же, с помощью pandas записать датафрейм в файл Excel очень легко. За это отвечает функция to_excel, как в следующем примере
diamonds.to_excel("Diamonds.xlsx", sheet_name='Diamonds')Для индивидуализации можно использовать следующие параметры. На самом деле их больше, но остальные нам пока не нужны.
na_rep = ""- Как должны отображаться отсутствующие значения. По умолчанию стоит пустая строка.
float_format=None- Здесь можно задать формат для количества знаков после запятой.
columns = None- Если вы хотите вывести не все столбцы, это можно указать здесь.
header = True- Определяет, будут ли выводиться названия столбцов. В качестве значения также можно указать список названий.
index = True- Этот флаг позволяет указать, нужно записывать индекс строки или нет.
index_label = None- Название индексного столбца.
startrow = 0- Строка в файле Excel, начиная с которой будет записываться таблица.
startcol = 0- Столбец в файле Excel, начиная с которого будет записываться таблица.
Запись нескольких таблиц в один файл Excel
Для pandas и это не является проблемой. Нужно только вооружиться функцией ExcelWriter. Вот как это работает:
with pd.ExcelWriter('test.xlsx') as writer:
iris.to_excel(writer, sheet_name='iris')
tips.to_excel(writer, sheet_name='tips')Запись файлов Excel с помощью XLWT
Запись файла xls (обратите внимание, что модуль xlwt не поддерживает новый формат xlsx, который используется в Excel начиная с версии 2007).
import xlwt
from datetime import datetime
wb = xlwt.Workbook()
ws = wb.add_sheet('Таблица1')
datumStyle = xlwt.easyxf(num_format_str='DD.MM.YYYY')
ws.write(0, 0, 1)
ws.write(0, 1, 1)
ws.write(0, 2, xlwt.Formula("A1+B1"))
ws.write(1, 0, datetime.now(),datumStyle)
ws.write(1, 2, 3.1415)
wb.save('Example-xlwt.xls')Запись файлов Excel с помощью Openpyxl
Запись файла тоже не вызывает никаких проблем:
from openpyxl import Workbook
wb = Workbook()# ...Заполнение книги…
wb.save("Example-openpyxl.xlsx")Помимо прочего, вы можете делать, например, следующее:
# Активировать первую таблицу
ws = wb.active# Писать можно прямо в ячейках
ws['C1'] = 3.1415# Также можно прикреплять целые строки
ws.append([1, 2, 3])# Ячейки с датами тоже работают
import datetime
ws['E2'] = datetime.datetime.now()Заключение по теме работы с файлами Excel с использованием Python
Подводя итог, можно сказать следующее:
Если вам требуется только читать таблицы или записывать их в файлы Excel, ваш вариант – pandas. В нём простая в использовании функция импорта/экспорта, да и фирменный тип данных DataFrame от pandas является идеальным для дальнейшей обработки данных.
Если вам нужен более высокий уровень контроля, и вы хотите считывать или редактировать отдельные ячейки, а также форматировать их и т.д., вам подойдёт openpyxl. Если по какой-то неведомой причине вам всё ещё приходится использовать формат xls (некоторые компании до сих пор от него не отказались), возьмите xlrt/xlwt/xlutils.
Надеюсь, вы почерпнули из этой статьи что-то полезное. Напишите в комментариях, какой из пакетов вы предпочитаете использовать. А если вы хотите помочь распространению знаний из области Data Science и поддержать меня в этом начинании, то поделитесь данным постом у себя в Twitter или Facebook.
Удачи в работе с Excel!
https://databraineo.com/ki-training-resources/python/datenimport-in-python-excel-dateien-einlesen-und-schreiben/