🧹 Как использовать Python для очистки и подготовки данных в Excel

Или: как перестать бояться грязных таблиц и начать жить

Как использовать Python для очистки и подготовки данных в Excel

Введение

Представьте: вы открываете Excel-файл, а там полный бардак. Пропуски, дубли, странные значения вроде "N/A" или "###", названия колонок со смещением, форматы дат в стиле «04.12.23» и текст в числовых столбцах.

Знакомо? 🤯

Вместо того, чтобы сражаться с этой таблицей вручную, давайте научимся автоматически очищать и готовить данные с помощью Python и библиотеки pandas. Это не только проще, но и круто: почувствуешь себя магом, который превращает хаос в структуру. 🧙‍♂️

Что будем использовать?

• pandas — наш главный инструмент для работы с таблицами.
• openpyxl — библиотека для чтения Excel-файлов.
• Excel-файл с грязными данными — назовем его raw_sales.xlsx.

Установка библиотек (если ещё не установлены):

pip install pandas openpyxl

📂 Сценарий: что за данные?

В файле raw_sales.xlsx у нас таблица продаж, но:

• Есть пустые строки и столбцы.
• Названия колонок в первой строке таблицы, но сдвинуты.
• Есть дубли строк.
• Есть строки с «нечисловыми» значениями.
• Есть пропуски и мусор типа N/A, не указано, --.

Наша цель — превратить этот хаос в аккуратный, чистый датафрейм, готовый к анализу. 💪

🧪 Шаг 1: Загружаем файл

import pandas as pd

# Загружаем Excel-файл, указываем движок
df = pd.read_excel('raw_sales.xlsx', engine='openpyxl')

# Смотрим, что загрузилось
print(df.head(10))

🕵️ Зачем смотреть head(10)? Потому что сюрпризы обычно прячутся в первых строках.

🧹 Шаг 2: Убираем полностью пустые строки и столбцы

# Удаляем пустые строки
df.dropna(how='all', inplace=True)

# Удаляем пустые столбцы
df.dropna(axis=1, how='all', inplace=True)

🗑 how='all' — удаляет строки/столбцы только если всё в них пустое. Частично пустые пока оставим.

🏷️ Шаг 3: Названия колонок

Часто в Excel названия колонок могут быть не в первой строке, а во второй или третьей. Допустим, у нас правильные заголовки на второй строке.

# Обновим заголовки: возьмём вторую строку как названия колонок
df.columns = df.iloc[1]

# Удалим первые две строки — они больше не нужны
df = df.iloc[2:].reset_index(drop=True)

🏗️ Мы используем вторую строку (индекс 1) как заголовки, а остальное очищаем.

🧼 Шаг 4: Приводим в порядок имена столбцов

# Переименовываем колонки: удаляем лишние пробелы и символы
df.columns = [str(col).strip().replace('\n', ' ') for col in df.columns]

✂️ Очень часто в Excel попадаются названия типа " Продажи \n (руб)" — чистим!

❌ Шаг 5: Удаляем дубликаты

# Удаляем полные дубликаты строк
df.drop_duplicates(inplace=True)

🔁 Бывает, кто-то скопировал данные дважды. Это быстро лечится.

❓ Шаг 6: Работа с пропущенными значениями

Сначала посмотрим, где у нас пропуски:

# Количество пропусков по каждому столбцу
print(df.isnull().sum())

Теперь можно либо удалить строки, либо заполнить:

# Заполняем пропуски в колонке "Регион" значением "Не указан"
df['Регион'] = df['Регион'].fillna('Не указан')

# Пропуски в числовых столбцах заменим на 0
df['Продажи (₽)'] = pd.to_numeric(df['Продажи (₽)'], errors='coerce')
df['Продажи (₽)'] = df['Продажи (₽)'].fillna(0)

🔧 errors='coerce' превращает мусор типа "abc" в NaN, а потом мы это заменяем на 0.

🗓️ Шаг 7: Приведение дат к нормальному виду

# Преобразуем столбец "Дата" в формат datetime
df['Дата'] = pd.to_datetime(df['Дата'], errors='coerce')

# Проверим строки с некорректной датой
print(df[df['Дата'].isnull()])

📅 Если в дате написано "Когда-нибудь", оно превратится в NaT (Not a Time).

📦 Шаг 8: Финальная проверка

# Проверим типы данных
print(df.dtypes)

# Сохраняем очищенные данные в новый файл
df.to_excel('clean_sales.xlsx', index=False)

✅ На этом этапе таблица — чистая, структурированная, без мусора и готова к анализу.

🎉 Итого: что мы сделали?

• Загрузили Excel-файл;
• Удалили пустые строки и дубли;
• Привели заголовки в порядок;
• Очистили и обработали пропуски;
• Преобразовали даты и числовые значения;
• Сохранили готовый результат.

Теперь ваш файл больше не выглядит как поле битвы между Excel и логикой. 🎯

🔮 Бонус: превращаем в функцию

Хочешь использовать это снова и снова?

def clean_excel(file_path, output_path):
df = pd.read_excel(file_path, engine='openpyxl')
df.dropna(how='all', inplace=True)
df.dropna(axis=1, how='all', inplace=True)
df.columns = df.iloc[1]
df = df.iloc[2:].reset_index(drop=True)
df.columns = [str(col).strip().replace('\n', ' ') for col in df.columns]
df.drop_duplicates(inplace=True)

if 'Регион' in df.columns:
df['Регион'] = df['Регион'].fillna('Не указан')

if 'Продажи (₽)' in df.columns:
df['Продажи (₽)'] = pd.to_numeric(df['Продажи (₽)'], errors='coerce').fillna(0)

if 'Дата' in df.columns:
df['Дата'] = pd.to_datetime(df['Дата'], errors='coerce')

df.to_excel(output_path, index=False)
print(f'Файл сохранён как: {output_path}')

📦 Теперь можешь применять к любому Excel-файлу — быстро, чисто и надёжно.

Заключение

Очистка данных — это не самая весёлая часть аналитики, но одна из самых важных. Грязные данные → грязные выводы. А благодаря pandas ты можешь:

• быстро навести порядок;
• автоматизировать рутину;
• и сэкономить себе кучу времени (и нервов).