Python для анализа данных Excel: от загрузки до продвинутой обработки

Расширенные примеры анализа Excel в Python

В этом разделе представлены неочевидные и редко используемые техники, которые могут пригодиться при работе с Excel-данными.

1. Чтение нескольких листов одновременно с разными настройками

file = 'report.xlsx'
# Чтение всех листов в словарь
sheets = pd.read_excel(file, sheet_name=None)  # возвращает OrderedDict
for name, df in sheets.items():
    print(f'Лист {name}: {df.shape}')

# Чтение нескольких выбранных листов с разными заголовками
sel = pd.read_excel(file, sheet_name=['Лист1', 'Лист3'], header=[0,1])

Параметр sheet_name=None загружает все листы. header=[0,1] означает, что первые две строки используются как многоуровневый заголовок. Это полезно для отчетов со сложными шапками.

2. Применение пользовательских функций к группам (GroupBy.apply)

def custom_agg(group):
    # Пример: если количество товара больше 10, возвращаем среднюю цену, иначе - медиану
    if group['Количество'].sum() > 10:
        return group['Цена'].mean()
    else:
        return group['Цена'].median()

result = df.groupby('Товар').apply(custom_agg).reset_index(name='Цена_особая')
print(result)

  Товар  Цена_особая
0     А        500.0
1     Б        300.0
2     В        700.0

Такой подход позволяет реализовать бизнес-логику, не укладывающуюся в стандартные агрегации.

3. Использование rolling windows для скользящих расчетов (временные ряды из Excel)

# Предположим, что данные отсортированы по дате
df_sorted = df.sort_values('Дата')
df_sorted['Скользящее_среднее_3'] = df_sorted['Количество'].rolling(window=3).mean()
print(df_sorted[['Дата','Количество','Скользящее_среднее_3']])

        Дата  Количество  Скользящее_среднее_3
0 2024-01-01          10                  NaN
1 2024-01-02          15                  NaN
2 2024-01-03           8           11.000000

Метод rolling требует числовых значений и упорядоченных данных. Первые (window-1) значений будут NaN.

4. Объединение данных из нескольких Excel-файлов с разными столбцами

import glob

files = glob.glob('data_*.xlsx')
df_list = []
for file in files:
    df_temp = pd.read_excel(file)
    df_list.append(df_temp)

# Конкатенация с выравниванием по общим столбцам (outer join)
df_combined = pd.concat(df_list, ignore_index=True, sort=False)
print(df_combined.shape)

Если столбцы в файлах различаются, concat создаст все уникальные столбцы, заполняя отсутствующие NaN.

5. Векторизованные операции с помощью NumPy для ускорения расчетов

# Создаем новый столбец 'Сумма' = количество * цена с учетом скидки
df['Сумма'] = df['Количество'].values * df['Цена'].values - df['Скидка'].values
# Или с использованием np.multiply
np_mult = np.multiply(df['Количество'].values, df['Цена'].values)
df['Сумма_alt'] = np_mult - df['Скидка'].values

# Применение универсальных функций (ufunc) к целому столбцу
df['Цена_округленная'] = np.round(df['Цена'], -2)  # округление до сотен
print(df.head())

       Дата Товар  Количество  Цена  Скидка  Сумма  Сумма_alt  Цена_округленная
0 2024-01-01     А          10   500     0.0  5000.0     5000.0             500.0
1 2024-01-02     Б          15   300    30.0  4470.0     4470.0             300.0
2 2024-01-03     В           8   700     0.0  5600.0     5600.0             700.0

Использование .values извлекает массив NumPy, что ускоряет операции по сравнению с чистыми Pandas-выражениями.

6. Экспорт в Excel с несколькими листами и настройкой условного форматирования

with pd.ExcelWriter('styled_output.xlsx', engine='xlsxwriter') as writer:
    # Лист с данными
    df.to_excel(writer, sheet_name='Данные', index=False)
    
    # Лист со сводной таблицей
    pivot = pd.pivot_table(df, index='Товар', values='Сумма', aggfunc='sum')
    pivot.to_excel(writer, sheet_name='Сводка')
    
    # Применение условного форматирования на первом листе
    workbook = writer.book
    worksheet = writer.sheets['Данные']
    # Форматирование для ячеек, где Количество > 10
    green_format = workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
    worksheet.conditional_format('C2:C100', {'type': 'cell', 'criteria': '>', 'value': 10, 'format': green_format})

Этот пример показывает, как создавать профессиональные отчеты прямо из Python.

7. Работа с пропусками: интерполяция и заполнение вперед/назад

# Интерполяция временного ряда
df['Количество'] = df['Количество'].interpolate(method='linear')

# Заполнение пропусков предыдущим значением (ffill) и следующим (bfill)
df['Цена'] = df['Цена'].fillna(method='ffill').fillna(method='bfill')

# Заполнение средним по группе (например, по товару)
df['Цена'] = df.groupby('Товар')['Цена'].transform(lambda x: x.fillna(x.mean()))

Выбор метода зависит от природы данных: для временных рядов часто подходит интерполяция, для категориальных - заполнение по группе.

8. Создание сводной таблицы с несколькими агрегациями и subtotals

pivot_multi = pd.pivot_table(df, 
                             index=['Товар', 'Дата'], 
                             values=['Количество', 'Сумма'],
                             aggfunc={'Количество': 'sum', 'Сумма': 'mean'},
                             margins=True, margins_name='Итого')
print(pivot_multi)

              Количество       Сумма
Товар Дата                          
А     2024-01-01          10  5000.000000
Б     2024-01-02          15  4470.000000
В     2024-01-03           8  5600.000000
Итого                      33  5023.333333

Параметр margins=True добавляет строку/столбец «Итого» с суммами/средними.

9. Использование регулярных выражений для очистки текстовых столбцов Excel

# Допустим, столбец 'Код' содержит лишние символы
import re

def clean_code(code):
    # Оставить только цифры и буквы
    return re.sub(r'[^A-Za-z0-9]', '', str(code))

df['Код_чист'] = df['Код'].apply(clean_code)

Это часто требуется при импорте данных из неструктурированных отчетов.

10. Оптимизация памяти при чтении больших Excel-файлов с указанием типов

dtypes = {'Количество': 'int8', 'Цена': 'float16', 'Товар': 'category'}
df = pd.read_excel('large.xlsx', dtype=dtypes)
# Экономия памяти особенно заметна для столбцов с повторяющимися строками (category)
print(df.memory_usage(deep=True))

Указание dtype позволяет явно задать типы и уменьшить потребление оперативной памяти, что критично при работе на ограниченных ресурсах.