# Installeer Pandas met pip
pip install pandas

# Of met conda (voor Anaconda gebruikers)
conda install pandas

# Installeer ook vaak gebruikte dependencies
pip install pandas numpy matplotlib

# Standaard import (gebruikt door 90% van developers)
import pandas as pd
import numpy as np

# Optioneel: veelgebruikte functies direct importeren
from pandas import DataFrame, Series
from pandas import read_csv, read_excel

# Check versie (laatste versie is aanbevolen)
print(pd.__version__)  # Bijv: 2.1.0

# Maak een eenvoudige DataFrame om te testen
df = pd.DataFrame({
    'Naam': ['Jan', 'Piet', 'Klaas'],
    'Leeftijd': [25, 30, 35],
    'Stad': ['Amsterdam', 'Rotterdam', 'Utrecht']
})

print(df)
print("Pandas werkt correct!")

# CSV inlezen met opties
df = pd.read_csv(
    'sales_data.csv',
    sep=';',                 # scheidingsteken (; ipv ,)
    encoding='utf-8',         # karakter encoding
    header=0,                # rij 0 bevat kolomnamen
    na_values=['NA', 'NULL'], # waarden die als NaN moeten worden gezien
    parse_dates=['Datum'],   # kolommen die datum/tijd zijn
    dtype={                  # data types specificeren
        'ProductID': 'str',
        'Quantity': 'int32'
    }
)

# Naar CSV
df.to_csv('output.csv', index=False, encoding='utf-8')

# Naar Excel
df.to_excel('output.xlsx', sheet_name='Data', index=False)

# Naar Parquet (efficiënt voor grote datasets)
df.to_parquet('output.parquet', compression='snappy')

# Naar JSON
df.to_json('output.json', orient='records')

# Naar SQL database
df.to_sql('tabel_naam', con=engine, if_exists='replace', index=False)

# Check ontbrekende waarden
print(df.isnull().sum())        # Totaal per kolom
print(df.isnull().sum().sum())  # Totaal in hele DataFrame

# Ontbrekende waarden behandelen
# Methode 1: Verwijderen
df_dropped = df.dropna()                    # Verwijder rijen met ANY NaN
df_dropped_cols = df.dropna(axis=1)         # Verwijder kolommen met ANY NaN
df_dropped_thresh = df.dropna(thresh=0.8*len(df))  # Houd rijen met ≥80% data

# Methode 2: Vervangen
df_filled = df.fillna(0)                    # Vervang alle NaN met 0
df_filled_mean = df.fillna(df.mean())       # Vervang met gemiddelde
df_filled_ffill = df.fillna(method='ffill')  # Forward fill
df_filled_bfill = df.fillna(method='bfill')  # Backward fill

# Specifiek per kolom
df['Sales'] = df['Sales'].fillna(df['Sales'].median())
df['Region'] = df['Region'].fillna('Unknown')

# Check duplicaten
print(df.duplicated().sum())  # Totaal aantal duplicaten
print(df[df.duplicated()])    # Toon de duplicaten

# Duplicaten verwijderen
df_no_dups = df.drop_duplicates()                     # Alle kolommen
df_no_dups_subset = df.drop_duplicates(subset=['Product', 'Region'])  # Specifieke kolommen
df_keep_last = df.drop_duplicates(keep='last')          # Houd laatste
df_keep_none = df.drop_duplicates(keep=False)         # Verwijder alle duplicaten

# Datatype conversie
df['Date'] = pd.to_datetime(df['Date'])
df['Category'] = df['Category'].astype('category')
df['Price'] = pd.to_numeric(df['Price'], errors='coerce')

# String operaties
df['Name'] = df['Name'].str.upper()          # Naar hoofdletters
df['Name'] = df['Name'].str.strip()         # Trim whitespace
df['Name'] = df['Name'].str.replace('  ', ' ')  # Dubbele spaties
df['Email_Domain'] = df['Email'].str.split('@').str[1]  # Extract domein

# Outlier detection en behandeling
Q1 = df['Sales'].quantile(0.25)
Q3 = df['Sales'].quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Outliers verwijderen
df_no_outliers = df[(df['Sales'] >= lower_bound) & (df['Sales'] <= upper_bound)]

# Outliers cappen
df['Sales_Capped'] = df['Sales'].clip(lower=lower_bound, upper=upper_bound)

# Datum kolom parsen
df['Datum'] = pd.to_datetime(df['Datum'], format='%Y-%m-%d')

# Extract datum componenten
df['Year'] = df['Datum'].dt.year
df['Month'] = df['Datum'].dt.month
df['Day'] = df['Datum'].dt.day
df['Weekday'] = df['Datum'].dt.day_name()
df['Quarter'] = df['Datum'].dt.quarter
df['Week'] = df['Datum'].dt.isocalendar().week

# Datum berekeningen
df['Days_Since'] = (pd.Timestamp('today') - df['Datum']).dt.days
df['Is_Weekend'] = df['Datum'].dt.weekday >= 5

# Filteren op datum
df_2024 = df[df['Datum'].dt.year == 2024]
df_jan = df[df['Datum'].dt.month == 1]
df_q1 = df[df['Datum'].dt.quarter == 1]
df_last_30_days = df[df['Datum'] > (pd.Timestamp('today') - pd.Timedelta(days=30))]

# Resampling (aggregeren over tijd)
# Stel: dagelijkse sales data
df_daily = df.set_index('Datum')

# Naar wekelijkse aggregatie
df_weekly = df_daily.resample('W').agg({
    'Sales': 'sum',
    'Profit': 'mean',
    'Quantity': 'count'
})

# Naar maandelijkse aggregatie
df_monthly = df_daily.resample('M').agg({
    'Sales': 'sum',
    'Profit': 'mean'
}).round(2)

# Rolling windows (bewegende berekeningen)
df_daily['Sales_MA7'] = df_daily['Sales'].rolling(window=7).mean()      # 7-dagen gemiddelde
df_daily['Sales_MA30'] = df_daily['Sales'].rolling(window=30).mean()    # 30-dagen gemiddelde
df_daily['Sales_Std30'] = df_daily['Sales'].rolling(window=30).std()     # 30-dagen std

# Expanding windows (cumulatief)
df_daily['Sales_Cumsum'] = df_daily['Sales'].expanding().sum()
df_daily['Sales_Cummean'] = df_daily['Sales'].expanding().mean()

# Period-over-period vergelijking
df_monthly['Sales_MoM'] = df_monthly['Sales'].pct_change() * 100  # Maand-op-maand %
df_monthly['Sales_YoY'] = df_monthly['Sales'].pct_change(periods=12) * 100  # Jaar-op-jaar %

# Shift (vergelijken met vorige periode)
df_daily['Sales_Lag1'] = df_daily['Sales'].shift(1)  # Vorige dag
df_daily['Sales_Diff'] = df_daily['Sales'] - df_daily['Sales'].shift(1)

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# 1. Data inlezen
orders = pd.read_csv('ecommerce_orders.csv', parse_dates=['order_date'])
products = pd.read_csv('products.csv')
customers = pd.read_csv('customers.csv')

# 2. Data cleaning
# Verwijder duplicaten
orders = orders.drop_duplicates()

# Handle missing values
orders['discount'] = orders['discount'].fillna(0)
customers['city'] = customers['city'].fillna('Unknown')

# Verwijder outliers in order_value
Q1 = orders['order_value'].quantile(0.01)
Q3 = orders['order_value'].quantile(0.99)
orders = orders[(orders['order_value'] >= Q1) & (orders['order_value'] <= Q3)]

# 3. Data samenvoegen
# Merge orders met products en customers
df = pd.merge(orders, products, on='product_id', how='left')
df = pd.merge(df, customers, on='customer_id', how='left')

# 4. Nieuwe features berekenen
df['net_revenue'] = df['order_value'] * (1 - df['discount'])
df['profit_margin'] = (df['net_revenue'] - df['cost_price']) / df['net_revenue']
df['order_month'] = df['order_date'].dt.to_period('M')
df['order_day_of_week'] = df['order_date'].dt.day_name()

# 5. Business KPI's berekenen
# Maandelijkse metrics
monthly_kpis = df.groupby('order_month').agg({
    'order_id': 'count',          # Aantal orders
    'net_revenue': 'sum',        # Totale omzet
    'customer_id': 'nunique',   # Unieke klanten
    'profit_margin': 'mean'      # Gemiddelde marge
}).round(2)

monthly_kpis.columns = ['order_count', 'total_revenue', 'unique_customers', 'avg_margin']

# Customer segmentatie
customer_stats = df.groupby('customer_id').agg({
    'order_id': 'count',
    'net_revenue': 'sum',
    'order_date': ['min', 'max']
}).round(2)

customer_stats.columns = ['total_orders', 'total_spent', 'first_order', 'last_order']

# RFM analysis (Recency, Frequency, Monetary)
max_date = df['order_date'].max()
rfm = df.groupby('customer_id').agg({
    'order_date': lambda x: (max_date - x.max()).days,  # Recency
    'order_id': 'count',                                # Frequency
    'net_revenue': 'sum'                                 # Monetary
})

rfm.columns = ['recency', 'frequency', 'monetary']

# Product performance analysis
product_performance = df.groupby(['product_id', 'product_name']).agg({
    'order_id': 'count',          # Aantal verkocht
    'net_revenue': 'sum',        # Totale omzet
    'profit_margin': 'mean',     # Gemiddelde marge
    'quantity': 'sum'            # Totale hoeveelheid
}).round(2)

product_performance = product_performance.sort_values(by='net_revenue', ascending=False)

# 6. Export resultaten
monthly_kpis.to_csv('monthly_kpis.csv')
product_performance.to_excel('product_performance.xlsx')
rfm.to_parquet('rfm_analysis.parquet')

print("Analyse voltooid! Resultaten geëxporteerd.")
print(f"Totaal aantal orders: {len(df):,}")
print(f"Totale omzet: €{df['net_revenue'].sum():,.2f}")
print(f"Gemiddelde order waarde: €{df['net_revenue'].mean():.2f}")