manual-process.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "import urllib.request, json \n",
    "import re\n",
    "import io\n",
    "import time\n",
    "import openpyxl\n",
    "from datetime import datetime,date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save timestamp to text file\n",
    "with open('timestamp.txt', 'w') as f:\n",
    "    f.write(datetime.now().strftime(\"%Y-%b-%d\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# If you need to install something in jupyter notebokes, you need these commands\n",
    "# import sys\n",
    "# !{sys.executable} -m pip install openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# As we only track prices for 5 years, you need to set that time point as a reference point.\n",
    "# Also as we are using the last January as a reference point, we need to set that as well.\n",
    "\n",
    "# set average price reference month\n",
    "avgpriceRefMonth=pd.Timestamp('2023-01-01 00:00:00')\n",
    "\n",
    "# starting reference point\n",
    "startref=pd.Timestamp('2018-01-01 00:00:00')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in metadata\n",
    "meta = pd.read_csv('./metadata.csv',index_col=0,parse_dates=['ITEM_START'],date_format=\"%Y%m\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a function to split a string at a certain occurance of a separator\n",
    "\n",
    "# https://stackoverflow.com/questions/36300158/split-text-after-the-second-occurrence-of-character\n",
    "def split(strng, sep, pos):\n",
    "    strng = strng.split(sep)\n",
    "    return sep.join(strng[:pos]), sep.join(strng[pos:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2024, 6, 1, 0, 0)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read in unchained csv\n",
    "unchained = pd.read_csv('unchained.csv')\n",
    "\n",
    "#find the last month in the unchained file\n",
    "# latestmonth=datetime.strptime(unchained.columns[-1],\"%Y-%m-%d %H:%M:%S\")\n",
    "latestmonth=datetime.strptime(unchained.columns[-1],\"%Y-%m-%d\")\n",
    "# and print it out\n",
    "latestmonth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Excel changes date formats into something funky. Use this if you need to convert it to python date format\n",
    "# un = pd.read_csv('unchained.csv', index_col=0)\n",
    "# columns = {}\n",
    "# for col in un.columns:\n",
    "#     try:\n",
    "#         columns[col] = datetime.strptime(str(col), \"%d/%m/%Y\").date()\n",
    "#         # columns[col] = datetime.strptime(str(col), \"%d/%m/%Y %H:%M\").date()\n",
    "#         # columns[col] = datetime.strptime(str(col), \"%Y-%m-%d %H:%M:%S\").date()\n",
    "#         # columns[col] = datetime.strptime(str(col), \"%Y%m\").date()\n",
    "#     except ValueError:\n",
    "#         pass\n",
    "# un.rename(columns=columns, inplace=True)\n",
    "# un.to_csv('unchained.csv',date_format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dataset=/economy/inflationandpriceindices/datasets/consumerpriceindicescpiandretailpricesindexrpiitemindicesandpricequotes/itemindicesjuly2024\n",
      "the date from url:july2024\n",
      "month from indices is different to latest month in unchained csv\n"
     ]
    }
   ],
   "source": [
    "# first get the /data.json from the cpi items and prices page from the ONS website\n",
    "with requests.Session() as s:\n",
    "    r=s.get(\"https://www.ons.gov.uk/economy/inflationandpriceindices/datasets/consumerpriceindicescpiandretailpricesindexrpiitemindicesandpricequotes/data\",headers={'User-Agent': 'Mozilla/5.0'})\n",
    "    data = r.json()\n",
    "    datasets = data['datasets']\n",
    "\n",
    "#go through the dataset and find the first one which doesn't contain the word framework, glossary or /pricequotes. The url includes pricesquotes so that slash is important. Save the index as the variable match  \n",
    "for i,dataset in enumerate(datasets):\n",
    "    match = i\n",
    "    if('framework' not in dataset['uri'] and 'glossary' not in dataset['uri'] and '/pricequotes' not in dataset['uri']):\n",
    "        break\n",
    "    \n",
    "#get the uri of the items dataset we want\n",
    "items = data['datasets'][match]['uri']\n",
    "print('dataset='+items)\n",
    "\n",
    "#get the month and year from the uri\n",
    "date=split(items,'itemindices',2)[1]\n",
    "print('the date from url:'+date)\n",
    "\n",
    "#parse it as a date\n",
    "itemmonth=datetime.strptime(date,\"%B%Y\")\n",
    "\n",
    "# check date to see if you need to download a file\n",
    "if(itemmonth!=latestmonth):\n",
    "    print('month from indices is different to latest month in unchained csv')\n",
    "    \n",
    "    with requests.Session() as s:\n",
    "        r=s.get(\"https://www.ons.gov.uk\"+items+\"/data\",headers={'User-Agent': 'Mozilla/5.0'})\n",
    "        itemspage = r.json()\n",
    "        csv = itemspage['downloads'][0]['file']\n",
    "    \n",
    "    # get the csv of the latest indices\n",
    "    with requests.Session() as s:\n",
    "        download = s.get(\"https://www.ons.gov.uk/file?uri=\"+items+\"/\"+csv,headers={'User-Agent': 'Mozilla/5.0'})\n",
    "        df=pd.read_csv(io.StringIO(download.content.decode('utf-8')))\n",
    "    \n",
    "    #get the index date which is the first cell\n",
    "    index_date=df.iloc[0,0]\n",
    "    \n",
    "    # parse columns as dates in unchained\n",
    "    # https://stackoverflow.com/questions/42472418/parse-file-headers-as-date-objects-in-python-pandas\n",
    "    columns = {}\n",
    "    for col in unchained.columns:\n",
    "        try:\n",
    "            columns[col] = datetime.strptime(str(col), \"%Y-%m-%d\")\n",
    "        except ValueError:\n",
    "            pass\n",
    "    unchained.rename(columns=columns, inplace=True)\n",
    "    \n",
    "    #join it onto existing csv\n",
    "    un=unchained.merge(df[['ITEM_ID','ALL_GM_INDEX']].rename(columns={\"ALL_GM_INDEX\": datetime.strptime(str(index_date),\"%Y%m\")}),on='ITEM_ID',how='left')\n",
    "    \n",
    "    #if last date is Jan, then chain it to december\n",
    "    if(un.columns[-1].month == 1):\n",
    "        print('chaining jan')\n",
    "        jancol=un.columns[-1]\n",
    "        prevdec=un.columns[-2]\n",
    "        for index,value in un.iloc[:,-1].items():\n",
    "            un.at[index,jancol]=un.loc[index,prevdec]*value/100\n",
    "    \n",
    "    un.set_index(\"ITEM_ID\",inplace=True)\n",
    "\n",
    "else:\n",
    "    print('Nothing to update')  \n",
    "    # parse columns as dates in unchained\n",
    "    # https://stackoverflow.com/questions/42472418/parse-file-headers-as-date-objects-in-python-pandas\n",
    "    columns = {}\n",
    "    for col in unchained.columns:\n",
    "        try:\n",
    "            columns[col] = datetime.strptime(str(col), \"%Y-%m-%d\")\n",
    "            # columns[col] = datetime.strptime(str(col), \"%Y-%m-%d %H:%M:%S\")\n",
    "        except ValueError:\n",
    "            pass\n",
    "    unchained.rename(columns=columns, inplace=True)\n",
    "    un=unchained\n",
    "    un.set_index(\"ITEM_ID\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#create a copy of unchained to create the chained indices\n",
    "chained = un.copy()\n",
    "\n",
    "for col in chained:\n",
    "    for i, row_value in chained[col].items():\n",
    "        # print(col,i,row_value,meta.loc[i,'ITEM_START'])\n",
    "        if(col>=meta.loc[i,'ITEM_START']):\n",
    "            if(col==startref):\n",
    "                chained.at[i,col]=100\n",
    "            # elif(col==meta.loc[i,'ITEM_START']):\n",
    "            #     sample.at[i,col]=row_value\n",
    "            elif(col<=startref+pd.tseries.offsets.DateOffset(years=1)):\n",
    "                chained.at[i,col]=row_value\n",
    "            else:\n",
    "                if(col.month==1 and col>startref+pd.tseries.offsets.DateOffset(years=1)):\n",
    "                    chained.at[i,col]=float(row_value)*float(chained.loc[i][datetime(col.year-1,1,1)])/100\n",
    "                else:\n",
    "                    chained.at[i,col]=float(row_value)*float(chained.loc[i][datetime(col.year,1,1)])/100\n",
    "\n",
    "        elif(col==meta.loc[i,'ITEM_START']-pd.tseries.offsets.DateOffset(months=1)):\n",
    "            chained.at[i,col]=100\n",
    "\n",
    "        else:\n",
    "            chained.at[i,col]=None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Then calculate average prices\n",
    "avgprice=chained.copy()\n",
    "\n",
    "for col in avgprice:\n",
    "    for i, row_value in avgprice[col].items():\n",
    "        if(row_value==None):\n",
    "            avgprice.at[i,col]=None\n",
    "        else:\n",
    "            avgprice.at[i,col]=float(row_value)/ \\\n",
    "            float(chained.loc[i,avgpriceRefMonth])* \\\n",
    "            float(meta.loc[i,'AVERAGE_PRICE'])\n",
    "            \n",
    "#rename columns to dates without time formats\n",
    "columns = {}\n",
    "for col in avgprice.columns:\n",
    "    try:\n",
    "        columns[col] = col.date()\n",
    "    except ValueError:\n",
    "        pass\n",
    "avgprice.rename(columns=columns, inplace=True)\n",
    "\n",
    "avgprice.astype(float).round(2).to_csv('avgprice.csv',date_format='%Y-%m-%d',na_rep='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# calculate annual growth\n",
    "annualgrowth=chained.copy()\n",
    "\n",
    "for col in annualgrowth:\n",
    "    for i, row_value in annualgrowth[col].items():\n",
    "        if(col<meta.loc[i,'ITEM_START']+pd.tseries.offsets.DateOffset(years=1,months=-1)):\n",
    "            annualgrowth.at[i,col]=None\n",
    "        else:\n",
    "            if(col<startref+pd.tseries.offsets.DateOffset(years=1)):\n",
    "                annualgrowth.at[i,col]=None\n",
    "            else:\n",
    "                annualgrowth.at[i,col]=(float(row_value)- \\\n",
    "                float(chained.loc[i,col-pd.tseries.offsets.DateOffset(years=1)])) * 100 / \\\n",
    "                float(chained.loc[i,col-pd.tseries.offsets.DateOffset(years=1)]) \n",
    "                \n",
    "                \n",
    "#rename columns to dates without time formats\n",
    "columns = {}\n",
    "for col in annualgrowth.columns:\n",
    "    try:\n",
    "        columns[col] = col.date()\n",
    "    except ValueError:\n",
    "        pass\n",
    "annualgrowth.rename(columns=columns, inplace=True)\n",
    "                \n",
    "annualgrowth.astype(float).round(0).astype(int,errors='ignore').to_csv('annualgrowth.csv',date_format='%Y-%m-%d',na_rep='',float_format=\"%.0f\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2018-01-01</th>\n",
       "      <th>2018-02-01</th>\n",
       "      <th>2018-03-01</th>\n",
       "      <th>2018-04-01</th>\n",
       "      <th>2018-05-01</th>\n",
       "      <th>2018-06-01</th>\n",
       "      <th>2018-07-01</th>\n",
       "      <th>2018-08-01</th>\n",
       "      <th>2018-09-01</th>\n",
       "      <th>2018-10-01</th>\n",
       "      <th>...</th>\n",
       "      <th>2023-09-01</th>\n",
       "      <th>2023-10-01</th>\n",
       "      <th>2023-11-01</th>\n",
       "      <th>2023-12-01</th>\n",
       "      <th>2024-01-01</th>\n",
       "      <th>2024-02-01</th>\n",
       "      <th>2024-03-01</th>\n",
       "      <th>2024-04-01</th>\n",
       "      <th>2024-05-01</th>\n",
       "      <th>2024-06-01</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ITEM_ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>520130</th>\n",
       "      <td>106.2</td>\n",
       "      <td>100.5</td>\n",
       "      <td>100.4</td>\n",
       "      <td>100.8</td>\n",
       "      <td>101.1</td>\n",
       "      <td>101.5</td>\n",
       "      <td>101.9</td>\n",
       "      <td>101.9</td>\n",
       "      <td>101.8</td>\n",
       "      <td>101.7</td>\n",
       "      <td>...</td>\n",
       "      <td>103.171</td>\n",
       "      <td>103.512</td>\n",
       "      <td>103.590</td>\n",
       "      <td>104.303</td>\n",
       "      <td>104.628425</td>\n",
       "      <td>100.668</td>\n",
       "      <td>101.100</td>\n",
       "      <td>101.660</td>\n",
       "      <td>102.598</td>\n",
       "      <td>102.651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520131</th>\n",
       "      <td>102.9</td>\n",
       "      <td>103.6</td>\n",
       "      <td>102.7</td>\n",
       "      <td>102.1</td>\n",
       "      <td>102.3</td>\n",
       "      <td>100.2</td>\n",
       "      <td>99.8</td>\n",
       "      <td>101.3</td>\n",
       "      <td>101.6</td>\n",
       "      <td>101.6</td>\n",
       "      <td>...</td>\n",
       "      <td>104.996</td>\n",
       "      <td>105.725</td>\n",
       "      <td>105.627</td>\n",
       "      <td>104.516</td>\n",
       "      <td>103.183421</td>\n",
       "      <td>101.055</td>\n",
       "      <td>101.604</td>\n",
       "      <td>100.958</td>\n",
       "      <td>101.136</td>\n",
       "      <td>99.470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520137</th>\n",
       "      <td>108.3</td>\n",
       "      <td>99.6</td>\n",
       "      <td>99.5</td>\n",
       "      <td>98.9</td>\n",
       "      <td>99.6</td>\n",
       "      <td>102.2</td>\n",
       "      <td>101.8</td>\n",
       "      <td>103.1</td>\n",
       "      <td>101.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>106.581</td>\n",
       "      <td>105.616</td>\n",
       "      <td>104.579</td>\n",
       "      <td>105.486</td>\n",
       "      <td>103.811937</td>\n",
       "      <td>100.924</td>\n",
       "      <td>101.505</td>\n",
       "      <td>100.543</td>\n",
       "      <td>103.076</td>\n",
       "      <td>103.302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520140</th>\n",
       "      <td>103.4</td>\n",
       "      <td>103.1</td>\n",
       "      <td>104.9</td>\n",
       "      <td>104.6</td>\n",
       "      <td>106.6</td>\n",
       "      <td>103</td>\n",
       "      <td>101.5</td>\n",
       "      <td>103.9</td>\n",
       "      <td>106.5</td>\n",
       "      <td>104.1</td>\n",
       "      <td>...</td>\n",
       "      <td>106.510</td>\n",
       "      <td>107.907</td>\n",
       "      <td>108.389</td>\n",
       "      <td>109.608</td>\n",
       "      <td>106.663929</td>\n",
       "      <td>100.124</td>\n",
       "      <td>101.761</td>\n",
       "      <td>101.934</td>\n",
       "      <td>101.117</td>\n",
       "      <td>101.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510439</th>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>...</td>\n",
       "      <td>107.534</td>\n",
       "      <td>106.664</td>\n",
       "      <td>106.459</td>\n",
       "      <td>108.471</td>\n",
       "      <td>107.889595</td>\n",
       "      <td>100.920</td>\n",
       "      <td>101.053</td>\n",
       "      <td>100.776</td>\n",
       "      <td>100.803</td>\n",
       "      <td>100.781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620315</th>\n",
       "      <td>104.6</td>\n",
       "      <td>100.5</td>\n",
       "      <td>100.3</td>\n",
       "      <td>100.5</td>\n",
       "      <td>100.6</td>\n",
       "      <td>101</td>\n",
       "      <td>101.5</td>\n",
       "      <td>102.4</td>\n",
       "      <td>103.5</td>\n",
       "      <td>103.9</td>\n",
       "      <td>...</td>\n",
       "      <td>103.140</td>\n",
       "      <td>103.525</td>\n",
       "      <td>104.161</td>\n",
       "      <td>105.209</td>\n",
       "      <td>105.416262</td>\n",
       "      <td>100.102</td>\n",
       "      <td>100.121</td>\n",
       "      <td>100.822</td>\n",
       "      <td>102.328</td>\n",
       "      <td>102.896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610204</th>\n",
       "      <td>104.7</td>\n",
       "      <td>100.5</td>\n",
       "      <td>100.9</td>\n",
       "      <td>101.1</td>\n",
       "      <td>101.6</td>\n",
       "      <td>101.7</td>\n",
       "      <td>101.6</td>\n",
       "      <td>102.1</td>\n",
       "      <td>102.2</td>\n",
       "      <td>101.7</td>\n",
       "      <td>...</td>\n",
       "      <td>104.438</td>\n",
       "      <td>103.350</td>\n",
       "      <td>102.832</td>\n",
       "      <td>101.731</td>\n",
       "      <td>102.009743</td>\n",
       "      <td>100.118</td>\n",
       "      <td>100.593</td>\n",
       "      <td>100.628</td>\n",
       "      <td>101.437</td>\n",
       "      <td>102.328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610303</th>\n",
       "      <td>105.3</td>\n",
       "      <td>100.6</td>\n",
       "      <td>100.4</td>\n",
       "      <td>97.8</td>\n",
       "      <td>97.2</td>\n",
       "      <td>101.2</td>\n",
       "      <td>101.1</td>\n",
       "      <td>98</td>\n",
       "      <td>102.2</td>\n",
       "      <td>100.8</td>\n",
       "      <td>...</td>\n",
       "      <td>100.837</td>\n",
       "      <td>102.039</td>\n",
       "      <td>99.620</td>\n",
       "      <td>99.211</td>\n",
       "      <td>101.679370</td>\n",
       "      <td>100.583</td>\n",
       "      <td>98.466</td>\n",
       "      <td>97.569</td>\n",
       "      <td>100.088</td>\n",
       "      <td>100.241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620303</th>\n",
       "      <td>100.6</td>\n",
       "      <td>100.1</td>\n",
       "      <td>100.1</td>\n",
       "      <td>100.2</td>\n",
       "      <td>100.8</td>\n",
       "      <td>101.6</td>\n",
       "      <td>102.2</td>\n",
       "      <td>102.7</td>\n",
       "      <td>103.6</td>\n",
       "      <td>104.4</td>\n",
       "      <td>...</td>\n",
       "      <td>104.932</td>\n",
       "      <td>104.936</td>\n",
       "      <td>105.418</td>\n",
       "      <td>105.439</td>\n",
       "      <td>105.527569</td>\n",
       "      <td>100.182</td>\n",
       "      <td>99.609</td>\n",
       "      <td>99.360</td>\n",
       "      <td>99.429</td>\n",
       "      <td>99.411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620307</th>\n",
       "      <td>99.7</td>\n",
       "      <td>103.4</td>\n",
       "      <td>107.5</td>\n",
       "      <td>109.9</td>\n",
       "      <td>116.5</td>\n",
       "      <td>120.5</td>\n",
       "      <td>125.1</td>\n",
       "      <td>128.4</td>\n",
       "      <td>131.3</td>\n",
       "      <td>133.7</td>\n",
       "      <td>...</td>\n",
       "      <td>98.778</td>\n",
       "      <td>94.677</td>\n",
       "      <td>91.695</td>\n",
       "      <td>89.075</td>\n",
       "      <td>86.916713</td>\n",
       "      <td>97.306</td>\n",
       "      <td>96.447</td>\n",
       "      <td>99.169</td>\n",
       "      <td>99.866</td>\n",
       "      <td>103.092</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>444 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        2018-01-01 2018-02-01 2018-03-01 2018-04-01 2018-05-01 2018-06-01  \\\n",
       "ITEM_ID                                                                     \n",
       "520130       106.2      100.5      100.4      100.8      101.1      101.5   \n",
       "520131       102.9      103.6      102.7      102.1      102.3      100.2   \n",
       "520137       108.3       99.6       99.5       98.9       99.6      102.2   \n",
       "520140       103.4      103.1      104.9      104.6      106.6        103   \n",
       "510439           -          -          -          -          -          -   \n",
       "...            ...        ...        ...        ...        ...        ...   \n",
       "620315       104.6      100.5      100.3      100.5      100.6        101   \n",
       "610204       104.7      100.5      100.9      101.1      101.6      101.7   \n",
       "610303       105.3      100.6      100.4       97.8       97.2      101.2   \n",
       "620303       100.6      100.1      100.1      100.2      100.8      101.6   \n",
       "620307        99.7      103.4      107.5      109.9      116.5      120.5   \n",
       "\n",
       "        2018-07-01 2018-08-01 2018-09-01 2018-10-01  ... 2023-09-01  \\\n",
       "ITEM_ID                                              ...              \n",
       "520130       101.9      101.9      101.8      101.7  ...    103.171   \n",
       "520131        99.8      101.3      101.6      101.6  ...    104.996   \n",
       "520137       101.8      103.1      101.6        100  ...    106.581   \n",
       "520140       101.5      103.9      106.5      104.1  ...    106.510   \n",
       "510439           -          -          -          -  ...    107.534   \n",
       "...            ...        ...        ...        ...  ...        ...   \n",
       "620315       101.5      102.4      103.5      103.9  ...    103.140   \n",
       "610204       101.6      102.1      102.2      101.7  ...    104.438   \n",
       "610303       101.1         98      102.2      100.8  ...    100.837   \n",
       "620303       102.2      102.7      103.6      104.4  ...    104.932   \n",
       "620307       125.1      128.4      131.3      133.7  ...     98.778   \n",
       "\n",
       "        2023-10-01 2023-11-01 2023-12-01  2024-01-01 2024-02-01 2024-03-01  \\\n",
       "ITEM_ID                                                                      \n",
       "520130     103.512    103.590    104.303  104.628425    100.668    101.100   \n",
       "520131     105.725    105.627    104.516  103.183421    101.055    101.604   \n",
       "520137     105.616    104.579    105.486  103.811937    100.924    101.505   \n",
       "520140     107.907    108.389    109.608  106.663929    100.124    101.761   \n",
       "510439     106.664    106.459    108.471  107.889595    100.920    101.053   \n",
       "...            ...        ...        ...         ...        ...        ...   \n",
       "620315     103.525    104.161    105.209  105.416262    100.102    100.121   \n",
       "610204     103.350    102.832    101.731  102.009743    100.118    100.593   \n",
       "610303     102.039     99.620     99.211  101.679370    100.583     98.466   \n",
       "620303     104.936    105.418    105.439  105.527569    100.182     99.609   \n",
       "620307      94.677     91.695     89.075   86.916713     97.306     96.447   \n",
       "\n",
       "        2024-04-01 2024-05-01 2024-06-01  \n",
       "ITEM_ID                                   \n",
       "520130     101.660    102.598    102.651  \n",
       "520131     100.958    101.136     99.470  \n",
       "520137     100.543    103.076    103.302  \n",
       "520140     101.934    101.117    101.672  \n",
       "510439     100.776    100.803    100.781  \n",
       "...            ...        ...        ...  \n",
       "620315     100.822    102.328    102.896  \n",
       "610204     100.628    101.437    102.328  \n",
       "610303      97.569    100.088    100.241  \n",
       "620303      99.360     99.429     99.411  \n",
       "620307      99.169     99.866    103.092  \n",
       "\n",
       "[444 rows x 78 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chained"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# calculate monthly growth\n",
    "monthlygrowth=chained.copy()\n",
    "\n",
    "for col in monthlygrowth:\n",
    "    for i, row_value in monthlygrowth[col].items():\n",
    "        if(col<meta.loc[i,'ITEM_START']):\n",
    "            monthlygrowth.at[i,col]=None\n",
    "        else:\n",
    "            if(col<startref+pd.tseries.offsets.DateOffset(months=1)):\n",
    "                monthlygrowth.at[i,col]=None\n",
    "            else:\n",
    "                monthlygrowth.at[i,col]=(float(row_value)- \\\n",
    "                float(chained.loc[i,col-pd.tseries.offsets.DateOffset(months=1)])) * 100 / \\\n",
    "                float(chained.loc[i,col-pd.tseries.offsets.DateOffset(months=1)])\n",
    "\n",
    "#rename columns to dates without time formats\n",
    "columns = {}\n",
    "for col in monthlygrowth.columns:\n",
    "    try:\n",
    "        columns[col] = col.date()\n",
    "    except ValueError:\n",
    "        pass\n",
    "monthlygrowth.rename(columns=columns, inplace=True)\n",
    "                \n",
    "monthlygrowth.astype(float).round(0).astype(int,errors='ignore').to_csv('monthlygrowth.csv',date_format='%Y-%m-%d',na_rep='',float_format=\"%.0f\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Finally save the unchained and chainedvnumbers to csv\n",
    "#rename columns to dates without time formats\n",
    "columns = {}\n",
    "for col in un.columns:\n",
    "    try:\n",
    "        columns[col] = col.date()\n",
    "    except ValueError:\n",
    "        pass\n",
    "un.rename(columns=columns, inplace=True)\n",
    "\n",
    "#and save it\n",
    "un.to_csv('unchained.csv')\n",
    "\n",
    "#rename columns to dates without time formats\n",
    "columns = {}\n",
    "for col in chained.columns:\n",
    "    try:\n",
    "        columns[col] = col.date()\n",
    "    except ValueError:\n",
    "        pass\n",
    "chained.rename(columns=columns, inplace=True)\n",
    "\n",
    "chained.astype(float).round(3).to_csv('chained.csv',date_format='%Y-%m-%d',na_rep='')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#turn it into a excel datadownload file\n",
    "# with pd.ExcelWriter(\"datadownload.xlsx\", mode=\"a\", if_sheet_exists=\"replace\", date_format=\"YYYY-MM-DD\", datetime_format=\"YYYY-MM-DD\") as writer:\n",
    "#     meta.drop(columns=['AVERAGE_PRICE']).to_excel(writer, sheet_name=\"metadata\")  \n",
    "#     # un.to_excel(writer, sheet_name=\"unchained\")\n",
    "#     chained.astype(float).round(3).transpose().to_excel(writer, sheet_name=\"chained\")\n",
    "#     avgprice.astype(float).round(2).fillna('').transpose().to_excel(writer, sheet_name=\"averageprice\")\n",
    "#     monthlygrowth.astype(float).round(0).fillna('').transpose().to_excel(writer, sheet_name=\"monthlygrowth\")\n",
    "#     annualgrowth.astype(float).round(0).fillna('').transpose().to_excel(writer,sheet_name=\"annualgrowth\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#turn it into a excel datadownload file\n",
    "with pd.ExcelWriter(\"datadownload.xlsx\", mode=\"a\", if_sheet_exists=\"replace\", date_format=\"YYYY-MM-DD\", datetime_format=\"YYYY-MM-DD\") as writer:\n",
    "    meta.drop(columns=['AVERAGE_PRICE']).to_excel(writer, sheet_name=\"Metadata\")  \n",
    "    # un.to_excel(writer, sheet_name=\"unchained\")\n",
    "    \n",
    "    # make it tidy, join on meta data, reorder columns by index\n",
    "    chained.astype(float).round(3).reset_index().melt(id_vars=['ITEM_ID'],var_name='Date',value_name='Value').dropna()\\\n",
    "    .merge(meta.reset_index()[['Category1','Category2','ITEM_ID','ITEM_DESC','WEIGHT\\SIZE']])\\\n",
    "    .iloc[:,[1,0,3,4,5,6,2]]\\\n",
    "    .to_excel(writer, index=False, sheet_name=\"Chained\")\n",
    "    \n",
    "    avgprice.astype(float).round(2).fillna('').reset_index().melt(id_vars=['ITEM_ID'],var_name='Date',value_name='Price').dropna()\\\n",
    "    .merge(meta.reset_index()[['Category1','Category2','ITEM_ID','ITEM_DESC','WEIGHT\\SIZE']])\\\n",
    "    .iloc[:,[1,0,3,4,5,6,2]]\\\n",
    "    .to_excel(writer, index=False, sheet_name=\"Average price\")\n",
    "    \n",
    "    monthlygrowth.astype(float).round(0).fillna('').reset_index().melt(id_vars=['ITEM_ID'],var_name='Date',value_name='Percentage').dropna() \\\n",
    "    .merge(meta.reset_index()[['Category1','Category2','ITEM_ID','ITEM_DESC','WEIGHT\\SIZE']])\\\n",
    "    .iloc[:,[1,0,3,4,5,6,2]]\\\n",
    "    .to_excel(writer, index=False, sheet_name=\"Monthly growth\")\n",
    "    \n",
    "    annualgrowth.astype(float).round(0).fillna('').reset_index().melt(id_vars=['ITEM_ID'],var_name='Date', value_name='Percentage').dropna() \\\n",
    "    .merge(meta.reset_index()[['Category1','Category2','ITEM_ID','ITEM_DESC','WEIGHT\\SIZE']])\\\n",
    "    .iloc[:,[1,0,3,4,5,6,2]]\\\n",
    "    .to_excel(writer,index=False, sheet_name=\"Annualgrowth\")\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.8.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}