transformations_but_difficult.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "200cae0f",
   "metadata": {},
   "source": [
    "Reading in the data from stat.gov"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "59324aad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import xmltodict\n",
    "import requests\n",
    "import re\n",
    "\n",
    "def get_dataset_ids(list_of_table_names):\n",
    "    r = requests.get(\"https://osp-rs.stat.gov.lt/rest_xml/dataflow/\")\n",
    "    metadata_dict = dict(xmltodict.parse(r.text))\n",
    "    dataset_ids = []\n",
    "    \n",
    "    for i in metadata_dict[\"mes:Structure\"][\"mes:Structures\"][\"str:Dataflows\"][\"str:Dataflow\"]:\n",
    "        matches = []\n",
    "        for j in list_of_table_names:\n",
    "            j = j.lower()\n",
    "            j = re.sub(r'[^\\w\\s]','',j)\n",
    "            j = re.sub(' +', ' ',j)\n",
    "            name = i[\"com:Name\"][0][\"#text\"].lower()\n",
    "            name = re.sub(r'[^\\w\\s]','',name)\n",
    "            name = re.sub(' +', ' ',name)\n",
    "            matches.append(j in name)\n",
    "        if any(matches):\n",
    "            dataset_ids.append(i[\"@id\"])\n",
    "            \n",
    "    return dataset_ids\n",
    "\n",
    "def construct_links(dataset_ids,start_period=\"2015-01\"):\n",
    "    links = []\n",
    "    link_beginning = \"https://osp-rs.stat.gov.lt/rest_json/data/\"\n",
    "\n",
    "    for i in dataset_ids:\n",
    "        links.append(link_beginning + i + \"/?startPeriod=\" + start_period)\n",
    "    \n",
    "    return links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "36c6d754",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "miestai = [\"Kauno m. sav.\",\"Vilniaus m. sav.\",\"Klaipėdos m. sav.\",\"Šiaulių m. sav.\",\"Panevežio m. sav.\",\"Alytaus m. sav.\"]\n",
    "\n",
    "def dataset_to_dataframe(link):\n",
    "    r = requests.get(link)\n",
    "    json_dict = r.json()\n",
    "    skip = False\n",
    "    \n",
    "    if not json_dict[\"dataSets\"][0][\"observations\"]:\n",
    "        skip = True\n",
    "    for i in json_dict[\"structure\"][\"attributes\"][\"dataSet\"]:\n",
    "        if i[\"id\"] == \"DS_REGIONAL\":\n",
    "            if i[\"values\"][0][\"id\"]==\"N\":\n",
    "                skip = True\n",
    "      #  if i[\"id\"] == \"DS_TIME_FORMAT\":\n",
    "      #      if i[\"values\"][0][\"name\"] not in [\"Metai\",\"Mokymo metai\"]:\n",
    "      #          skip = True\n",
    "    \n",
    "    if not skip:\n",
    "        dimensions_dict = {}\n",
    "\n",
    "        for i in json_dict[\"structure\"][\"dimensions\"][\"observation\"]:\n",
    "            dimensions_dict[i[\"name\"]] = i[\"keyPosition\"]\n",
    "\n",
    "            \n",
    "      #  if list(dimensions_dict.keys())[0] != \"Administracinė teritorija\":\n",
    "       #     return \"This dataset has an unusual spatial dimension\"\n",
    "        list_of_mappings = []\n",
    "\n",
    "        for i in json_dict[\"structure\"][\"dimensions\"][\"observation\"]:\n",
    "            mapping_dict = {}\n",
    "            for ind,j in enumerate(i[\"values\"]):\n",
    "                mapping_dict[str(ind)] = j[\"name\"]\n",
    "            list_of_mappings.append(mapping_dict)   \n",
    "\n",
    "        rows = []\n",
    "\n",
    "        for i in json_dict[\"dataSets\"][0][\"observations\"].keys():\n",
    "            rows.append(i.split(\":\"))\n",
    "\n",
    "        df = pd.DataFrame(rows)\n",
    "        for i, j in enumerate(list_of_mappings):\n",
    "            df[i].replace(j,inplace=True)\n",
    "\n",
    "        df.columns = dimensions_dict.keys()\n",
    "\n",
    "        \n",
    "        values = []\n",
    "\n",
    "        for i in json_dict[\"dataSets\"][0][\"observations\"].values():\n",
    "            values.append(i[0])\n",
    "        \n",
    "        df[\"Reikšmė\"]=pd.Series(values)\n",
    "\n",
    "        df=df[df[\"Administracinė teritorija\"].isin(miestai)]\n",
    "        \n",
    "        df.name = json_dict[\"structure\"][\"name\"]\n",
    "        \n",
    "        return df\n",
    "    else:\n",
    "        print(\"Skipped\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1575,
   "id": "a46d1c00",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['S1R078_M8010302',\n",
       " 'S1R079_M8010301',\n",
       " 'S5R003_M7030327_3',\n",
       " 'S5R003_M7030211_3',\n",
       " 'S5R087_M7030320_1',\n",
       " 'S1R121_M9070335_1',\n",
       " 'S1R121_M9070335_2',\n",
       " 'S1R124_M9070338_1']"
      ]
     },
     "execution_count": 1575,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_names = [\"Perdirbtos komunalinės atliekos\",\n",
    "            \"Teršalų, išmestų į aplinkos orą iš stacionarių taršos šaltinių\",\"Ūkio, buities ir gamybos nuotekų išleidimas į paviršinius vandenis\",\n",
    "              \"Vandens sunaudojimas\",\"Autobusų rida\",\"Vidutiniškai vienam gyventojui tenka kelionių autobusais\"]\n",
    "\n",
    "table_ids=get_dataset_ids(table_names)\n",
    "\n",
    "table_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1577,
   "id": "f27e4e6b",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Skipped\n",
      "Skipped\n",
      "Skipped\n"
     ]
    }
   ],
   "source": [
    "links=construct_links(table_ids)\n",
    "\n",
    "df_list = []\n",
    "for i in links:\n",
    "    df_list.append(dataset_to_dataframe(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "dc352ccb",
   "metadata": {},
   "outputs": [],
   "source": [
    "table_names = [\"Nuolatinių gyventojų skaičius liepos 1 d.\"]\n",
    "table_ids=get_dataset_ids(table_names)\n",
    "\n",
    "links=construct_links(table_ids)\n",
    "\n",
    "population_df = dataset_to_dataframe(links[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1562,
   "id": "d0eae921",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "population_df = population_df[population_df.iloc[:,1] == \"Miestas ir kaimas\"].iloc[:,[0,3,4]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1829,
   "id": "47b11fc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list2 = [df_list[0],df_list[1],df_list[2],df_list[4],df_list[6]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1830,
   "id": "ca0cfe5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list3 = []\n",
    "for i in df_list2:\n",
    "    x = pd.merge(i,population_df,on=[\"Laikotarpis\",\"Administracinė teritorija\"])\n",
    "    x[\"per_thousand\"]= x[\"Reikšmė_x\"]/x[\"Reikšmė_y\"]*1000\n",
    "    df_list3.append(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1831,
   "id": "b6130a1a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for i,j in enumerate(df_list3):\n",
    "    if i != 3:\n",
    "        j.drop([\"Matavimo vienetai\",\"Reikšmė_x\",\"Reikšmė_y\"],axis=1,inplace=True)\n",
    "        j.rename(columns={\"Administracinė teritorija\": \"city\",\"Laikotarpis\":\"x\",\"per_thousand\":\"y\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1810,
   "id": "65114de1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list3[3] = df_list3[3][df_list3[3].iloc[:,1]==\"Autobusai\"]\n",
    "\n",
    "df_list3[3].drop(columns=[\"Matavimo vienetai\",\"Reikšmė_y\",\"per_thousand\",\"Transporto rūšis (Autobusai)\"],inplace=True)\n",
    "df_list3[3] = df_list3[3].replace(\"Klaipėdos m. sav.\", \"Klaipėda\").replace(\"Kauno m. sav.\", \"Kaunas\").replace(\"Vilniaus m. sav.\", \"Vilnius\").replace(\"Panevėžio m. sav.\", \"Panevėžys\").replace(\"Šiaulių m. sav.\", \"Šiauliai\").replace(\"Alytaus m. sav.\", \"Alytus\").round(1)\n",
    "df_list3[3].rename(columns={\"Administracinė teritorija\": \"city\",\"Laikotarpis\":\"x\",\"Reikšmė_x\":\"y\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1816,
   "id": "c601ffdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list3[3].to_csv(\"kelioniu_kiekis.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1827,
   "id": "57b66867",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped=df_list3[4][df_list3[4][\"Teršalai\"].isin([\"Azoto oksidai, tonos\",\"Anglies monoksidas, tonos\",\"Kietosios medžiagos\"])].round(1).groupby(\"Teršalai\")\n",
    "\n",
    "l=[grouped.get_group(x) for x in grouped.groups]\n",
    "l=[i.iloc[:,[0,2,3]].replace(\"Klaipėdos m. sav.\", \"Klaipėda\").replace(\"Kauno m. sav.\", \"Kaunas\").replace(\"Vilniaus m. sav.\", \"Vilnius\").replace(\"Panevėžio m. sav.\", \"Panevėžys\").replace(\"Šiaulių m. sav.\", \"Šiauliai\").replace(\"Alytaus m. sav.\", \"Alytus\") for i in l]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1823,
   "id": "33e10c26",
   "metadata": {},
   "outputs": [],
   "source": [
    "l[0].to_csv(\"tersalai_co.csv\",index=False)\n",
    "l[1].to_csv(\"tersalai_no.csv\",index=False)\n",
    "l[2].to_csv(\"tersalai_kietosios.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1833,
   "id": "7ae0c1b9",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "grouped=df_list3[1][df_list3[1].iloc[:,0].isin([\"Sunaudota vandens energetikos reikmėms\",\"Sunaudota vandens ūkio ir buities reikmėms\"])].round(1).groupby(\"Vandens naudojimo paskirtis\")\n",
    "\n",
    "l=[grouped.get_group(x) for x in grouped.groups]\n",
    "l=[i.iloc[:,[1,2,3]].replace(\"Klaipėdos m. sav.\", \"Klaipėda\").replace(\"Kauno m. sav.\", \"Kaunas\").replace(\"Vilniaus m. sav.\", \"Vilnius\").replace(\"Panevėžio m. sav.\", \"Panevėžys\").replace(\"Šiaulių m. sav.\", \"Šiauliai\").replace(\"Alytaus m. sav.\", \"Alytus\") for i in l]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1838,
   "id": "9975a904",
   "metadata": {},
   "outputs": [],
   "source": [
    "l[1].to_csv(\"vandens_sunaudojimas_buiciai.csv\",index=False)\n",
    "l[0].to_csv(\"vandens_sunaudojimas_energetikai.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1822,
   "id": "141a137a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list3[2][df_list3[2][\"Reiso tipas\"]==\"Reguliarus reisas\"].iloc[:,[1,2,3]].round(1).replace(\"Klaipėdos m. sav.\", \"Klaipėda\").replace(\"Kauno m. sav.\", \"Kaunas\").replace(\"Vilniaus m. sav.\", \"Vilnius\").replace(\"Panevėžio m. sav.\", \"Panevėžys\").replace(\"Šiaulių m. sav.\", \"Šiauliai\").replace(\"Alytaus m. sav.\", \"Alytus\").to_csv(\"viesojo_rida.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c347d0c2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2e1bb64d",
   "metadata": {},
   "source": [
    "Waste data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 961,
   "id": "0a0b92b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "waste = pd.read_csv(\"urb_cenv_1_Data.csv\",encoding = \"ISO-8859-1\",na_values=\":\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 962,
   "id": "4c6dc697",
   "metadata": {},
   "outputs": [],
   "source": [
    "miestai = [\"Klaipeda\",\"Siauliai\",\"Vilnius\",\"Kaunas\",\"Panevezys\",\"Alytus\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 963,
   "id": "d36703b9",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dovyd\\AppData\\Local\\Temp/ipykernel_13704/1620741886.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  waste = waste[waste[\"CITIES\"].isin(miestai)][(waste[\"TIME\"]>=2015) & (waste[\"TIME\"]<2020)].iloc[:,[0,1,3]]\n"
     ]
    }
   ],
   "source": [
    "waste = waste[waste[\"CITIES\"].isin(miestai)][(waste[\"TIME\"]>=2015) & (waste[\"TIME\"]<2020)].iloc[:,[0,1,3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 964,
   "id": "474ab48a",
   "metadata": {},
   "outputs": [],
   "source": [
    "l = []\n",
    "for i in waste[\"Value\"]:\n",
    "    l.append(float(i))\n",
    "\n",
    "waste[\"Value\"] = l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 965,
   "id": "19fd6f00",
   "metadata": {},
   "outputs": [],
   "source": [
    "waste.rename(columns={\"TIME\":\"x\",\"CITIES\":\"city\",\"Value\":\"y\"},inplace=True)\n",
    "waste.replace({\"Panevezys\":\"Panevėžys\",\"Siauliai\":\"Šiauliai\",\"Klaipeda\":\"Klaipėda\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 966,
   "id": "f0048e99",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"pop.csv\").drop(columns=[\"Rodiklis\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 967,
   "id": "5af0834f",
   "metadata": {},
   "outputs": [],
   "source": [
    "population_df = df[df.iloc[:,2] == \"Miestas ir kaimas\"].iloc[:,[0,1,4]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 968,
   "id": "4fc1eb6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "population_df = population_df.replace(\"Klaipėdos m. sav.\", \"Klaipėda\").replace(\"Kauno m. sav.\", \"Kaunas\").replace(\"Vilniaus m. sav.\", \"Vilnius\").replace(\"Panevėžio m. sav.\", \"Panevėžys\").replace(\"Šiaulių m. sav.\", \"Šiauliai\").replace(\"Alytaus m. sav.\", \"Alytus\")\n",
    "population_df.rename(columns={\"Administracinė teritorija\":\"city\",\"Laikotarpis\":\"x\",\"Reikšmė\":\"y\"},inplace=True)\n",
    "population_df[\"x\"]=pd.to_numeric(population_df[\"x\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 969,
   "id": "f1339f10",
   "metadata": {},
   "outputs": [],
   "source": [
    "waste = pd.merge(waste,population_df,on=[\"x\",\"city\"])\n",
    "waste[\"y\"]= waste[\"y_x\"]/waste[\"y_y\"]*1000\n",
    "\n",
    "waste = waste.loc[:,[\"x\",\"city\",\"y\"]]\n",
    "waste.dropna().round(2).to_csv(\"kpi/siuksles_surinktos.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3a2e007",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ce6f5e8d",
   "metadata": {},
   "source": [
    "Calculating the score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "43fdae7f",
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir(\"..\")\n",
    "os.chdir(\"..\")\n",
    "os.chdir(\"lws\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4292ddf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "e2c96b64",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "files = os.listdir(\"kpi\")\n",
    "\n",
    "files = [i for i in files if i not in  [\"categories_2.csv\",\"indicators_scores_2.csv\",\"kpi.csv\",\"indicators_scores.csv\",\"categories.csv\",\"categories_ranks.csv\",\"totalranks.csv\",\"siuksles.csv\",\"oras.csv\",\"transportas.csv\",\"vanduo.csv\",\"lycatbar.csv\",\"lycats.csv\",\"total.csv\"]]\n",
    "files_main = [i.split(\".\")[0] for i in files]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ec7aa33b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"kpi/\"+files[0]).rename(columns={\"y\":files_main[0]})\n",
    "for i,j in enumerate(files[1:]):\n",
    "    df = pd.merge(df,pd.read_csv(\"kpi/\"+j),on=[\"x\",\"city\"],how=\"outer\").rename(columns={\"y\":files_main[i+1]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "60f227ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "trans = [2,10]\n",
    "water = [3,8,9]\n",
    "air = [4,5,6]\n",
    "trash = [7]\n",
    "\n",
    "categories = [trans,water,air,trash]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "3bc5d17f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[:,[2,3,10]] =  -1 * df.iloc[:,[2,3,10]]\n",
    "df = df.groupby(['city'], sort=False).apply(lambda x: x.ffill())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ef39a107",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped = df.groupby(\"x\")\n",
    "\n",
    "l=[grouped.get_group(x) for x in grouped.groups]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "70cd4efb",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dovyd\\anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:1851: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._setitem_single_column(loc, val, pi)\n"
     ]
    }
   ],
   "source": [
    "df_list = []\n",
    "df_ranks_list = []\n",
    "for df in l:\n",
    "    df_min_max = df.iloc[:,2:]\n",
    "    df.iloc[:,2:] = abs(((df_min_max-df_min_max.min())/(df_min_max.max()-df_min_max.min()))-1)\n",
    "    for j,i in enumerate(categories):\n",
    "        df.iloc[:,i] = df.iloc[:,i] * (25/len(i))\n",
    "        df[\"cat\"+str(j)]=df.iloc[:,categories[j]].sum(axis=1)\n",
    "    df.rename(columns={\"cat0\":\"transportas\",\"cat1\":\"vanduo\",\"cat2\":\"oras\",\"cat3\":\"šiukšles\"},inplace=True)\n",
    "    df[\"total\"] = df.iloc[:,11:11+len(categories)].sum(axis=1)\n",
    "    indices = [0,1,11,12,13,14,15]\n",
    "    df= df.iloc[:,indices]\n",
    "    \n",
    "    df = df.round(1)\n",
    "    df_ranks = df.copy()\n",
    "    df_ranks.iloc[:,2:] = df.iloc[:,2:].rank(ascending=False).rename(columns = lambda x: x+\"_rank\")\n",
    "    df_ranks[\"city\"]=df[\"city\"]\n",
    "    df_ranks_list.append(df_ranks)\n",
    "    df_list.append(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1065,
   "id": "eb6852eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(df_list).to_csv(\"categories.csv\",index=False)\n",
    "pd.concat(df_ranks_list).to_csv(\"categories_ranks.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1071,
   "id": "a6b5458a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(df_list).to_csv(\"indicators_scores.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6442399",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37182b8b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ac2f63ae",
   "metadata": {},
   "source": [
    "Algorithm for recalculating the weights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1016,
   "id": "7ca10b0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"kpi/categories.csv\")\n",
    "weights = [25,25,25,25]\n",
    "df.iloc[:,[2,3,4,5]] = df.iloc[:,[2,3,4,5]] * pd.Series(weights,index = df.columns[[2,3,4,5]]) / 25 * 100 / sum(weights)\n",
    "df[\"total\"]= df.iloc[:,[2,3,4,5]].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 934,
   "id": "b1b20146",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_ranks = df.copy()\n",
    "df_ranks.iloc[:,2:] = df_ranks.iloc[:,1:].groupby(\"x\").rank(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5b71363",
   "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}