Pandas и openpyxl: взаимодействие для обработки данных

Расширенные примеры работы с Pandas и openpyxl

Чтение Excel с пропуском строк и обработкой дат

Иногда данные начинаются не с первой строки, а даты представлены в нестандартном формате. Пример файла dates.xlsx с шапкой в строке 3 и датами в столбце B.

import pandas as pd
df = pd.read_excel('dates.xlsx', header=3, parse_dates=['Date'], engine='openpyxl')
print(df.dtypes)
print(df.head())

Date    datetime64[ns]
Value           float64
dtype: object
        Date  Value
0 2023-01-01  10.5
1 2023-01-02  12.3

Параметр parse_dates автоматически преобразует столбец в pandas Timestamp, а header сдвигает начало данных.

Запись DataFrame с условным форматированием через openpyxl

Допустим, требуется выделить красным ячейки, где значение меньше нуля. Сначала записываем DataFrame через Pandas, затем добавляем условное форматирование.

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

df = pd.DataFrame({'A': [1, -2, 3], 'B': [-4, 5, -6]})
with pd.ExcelWriter('cond_format.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False)
    book = writer.book
    sheet = book.active
    red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
    sheet.conditional_formatting.add('A2:C4',
        CellIsRule(operator='lessThan', formula=['0'], fill=red_fill))
wb.save('cond_format.xlsx')

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

Объединение данных из нескольких листов в один DataFrame

Часто Excel содержит по листу на месяц, и требуется свести все данные вместе. Использование sheet_name=None загружает все листы в словарь.

import pandas as pd
all_sheets = pd.read_excel('months.xlsx', sheet_name=None, engine='openpyxl')
df_all = pd.concat(all_sheets.values(), ignore_index=True)
print(len(df_all))  # общее количество строк

Если листы имеют разную структуру, перед объединением нужно привести их к единой схеме, добавив столбец с именем листа:

dfs = []
for name, sheet_df in all_sheets.items():
    sheet_df['Месяц'] = name
    dfs.append(sheet_df)
df_combined = pd.concat(dfs, ignore_index=True)

Добавление формул в ячейки при записи через Pandas + openpyxl

Формулы Excel (SUM, AVERAGE) можно добавить после записи данных. Пример: после таблицы чисел вставляется строка с суммой.

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame({'Числа': [10, 20, 30]})
with pd.ExcelWriter('formulas.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False, startrow=0)
    book = writer.book
    sheet = book.active
    last_row = len(df) + 1  # предполагаем, что данные начинаются с A1
    sheet.cell(row=last_row+1, column=1, value='=SUM(A2:A{})'.format(last_row))
    # можно добавить подпись
    sheet.cell(row=last_row, column=1, value='Итого')

Формула будет вычислена при открытии файла в Excel.

Изменение существующего файла без потери форматирования

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

import pandas as pd
from openpyxl import load_workbook

book = load_workbook('existing.xlsx')
sheet = book['Data']
data = sheet.values
columns = next(data)[0:]  # первая строка - заголовки
df = pd.DataFrame(data, columns=columns)
# модификация DataFrame
df['New_Col'] = df['Old_Col'] * 2

# Очистка листа и запись обновлённых данных
sheet.delete_rows(1, sheet.max_row)
for r_idx, row in enumerate(df.itertuples(index=False), 1):
    for c_idx, value in enumerate(row, 1):
        sheet.cell(row=r_idx, column=c_idx, value=value)

# Другие листы и их форматирование не затронуты
book.save('existing_updated.xlsx')

Этот метод позволяет сохранить стили, объединённые ячейки и прочие элементы, не поддерживаемые Pandas.