Predict sales for November and December 2018.

import glob
import os
import pandas as pd
import re
import numpy as np
import datetime as dt
from sklearn.linear_model import LinearRegression
import seaborn as sns
from matplotlib import pyplot as plt

# Set font for Chinese characters
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

sns.set_style("darkgrid", {"font.sans-serif": ['SimHei', 'Droid Sans Fallback']})
os.chdir('./path/to/data/directory')
file_list = glob.glob('*market_recent_sales.xlsx')

def read_and_process_file(file_name):
    category = re.search(r'.*(?=市场)', file_name).group()
    data_frame = pd.read_excel(file_name)
    if data_frame['date'].dtype == 'int64':
        data_frame['date'] = pd.to_datetime(data_frame['date'], unit='D', origin=pd.Timestamp('1899-12-30'))
    data_frame.rename(columns={data_frame.columns[1]: category}, inplace=True)
    data_frame.set_index('date', inplace=True)
    return data_frame

data_frames = [read_and_process_file(f) for f in file_list]
combined_df = pd.concat(data_frames, axis=1).reset_index()
months = combined_df['date'].dt.month

def predict_sales(data, months):
    for m in [11, 12]:
        monthly_data = data[months == m]
        x_train = np.array(monthly_data['date'].dt.year).reshape(-1, 1)
        y_test = [pd.datetime(2018, m, 1)]
        for col in range(1, len(monthly_data.columns)):
            y_train = np.array(monthly_data.iloc[:, col]).reshape(-1, 1)
            model = LinearRegression().fit(x_train, y_train)
            prediction = model.predict(np.array([2018]).reshape(-1, 1))
            y_test.append(round(prediction[0][0], 2))
        new_row = pd.DataFrame([dict(zip(data.columns, y_test))])
        data = new_row.append(data)
    return data

updated_df = predict_sales(combined_df, months)
updated_df.reset_index(drop=True, inplace=True)
updated_df = updated_df[updated_df['date'].dt.year != 2015]
updated_df['total_sales'] = updated_df.sum(axis=1)
updated_df.insert(1, 'year', updated_df['date'].dt.year)

Trend Analysis

Market Trend Over Three Years

yearly_trend = updated_df.groupby('year').sum().reset_index()
sns.relplot(x='year', y='total_sales', kind='line', marker='o', data=yearly_trend, height=4)
plt.title('Market Trend Over Three Years')
plt.xticks(yearly_trend.year, rotation=45)
plt.xlabel('Year')
plt.ylabel('Total Sales')
plt.show()

Sales Trends of Various Markets Over Three Years

fig, ax = plt.subplots(figsize=(10, 6))
sns.lineplot(data=yearly_trend.set_index('year').iloc[:, :-1], dashes=False, marker='^')
plt.title('Sales Trends of Various Markets Over Three Years')
plt.xticks(yearly_trend.year, rotation=45)
for year, sales in zip(yearly_trend.year, yearly_trend['rodent_control']):
    plt.text(year, sales, '{:.3e}'.format(sales), ha='center', va='bottom', size=12)
plt.xlabel('Year')
plt.ylabel('Total Sales')
plt.show()

Growth Trend of Rodent Control Products Over Three Years

g = sns.FacetGrid(yearly_trend, height=5)
g.map(sns.barplot, 'year', 'rodent_control', color='wheat')
g.map(sns.pointplot, 'year', 'rodent_control')
for idx, sales in enumerate(yearly_trend['rodent_control']):
    plt.text(idx, sales, '{:.3e}'.format(sales), ha='center', va='bottom', size=12)
plt.xlabel('Year')
plt.ylabel('Growth Trend of Rodent Control Products')
plt.xticks(rotation=45)
plt.show()

Annnual Market Share of Each Sub-market

yearly_percentage = yearly_trend.iloc[:, 1:-1].div(yearly_trend.total_sales, axis=0)
yearly_percentage.index = yearly_trend.year
yearly_percentage.plot(kind='bar', stacked=True, figsize=(10, 8), colormap='tab10')
for idx, share in enumerate(yearly_percentage['rodent_control']):
    plt.text(idx, share / 2, '{:.2f}%'.format(share * 100), ha='center', va='bottom', size=12, color='white')
plt.xlabel('Year')
plt.ylabel('Market Share')
plt.title('Market Share of Each Sub-market Over Three Years')
plt.show()

Annual Growth Rate of Various Markets

market_data = yearly_trend.iloc[:, 1:-1]
growth_rate = market_data.diff().iloc[1:, :].reset_index(drop=True) / market_data.iloc[:2, :]
growth_rate.index = ['2016-2017', '2017-2018']

fig, ax = plt.subplots(figsize=(10, 8))
sns.lineplot(data=growth_rate, dashes=False)
plt.title('Annual Growth Rate of Various Markets')
plt.xlabel('Year')
plt.ylabel('Annual Growth Rate')
plt.show()

Transaction Index Proportion

brand_data = pd.read_excel('top100_brands_data.xlsx')
brand_data['transaction_index_ratio'] = brand_data['transaction_index'] / brand_data['transaction_index'].sum()
brand_data.plot(x='brand', y='transaction_index_ratio', kind='bar', figsize=(15, 5))
plt.show()

HHI = sum(brand_data['transaction_index_ratio'] ** 2)
print(HHI)

Merge All File

all_files = glob.glob('*.xlsx')
merged_data = pd.concat([pd.read_excel(f) for f in all_files], sort=False)

Remove Columns with More Than 56% Missing Values

data_with_drops = pd.read_excel(r"path/to/safety_books.xlsx", sheet_name="Sheet1")
columns_to_drop = data_with_drops.isna().mean() > 0.56
filtered_data = data_with_drops.loc[:, ~columns_to_drop]

unique_value_mask = np.array([len(filtered_data[col].unique()) == 1 for col in filtered_data.columns])
final_data = filtered_data.loc[:, ~unique_value_mask]

Column Indexing

publisher_index = final_data.columns.get_loc('publisher')
subset_data = final_data.iloc[:, :publisher_index]

Deletion Operations

final_data.drop(columns="book_title", inplace=True)
final_data.drop(index=0, inplace=True)

Type Conversion

final_data['item_id'] = final_data['item_id'].astype('object')
final_data.reset_index(drop=True, inplace=True)

Plotting Techniques

category_data.plot.barh()
category_data.plot.pie(autopct='%.2f')
final_data['price'].plot.hist()
filtered_final_data = final_data[final_data['price_range'] == '0_50']
filtered_final_data['price'].plot.hist()

Interactive Pie Chart

import plotly.graph_objects as go
interactive_fig = go.Figure(data=[go.Pie(labels=category_data.index, values=category_data.values)])
interactive_fig.show()

Plot Three Pie Charts Together

fig, axs = plt.subplots(1, 3, figsize=(10, 6))
bai31['sales'].plot.pie(autopct='%.f', title='Bayer', startangle=30, ax=axs[0])
axs[0].set_ylabel('')
an31['sales_30d'].plot.pie(autopct='%.f', title='Ansu', startangle=60, ax=axs[1])
axs[1].set_ylabel('')
kl31['sales_30d'].plot.pie(autopct='%.f', title='Keling', startangle=90, ax=axs[2])
axs[2].set_ylabel('')

Binning Operation

pricing_data = pd.read_excel(r"path/to/safety_books.xlsx")
bins = [0, 40, 80, 120]
labels = ['0_40', '40_80', '80_120']
pricing_data['price_range'] = pd.cut(pricing_data['price'], bins, labels=labels, include_lowest=True)
print(pricing_data['price_range'].value_counts())

Relative Competitive Degree

competitive_data['relative_competitiveness'] = 1 - (competitive_data['avg_single_item_sales'] - competitive_data['avg_single_item_sales'].min()) / (
    competitive_data['avg_single_item_sales'].max() - competitive_data['avg_single_item_sales'].min())

Top 5% Prices

top_prices = final_data[final_data['price'] >= final_data['price'].quantile(0.95)]

Top 10 Items

top_items = final_data.sort_values('transaction_index', ascending=False).reset_index(drop=True).iloc[:10, :]

Count Unique Values

unique_titles_count = final_data['book_title'].value_counts().count()

Difefrence Between loc[:, 'price'] and loc[:, ['price']]

loc[:, 'price'] returns a Series.

loc[:, ['price']] returns a DataFrame.

Mask Replacement and describe Method

def cap_outliers(series):
    upper_quartile = series.quantile(0.9)
    capped_series = series.mask(series > upper_quartile, upper_quartile)
    return capped_series

def apply_capping(data_frame):
    df_copy = data_frame.copy()
    df_copy['price'] = cap_outliers(df_copy['price'])
    return df_copy

capped_data = apply_capping(final_data)
print(capped_data.describe(percentiles=[0.1, 0.9, 0.99]))