Продвинутый гайд: Работа с Excel таблицами в Pandas
В работе с данными часто приходится обрабатывать и анализировать таблицы в формате Excel. Библиотека Pandas, предоставляющая множество возможностей для работы с таблицами, в том числе и в формате Excel, позволяет нам делать это максимально эффективно.
Семен Лобачевский
В этом руководстве мы рассмотрим основные и продвинутые техники работы с Excel в Pandas с конкретными примерами кода.
Чтение Excel файлов в Pandas
Pandas позволяет считывать данные из Excel с помощью функции pd.read_excel(). Основные параметры:
sheet_name: имя или номер листа, который нужно загрузить.skiprows: количество строк, которые нужно пропустить в начале.usecols: список столбцов для загрузки.
import pandas as pd
# Чтение файла Excel и загрузка конкретного листа
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
Чтение нескольких листов одновременно
Часто возникает необходимость загрузить несколько листов в один DataFrame или в словарь DataFrames. С помощью аргумента sheet_name=None можно загрузить сразу все листы.
# Загрузка всех листов в словарь DataFrames
dfs = pd.read_excel('data.xlsx', sheet_name=None)
print(dfs.keys()) # Печать названий листов
Запись данных в Excel
Запись данных в Excel возможна с помощью метода to_excel(). Важно помнить, что если требуется записать несколько листов в один файл, то для этого нужно использовать ExcelWriter.
# Создаем ExcelWriter для записи на несколько листов
with pd.ExcelWriter('output.xlsx') as writer:
df.to_excel(writer, sheet_name='Sheet1')
another_df.to_excel(writer, sheet_name='Sheet2')
Обработка большого объема данных в Excel
Для больших файлов Pandas поддерживает параметр chunksize, который загружает данные порциями:
# Чтение Excel файла порциями
for chunk in pd.read_excel('large_data.xlsx', chunksize=1000):
process(chunk) # Функция обработки данных
Продвинутая фильтрация данных
Для фильтрации данных в Excel можно использовать функции Pandas, такие как query(), loc[], и iloc[].
# Фильтрация данных с помощью query
filtered_df = df.query('Age > 30 & Salary > 50000')
Группировка и агрегация
Метод groupby() позволяет агрегировать данные по столбцам:
# Группировка данных и подсчет средней зарплаты по департаментам
avg_salary = df.groupby('Department')['Salary'].mean()
Обработка пропущенных значений
Пропущенные значения можно обрабатывать с помощью методов fillna(), dropna() и других.
# Заполнение пропущенных значений медианой столбца
df['Salary'] = df['Salary'].fillna(df['Salary'].median())
Слияние и объединение таблиц
Если у вас есть несколько таблиц Excel, их можно объединить с помощью merge() или concat().
# Объединение таблиц по общему столбцу
merged_df = pd.merge(df1, df2, on='EmployeeID')
Использование стилей Pandas для форматирования
С помощью Styler можно форматировать таблицы перед экспортом.
# Применение стиля для подсветки максимальных значений
styled_df = df.style.highlight_max(axis=0)
styled_df.to_excel('styled_output.xlsx', engine='openpyxl')
Чтение и запись с сохранением формул
Обычно Pandas не сохраняет формулы при загрузке или записи данных в Excel, но, используя openpyxl, можно работать с ячейками, содержащими формулы, или вставлять их вручную при записи.
from openpyxl import load_workbook
import pandas as pd
# Чтение файла с формулами
wb = load_workbook('data_with_formulas.xlsx', data_only=False) # data_only=False для сохранения формул
sheet = wb['Sheet1']
df = pd.DataFrame(sheet.values)
Если необходимо добавить формулу при записи:
with pd.ExcelWriter('output_with_formula.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Sheet1', index=False)
ws = writer.sheets['Sheet1']
ws['C2'] = '=SUM(A2:B2)' # Пример формулы
Обработка сводных таблиц (Pivot Tables)
Сводные таблицы позволяют быстро агрегировать и обобщать данные. В Pandas есть аналог Excel-сводной таблицы с использованием pivot_table().
# Создание сводной таблицы с агрегированием
pivot_df = df.pivot_table(values='Salary', index='Department', columns='Gender', aggfunc='mean')
Использование условного форматирования
С помощью Styler Pandas можно применять условное форматирование, которое будет видно при экспорте таблицы в Excel.
# Условное форматирование: подсветка высоких значений в столбце "Salary"
styled_df = df.style.applymap(lambda x: 'background-color: yellow' if x > 50000 else '')
styled_df.to_excel('conditional_formatting.xlsx', engine='openpyxl')
Обработка нескольких файлов Excel
Когда требуется обработать несколько файлов Excel, можно использовать glob для загрузки всех файлов в указанной папке и объединения их в один DataFrame.
import pandas as pd
import glob
# Загрузка всех файлов Excel из папки и их объединение
files = glob.glob('data_folder/*.xlsx')
df_list = [pd.read_excel(file) for file in files]
combined_df = pd.concat(df_list, ignore_index=True)
Создание динамических диаграмм в Excel
Pandas можно использовать совместно с XlsxWriter для создания динамических диаграмм на основе таблиц.
# Пример создания диаграммы
with pd.ExcelWriter('chart_example.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Настройка и добавление диаграммы
chart = workbook.add_chart({'type': 'column'})
chart.add_series({
'categories': ['Sheet1', 1, 0, len(df), 0],
'values': ['Sheet1', 1, 1, len(df), 1],
'name': 'Salary'
})
worksheet.insert_chart('D2', chart)
Обработка данных из Excel как из базы данных (SQL-запросы)
С помощью библиотеки pandasql можно выполнять SQL-запросы к таблицам Excel.
import pandas as pd
from pandasql import sqldf
# Выполнение SQL-запроса к DataFrame
query = "SELECT * FROM df WHERE Salary > 50000"
result = sqldf(query, locals())
Использование Excel как источника данных для анализа и моделей машинного обучения
Иногда данные из Excel используются для подготовки моделей. Мы можем загружать данные, преобразовывать их и использовать для обучения моделей.
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
# Пример подготовки данных и обучения модели
X = df[['Experience', 'Education_Level']]
y = df['Salary']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
model = LinearRegression()
model.fit(X_train, y_train)
Автоматизация анализа и создания отчётов
Создание автоматических отчётов с использованием Pandas и ExcelWriter, например, с помощью регулярного расписания или запуска из командной строки.
def create_report(data_file, output_file):
df = pd.read_excel(data_file)
summary = df.groupby('Department').agg({'Salary': ['mean', 'sum'], 'EmployeeID': 'count'})
summary.columns = ['Average Salary', 'Total Salary', 'Employee Count']
with pd.ExcelWriter(output_file) as writer:
df.to_excel(writer, sheet_name='Data')
summary.to_excel(writer, sheet_name='Summary')
create_report('data.xlsx', 'automated_report.xlsx')
Удаление дубликатов и очистка данных
При работе с данными необходимо уметь эффективно убирать дубликаты и обрабатывать некорректные данные.
# Удаление дубликатов
df = df.drop_duplicates()
# Удаление строк с некорректными данными в определённых столбцах
df = df.dropna(subset=['Salary', 'Department'])
Эти продвинутые техники помогут вам работать с Excel в Pandas на новом уровне, автоматизируя рутинные задачи и повышая эффективность анализа данных. Использование данных методов вместе с базовыми операциями позволяет обрабатывать Excel-файлы как полноценные источники данных для анализа и отчётности.
