weekly_report_master-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "aaa6043e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('display.max_columns',None)\n",
    "pd.set_option('display.max_rows',None)\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import datetime as dt\n",
    "from datetime import datetime\n",
    "import openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3968e747",
   "metadata": {},
   "outputs": [],
   "source": [
    "rd = pd.read_excel('PowerBI Monthly Sales Dashboard FY2021-22_sc.xlsx', sheet_name='Raw Data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d3486c4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "rd.columns = [' '.join(i.split()) for i in rd.columns]\n",
    "rd.columns = rd.columns.str.lower()\n",
    "rd.columns = rd.columns.str.replace(' ','_')\n",
    "rd1 = rd[['date','month','country','r._dealer_name','sub_category','product_code','product_packaging',\\\n",
    "          'sales_after_discount','data_type','focus_type','fiscal_year']]\n",
    "rd1.rename(columns={'r._dealer_name':'dealer_name', 'sales_after_discount': 'sales'}, inplace=True)\n",
    "fy21 = rd1[rd1.fiscal_year=='2021/2022']\n",
    "fy21.dealer_name = fy21.dealer_name.astype(str)\n",
    "fy21.dealer_name = [' '.join(i.split()) for i in fy21.dealer_name]\n",
    "fy21.product_code=fy21.product_code.astype(str)\n",
    "fy21.product_code = [' '.join(i.split()) for i in fy21.product_code]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "95ddfa14",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def powerbi(cty):\n",
    "    fy21_c = fy21[fy21.country==cty]\n",
    "    \n",
    "    def cat(x,y,z):\n",
    "\n",
    "        for m, s in zip(y,z):\n",
    "            if m == 'Apr':\n",
    "                df.loc[0,x]=s\n",
    "            elif m == 'May':\n",
    "                df.loc[1,x]=s\n",
    "            elif m == 'Jun':\n",
    "                df.loc[2,x]=s\n",
    "            elif m == 'Jul':\n",
    "                df.loc[3,x]=s\n",
    "            elif m == 'Aug':\n",
    "                df.loc[4,x]=s\n",
    "            elif m == 'Sep':\n",
    "                df.loc[5,x]=s\n",
    "            elif m == 'Oct':\n",
    "                df.loc[6,x]=s\n",
    "            elif m == 'Nov':\n",
    "                df.loc[7,x]=s\n",
    "            elif m == 'Dec':\n",
    "                df.loc[8,x]=s\n",
    "            elif m == 'Jan':\n",
    "                df.loc[9,x]=s\n",
    "            elif m == 'Feb':\n",
    "                df.loc[10,x]=s\n",
    "            elif m == 'Mar':\n",
    "                df.loc[11,x]=s\n",
    "        return df.round(0)\n",
    "\n",
    "    d = pd.date_range(start='2021-Apr', end='2022-04', freq='m')\n",
    "    df = pd.DataFrame({'month':d})\n",
    "    df.month = df.month.dt.to_period('m')\n",
    "\n",
    "    # work on budget\n",
    "    bud = fy21_c[fy21_c.data_type=='Budget']\n",
    "    bud_s = bud[bud.focus_type=='Speed']\n",
    "    bud_s_group = bud_s.groupby('month')['sales'].sum().reset_index()\n",
    "\n",
    "    x = 'speed_t'\n",
    "    y= bud_s_group.month\n",
    "    z = bud_s_group.sales\n",
    "    df = cat(x,y,z)\n",
    "\n",
    "\n",
    "    # add growth into the dataframe df\n",
    "    bud_g = bud[bud.focus_type=='Growth']\n",
    "    bud_g_group = bud_g.groupby('month')['sales'].sum().reset_index()\n",
    "\n",
    "    # integrate growth into df\n",
    "    x = 'growth_t'\n",
    "    y= bud_g_group.month\n",
    "    z = bud_g_group.sales\n",
    "    df = cat(x,y,z)\n",
    "\n",
    "    # create new total column\n",
    "    df['total_t']=df.speed_t + df.growth_t\n",
    "    df['total_t_c']=df.total_t.cumsum()\n",
    "\n",
    "    # cumsum for speed and growth target\n",
    "    df['speed_t_c']=df.speed_t.cumsum()\n",
    "    df['growth_t_c']=df.growth_t.cumsum()\n",
    "\n",
    "    # add actual into df\n",
    "    act = fy21_c[fy21_c.data_type=='Actual']\n",
    "\n",
    "    # group speed sales by month\n",
    "    act_s = act[act.focus_type=='Speed']\n",
    "    act_s_group = act_s.groupby('month')['sales'].sum().reset_index()\n",
    "\n",
    "    # integrate act speed sales into df\n",
    "    x = 'speed_a'\n",
    "    y= act_s_group.month\n",
    "    z = act_s_group.sales\n",
    "    df = cat(x,y,z)\n",
    "\n",
    "    # group growth sales by month\n",
    "    act_g = act[act.focus_type=='Growth']\n",
    "    act_g_group = act_g.groupby('month')['sales'].sum().reset_index()\n",
    "\n",
    "    # integrate act growth sales into df\n",
    "    x = 'growth_a'\n",
    "    y= act_g_group.month\n",
    "    z = act_g_group.sales\n",
    "    df = cat(x,y,z)\n",
    "\n",
    "    # cumsum for speed and growth actual\n",
    "    df['speed_a_c']=df.speed_a.cumsum()\n",
    "    df['growth_a_c']=df.growth_a.cumsum()\n",
    "\n",
    "    # add act\n",
    "    df['total_a']=df.speed_a + df.growth_a\n",
    "    df['total_a_c']=df.total_a.cumsum()\n",
    "    df['country']=cty\n",
    "\n",
    "    # groupby dealer\n",
    "    dd = fy21_c[['dealer_name','data_type','sales']]\n",
    "    ddg = dd.groupby(['dealer_name','data_type'])['sales'].sum().reset_index()\n",
    "    ddg['country']=cty\n",
    "\n",
    "    return df, ddg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "aec49554",
   "metadata": {},
   "outputs": [],
   "source": [
    "wr = pd.DataFrame()\n",
    "ab = pd.DataFrame()\n",
    "cty_list = ['Malaysia','Thailand','Philippines','Singapore','Vietnam','Indonesia']\n",
    "\n",
    "for cty in cty_list:\n",
    "    a, b = powerbi(cty)\n",
    "    wr = wr.append(a)\n",
    "    ab = ab.append(b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "550aa248",
   "metadata": {},
   "outputs": [],
   "source": [
    "wr.to_csv('pbi_weekly_report_all.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4cd0dd6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ab.to_csv('pbi_dealer_actual_budget_all.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f6a9757",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}