Python против Excel: как автоматизировать таблицы и сэкономить время

import pandas as pd

# Чтение Excel-файла
df = pd.read_excel('sales_data.xlsx')

# Базовая аналитика
total_sales = df['Revenue'].sum()
average_by_region = df.groupby('Region')['Revenue'].mean()

# Запись результатов в новый файл
with pd.ExcelWriter('sales_report.xlsx') as writer:
df.to_excel(writer, sheet_name='Raw Data')
average_by_region.to_excel(writer, sheet_name='Regional Analysis')

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

# Создание новой книги
wb = Workbook()
ws = wb.active

# Заполнение данными
ws['A1'] = 'Квартал'
ws['B1'] = 'Продажи'
quarters = ['Q1', 'Q2', 'Q3', 'Q4']
sales = [12500, 15600, 14800, 18900]

for i, (q, s) in enumerate(zip(quarters, sales), 2):
ws[f'A{i}'] = q
ws[f'B{i}'] = s

# Форматирование
header_font = Font(bold=True, size=12)
header_fill = PatternFill("solid", fgColor="DDDDDD")

for cell in ws[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')

# Сохранение
wb.save('formatted_sales.xlsx')

import pandas as pd

# Чтение всего файла
df = pd.read_excel('data.xlsx')

# Чтение конкретного листа
df = pd.read_excel('data.xlsx', sheet_name='Sales 2023')

# Чтение нескольких листов
sheets = pd.read_excel('data.xlsx', sheet_name=['Q1', 'Q2', 'Q3'])

# Чтение определенного диапазона
df = pd.read_excel('data.xlsx', usecols="A:C", skiprows=2, nrows=100)

# Базовая информация о данных
print(df.info())
print(df.describe())

# Выборка данных
recent_sales = df[df['Date'] > '2023-01-01']
high_value = df[df['Amount'] > 10000]

# Агрегация и группировка
by_region = df.groupby('Region').sum()
monthly = df.resample('M', on='Date').sum()

# Трансформации
df['Discount'] = df['Amount'] * 0.1
df['Category'] = df['Product'].apply(lambda x: 'Premium' if 'Gold' in x else 'Standard')

# Простая запись в файл
df.to_excel('results.xlsx', index=False)

# Запись нескольких таблиц на разные листы
with pd.ExcelWriter('quarterly_report.xlsx') as writer:
df.to_excel(writer, sheet_name='Summary', index=False)
by_region.to_excel(writer, sheet_name='Regional')
monthly.to_excel(writer, sheet_name='Monthly Trends')

# Чтение только нужных столбцов
df = pd.read_excel('huge_dataset.xlsx', usecols=['Date', 'Customer', 'Amount'])

# Чтение данных порциями
chunks = []
for chunk in pd.read_excel('huge_dataset.xlsx', chunksize=10000):
# обработка каждого фрагмента
processed = some_calculation(chunk)
chunks.append(processed)

# Объединение обработанных фрагментов
result = pd.concat(chunks)

# Преобразование типов данных
df['Date'] = pd.to_datetime(df['Date'])
df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce') # 'coerce' заменяет ошибки на NaN

# Очистка данных
df = df.dropna(subset=['Customer', 'Amount']) # удаление строк с отсутствующими значениями
df['Product'] = df['Product'].str.strip().str.upper() # стандартизация текста

# Чтение нескольких файлов
files = ['sales_jan.xlsx', 'sales_feb.xlsx', 'sales_mar.xlsx']
all_data = pd.concat([pd.read_excel(f) for f in files])

# Слияние таблиц по ключу (аналог VLOOKUP в Excel)
customers = pd.read_excel('customers.xlsx')
orders = pd.read_excel('orders.xlsx')
report = pd.merge(orders, customers, on='CustomerID', how='left')

# Выявление и обработка дубликатов
duplicates = df[df.duplicated(subset=['OrderID', 'ProductID'], keep=False)]
df = df.drop_duplicates(subset=['OrderID', 'ProductID'], keep='first')

# Обнаружение выбросов с помощью статистики
Q1 = df['Revenue'].quantile(0.25)
Q3 = df['Revenue'].quantile(0.75)
IQR = Q3 – Q1
outliers = df[(df['Revenue'] < Q1 – 1.5 * IQR) | (df['Revenue'] > Q3 + 1.5 * IQR)]

# Заполнение пропущенных значений с помощью интеллектуальных методов
df['Price'] = df['Price'].fillna(df.groupby('Category')['Price'].transform('median'))

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Border, Side
from openpyxl.formatting.rule import ColorScaleRule

# Запись данных с pandas
df.to_excel('styled_report.xlsx', index=False)

# Дополнительное форматирование с openpyxl
wb = load_workbook('styled_report.xlsx')
ws = wb.active

# Создание цветовых шкал
ws.conditional_formatting.add('C2:C100', 
ColorScaleRule(start_color='FFAAAA', end_color='AAFFAA', start_type='min', end_type='max'))

# Специальное форматирование для определенных значений
red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')
for row in ws['B2:B100']:
for cell in row:
if cell.value and cell.value < 0:
cell.fill = red_fill
cell.font = Font(color='FF0000', bold=True)

wb.save('styled_report.xlsx')

import matplotlib.pyplot as plt
import seaborn as sns
from io import BytesIO

# Создание визуализации
plt.figure(figsize=(10, 6))
sns.barplot(data=df, x='Category', y='Sales', hue='Region')
plt.title('Sales by Category and Region')
plt.tight_layout()

# Сохранение графика в Excel
buffer = BytesIO()
plt.savefig(buffer)
with pd.ExcelWriter('report_with_charts.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Data', index=False)
worksheet = writer.sheets['Data']
worksheet.insert_image('G2', '', {'image_data': buffer})

# Пример скрипта для автоматизации
import schedule
import time
import os
import pandas as pd
from datetime import datetime

def process_daily_reports():
# Поиск новых файлов в директории
files = [f for f in os.listdir('incoming_reports') if f.endswith('.xlsx')]
if not files:
print(f"{datetime.now()}: No new files to process")
return

# Обработка каждого файла
all_data = []
for file in files:
df = pd.read_excel(f'incoming_reports/{file}')
# Здесь ваша логика обработки
all_data.append(df)
os.rename(f'incoming_reports/{file}', f'processed/{file}')

# Объединение и сохранение результатов
combined = pd.concat(all_data)
combined.to_excel(f'daily_summaries/summary_{datetime.now().strftime("%Y%m%d")}.xlsx', index=False)
print(f"{datetime.now()}: Processed {len(files)} files")

# Планирование выполнения каждый день в 8 утра
schedule.every().day.at("08:00").do(process_daily_reports)

# Цикл выполнения задач
while True:
schedule.run_pending()
time.sleep(60)

from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler

# Подготовка данных для кластеризации клиентов
features = df[['TotalSpent', 'VisitFrequency', 'AverageBasket']].copy()
scaler = StandardScaler()
features_scaled = scaler.fit_transform(features)

# Применение алгоритма кластеризации
kmeans = KMeans(n_clusters=4, random_state=42)
df['CustomerSegment'] = kmeans.fit_predict(features_scaled)

# Сохранение результатов с сегментами клиентов
df.to_excel('customer_segments.xlsx', index=False)

import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import os

# Определение источников данных
sources = {
'online': 'online_sales.xlsx',
'retail': 'retail_sales.xlsx',
'wholesale': 'wholesale_orders.xlsx'
}

# Функция консолидации данных
def consolidate_sales_data():
dataframes = []
for source_name, file_path in sources.items():
df = pd.read_excel(file_path)
df['Source'] = source_name # добавляем метку источника
dataframes.append(df)

# Объединяем все источники
all_sales = pd.concat(dataframes, ignore_index=True)

# Преобразуем даты и числовые значения
all_sales['Date'] = pd.to_datetime(all_sales['Date'])
all_sales['Revenue'] = pd.to_numeric(all_sales['Revenue'], errors='coerce')

return all_sales

# Функция анализа данных
def analyze_sales(data):
# Агрегация по различным измерениям
daily = data.groupby([data['Date'].dt.date, 'Source'])['Revenue'].sum().reset_index()
product_performance = data.groupby('ProductID')['Revenue'].sum().sort_values(descending=True)
category_performance = data.groupby('Category')['Revenue'].sum().sort_values(descending=True)

# Расчет тренда по сравнению с предыдущим периодом
today = datetime.now().date()
yesterday = today – timedelta(days=1)
today_sales = data[data['Date'].dt.date == today]['Revenue'].sum()
yesterday_sales = data[data['Date'].dt.date == yesterday]['Revenue'].sum()
growth_rate = ((today_sales – yesterday_sales) / yesterday_sales * 100) if yesterday_sales else 0

return {
'daily': daily,
'product_performance': product_performance,
'category_performance': category_performance,
'growth_rate': growth_rate
}

# Функция создания визуализаций
def create_visualizations(analysis_data):
# График по источникам продаж
plt.figure(figsize=(10, 6))
pivot = analysis_data['daily'].pivot(index='Date', columns='Source', values='Revenue')
pivot.plot(kind='bar', stacked=True)
plt.title('Revenue by Source')
plt.tight_layout()
plt.savefig('source_revenue.png')

# График топ-5 продуктов
plt.figure(figsize=(10, 6))
analysis_data['product_performance'].head(5).plot(kind='bar')
plt.title('Top 5 Products')
plt.tight_layout()
plt.savefig('top_products.png')

# График по категориям
plt.figure(figsize=(10, 6))
analysis_data['category_performance'].plot(kind='pie', autopct='%1.1f%%')
plt.title('Revenue by Category')
plt.tight_layout()
plt.savefig('category_revenue.png')

# Функция генерации Excel-отчета
def generate_report(analysis_data):
report_date = datetime.now().strftime('%Y-%m-%d')
output_file = f'Sales_Report_{report_date}.xlsx'

# Создаем книгу Excel с несколькими листами
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# Лист с ежедневными данными
analysis_data['daily'].to_excel(writer, sheet_name='Daily Sales', index=False)

# Лист с производительностью продуктов
analysis_data['product_performance'].to_excel(writer, sheet_name='Product Performance')

# Лист с производительностью категорий
analysis_data['category_performance'].to_excel(writer, sheet_name='Category Performance')

# Лист с сводкой
summary = pd.DataFrame({
'Metric': ['Total Revenue', 'Growth Rate', 'Top Product', 'Top Category'],
'Value': [
analysis_data['daily']['Revenue'].sum(),
f"{analysis_data['growth_rate']:.2f}%",
analysis_data['product_performance'].index[0],
analysis_data['category_performance'].index[0]
]
})
summary.to_excel(writer, sheet_name='Summary', index=False)

# Добавление изображений в отчет (это требует дополнительной настройки openpyxl)
# Здесь код для вставки графиков

print(f"Report generated: {output_file}")

# Основная функция
def run_sales_report():
sales_data = consolidate_sales_data()
analysis_results = analyze_sales(sales_data)
create_visualizations(analysis_results)
generate_report(analysis_results)

# Запуск скрипта
if __name__ == "__main__":
run_sales_report()

import pandas as pd
import numpy as np
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

def load_financial_data():
# Загрузка фактических данных
actual_df = pd.read_excel('actual_financials.xlsx')

# Загрузка плановых показателей
plan_df = pd.read_excel('financial_plan.xlsx')

return actual_df, plan_df

def calculate_kpis(actual, plan):
# Объединение данных
merged = pd.merge(actual, plan, on=['Department', 'KPI'], suffixes=('_actual', '_plan'))

# Расчет отклонений
merged['Absolute_Deviation'] = merged['Value_actual'] – merged['Value_plan']
merged['Percent_Deviation'] = (merged['Absolute_Deviation'] / merged['Value_plan'] * 100).round(2)

# Определение статуса
conditions = [
(merged['Percent_Deviation'] < -10),
(merged['Percent_Deviation'] > 10),
(merged['Percent_Deviation'].between(-10, 10))
]
choices = ['Critical', 'Exceeding', 'Normal']
merged['Status'] = np.select(conditions, choices, default='Unknown')

return merged

def generate_alert_report(kpi_data):
# Фильтрация только критических показателей
critical = kpi_data[kpi_data['Status'] == 'Critical']

if len(critical) == 0:
return None, "No critical deviations detected."

# Создание отчета для отправки
output_file = 'financial_alerts.xlsx'
with pd.ExcelWriter(output_file) as writer:
critical.to_excel(writer, sheet_name='Critical Alerts', index=False)

# Добавление сводки по отделам
summary = critical.groupby('Department')[['Absolute_Deviation']].sum().reset_index()
summary.to_excel(writer, sheet_name='Department Summary', index=False)

# Создание HTML-содержимого для email
html = """
<html>
<body>
<h2>Financial KPI Alert Report</h2>
<p>The following KPIs have critical deviations:</p>
<table border="1">
<tr>
<th>Department</th>
<th>KPI</th>
<th>Actual</th>
<th>Plan</th>
<th>Deviation %</th>
</tr>
"""

for _, row in critical.iterrows():
html += f"""
<tr>
<td>{row['Department']}</td>
<td>{row['KPI']}</td>
<td>{row['Value_actual']:,.2f}</td>
<td>{row['Value_plan']:,.2f}</td>
<td style="color: red;">{row['Percent_Deviation']:,.2f}%</td>
</tr>
"""

html += """
</table>
<p>Please see the attached file for details.</p>
</body>
</html>
"""

return output_file, html

def send_email_alert(file_path, html_content, recipients):
# Настройка SMTP-сервера
smtp_server = "smtp.company.com"
smtp_port = 587
smtp_user = "alerts@company.com"
smtp_password = "your_password"

# Создание сообщения
msg = MIMEMultipart()
msg['From'] = smtp_user
msg['To'] = ", ".join(recipients)
msg['Subject'] = "ALERT: Critical Financial KPI Deviations"

# Добавление HTML-контента
msg.attach(MIMEText(html_content, 'html'))

# Добавление вложения
with open(file_path, 'rb') as f:
attachment = MIMEApplication(f.read(), _subtype='xlsx')
attachment.add_header('Content-Disposition', 'attachment', filename='financial_alerts.xlsx')
msg.attach(attachment)

# Отправка email
with smtplib.SMTP(smtp_server, smtp_port) as server:
server.starttls()
server.login(smtp_user, smtp_password)
server.send_message(msg)

print(f"Alert email sent to: {recipients}")

# Основная функция
def monitor_financial_kpis():
# Список получателей для уведомлений
finance_team = ["cfo@company.com", "finance_director@company.com"]

# Загрузка данных
actual, plan = load_financial_data()

# Расчет KPI и отклонений
kpi_analysis = calculate_kpis(actual, plan)

# Генерация отчета с предупреждениями
alert_file, alert_html = generate_alert_report(kpi_analysis)

# Отправка уведомления, если есть критические отклонения
if alert_file:
send_email_alert(alert_file, alert_html, finance_team)
else:
print(alert_html) # "No critical deviations detected"

# Сохранение полного анализа
kpi_analysis.to_excel('kpi_analysis.xlsx', index=False)

# Запуск мониторинга
if __name__ == "__main__":
monitor_financial_kpis()