Python pandas excel листы

Чтение и запись файлов Excel (XLSX) в Python

Pandas можно использовать для чтения и записи файлов Excel с помощью Python. Это работает по аналогии с другими форматами. В этом материале рассмотрим, как это делается с помощью DataFrame.

Помимо чтения и записи рассмотрим, как записывать несколько DataFrame в Excel-файл, как считывать определенные строки и колонки из таблицы и как задавать имена для одной или нескольких таблиц в файле.

Установка Pandas

Для начала Pandas нужно установить. Проще всего это сделать с помощью pip .

Если у вас Windows, Linux или macOS:

pip install pandas # или pip3

В процессе можно столкнуться с ошибками ModuleNotFoundError или ImportError при попытке запустить этот код. Например:

ModuleNotFoundError: No module named 'openpyxl'

В таком случае нужно установить недостающие модули:

pip install openpyxl xlsxwriter xlrd # или pip3

Запись в файл Excel с python

Будем хранить информацию, которую нужно записать в файл Excel, в DataFrame . А с помощью встроенной функции to_excel() ее можно будет записать в Excel.

Сначала импортируем модуль pandas . Потом используем словарь для заполнения DataFrame :

 
import pandas as pd 
 
df = pd.DataFrame( 'FC Bayern München', 'FC Barcelona', 'Juventus'], 
'League': ['English Premier League (1)', 'Spain Primera Division (1)', 
'English Premier League (1)', 'German 1. Bundesliga (1)', 
'Spain Primera Division (1)', 'Italian Serie A (1)'], 
'TransferBudget': [176000000, 188500000, 90000000, 
100000000, 180500000, 105000000]>) 
 
Ключи в словаре — это названия колонок. А значения станут строками с информацией.
 
Теперь можно использовать функцию to_excel() для записи содержимого в файл. Единственный аргумент — это путь к файлу:
 
А вот и созданный файл Excel:
 
 
Стоит обратить внимание на то, что в этом примере не использовались параметры. Таким образом название листа в файле останется по умолчанию — «Sheet1». В файле может быть и дополнительная колонка с числами. Эти числа представляют собой индексы, которые взяты напрямую из DataFrame.
 
Поменять название листа можно, добавив параметр sheet_name в вызов to_excel() :
 
 
df.to_excel('./teams.xlsx', sheet_name='Budgets', index=False) 
 
Также можно добавили параметр index со значением False , чтобы избавиться от колонки с индексами. Теперь файл Excel будет выглядеть следующим образом:
 
 
Запись нескольких DataFrame в файл Excel
 
Также есть возможность записать несколько DataFrame в файл Excel. Для этого можно указать отдельный лист для каждого объекта:
 
 
salaries1 = pd.DataFrame( 'Salary': [560000, 220000, 125000]>) 
 
salaries2 = pd.DataFrame( 'Salary': [370000, 270000, 240000]>) 
 
salaries3 = pd.DataFrame( 'Salary': [160000, 260000, 250000]>) 
 
salary_sheets =  
writer = pd.ExcelWriter('./salaries.xlsx', engine='xlsxwriter') 
 
for sheet_name in salary_sheets.keys(): 
salary_sheets[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False) 
 
writer.save() 
 
Здесь создаются 3 разных DataFrame с разными названиями, которые включают имена сотрудников, а также размер их зарплаты. Каждый объект заполняется соответствующим словарем.
 
Объединим все три в переменной salary_sheets , где каждый ключ будет названием листа, а значение — объектом DataFrame .
 
Дальше используем движок xlsxwriter для создания объекта writer . Он и передается функции to_excel() .
 
Перед записью пройдемся по ключам salary_sheets и для каждого ключа запишем содержимое в лист с соответствующим именем. Вот сгенерированный файл:
 
 
Можно увидеть, что в этом файле Excel есть три листа: Group1, Group2 и Group3. Каждый из этих листов содержит имена сотрудников и их зарплаты в соответствии с данными в трех DataFrame из кода.
 
Параметр движка в функции to_excel() используется для определения модуля, который задействуется библиотекой Pandas для создания файла Excel. В этом случае использовался xslswriter , который нужен для работы с классом ExcelWriter . Разные движка можно определять в соответствии с их функциями.
 
В зависимости от установленных в системе модулей Python другими параметрами для движка могут быть openpyxl (для xlsx или xlsm) и xlwt (для xls). Подробности о модуле xlswriter можно найти в официальной документации.
 
Наконец, в коде была строка writer.save() , которая нужна для сохранения файла на диске.
 
Чтение файлов Excel с python
 
По аналогии с записью объектов DataFrame в файл Excel, эти файлы можно и читать, сохраняя данные в объект DataFrame . Для этого достаточно воспользоваться функцией read_excel() :
 
Источник
 
Чтение таблиц Excel в Pandas
 
 
Хотя многие Data Scientist’ы больше привыкли работать с CSV-файлами, на практике очень часто приходится сталкиваться с обычными Excel-таблицами. Поэтому сегодня мы расскажем, как читать Excel-файлы в Pandas, а также рассмотрим основные возможности Python-библиотеки OpenPyXL для чтения метаданных ячеек.
 
Дополнительные зависимости для возможности чтения Excel таблиц
 
Для чтения таблиц Excel в Pandas требуются дополнительные зависимости:
 
 
xlrd поддерживает старые и новые форматы MS Excel [1];
 
OpenPyXL поддерживает новые форматы MS Excel (.xlsx) [2];
 
ODFpy поддерживает свободные форматы OpenDocument (.odf, .ods и .odt) [3];
 
pyxlsb поддерживает бинарные MS Excel файлы (формат .xlsb) [4].
 
 
Мы рекомендуем установить только OpenPyXL, поскольку он нам пригодится в дальнейшем. Для этого в командной строке прописывается следующая операция:
 
Затем в Pandas нужно указать путь к Excel-файлу и одну из установленных зависимостей. Python-код выглядит следующим образом:
 
import pandas as pd pd.read_excel(io='temp1.xlsx', engine='openpyxl') # Name Age Weight 0 Alex 35 87 1 Lesha 57 72 2 Nastya 21 64
 
Читаем несколько листов
 
Excel-файл может содержать несколько листов. В Pandas, чтобы прочитать конкретный лист, в аргументе нужно указать sheet_name . Можно указать список названий листов, тогда Pandas вернет словарь (dict) с объектами DataFrame:
 
dfs = pd.read_excel(io='temp1.xlsx', engine='openpyxl', sheet_name=['Sheet1', 'Sheet2']) dfs #
 
Если таблицы в словаре имеют одинаковые атрибуты, то их можно объединить в один DataFrame. В Python это выглядит так:
 
pd.concat(dfs).reset_index(drop=True) Name Age Weight 0 Alex 35 87 1 Lesha 57 72 2 Nastya 21 64 3 Gosha 43 95 4 Anna 24 65 5 Lena 22 78
 
Указание диапазонов
 
Таблицы могут размещаться не в самом начале, а как, например, на рисунке ниже. Как видим, таблица располагается в диапазоне A:F.
 
 
Чтобы прочитать такую таблицу, нужно указать диапазон в аргументе usecols . Также дополнительно можно добавить header — номер заголовка таблицы, а также nrows — количество строк, которые нужно прочитать. В аргументе header всегда передается номер строки на единицу меньше, чем в Excel-файле, поскольку в Python индексация начинается с 0 (на рисунке это номер 5, тогда указываем 4):
 
pd.read_excel(io='temp1.xlsx', engine='openpyxl', usecols='D:F', header=4, # в excel это №5 nrows=3) # Name Age Weight 0 Gosha 43 95 1 Anna 24 65 2 Lena 22 78
 
Читаем таблицы в OpenPyXL
 
Pandas прочитывает только содержимое таблицы, но игнорирует метаданные: цвет заливки ячеек, примечания, стили таблицы и т.д. В таком случае пригодится библиотека OpenPyXL. Загрузка файлов осуществляется через функцию load_workbook , а к листам обращаться можно через квадратные скобки:
 
from openpyxl import load_workbook wb = load_workbook('temp2.xlsx') ws = wb['Лист1'] type(ws) # openpyxl.worksheet.worksheet.Worksheet
 
 
Допустим, имеется Excel-файл с несколькими таблицами на листе (см. рисунок выше). Если бы мы использовали Pandas, то он бы выдал следующий результат:
 
pd.read_excel(io='temp2.xlsx', engine='openpyxl') # Name Age Weight Unnamed: 3 Name.1 Age.1 Weight.1 0 Alex 35 87 NaN Tanya 25 66 1 Lesha 57 72 NaN Gosha 43 77 2 Nastya 21 64 NaN Tolya 32 54
 
Можно, конечно, заняться обработкой и привести таблицы в нормальный вид, а можно воспользоваться OpenPyXL, который хранит таблицу и его диапазон в словаре. Чтобы посмотреть этот словарь, нужно вызвать ws.tables.items . Вот так выглядит Python-код:
 
ws.tables.items() wb = load_workbook('temp2.xlsx') ws = wb['Лист1'] ws.tables.items() # [('Таблица1', 'A1:C4'), ('Таблица13', 'E1:G4')]
 
Обращаясь к каждому диапазону, можно проходить по каждой строке или столбцу, а внутри них – по каждой ячейке. Например, следующий код на Python таблицы объединяет строки в список, где первая строка уходит на заголовок, а затем преобразует их в DataFrame:
 
dfs = [] for table_name, value in ws.tables.items(): table = ws[value] header, *body = [[cell.value for cell in row] for row in table] df = pd.DataFrame(body, columns=header) dfs.append(df)
 
Если таблицы имеют одинаковые атрибуты, то их можно соединить в одну:
 
pd.concat(dfs) # Name Age Weight 0 Alex 35 87 1 Lesha 57 72 2 Nastya 21 64 0 Tanya 25 66 1 Gosha 43 77 2 Tolya 32 54
 
Сохраняем метаданные таблицы
 
Как указано в коде выше, у ячейки OpenPyXL есть атрибут value , который хранит ее значение. Помимо value , можно получить тип ячейки ( data_type ), цвет заливки ( fill ), примечание ( comment ) и др.
 
 
Например, требуется сохранить данные о цвете ячеек. Для этого мы каждую ячейку с числами перезапишем в виде , где RGB — значение цвета в формате RGB (red, green, blue). Python-код выглядит следующим образом:
 
# _TYPES = data = [] for row in ws.rows: row_cells = [] for cell in row: cell_value = cell.value if cell.data_type == 'n': cell_value = f"," row_cells.append(cell_value) data.append(row_cells)
 
Первым элементом списка является строка-заголовок, а все остальное уже значения таблицы:
 
pd.DataFrame(data[1:], columns=data[0]) # Name Age Weight 0 Alex 35,00000000 87,00000000 1 Lesha 57,00000000 72,FFFF0000 2 Nastya 21,FF00A933 64,00000000
 
Теперь представим атрибуты в виде индексов с помощью метода stack , а после разобьём все записи на значение и цвет методом str.split :
 
(pd.DataFrame(data[1:], columns=data[0]) .set_index('Name') .stack() .str.split(',', expand=True) ) # 0 1 Name Alex Age 35 00000000 Weight 87 00000000 Lesha Age 57 00000000 Weight 72 FFFF0000 Nastya Age 21 FF00A933 Weight 64 0000000
 
Осталось только переименовать 0 и 1 на Value и Color, а также добавить атрибут Variable, который обозначит Вес и Возраст. Полный код на Python выглядит следующим образом:
 
(pd.DataFrame(data[1:], columns=data[0]) .set_index('Name') .stack() .str.split(',', expand=True) .set_axis(['Value', 'Color'], axis=1) .rename_axis(index=['Name', 'Variable']) .reset_index() ) # Name Variable Value Color 0 Alex Age 35 00000000 1 Alex Weight 87 00000000 2 Lesha Age 57 00000000 3 Lesha Weight 72 FFFF0000 4 Nastya Age 21 FF00A933 5 Nastya Weight 64 00000000
 
Ещё больше подробностей о работе с таблицами в Pandas, а также их обработке на реальных примерах Data Science задач, вы узнаете на наших курсах по Python в лицензированном учебном центре обучения и повышения квалификации IT-специалистов в Москве.
 
Источник
 
Читайте также:  Porter stemmer russian python