Проекты

with cte as(
select 
    a.Product,
    a.Category,
    a.Brand,
    a.Description,
    a.Sub_Category,
    a.Sale_Price,
    a.Cost_Price,
    b.Date,
    b.Customer_Type,
    b.Country,
    b.Discount_Band,
    b.Units_Sold,
    b.Discount,
    (Sale_Price * Units_Sold) as revenue,
    (Cost_Price * Units_Sold) as total_cost,
    FORMAT(Date, 'MMMM', 'en-US') as month,
    FORMAT(Date, 'yyyy') as year
from Product_Data_Full a
join Product_Sales_Full b on a.Product_ID = b.Product
)
select *,
    (1 - a.Discount * 1.0 / 100) * a.revenue as discount_revenue
from cte a
join Discount_Data_Full b 
    on a.Discount_Band = b.Discount_Band 
    and a.month = b.Month

import pandas as pd

# Загрузка исходных данных
df = pd.read_csv('Sample - Superstore.csv')

# 1. Product Data
product_data = df[['Product ID', 'Product Name', 'Category', 'Sub-Category']].drop_duplicates()
# Добавляем недостающие колонки с расчетными данными
product_data['Cost Price'] = (df.groupby('Product ID')['Sales'].transform('mean') * 0.7).round().astype(int)
product_data['Sale Price'] = df.groupby('Product ID')['Sales'].transform('mean').round().astype(int)
product_data['Brand'] = df['Product Name'].str.split().str[0]  
product_data['Description'] = '"' + df['Product Name'] + '"'
product_data['Cost Price'] = '$' + product_data['Cost Price'].astype(str)
product_data['Sale Price'] = '$' + product_data['Sale Price'].astype(str)
product_data = product_data.rename(columns={'Product Name': 'Product'})
product_data.to_csv('Product_Data_Full.csv', index=False)
# 2. Product Sales 
product_sales = df[['Order Date', 'Segment', 'Country', 'Product ID', 'Discount', 'Quantity']].copy()

product_sales['Order Date'] = pd.to_datetime(product_sales['Order Date'])

product_sales['Order Date'] = product_sales['Order Date'].apply(
    lambda x: f"1/{x.month}/{x.year}"
)

product_sales['Discount Band'] = pd.cut(df['Discount'], 
                                     bins=[-0.1, 0, 0.15, 0.25, 1], 
                                     labels=['None', 'Low', 'Medium', 'High'])
product_sales = product_sales.rename(columns={
    'Order Date': 'Date',
    'Segment': 'Customer Type', 
    'Product ID': 'Product', 
    'Quantity': 'Units Sold'
})
product_sales.to_csv('Product_Sales_Full.csv', index=False)

# 3. Discount Data
months = ['January', 'February', 'March', 'April', 'May', 'June', 
          'July', 'August', 'September', 'October', 'November', 'December']

discount_bands = ['None', 'Low', 'Medium', 'High']

# Создаем все комбинации месяцев и дисконтных бэндов
discount_data = []
for month in months:
    for band in discount_bands:
        discount_data.append({'Month': month, 'Discount Band': band})

discount_df = pd.DataFrame(discount_data)

# Добавляем значения скидок 
discount_values = [0,5,8,12,0,6,9,14,0,4,7,11,0,5,10,16,0,7,12,18,
                   0,6,8,13,0,8,14,21,0,7,11,17,0,5,9,15,0,9,15,22,
                   0,10,16,24,0,12,20,28]

discount_df['Discount'] = discount_values
discount_df.to_csv('Discount_Data_Full.csv', index=False)