emp_subpath = 'data/emp.csv'

emp_df = pd.read_csv(emp_subpath)
emp_df.name = 'Employee'  # name of the table
display(emp_df.head(3))

zip_tax = 'data/zip_tax.csv'
zip_tax_df = pd.read_csv(zip_tax)
zip_tax_df.name = 'Zip taxes'  # name of the table
display(zip_tax_df.head(3))

computer_readable_columns(emp_df, zip_tax_df)  # fixing column names

print(f'[{emp_df.name}] Column names after fixing:', list(emp_df.columns))
print(f'[{zip_tax_df.name}] Column names after fixing:', list(zip_tax_df.columns))

# Display data-types:
display_dtypes(emp_df, zip_tax_df)
# Check null values in columns:
check_not_null_columns(emp_df, zip_tax_df)
# Display duplicated values if existed:
multi_display_duplicated_values(emp_df, zip_tax_df, skip_cols=['zip', 'salary', 'tax_rate'])
# Display unique values if exists:
multi_display_unique_values(emp_df, zip_tax_df, skip_cols=['emp_id'])

# Number of the unique values in each column:
display(emp_df.nunique().to_frame('Number of unique values').T)

emp_with_taxes_df = pd.merge(emp_df, zip_tax_df, how='left', left_on='zip', right_on='zip')
emp_with_taxes_df.head()

# Gross pay - what employees earn before taxes
# Net pay - what employees earn after taxes
emp_with_taxes_df['net_salary'] = emp_with_taxes_df['salary'] / 100 * (100 - emp_with_taxes_df['tax_rate'])
display(emp_with_taxes_df)

print("Mean Net salary among employees:", emp_with_taxes_df['net_salary'].mean())

print("Median Net salary among employees:", emp_with_taxes_df['net_salary'].median())

computer_readable_columns(sales_df)  # renaming of the columns

display(sales_df.head())

# Display data-types:
display_dtypes(sales_df)
# Check null values in columns (nothing will be printed if no null values):
check_not_null_columns(sales_df)

# Date columns needed to be converted `object` -> `date` (will be converted to `datetime64[ns]`)
date_fmt: str = '%m/%d/%Y'  # date format
sales_df['order_date'] = pd.to_datetime(sales_df['order_date'], format=date_fmt)
sales_df['ship_date'] = pd.to_datetime(sales_df['ship_date'], format=date_fmt)

# Number of the unique values in each column:
display(sales_df.nunique().to_frame('Number of unique values').T)

# Display unique values if exists:
_ = display_unique_values(
    sales_df, 'product_category', 'product_sub_category', 'product_container',
    'customer_segment', 'ship_mode', 'province', 'order_priority')

filtering = sales_df['product_sub_category'] == 'Computer Peripherals'
filtering = filtering & (sales_df['order_date'].dt.year.isin([2009, 2010]))

res_name = 'Mean Sales of `Computer Peripherals`'
sales_df[filtering].groupby(sales_df['order_date'].dt.year)['sales'].mean().to_frame(res_name).reset_index()

# Filtering only needed values:
filtering = sales_df['product_category'].isin(['Furniture', 'Technology'])
filtering = filtering & (sales_df['order_date'].dt.year.isin([2009, 2010, 2011]))

# Grouping by `product_category` and year of the `order_date` field:
sales_grouped = sales_df[filtering].groupby([sales_df['product_category'], sales_df['order_date'].dt.year])

# Summary sales of the categories `Furniture` and `Technology`:
summary_sales_df = sales_grouped['sales'].sum().to_frame('Summary Sales').reset_index()

# Summary number of the orders of the categories `Furniture` and `Technology`:
summary_order_counts_df = sales_grouped['order_quantity'].sum().to_frame('Number of orders').reset_index()

# Outer-Joining for pretty showing in single table:
summary_sales_df = pd.merge(summary_sales_df, summary_order_counts_df,  how='outer', left_on=['product_category', 'order_date'], right_on = ['product_category', 'order_date'])
display(summary_sales_df)

# Filtering only needed values:
filtering = sales_df['product_sub_category'] == 'Paper'

# Grouping by year of the `order_date` field. Calculate mean of sales per each year:
display(sales_df[filtering].groupby([sales_df['order_date'].dt.year]).median()['sales'].to_frame('Median sales').reset_index())

print("Summary median of sales per all years:", sales_df[filtering]['sales'].median())

# Filtering only needed values:
filtering = sales_df['product_category'] == 'Office Supplies'
filtering = filtering & (sales_df['order_date'].dt.year == 2012)

print('Standard deviation of the product category `Office Supplies` sales at 2012:',
      sales_df[filtering]['sales'].std())

filtering = sales_df['product_sub_category'].isin(['Paper', 'Computer Peripherals'])
res_df = sales_df[filtering].copy()  # temp DataFrame to not overwrite the original data

# Pearson’s correlation: covariance(X, Y) / (std(X) * std(Y))
#  Where covariance(X, Y) = (sum(x - mean(X)) * (y - mean(Y)) ) * 1/(n-1)

# Note: `.reset_index(drop=True)` is needed because pandas don't allow you to calculate
#  the correlation between rows with different indexes values:
paper_sales = res_df[res_df['product_sub_category'] == 'Paper']['sales'].reset_index(drop=True)
computer_peripherals_sales = res_df[res_df['product_sub_category'] == 'Computer Peripherals']['sales'].reset_index(
    drop=True)
corr = paper_sales.corr(computer_peripherals_sales)
print("Correlation between sales of the product sub-category `Paper` "
      "and another sub-category `Computer Peripherals`: ", corr)

res_df.sort_values(by=['order_date'], inplace=True)
fig = px.histogram(
    res_df, x='order_date', y='sales', color='product_sub_category', marginal='rug',
    nbins=100, hover_data=res_df.columns, title='Sales',
)
fig.update_layout(
    bargap=0.2
)
fig.show(PLOTLY_PLOT_SHOW_TYPE)

# Filtering only needed values:
filtering = sales_df['product_category'] == 'Office Supplies'
filtering = filtering & (sales_df['order_date'].dt.year == 2012)

print('Standard deviation of the order quantity for product category `Office Supplies` at 2012:',
      sales_df[filtering]['order_quantity'].std())

computer_readable_columns(stock_df)  # fixing column names
display(stock_df.head())

# Display data-types:
display_dtypes(stock_df)
# Check null values in columns:
check_not_null_columns(stock_df)
# Display duplicated values if existed:
display_duplicated_values(stock_df, 'date')

# Number of the unique values in each column:
display(stock_df.nunique().to_frame('Number of unique values').T)

def min_max(x):
    return pd.Series(index=['min','max'],data=[x.min(),x.max()])


unique_date = stock_df['date'].unique()
print('Min value of the `date` string-column:', unique_date.min())
print('Max value of the `date` string-column:', unique_date.max())
split_date = stock_df['date'].str.split('/', expand=True)
split_date.columns = [f'{col}_part' for col in split_date.columns]
display(split_date.apply(min_max))
split_date_unique = display_unique_values(split_date, to_display=False)
display(split_date_unique.apply(lambda x: x.sort_values()))

# Displaying random sample of data:
display(stock_df['date'].sample(n=10))

stock_dtm_sorted_df = stock_df.set_index('date')
# NOTE: don't forget to sort your data by time:
stock_dtm_sorted_df.sort_index(inplace=True)
companies: set[str] = set(stock_dtm_sorted_df.columns)

stock_dtm_sorted_df.head()

rolling_range2dtm_stock = dict()
days = [30, 60, 90]
for d in days:
    offset_str = f'{d}D'
    # NOTE: `.dropna()` will drop first `d`-rows, because they can't be calculated
    rolling_range2dtm_stock[offset_str] = stock_dtm_sorted_df.rolling(offset_str).agg(
        [pd.Series.mean, pd.Series.std]).dropna()

    print(f'\nMoving Mean and Std by {d} days period:')
    display(rolling_range2dtm_stock[offset_str].head())

clr_opacity = 0.2  # color opacity for the standard deviation band-plot
line_modes = cycle(['solid', 'dashdot', 'dot', 'dash'])

fig = go.Figure()
for offset_str, rolling_stock_df in rolling_range2dtm_stock.items():
    line_mode = next(line_modes)
    companies: set[str] = set([col_subcol[0] for col_subcol in rolling_stock_df.columns])
    _df = rolling_stock_df.reset_index()  # `date` column will be moved from index to column
    _df = _df.sort_values(by='date')

    # Reset of the colors:
    # colors = cycle(px.colors.qualitative.Light24)  # wrapping with `cycle` for cycled colors getting
    colors = cycle(px.colors.qualitative.Dark24)  # wrapping with `cycle` for cycled colors getting
    # More colors: https://plotly.com/python/discrete-color/#color-sequences-in-plotly-express

    for company, color in zip(companies, colors):

        # Create upper bound trace
        fig.add_trace(go.Scatter(
            # name=f'{company}: Upper Bound',
            name=f'{offset_str} Upper Bound',
            legendgroup=company,
            showlegend=False,
            x=_df['date'],
            y=_df[company]['mean'] + _df[company]['std'],
            mode='lines',
            fillcolor=plotly_hex2rgba_color(color, clr_opacity),
            # line_color='rgba(255,255,255,0)',
            line_color=plotly_hex2rgba_color(color, clr_opacity),
            line=dict(width=0, dash=line_mode),
        ))

        # Create lower bound trace
        fig.add_trace(go.Scatter(
            # name=f'{company}: Lower Bound',
            name=f'{offset_str} Lower Bound',
            legendgroup=company,
            showlegend=False,
            x=_df['date'],
            y=_df[company]['mean'] - _df[company]['std'],
            mode='lines',
            fillcolor=plotly_hex2rgba_color(color, clr_opacity),
            # line_color='rgba(255,255,255,0)',
            line_color=plotly_hex2rgba_color(color, clr_opacity),
            fill='tonexty',
            line=dict(width=0, dash=line_mode),
        ))

        # Adding mean measure trace
        fig.add_trace(go.Scatter(
            x=_df['date'],
            y=_df[company]['mean'],
            mode='lines+markers',
            name=f'{company}: {offset_str} mean',
            legendgroup=company,
            line=dict(color=color, dash=line_mode),
            marker=dict(size=2),
            # error_y=dict(array=df[company]['std'], visible=False),
        ))

fig.update_layout(
    yaxis_title='Stock Price',
    xaxis_title='Date',
    title='Mean and Std of stock market',
)
fig.show(PLOTLY_PLOT_SHOW_TYPE)

# FIXME legend day-buttons: add buttons to filter by "90D", "60D", "30D" and see all companies at stock

std_stock_df = stock_dtm_sorted_df.std().to_frame('Standard deviation').sort_values(by='Standard deviation')
display(std_stock_df.T)

fig = px.bar(std_stock_df)
fig.update_layout(
    yaxis_title='Standard deviation',
    xaxis_title='Company',
    title='Std of stock market per all time',
)
fig.show(PLOTLY_PLOT_SHOW_TYPE)

# NOTE 1: `.dropna()` will drop first row, because it can't be calculated with diff
# NOTE 2: don't forget to sort your data by time. Actually - already sorted by time.
# stock_dtm_sorted_df.sort_index(inplace=True)
stock_diff_df = stock_dtm_sorted_df.diff().dropna()

display(stock_diff_df.head())  # stock_dtm_sorted_df is already sorted


def positive(col):
    return (col > 0).sum()


def negative(col):
    return (col < 0).sum()


pos_neg_stock = stock_diff_df.agg([positive, negative]).rename(index={
    positive.__name__: 'Number of days stock raising',
    negative.__name__: 'Number of days stock decreasing'})

display(pos_neg_stock)

# NOTE 1: `.dropna()` will drop first row, because it can't be calculated with diff
# NOTE 2: don't forget to sort your data by time. Actually - already sorted by time.
# stock_dtm_sorted_df.sort_index(inplace=True)
stock_diff_df = stock_dtm_sorted_df.diff().dropna()

display(stock_diff_df.head())  # stock_dtm_sorted_df is already sorted


def positive(col):
    return (col > 0).sum()


def negative(col):
    return (col < 0).sum()


pos_neg_stock = stock_diff_df.agg([positive, negative]).rename(index={
    positive.__name__: 'Number of days stock raising',
    negative.__name__: 'Number of days stock decreasing'})

display(pos_neg_stock)