python_useful_functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d3fb07f4",
   "metadata": {},
   "source": [
    "# Useful Python functions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4c33e80",
   "metadata": {},
   "source": [
    "This notebook provides some helpful python functions that allow data cleaning for some of the trickiest dataets to download-"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fe1e6f4",
   "metadata": {},
   "source": [
    "Next there is the example of the dataset available at: https://ajuntament.barcelona.cat/estadistica/castella/Estadistiques_per_temes/Economia/Renda_i_tributs/Distribucio_territorial_renda/evo/rfbarris.htm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "15c18b8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e6fea29e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# In pandas.read_excel there are some parameters that allow you to choose the header row or the colums you want to use\n",
    "# and remove other columns\n",
    "\n",
    "df = pd.read_excel('../dataset_renta.xlsx', header = 8, \n",
    "                   usecols = [0,4,5,6,7])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c9798141",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['1. el Raval',\n",
       " '2. el Barri Gòtic',\n",
       " '3. la Barceloneta',\n",
       " '4. Sant Pere, Santa Caterina i la Ribera',\n",
       " '5. el Fort Pienc',\n",
       " '6. la Sagrada Família',\n",
       " \"7. la Dreta de l'Eixample\",\n",
       " \"8. l'Antiga Esquerra de l'Eixample\",\n",
       " \"9. la Nova Esquerra de l'Eixample\",\n",
       " '10. Sant Antoni',\n",
       " '11. el Poble Sec - AEI Parc Montjuïc',\n",
       " '12. la Marina del Prat Vermell - AEI Zona Franca',\n",
       " '13. la Marina de Port',\n",
       " '14. la Font de la Guatlla',\n",
       " '15. Hostafrancs',\n",
       " '16. la Bordeta',\n",
       " '17. Sants - Badal',\n",
       " '18. Sants',\n",
       " '19. les Corts',\n",
       " '20. la Maternitat i Sant Ramon',\n",
       " '21. Pedralbes',\n",
       " '22. Vallvidrera, el Tibidabo i les Planes',\n",
       " '23. Sarrià',\n",
       " '24. les Tres Torres',\n",
       " '25. Sant Gervasi - la Bonanova',\n",
       " '26. Sant Gervasi - Galvany',\n",
       " '27. el Putxet i el Farró',\n",
       " '28. Vallcarca i els Penitents',\n",
       " '29. el Coll',\n",
       " '30. la Salut',\n",
       " '31. la Vila de Gràcia',\n",
       " \"32. el Camp d'en Grassot i Gràcia Nova\",\n",
       " '33. el Baix Guinardó',\n",
       " '34. Can Baró',\n",
       " '35. el Guinardó',\n",
       " \"36. la Font d'en Fargues\",\n",
       " '37. el Carmel',\n",
       " '38. la Teixonera',\n",
       " '39. Sant Genís dels Agudells',\n",
       " '40. Montbau',\n",
       " \"41. la Vall d'Hebron\",\n",
       " '42. la Clota',\n",
       " '43. Horta',\n",
       " '44. Vilapicina i la Torre Llobeta',\n",
       " '45. Porta',\n",
       " '46. el Turó de la Peira',\n",
       " '47. Can Peguera',\n",
       " '48. la Guineueta',\n",
       " '49. Canyelles',\n",
       " '50. les Roquetes',\n",
       " '51. Verdun',\n",
       " '52. la Prosperitat',\n",
       " '53. la Trinitat Nova',\n",
       " '54. Torre Baró',\n",
       " '55. Ciutat Meridiana',\n",
       " '56. Vallbona',\n",
       " '57. la Trinitat Vella',\n",
       " '58. Baró de Viver',\n",
       " '59. el Bon Pastor',\n",
       " '60. Sant Andreu',\n",
       " '61. la Sagrera',\n",
       " '62. el Congrés i els Indians',\n",
       " '63. Navas',\n",
       " \"64. el Camp de l'Arpa del Clot\",\n",
       " '65. el Clot',\n",
       " '66. el Parc i la Llacuna del Poblenou',\n",
       " '67. la Vila Olímpica del Poblenou',\n",
       " '68. el Poblenou',\n",
       " '69. Diagonal Mar i el Front Marítim del Poblenou',\n",
       " '70. el Besòs i el Maresme',\n",
       " '71. Provençals del Poblenou',\n",
       " '72. Sant Martí de Provençals',\n",
       " '73. la Verneda i la Pau']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We replace the names of the variables from the dataset\n",
    "\n",
    "df.columns = ['Barri', 'T1', 'T2', 'T3', 'T4']\n",
    "\n",
    "# dropna() removes all the rows with nan values\n",
    "df = df.dropna()\n",
    "\n",
    "# You can remove rows, in this case the first one\n",
    "df = df.drop(index = 0)\n",
    "\n",
    "\n",
    "# Some of the datasets can have blank spaces in the string values, with a simple map a lambda function and the\n",
    "# function lstrip  (left-strip) you can get rido of certain characters\n",
    "df.Barri.map(lambda x : x.lstrip('     '))\n",
    "\n",
    "# Again, it can be that some string have other no desirable characters, you can first replace them to blank spaces\n",
    "# and then remove all of them from certain columns\n",
    "df[['Districte', 'Barri']] = [i.replace('\\xa0', ' ').split(' ', 1) for i in df.Barri]\n",
    "[i.lstrip(' ').rstrip(' ') for i in df.Barri]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2c8d0eb2",
   "metadata": {},
   "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>Barri</th>\n",
       "      <th>T1</th>\n",
       "      <th>T2</th>\n",
       "      <th>T3</th>\n",
       "      <th>T4</th>\n",
       "      <th>Districte</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>el Raval</td>\n",
       "      <td>864.5</td>\n",
       "      <td>853.3</td>\n",
       "      <td>833.9</td>\n",
       "      <td>787.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>el Barri Gòtic</td>\n",
       "      <td>1110.2</td>\n",
       "      <td>1041.0</td>\n",
       "      <td>982.5</td>\n",
       "      <td>1003.9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>la Barceloneta</td>\n",
       "      <td>899.5</td>\n",
       "      <td>856.5</td>\n",
       "      <td>823.1</td>\n",
       "      <td>875.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sant Pere, Santa Caterina i la Ribera</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>919.8</td>\n",
       "      <td>970.9</td>\n",
       "      <td>888.9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>el Fort Pienc</td>\n",
       "      <td>1059.9</td>\n",
       "      <td>999.9</td>\n",
       "      <td>997.4</td>\n",
       "      <td>959.5</td>\n",
       "      <td>2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Diagonal Mar i el Front Marítim del Poblenou</td>\n",
       "      <td>1233.2</td>\n",
       "      <td>840.9</td>\n",
       "      <td>1074.4</td>\n",
       "      <td>1392.6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>el Besòs i el Maresme</td>\n",
       "      <td>749.7</td>\n",
       "      <td>696.9</td>\n",
       "      <td>801.5</td>\n",
       "      <td>731.6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Provençals del Poblenou</td>\n",
       "      <td>1000.1</td>\n",
       "      <td>1076.6</td>\n",
       "      <td>956.8</td>\n",
       "      <td>859.9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Sant Martí de Provençals</td>\n",
       "      <td>863.0</td>\n",
       "      <td>854.3</td>\n",
       "      <td>822.4</td>\n",
       "      <td>807.4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>la Verneda i la Pau</td>\n",
       "      <td>765.3</td>\n",
       "      <td>767.5</td>\n",
       "      <td>782.4</td>\n",
       "      <td>765.9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              Barri      T1      T2      T3  \\\n",
       "2                                        el Raval     864.5   853.3   833.9   \n",
       "3                                  el Barri Gòtic    1110.2  1041.0   982.5   \n",
       "4                                  la Barceloneta     899.5   856.5   823.1   \n",
       "5           Sant Pere, Santa Caterina i la Ribera    1030.0   919.8   970.9   \n",
       "6                                   el Fort Pienc    1059.9   999.9   997.4   \n",
       "..                                              ...     ...     ...     ...   \n",
       "70   Diagonal Mar i el Front Marítim del Poblenou    1233.2   840.9  1074.4   \n",
       "71                          el Besòs i el Maresme     749.7   696.9   801.5   \n",
       "72                        Provençals del Poblenou    1000.1  1076.6   956.8   \n",
       "73                       Sant Martí de Provençals     863.0   854.3   822.4   \n",
       "74                            la Verneda i la Pau     765.3   767.5   782.4   \n",
       "\n",
       "        T4 Districte  \n",
       "2    787.0         1  \n",
       "3   1003.9         1  \n",
       "4    875.0         1  \n",
       "5    888.9         1  \n",
       "6    959.5         2  \n",
       "..     ...       ...  \n",
       "70  1392.6        10  \n",
       "71   731.6        10  \n",
       "72   859.9        10  \n",
       "73   807.4        10  \n",
       "74   765.9        10  \n",
       "\n",
       "[73 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you want to split a column into two different oclumsn, you can use the function split()\n",
    "#indicating on what character to split\n",
    "df[['New_variable', 'Barri']] = [i.split('.', 1) for i in df.Barri]\n",
    "\n",
    "# You can drop non desired columnso or rows using drop(), axis = 0 are columns, 1 are rows\n",
    "df = df.drop(labels = 'New_variable', axis = 1)\n",
    "\n",
    "# There might be some difficult converting strings to numeric values:\n",
    "# First make a list of the desired numeric columns\n",
    "numeric_columns = ['T1','T2','T3','T4']\n",
    "\n",
    "# Using a for loop you can replace the '.' for blank spaces and the ',' to '.' so the function\n",
    "# pandas.to_numeric can convert the strings to numbers\n",
    "for i in numeric_columns:\n",
    "    df[i.strip()] = pd.to_numeric(df[i].str.strip('\\xa0').map(lambda x : x.replace('.', '')).map(lambda x : x.replace(',', '.')).replace(['nd','-'], np.nan))\n",
    "\n",
    "pd.set_option('display.max_rows', 10)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12b1b3ce",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}