Операции с файлами Excel в Pandas

Pandas предоставляет широкие возможности работы с файлами Excel, помогая нам удобно читать и записывать.xlsи.xlsxфайлы, поддерживая сложные операции, такие как несколько листов, индексация и выбор столбцов. Это незаменимый инструмент в анализе данных.

Действие Метод Описание
Чтение файла Excel pd.read_excel() Прочитать файл Excel и вернуть DataFrame
Запись DataFrame в Excel DataFrame.to_excel() Записать DataFrame в файл Excel
Загрузка файла Excel pd.ExcelFile() Загрузите файл Excel и получите доступ к нескольким листам
Используйте ExcelWriter для записи нескольких листов pd.ExcelWriter() Запишите несколько DataFrames на разные листы в одном файле Excel

pd.read_excel() - 读取 Excel 文件

pd.read_excel()Метод используется для чтения данных из файла Excel и загрузки их в DataFrame. Он поддерживает чтение.xlsи.xlsxформатированных файлов.

Синтаксис выглядит так:

pandas.read_excel(io, sheet_name=0, *, header=0, names=None, index_col=None, usecols=None, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, parse_dates=False, date_parser=<no_default>, date_format=None, thousands=None, decimal='.', comment=None, skipfooter=0, storage_options=None, dtype_backend=<no_default>, engine_kwargs=None)

Описание параметра:

  • io: Это обязательный параметр, задающий путь или файловый объект файла Excel для чтения.

  • sheet_name=0: Укажите имя или индекс листа для чтения. Значение по умолчанию — 0, то есть первый лист.

  • header=0: Укажите строку, которую нужно использовать в качестве имен столбцов. Значение по умолчанию — 0, то есть первая строка.

  • names=None: Список, используемый для указания имен столбцов. Если он указан, то переопределит имена столбцов в файле.

  • index_col=None: Укажите столбец, который нужно использовать в качестве индекса строк. Это может быть имя столбца или число.

  • usecols=None: Укажите столбцы для чтения. Это может быть список имен столбцов или список индексов столбцов.

  • dtype=None: Укажите типы данных столбцов. Это может быть в формате словаря, где ключи — имена столбцов, а значения — типы данных.

  • engine=None: Укажите движок синтаксического анализа. По умолчанию используетсяNone, pandas автоматически выберет.

  • converters=None: Словарь функций, используемых для преобразования данных.

  • true_values=None: Укажите значения, которые следует обрабатывать как логическиеTrueзначения.

  • false_values=None: Укажите значения, которые следует обрабатывать как логическиеFalseзначения.

  • skiprows=None: Укажите количество строк для пропуска или список строк для пропуска.

  • nrows=None: Укажите количество строк для чтения.

  • na_values=None: Укажите значения, которые следует обрабатывать как пропущенные.

  • keep_default_na=True: Укажите, следует ли обрабатывать пропущенные значения по умолчанию (такие какNaN) разбирать какNA。

  • na_filter=True: Укажите, следует ли преобразовывать данные вNA。

  • verbose=False: Укажите, следует ли выводить подробную информацию о ходе выполнения.

  • parse_dates=False: Укажите, следует ли разбирать даты.

  • date_parser=<no_default>: Функция, используемая для разбора дат.

  • date_format=None: Укажите формат дат.

  • thousands=None: Укажите разделитель тысяч.

  • decimal='.': Укажите символ десятичной точки.

  • comment=None: Укажите символ комментария.

  • skipfooter=0: Укажите количество строк, которые нужно пропустить в конце файла.

  • storage_options=None: Словарь параметров для облачного хранилища.

  • dtype_backend=<no_default>: Укажите бэкенд типа данных.

  • engine_kwargs=None: Словарь дополнительных параметров для передачи движку.

В этой статье используетсяexample_pandas_data.xlsxв качестве примера. Вы можетескачать example_pandas_data.xlsxдля тестирования.

Пример

import pandas as pd

# Чтение файла data.xlsx
df = pd.read_excel('example_pandas_data.xlsx')

# Вывести прочитанный DataFrame
print(df)

Вывод:

      表格 1 Unnamed: 1   Unnamed: 2
0     Name        Age         City
1    Alice         25     New York
2      Bob         30  Los Angeles
3  Charlie         35      Chicago

read_excel по умолчанию читает первый лист (sheet_name=0). Если предположить, что файл data.xlsx содержит только один лист, прочитанные данные сохраняются в DataFrame.

Если файл data.xlsx содержит несколько листов, можно указать sheet_name, чтобы прочитать данные с конкретного листа, напримерpd.read_excel('data.xlsx', sheet_name='Sheet1')。

Пример

import pandas as pd

# Прочитать первый лист по умолчанию
df = pd.read_excel('data.xlsx')
print(df)

# Прочитать содержимое указанного листа (по имени листа)
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df)

# Прочитать несколько листов, возвращая словарь
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])
print(dfs)

# Настройте названия столбцов и пропустите первые две строки
df = pd.read_excel('data.xlsx', header=None, names=['A', 'B', 'C'], skiprows=2)
print(df)

DataFrame.to_excel() - запись DataFrame в файл Excel

to_excel()Метод используется для записи DataFrame в файл Excel, поддерживая.xlsи.xlsxформаты.

Синтаксис выглядит так:

DataFrame.to_excel(excel_writer, *, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, inf_rep='inf', freeze_panes=None, storage_options=None, engine_kwargs=None)

Описание параметра:

  • excel_writer: Это обязательный параметр, указывающий путь к файлу Excel или файловый объект для записи.

  • sheet_name='Sheet1': Укажите имя листа для записи; по умолчанию —'Sheet1'。

  • na_rep='': Укажите строку, представляющую отсутствующие значения (NaN) в файле Excel. По умолчанию — пустая строка.

  • float_format=None: Укажите формат чисел с плавающей запятой. Если этоNone, используется формат Excel по умолчанию.

  • columns=None: Укажите столбцы для записи. Если этоNone, записываются все столбцы.

  • header=True: Укажите, записывать ли имена столбцов в качестве первой строки. Если этоFalse, имена столбцов не записываются.

  • index=True: Укажите, записывать ли индекс в качестве первого столбца. Если этоFalse, индекс не записывается.

  • index_label=None: Укажите метку для столбца индекса. Если этоNone, метка индекса не записывается.

  • startrow=0: Укажите номер строки, с которой начинать запись. По умолчанию — строка 0.

  • startcol=0: Укажите номер столбца, с которого начинать запись. По умолчанию — столбец 0.

  • engine=None: Укажите движок, используемый при записи в файл Excel. По умолчаниюNone, pandas автоматически выберет.

  • merge_cells=True: Укажите, объединять ли ячейки. Если этоTrue, ячейки с одинаковым значением объединяются.

  • inf_rep='inf': Укажите строку, представляющую бесконечные значения в файле Excel. По умолчанию —'inf'。

  • freeze_panes=None: Укажите позицию закреплённых областей. Если она равнаNone, области не закреплены.

  • storage_options=None: Словарь параметров для облачного хранилища.

  • engine_kwargs=None: Словарь дополнительных параметров для передачи движку.

Пример

import pandas as pd

# Создание простого DataFrame
df = pd.DataFrame({
'Name': ['Alice', 'Bob', 'Charlie'],
'Age': [25, 30, 35],
'City': ['Нью-Йорк', 'Лос-Анджелес', 'Чикаго']
})

# Записать DataFrame в файл Excel, в лист 'Sheet1'
df.to_excel('output.xlsx', sheet_name='Sheet1', index=False)

# Записать несколько листов с помощью ExcelWriter
with pd.ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
df.to_excel(writer, sheet_name='Sheet2', index=False)

ExcelFile - Загрузка файла Excel

ExcelFileЭто класс для чтения файлов Excel. Он позволяет работать с несколькими листами и получать доступ к данным в них без повторного открытия файла.

Синтаксис выглядит так:

excel_file = pd.ExcelFile('data.xlsx')

Общие методы:

Метод Описание функции
sheet_names Возвращает список имён всех листов в файле
parse(sheet_name) Разобрать указанный лист и вернуть DataFrame
close() Закрыть файл, чтобы освободить ресурсы

Пример

import pandas as pd

# Используйте ExcelFile для загрузки файла Excel
excel_file = pd.ExcelFile('data.xlsx')

# Просмотреть имена всех листов
print(excel_file.sheet_names)

# Прочитать указанный лист
df = excel_file.parse('Sheet1')
print(df)

# Закрыть файл
excel_file.close()

ExcelWriter — запись файла Excel

ExcelWriter — это класс, предоставляемый pandas, используемый для записи объектов DataFrame или Series в файлы Excel. С помощью ExcelWriter можно записать несколько рабочих листов в один файл Excel и получить более гибкий контроль над процессом записи.

Синтаксис выглядит так:

pandas.ExcelWriter(path, engine=None, date_format=None, datetime_format=None, mode='w', storage_options=None, if_sheet_exists=None, engine_kwargs=None)

Описание параметра:

  • path: Это обязательный параметр, указывающий путь, URL или файловый объект файла Excel для записи. Это может быть локальный путь к файлу, путь к удалённому хранилищу (например, S3), ссылка URL или уже открытый файловый объект.。

  • engine: Это необязательный параметр, используемый для указания движка для записи в файл Excel. Если онNone, pandas автоматически выберет доступный движок (по умолчанию он предпочитаетopenpyxl, а если он недоступен, выбирает другой доступный движок). Распространенные движки включают'openpyxl'(для.xlsxфайлов),'xlsxwriter'(обеспечивает расширенное форматирование и возможности построения диаграмм),'odf'(для форматов OpenDocument, таких как.ods) и т.д.。

  • date_format: Это необязательный параметр, задающий строку формата для дат, записываемых в файл Excel, например"YYYY-MM-DD"。

  • datetime_formatЭто необязательный параметр, который задает строку формата для объектов datetime, записываемых в файл Excel, например"YYYY-MM-DD HH:MM:SS"。

  • modeЭто необязательный параметр, по умолчанию равный'w', указывающий режим записи. Если установлено в'a', это означает режим добавления, добавление данных в существующий файл (поддерживается только некоторыми движками, такими какopenpyxl）。

  • storage_optionsЭто необязательный параметр, используемый для указания дополнительных параметров подключения к бэкенду хранилища, таких как данные аутентификации, права доступа и т. д., применимо для записи в удалённое хранилище (например, S3, GCS)。

  • if_sheet_existsЭто необязательный параметр, по умолчанию равный'error', определяющий поведение, когда лист уже существует. Возможные варианты:'error'(вызвать ошибку),'new'(создать новый лист),'replace'(заменить содержимое существующего листа),'overlay'(перезаписать поверх существующего листа)。

  • engine_kwargsЭто необязательный параметр, используемый для передачи других именованных аргументов движку. Эти аргументы передаются функциям соответствующего движка, напримерxlsxwriter.Workbook(file, **engine_kwargs)илиopenpyxl.Workbook(**engine_kwargs)ожидание。

Создание объекта ExcelWriter:

Пример

with ExcelWriter('output.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1')

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

Запись в несколько листов:

Вы можете использовать один и тот же объект ExcelWriter для записи разных DataFrame на разные листы в одном файле Excel.

Пример

df1 = pd.DataFrame([["AAA", "BBB"]], columns=["Spam", "Egg"])  
df2 = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
with pd.ExcelWriter("path_to_file.xlsx") as writer:
    df1.to_excel(writer, sheet_name="Sheet1")  
    df2.to_excel(writer, sheet_name="Sheet2")

Настройка формата даты или datetime:

Пример

from datetime import date, datetime  
df = pd.DataFrame(
    [
        [date(2014, 1, 31), date(1999, 9, 24)],
        [datetime(1998, 5, 26, 23, 33, 4), datetime(2014, 2, 28, 13, 5, 13)],
    ],
    index=["Date", "Datetime"],
    columns=["X", "Y"],
)  
with pd.ExcelWriter(
    "path_to_file.xlsx",
    date_format="YYYY-MM-DD",
    datetime_format="YYYY-MM-DD HH:MM:SS"
) as writer:
    df.to_excel(writer)

Добавление содержимого в существующий файл Excel:

Пример

with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sheet3")

Использование параметра if_sheet_exists для замены существующего листа:

Пример

with ExcelWriter(
    "path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="replace",
) as writer:
    df.to_excel(writer, sheet_name="Sheet1")

Запись нескольких DataFrames на один и тот же рабочий лист; обратите внимание, что параметр if_sheet_exists нужно установить в overlay:

Пример

with ExcelWriter("path_to_file.xlsx",
    mode="a",
    engine="openpyxl",
    if_sheet_exists="overlay",
) as writer:
    df1.to_excel(writer, sheet_name="Sheet1")
    df2.to_excel(writer, sheet_name="Sheet1", startcol=3)

Сохранение файла Excel в памяти:

Пример

import io
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])
buffer = io.BytesIO()
with pd.ExcelWriter(buffer) as writer:
    df.to_excel(writer)

Упаковка файла Excel в сжатый zip-файл:

Пример

import zipfile  
df = pd.DataFrame([["ABC", "XYZ"]], columns=["Foo", "Bar"])  
with zipfile.ZipFile("path_to_file.zip", "w") as zf:
    with zf.open("filename.xlsx", "w") as buffer:
        with pd.ExcelWriter(buffer) as writer:
            df.to_excel(writer)

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

Пример

with pd.ExcelWriter(
    "path_to_file.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"nan_inf_to_errors": True}}
) as writer:
    df.to_excel(writer)

В режиме добавления engine_kwargs будет передан в load_workbook из openpyxl:

Пример

with pd.ExcelWriter(
    "path_to_file.xlsx",
    engine="openpyxl",
    mode="a",
    engine_kwargs={"keep_vba": True}
) as writer:
    df.to_excel(writer, sheet_name="Sheet2")
Другие расширения