Esercizio1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3ddec69f-c350-4285-9d2a-55402aeb95ce",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from dateutil.relativedelta import relativedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d8d523bf-147b-468f-ab29-cfccfa348659",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"DMnuovo.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54694dde-32b5-453c-8f09-74c9b13b73b5",
   "metadata": {},
   "source": [
    "vado a caricare il mio set di dati all'interno di un dataframe pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6924b2c2-abaa-44cd-9945-714c644a29b4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['Birthdate']:\n",
    "    if c.year>2010:\n",
    "        f = pd.Timestamp(c)\n",
    "        f = f - relativedelta(years=100)\n",
    "        df['Birthdate'] = df['Birthdate'].replace(c,f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bca7d7ab-1cf7-4ffb-81c0-7133c2334ab5",
   "metadata": {},
   "source": [
    "ho iterato un ciclo che prende in unput un oggetto della colonna birthdate alla volta e, se l'anno di questa data risulta essere maggiore di 2010, allora a questa data verranno sottratti 100 anni per riportarlo alla normalità."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3d9c8192-63b3-4cb4-900a-774f03f28ea7",
   "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>Customer</th>\n",
       "      <th>SSN</th>\n",
       "      <th>Birthdate</th>\n",
       "      <th>Age</th>\n",
       "      <th>Region</th>\n",
       "      <th>CredCardUser</th>\n",
       "      <th>Income</th>\n",
       "      <th>Purchases</th>\n",
       "      <th>AmtSpent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>539-84-9599</td>\n",
       "      <td>1944-10-26</td>\n",
       "      <td>78</td>\n",
       "      <td>East</td>\n",
       "      <td>0</td>\n",
       "      <td>62900</td>\n",
       "      <td>4</td>\n",
       "      <td>2080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>444-05-4079</td>\n",
       "      <td>1932-01-01</td>\n",
       "      <td>67</td>\n",
       "      <td>West</td>\n",
       "      <td>1</td>\n",
       "      <td>23300</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>418-18-5649</td>\n",
       "      <td>1973-08-17</td>\n",
       "      <td>25</td>\n",
       "      <td>East</td>\n",
       "      <td>1</td>\n",
       "      <td>48700</td>\n",
       "      <td>8</td>\n",
       "      <td>3990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>065-63-3311</td>\n",
       "      <td>1947-08-02</td>\n",
       "      <td>51</td>\n",
       "      <td>West</td>\n",
       "      <td>1</td>\n",
       "      <td>137600</td>\n",
       "      <td>2</td>\n",
       "      <td>920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>059-58-9566</td>\n",
       "      <td>1948-10-03</td>\n",
       "      <td>50</td>\n",
       "      <td>East</td>\n",
       "      <td>0</td>\n",
       "      <td>101400</td>\n",
       "      <td>2</td>\n",
       "      <td>1000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1495</th>\n",
       "      <td>1496</td>\n",
       "      <td>347-70-0762</td>\n",
       "      <td>1965-09-28</td>\n",
       "      <td>33</td>\n",
       "      <td>West</td>\n",
       "      <td>0</td>\n",
       "      <td>71800</td>\n",
       "      <td>2</td>\n",
       "      <td>970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1496</th>\n",
       "      <td>1497</td>\n",
       "      <td>638-19-2849</td>\n",
       "      <td>1930-07-31</td>\n",
       "      <td>68</td>\n",
       "      <td>South</td>\n",
       "      <td>0</td>\n",
       "      <td>121100</td>\n",
       "      <td>5</td>\n",
       "      <td>2540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>1498</td>\n",
       "      <td>670-57-4549</td>\n",
       "      <td>1954-07-21</td>\n",
       "      <td>44</td>\n",
       "      <td>North</td>\n",
       "      <td>1</td>\n",
       "      <td>64000</td>\n",
       "      <td>4</td>\n",
       "      <td>2160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1498</th>\n",
       "      <td>1499</td>\n",
       "      <td>166-84-2698</td>\n",
       "      <td>1966-10-30</td>\n",
       "      <td>32</td>\n",
       "      <td>South</td>\n",
       "      <td>0</td>\n",
       "      <td>91000</td>\n",
       "      <td>6</td>\n",
       "      <td>2910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1499</th>\n",
       "      <td>1500</td>\n",
       "      <td>366-03-5021</td>\n",
       "      <td>1934-09-23</td>\n",
       "      <td>64</td>\n",
       "      <td>South</td>\n",
       "      <td>0</td>\n",
       "      <td>121400</td>\n",
       "      <td>1</td>\n",
       "      <td>530</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1500 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Customer          SSN  Birthdate  Age  Region  CredCardUser  Income  \\\n",
       "0            1  539-84-9599 1944-10-26   78    East             0   62900   \n",
       "1            2  444-05-4079 1932-01-01   67    West             1   23300   \n",
       "2            3  418-18-5649 1973-08-17   25    East             1   48700   \n",
       "3            4  065-63-3311 1947-08-02   51    West             1  137600   \n",
       "4            5  059-58-9566 1948-10-03   50    East             0  101400   \n",
       "...        ...          ...        ...  ...     ...           ...     ...   \n",
       "1495      1496  347-70-0762 1965-09-28   33    West             0   71800   \n",
       "1496      1497  638-19-2849 1930-07-31   68   South             0  121100   \n",
       "1497      1498  670-57-4549 1954-07-21   44   North             1   64000   \n",
       "1498      1499  166-84-2698 1966-10-30   32   South             0   91000   \n",
       "1499      1500  366-03-5021 1934-09-23   64   South             0  121400   \n",
       "\n",
       "      Purchases  AmtSpent  \n",
       "0             4      2080  \n",
       "1             0         0  \n",
       "2             8      3990  \n",
       "3             2       920  \n",
       "4             2      1000  \n",
       "...         ...       ...  \n",
       "1495          2       970  \n",
       "1496          5      2540  \n",
       "1497          4      2160  \n",
       "1498          6      2910  \n",
       "1499          1       530  \n",
       "\n",
       "[1500 rows x 9 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bf176b28-5b98-4965-a3b2-dbc39c8b7991",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['CredCardUser']:\n",
    "    if c!=0 and c!=1:\n",
    "        print(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c2a8030-00e2-446c-aa78-0b244a474648",
   "metadata": {},
   "source": [
    "ho controllato che tutti i campi di credcarduser fossero valori 0 o 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0890d707-3265-439d-a9f7-2f4cdbf1a94b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['Purchases']:\n",
    "    if c == 9999:\n",
    "        df['Purchases'] = df['Purchases'].replace(c,None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32a25812-6998-4156-aa4c-7bb754e9620c",
   "metadata": {
    "tags": []
   },
   "source": [
    "vado a modificare i valori non validi (9999), con il valore null momentaneamente, per andarmi a calcolare la media dei valori restanti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "172ae240-1be1-4458-99d4-3ff99bc38691",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.948717948717949"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Purchases'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eca9307-887e-4ccf-9593-1a82b8b954e0",
   "metadata": {},
   "source": [
    "ho quindi calcolato la media della colonna con i valori dei Purchases, e lo vado a sostituire ai null inseriti precedentemente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e11933e4-ab89-4bae-9611-641fe4a843aa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['Purchases']:\n",
    "    if c == None:\n",
    "        df['Purchases'] = df['Purchases'].replace('None',3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c811f7d1-e957-4de4-adc8-8c82ed83772a",
   "metadata": {},
   "source": [
    "in questo modo abbiamo rimpiazzato i precedenti valori none, con la media approssimata per eccesso."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "bb5303de-7f47-4c68-93de-ad87465d8f4d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "nazioni = ['West','North','South','East']\n",
    "\n",
    "for c in df['Region']:\n",
    "    if c not in nazioni:\n",
    "        momentanea = c.replace(\" \",\"\")\n",
    "        df['Region'] = df['Region'].replace(c,momentanea)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2d07150-0229-44a2-8d51-7b358ba96324",
   "metadata": {},
   "source": [
    "ho definito una lista con le regioni senza gli spazi e faccio in modo che, quando troverò una regione con degli spazi, quindi formattata male, elimini gli spazi dalle stringhe, e che me le rimpiazzi senza, infatti :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "eb9f54be-b64f-4d30-98fa-ae2333847b47",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['Region']:\n",
    "    if c not in nazioni:\n",
    "        print(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cebc2152-4ed0-47e1-a13f-1e813655676e",
   "metadata": {},
   "source": [
    "non mi stampa nulla dato che nessuna regione è formattata male"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "924cbc87-0989-4b6f-b996-89fc72a3e5c0",
   "metadata": {},
   "source": [
    "vado ora a verificare che i valori degli SSN siano tutti diversi tra loro, vado infatti a contare i valori unici "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1f5b1f2d-e456-49bc-8590-79f4d8338bee",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "463    680-00-1375\n",
      "617    283-42-4994\n",
      "Name: SSN, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['SSN'][df['SSN'].duplicated()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95fcfc27-ef7c-4406-b80d-92600ed04511",
   "metadata": {},
   "source": [
    "Possiamo vedere come ci siano degli SSN uguali tra loro, andando ad elencare i duplicati della colonna SSN. Vado in questo caso ad eliminare i valori dato che non so se devo farlo o meno."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f338502-2ab3-4b46-840d-80d32abcdda1",
   "metadata": {},
   "source": [
    "Salvo gli indici dei valori in una lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f19243c5-b0bf-4168-932c-332344e91051",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "valori_da_eliminare = df['SSN'][df['SSN'].duplicated()].index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a236ab9-db3d-4da6-a407-69078485efb6",
   "metadata": {},
   "source": [
    "vado ad eliminare le righe che corrispondono agli indici salvati."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a9b12d34-bc6c-432f-bde7-ff7bce3d02b1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.drop(valori_da_eliminare, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ee43195-35ea-4cc7-a3d2-059a0609cc92",
   "metadata": {},
   "source": [
    "ho così eliminato le due righe che corrispondevano a quei determinati indici, dove vi erano gli SSN duplicati"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6a78d0a9-18b3-4a72-9299-3a7535159931",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series([], Name: SSN, dtype: object)\n"
     ]
    }
   ],
   "source": [
    "print(df['SSN'][df['SSN'].duplicated()])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c2f05e7-50e1-46e4-babc-c0d42b2ec1b5",
   "metadata": {},
   "source": [
    "se infatti andiamo a rieseguire questa istruzione vediamo che non ci sono SSN duplicati."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b4e64be-a8bb-413c-a091-0c8f86d49357",
   "metadata": {},
   "source": [
    "ora andiamo a far si che tutte le età che per sbaglio sono state scritte negative, siano riportate come normali"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "48726d77-dbe2-444b-8f78-f46086f55a14",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for c in df['Age']:\n",
    "    if c<0:\n",
    "        f = c*-1\n",
    "        df['Age'] = df['Age'].replace(c,f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0abb5c1c-8985-49a6-bfa7-2891d1f3facb",
   "metadata": {},
   "source": [
    "abbiamo fatto si che le età fossero riportate come dovrebbero."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5abfa81f-1293-4860-a8ed-80618ca6ded8",
   "metadata": {},
   "source": [
    "possiamo adesso esportare il file finito, con i dati ripuliti in excel o qualsivoglia formato."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d666564e-7060-425c-8af1-8954a2fa626c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.to_excel(\"DatiRipuliti.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e7cb393-9eac-407c-9cd5-7fe26584bb10",
   "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.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}