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

1. Зачем объединять данные?

В современном мире данных довольно редко можно встретить всю нужную информацию в одном файле. Чаще всего данные разбросаны по нескольким таблицам и файлам, и для полноценного анализа их нужно объединять. Например, у вас могут быть данные о продажах за каждый месяц в отдельных файлах. Чтобы проанализировать общие тренды, нужно объединить эти данные в одну таблицу.

Кроме того, объединение данных помогает:

• Сэкономить время на ручной сборке отчетов.
• Уменьшить количество ошибок, связанных с ручным переносом данных.
• Автоматизировать рабочий процесс анализа данных.

2. Методы объединения данных

Конкатенация данных

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

import pandas as pd

# Создадим два DataFrame с похожей структурой
data1 = {
    'Product': ['Apples', 'Bananas'],
    'Sales': [100, 150]
}

data2 = {
    'Product': ['Cherries', 'Dates'],
    'Sales': [75, 200]
}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# Вертикальная конкатенация (добавляем строки)
combined = pd.concat([df1, df2], ignore_index=True)
print(combined)

В результате мы получим объединенный DataFrame со всеми строками из обоих источников. Обратите внимание на параметр ignore_index=True, который сбрасывает индексы в итоговом DataFrame.

Объединение данных с использованием метода merge

Когда у вас есть данные, которые нужно объединить на основе общих столбцов (например, id или Product), на помощь приходит метод merge. Это как свидание для ваших данных — они должны иметь что-то общее, чтобы пойти на него.

# Создаем пример данных
sales_data = {
    'Product': ['Apples', 'Bananas', 'Cherries'],
    'Sales': [100, 150, 75]
}

price_data = {
    'Product': ['Apples', 'Bananas', 'Dates'],
    'Price': [1.2, 0.8, 3.0]
}

sales_df = pd.DataFrame(sales_data)
price_df = pd.DataFrame(price_data)

# Объединяем DataFrame по столбцу 'Product'
merged_df = pd.merge(sales_df, price_df, on='Product', how='inner')
print(merged_df)

Несмотря на то, что у нас есть информация по каждому продукту, merge возвращает только те строки, где есть совпадение по ключевому столбцу Product.

Разберем типы объединений:

• inner (по умолчанию): возвращает только те строки, которые присутствуют в обоих DataFrame.
• outer: возвращает все строки, заполняя отсутствующие значения NaN.
• left: возвращает все строки из левого DataFrame и соответствующие строки из правого.
• right: возвращает все строки из правого DataFrame и соответствующие строки из левого.

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

Теперь, когда мы освежили в памяти как работает объединение в pandas, давайте применим это знание на практике и объединим данные из нескольких листов одного Excel-файла.

# Читаем Excel-файл с несколькими листами
excel_file = pd.ExcelFile('sales_data.xlsx')

# Список имен всех листов
sheet_names = excel_file.sheet_names

# Читаем данные из каждого листа в отдельный DataFrame и помещаем их в список
dataframes = [pd.read_excel(excel_file, sheet_name=sheet_name) for sheet_name in sheet_names]

# Проводим вертикальную конкатенацию всех DataFrame
all_sales_data = pd.concat(dataframes, ignore_index=True)

print(all_sales_data)

Здесь мы читаем все листы из Excel-файла в отдельные DataFrame, а затем соединяем их в один. Мощно, не правда ли?

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

На практике вы можете столкнуться с ситуацией, когда данные распределены по нескольким файлам. Рассмотрим, как автоматизировать процесс их объединения.

import glob

# Путь к файлам
file_path = r'data/sales_*.xlsx'

# Получаем список всех файлов, соответствующих шаблону
files = glob.glob(file_path)

# Читаем данные из каждого файла в отдельный DataFrame и помещаем их в список
dataframes = [pd.read_excel(file) for file in files]

# Проводим вертикальную конкатенацию всех DataFrame
all_files_data = pd.concat(dataframes, ignore_index=True)

print(all_files_data)

Мы используем библиотеку glob для получения списка файлов, соответствующих указанному шаблону, и затем объединяем данные из каждого файла в один DataFrame.

3. Реальные примеры использования

Объединение данных для бизнес-анализа

Предположим, вы аналитик, работающий с данными о продажах за каждый месяц в отдельном файле. С помощью вышеупомянутых техник вы можете с легкостью объединить данные для анализа годовых трендов и сделать актуальные выводы.

Автоматизация отчетов

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

Обработка данных из разных источников

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

4. Типичные ошибки и их исправление

Частая ошибка — это несовпадение названий столбцов в объединяемых DataFrame. Убедитесь, что столбцы, по которым вы объединяете данные, действительно одинаковы и в случае необходимости используйте параметры left_on и right_on в методе merge.

Иногда данные могут содержать отсутствующие значения после объединения. Используйте методы заполнения значениями fillna или удаление dropna, чтобы бороться с этой проблемой.

Если ваши данные содержат дубликаты, метод concat может привести к получению более длинного DataFrame, чем вы ожидаете. В этом случае используйте метод drop_duplicates для очистки.

Теперь, когда мы осветили методы и приемы объединения данных в pandas, у вас есть инструменты для того, чтобы справляться с реальными задачами автоматизации и анализа данных. Используйте эти знания во благо!