Exersice4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7e205e65-8ff6-4eed-9d38-dec8513373c3",
   "metadata": {},
   "source": [
    "<h1>Exercise 4</h1>\n",
    "TKO_7093 Statistical Data Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "89d97d72-1491-4bf0-8de0-6e6b8bd80f1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import scipy\n",
    "from scipy import stats\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9c8e8c2-ea12-4f49-b3ff-aa17d0a01199",
   "metadata": {},
   "source": [
    "<h2>1.</h2>\n",
    "Download the file allbp.data from the Thyroid Disease Data Set available at <a href=\"https://archive.ics.uci.edu/ml/datasets/Thyroid+Disease\">https://archive.ics.uci.edu/ml/datasets/Thyroid+Disease</a> (see the files in the Data Folder).<br>\n",
    "Load and preprocess the data so that it is ready for analysis. (Check categorical variables, missing values, variable names and so on.) Use the file allbp.names to your advantage.\n",
    "\n",
    "* How many observations and how many variables are there in the data?\n",
    "\n",
    "* Which variables have missing values? How many?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0f401fee-8f77-410b-820f-f922e30badb1",
   "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>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>on thyroxine</th>\n",
       "      <th>query on thyroxine</th>\n",
       "      <th>on antithyroid medication</th>\n",
       "      <th>sick</th>\n",
       "      <th>pregnant</th>\n",
       "      <th>thyroid surgery</th>\n",
       "      <th>I131 treatment</th>\n",
       "      <th>query hypothyroid</th>\n",
       "      <th>...</th>\n",
       "      <th>TT4 measured</th>\n",
       "      <th>TT4</th>\n",
       "      <th>T4U measured</th>\n",
       "      <th>T4U</th>\n",
       "      <th>FTI measured</th>\n",
       "      <th>FTI</th>\n",
       "      <th>TBG measured</th>\n",
       "      <th>TBG</th>\n",
       "      <th>referral source</th>\n",
       "      <th>CLASS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41.0</td>\n",
       "      <td>F</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>125.0</td>\n",
       "      <td>t</td>\n",
       "      <td>1.14</td>\n",
       "      <td>t</td>\n",
       "      <td>109.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SVHC</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23.0</td>\n",
       "      <td>F</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>102.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46.0</td>\n",
       "      <td>M</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>109.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.91</td>\n",
       "      <td>t</td>\n",
       "      <td>120.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70.0</td>\n",
       "      <td>F</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>175.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>70.0</td>\n",
       "      <td>F</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>61.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.87</td>\n",
       "      <td>t</td>\n",
       "      <td>70.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SVI</td>\n",
       "      <td>negative</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>2795</th>\n",
       "      <td>70.0</td>\n",
       "      <td>M</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>155.0</td>\n",
       "      <td>t</td>\n",
       "      <td>1.05</td>\n",
       "      <td>t</td>\n",
       "      <td>148.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SVI</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2796</th>\n",
       "      <td>73.0</td>\n",
       "      <td>M</td>\n",
       "      <td>f</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>63.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.88</td>\n",
       "      <td>t</td>\n",
       "      <td>72.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2797</th>\n",
       "      <td>75.0</td>\n",
       "      <td>M</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>147.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.80</td>\n",
       "      <td>t</td>\n",
       "      <td>183.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2798</th>\n",
       "      <td>60.0</td>\n",
       "      <td>F</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>100.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.83</td>\n",
       "      <td>t</td>\n",
       "      <td>121.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>other</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2799</th>\n",
       "      <td>81.0</td>\n",
       "      <td>F</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>f</td>\n",
       "      <td>...</td>\n",
       "      <td>t</td>\n",
       "      <td>114.0</td>\n",
       "      <td>t</td>\n",
       "      <td>0.99</td>\n",
       "      <td>t</td>\n",
       "      <td>115.0</td>\n",
       "      <td>f</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SVI</td>\n",
       "      <td>negative</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2800 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       age sex on thyroxine query on thyroxine on antithyroid medication sick  \\\n",
       "0     41.0   F            f                  f                         f    f   \n",
       "1     23.0   F            f                  f                         f    f   \n",
       "2     46.0   M            f                  f                         f    f   \n",
       "3     70.0   F            t                  f                         f    f   \n",
       "4     70.0   F            f                  f                         f    f   \n",
       "...    ...  ..          ...                ...                       ...  ...   \n",
       "2795  70.0   M            f                  f                         f    f   \n",
       "2796  73.0   M            f                  t                         f    f   \n",
       "2797  75.0   M            f                  f                         f    f   \n",
       "2798  60.0   F            f                  f                         f    f   \n",
       "2799  81.0   F            f                  f                         f    f   \n",
       "\n",
       "     pregnant thyroid surgery I131 treatment query hypothyroid  ...  \\\n",
       "0           f               f              f                 f  ...   \n",
       "1           f               f              f                 f  ...   \n",
       "2           f               f              f                 f  ...   \n",
       "3           f               f              f                 f  ...   \n",
       "4           f               f              f                 f  ...   \n",
       "...       ...             ...            ...               ...  ...   \n",
       "2795        f               f              f                 f  ...   \n",
       "2796        f               f              f                 f  ...   \n",
       "2797        f               f              f                 f  ...   \n",
       "2798        f               f              f                 f  ...   \n",
       "2799        f               f              f                 f  ...   \n",
       "\n",
       "     TT4 measured    TT4 T4U measured   T4U FTI measured    FTI TBG measured  \\\n",
       "0               t  125.0            t  1.14            t  109.0            f   \n",
       "1               t  102.0            f   NaN            f    NaN            f   \n",
       "2               t  109.0            t  0.91            t  120.0            f   \n",
       "3               t  175.0            f   NaN            f    NaN            f   \n",
       "4               t   61.0            t  0.87            t   70.0            f   \n",
       "...           ...    ...          ...   ...          ...    ...          ...   \n",
       "2795            t  155.0            t  1.05            t  148.0            f   \n",
       "2796            t   63.0            t  0.88            t   72.0            f   \n",
       "2797            t  147.0            t  0.80            t  183.0            f   \n",
       "2798            t  100.0            t  0.83            t  121.0            f   \n",
       "2799            t  114.0            t  0.99            t  115.0            f   \n",
       "\n",
       "      TBG referral source     CLASS  \n",
       "0     NaN            SVHC  negative  \n",
       "1     NaN           other  negative  \n",
       "2     NaN           other  negative  \n",
       "3     NaN           other  negative  \n",
       "4     NaN             SVI  negative  \n",
       "...   ...             ...       ...  \n",
       "2795  NaN             SVI  negative  \n",
       "2796  NaN           other  negative  \n",
       "2797  NaN           other  negative  \n",
       "2798  NaN           other  negative  \n",
       "2799  NaN             SVI  negative  \n",
       "\n",
       "[2800 rows x 30 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loc = r'C:\\Users\\peppi\\Desktop\\lipasto\\statisticalDataAnalysis\\allbp.data'\n",
    "df = pd.read_csv(loc, sep=\",\", na_values=\"?\", header=None, names=['age', 'sex', 'on thyroxine', 'query on thyroxine', 'on antithyroid medication', 'sick', \n",
    "                                                                  'pregnant', 'thyroid surgery', 'I131 treatment', 'query hypothyroid', 'query hyperthyroid', \n",
    "                                                                  'lithium', 'goitre', 'tumor', 'hypopituitary', 'psych', 'TSH measured', 'TSHz', 'T3 measured', \n",
    "                                                                  'T3', 'TT4 measured', 'TT4', 'T4U measured', 'T4U', 'FTI measured', 'FTI', 'TBG measured', 'TBG', \n",
    "                                                                  'referral source', 'CLASS'])\n",
    "#remove extra chars at the end of line\n",
    "df.CLASS = df.CLASS.str.split('.',expand=True).iloc[:,0]\n",
    "df\n",
    "\n",
    "# 29 variables \n",
    "# 2800 observations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e6e492c7-c257-4c91-89a9-2d94c54cef61",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['age', 'sex', 'TSHz', 'T3', 'TT4', 'T4U', 'FTI', 'TBG']\n"
     ]
    }
   ],
   "source": [
    "vars_with_na = [var for var in df.columns if df[var].isnull().sum() > 0]\n",
    "print (vars_with_na)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f9b93043-eeae-49a2-b8fc-7077562b8f7c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Column age has 1 NaN values\n",
      "Column sex has 110 NaN values\n",
      "Column TSHz has 284 NaN values\n",
      "Column T3 has 585 NaN values\n",
      "Column TT4 has 184 NaN values\n",
      "Column T4U has 297 NaN values\n",
      "Column FTI has 295 NaN values\n",
      "Column TBG has 2800 NaN values\n"
     ]
    }
   ],
   "source": [
    "for v in vars_with_na:\n",
    "    sum = df[v].isna().sum()\n",
    "    print(\"Column\", v, \"has\", sum, \"NaN values\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ce172a9-4d9e-4038-8190-e32f8d5ae133",
   "metadata": {},
   "source": [
    "<h2>2.</h2>\n",
    "Using the data you prepared above,\n",
    "\n",
    "* calculate the mean and standard deviation for each quantitative variable.\n",
    "\n",
    "* calculate the frequency and relative frequency of the yes value for each indicator variable (i.e. a variable with only yes/no values).\n",
    "\n",
    "* (BONUS) calculate the frequency and relative frequency of each observed value for each other categorical variable.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0cb48d06-db1e-418b-a87d-ae29811190c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate the mean and standard deviation for each quantitative variable.\n",
    "info = df.describe(include=np.number)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "53313305-5b5b-4734-a21e-17536f12e142",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age                          float64\n",
       "sex                           object\n",
       "on thyroxine                  object\n",
       "query on thyroxine            object\n",
       "on antithyroid medication     object\n",
       "sick                          object\n",
       "pregnant                      object\n",
       "thyroid surgery               object\n",
       "I131 treatment                object\n",
       "query hypothyroid             object\n",
       "query hyperthyroid            object\n",
       "lithium                       object\n",
       "goitre                        object\n",
       "tumor                         object\n",
       "hypopituitary                 object\n",
       "psych                         object\n",
       "TSH measured                  object\n",
       "TSHz                         float64\n",
       "T3 measured                   object\n",
       "T3                           float64\n",
       "TT4 measured                  object\n",
       "TT4                          float64\n",
       "T4U measured                  object\n",
       "T4U                          float64\n",
       "FTI measured                  object\n",
       "FTI                          float64\n",
       "TBG measured                  object\n",
       "TBG                          float64\n",
       "referral source               object\n",
       "CLASS                         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "be6b655f-bf5a-44d4-8dd8-0cf1480cef0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age                           float64\n",
       "sex                          category\n",
       "on thyroxine                 category\n",
       "query on thyroxine           category\n",
       "on antithyroid medication    category\n",
       "sick                         category\n",
       "pregnant                     category\n",
       "thyroid surgery              category\n",
       "I131 treatment               category\n",
       "query hypothyroid            category\n",
       "query hyperthyroid           category\n",
       "lithium                      category\n",
       "goitre                       category\n",
       "tumor                        category\n",
       "hypopituitary                category\n",
       "psych                        category\n",
       "TSH measured                 category\n",
       "TSHz                          float64\n",
       "T3 measured                  category\n",
       "T3                            float64\n",
       "TT4 measured                 category\n",
       "TT4                           float64\n",
       "T4U measured                 category\n",
       "T4U                           float64\n",
       "FTI measured                 category\n",
       "FTI                           float64\n",
       "TBG measured                 category\n",
       "TBG                           float64\n",
       "referral source              category\n",
       "CLASS                        category\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# change object to categorical\n",
    "for c in df:\n",
    "    if df[c].dtypes == object:\n",
    "        df[c] = df[c].astype('category')\n",
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6c08d48f-ab58-4b13-a4fc-6d92a7fa7c08",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Frequency= \n",
      " F    1830\n",
      "M     860\n",
      "Name: sex, dtype: int64 \n",
      " Relative frequency= \n",
      " F    0.680297\n",
      "M    0.319703\n",
      "Name: sex, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2470\n",
      "t     330\n",
      "Name: on thyroxine, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.882143\n",
      "t    0.117857\n",
      "Name: on thyroxine, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2760\n",
      "t      40\n",
      "Name: query on thyroxine, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.985714\n",
      "t    0.014286\n",
      "Name: query on thyroxine, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2766\n",
      "t      34\n",
      "Name: on antithyroid medication, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.987857\n",
      "t    0.012143\n",
      "Name: on antithyroid medication, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2690\n",
      "t     110\n",
      "Name: sick, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.960714\n",
      "t    0.039286\n",
      "Name: sick, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2759\n",
      "t      41\n",
      "Name: pregnant, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.985357\n",
      "t    0.014643\n",
      "Name: pregnant, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2761\n",
      "t      39\n",
      "Name: thyroid surgery, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.986071\n",
      "t    0.013929\n",
      "Name: thyroid surgery, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2752\n",
      "t      48\n",
      "Name: I131 treatment, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.982857\n",
      "t    0.017143\n",
      "Name: I131 treatment, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2637\n",
      "t     163\n",
      "Name: query hypothyroid, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.941786\n",
      "t    0.058214\n",
      "Name: query hypothyroid, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2627\n",
      "t     173\n",
      "Name: query hyperthyroid, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.938214\n",
      "t    0.061786\n",
      "Name: query hyperthyroid, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2786\n",
      "t      14\n",
      "Name: lithium, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.995\n",
      "t    0.005\n",
      "Name: lithium, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2775\n",
      "t      25\n",
      "Name: goitre, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.991071\n",
      "t    0.008929\n",
      "Name: goitre, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2729\n",
      "t      71\n",
      "Name: tumor, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.974643\n",
      "t    0.025357\n",
      "Name: tumor, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2799\n",
      "t       1\n",
      "Name: hypopituitary, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.999643\n",
      "t    0.000357\n",
      "Name: hypopituitary, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2665\n",
      "t     135\n",
      "Name: psych, dtype: int64 \n",
      " Relative frequency= \n",
      " f    0.951786\n",
      "t    0.048214\n",
      "Name: psych, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " t    2516\n",
      "f     284\n",
      "Name: TSH measured, dtype: int64 \n",
      " Relative frequency= \n",
      " t    0.898571\n",
      "f    0.101429\n",
      "Name: TSH measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " t    2215\n",
      "f     585\n",
      "Name: T3 measured, dtype: int64 \n",
      " Relative frequency= \n",
      " t    0.791071\n",
      "f    0.208929\n",
      "Name: T3 measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " t    2616\n",
      "f     184\n",
      "Name: TT4 measured, dtype: int64 \n",
      " Relative frequency= \n",
      " t    0.934286\n",
      "f    0.065714\n",
      "Name: TT4 measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " t    2503\n",
      "f     297\n",
      "Name: T4U measured, dtype: int64 \n",
      " Relative frequency= \n",
      " t    0.893929\n",
      "f    0.106071\n",
      "Name: T4U measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " t    2505\n",
      "f     295\n",
      "Name: FTI measured, dtype: int64 \n",
      " Relative frequency= \n",
      " t    0.894643\n",
      "f    0.105357\n",
      "Name: FTI measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " f    2800\n",
      "Name: TBG measured, dtype: int64 \n",
      " Relative frequency= \n",
      " f    1.0\n",
      "Name: TBG measured, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " other    1632\n",
      "SVI       771\n",
      "SVHC      275\n",
      "STMW       91\n",
      "SVHD       31\n",
      "Name: referral source, dtype: int64 \n",
      " Relative frequency= \n",
      " other    0.582857\n",
      "SVI      0.275357\n",
      "SVHC     0.098214\n",
      "STMW     0.032500\n",
      "SVHD     0.011071\n",
      "Name: referral source, dtype: float64 \n",
      "\n",
      "Frequency= \n",
      " negative                     2667\n",
      "increased binding protein     124\n",
      "decreased binding protein       9\n",
      "Name: CLASS, dtype: int64 \n",
      " Relative frequency= \n",
      " negative                     0.952500\n",
      "increased binding protein    0.044286\n",
      "decreased binding protein    0.003214\n",
      "Name: CLASS, dtype: float64 \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# calculate the frequency and relative frequency of the yes value for each indicator variable \n",
    "# (i.e. a variable with only yes/no values).\n",
    "for c in df:\n",
    "    if pd.api.types.is_categorical_dtype(df[c]):\n",
    "        absolute = df[c].value_counts()\n",
    "        relative = df[c].value_counts(normalize=True)\n",
    "        print(\"Frequency=\", \"\\n\", absolute, \"\\n\", \"Relative frequency=\",\"\\n\", relative, \"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30368b09-89da-43af-85c0-2856cc50a04a",
   "metadata": {},
   "source": [
    "<h2>3.</h2>\n",
    "Download and unzip the DataSet SOFT file from <a href=\"https://www.ncbi.nlm.nih.gov/sites/GDSbrowser?acc=GDS5037\">https://www.ncbi.nlm.nih.gov/sites/GDSbrowser?acc=GDS5037</a>. The SOFT file format contains tabular gene expression data accompanied by its metadata. The rows that start with either ^, !, or # are metadata, which can be ignored in this exercise.<br><br>\n",
    "\n",
    "Some useful pieces of information about the file GDS5037.soft:<br>\n",
    "\n",
    "The first 160 rows and the last row are metadata.<br>\n",
    "The ID_REF column contains probe ids. Use them as row names.<br>\n",
    "The IDENTIFIER column contains gene names.<br>\n",
    "The other columns contain gene expression levels for subjects.<br>\n",
    "Load and filter the data such that your data frame only contains the probe and subject ids listed in the files 43-probes.ids and 43-subjects.ids, respectively.<br>\n",
    "\n",
    "* For each subject, calculate the mean expression level over the genes. Do you notice any obvious differences between the subjects?\n",
    "\n",
    "* For each probe, calculate the mean expression level over the subjects. Do you notice any obvious differences between the probes?\n",
    "\n",
    "* How many probes are there with a mean greater than 10.0? Which genes do they correspond to?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cd4708d0-4369-4710-b9e6-4ad2be92574a",
   "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>IDENTIFIER</th>\n",
       "      <th>GSM1068478</th>\n",
       "      <th>GSM1068479</th>\n",
       "      <th>GSM1068481</th>\n",
       "      <th>GSM1068482</th>\n",
       "      <th>GSM1068483</th>\n",
       "      <th>GSM1068486</th>\n",
       "      <th>GSM1068487</th>\n",
       "      <th>GSM1068488</th>\n",
       "      <th>GSM1068490</th>\n",
       "      <th>...</th>\n",
       "      <th>GSM1068529</th>\n",
       "      <th>GSM1068530</th>\n",
       "      <th>GSM1068534</th>\n",
       "      <th>GSM1068536</th>\n",
       "      <th>GSM1068541</th>\n",
       "      <th>GSM1068553</th>\n",
       "      <th>GSM1068554</th>\n",
       "      <th>GSM1068558</th>\n",
       "      <th>GSM1068559</th>\n",
       "      <th>GSM1068564</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID_REF</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>A_23_P100001</th>\n",
       "      <td>FAM174B</td>\n",
       "      <td>11.76500</td>\n",
       "      <td>11.15030</td>\n",
       "      <td>11.82380</td>\n",
       "      <td>10.96300</td>\n",
       "      <td>11.63470</td>\n",
       "      <td>11.07970</td>\n",
       "      <td>11.30700</td>\n",
       "      <td>11.17300</td>\n",
       "      <td>11.01220</td>\n",
       "      <td>...</td>\n",
       "      <td>11.46210</td>\n",
       "      <td>11.13460</td>\n",
       "      <td>11.53310</td>\n",
       "      <td>11.30700</td>\n",
       "      <td>11.03410</td>\n",
       "      <td>10.68630</td>\n",
       "      <td>11.13070</td>\n",
       "      <td>11.19560</td>\n",
       "      <td>11.13110</td>\n",
       "      <td>10.79130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100011</th>\n",
       "      <td>AP3S2</td>\n",
       "      <td>6.68845</td>\n",
       "      <td>6.29378</td>\n",
       "      <td>5.77885</td>\n",
       "      <td>6.20746</td>\n",
       "      <td>4.78520</td>\n",
       "      <td>5.38453</td>\n",
       "      <td>5.88975</td>\n",
       "      <td>6.39307</td>\n",
       "      <td>6.00136</td>\n",
       "      <td>...</td>\n",
       "      <td>6.53933</td>\n",
       "      <td>5.77446</td>\n",
       "      <td>5.84170</td>\n",
       "      <td>5.98821</td>\n",
       "      <td>5.76583</td>\n",
       "      <td>5.92124</td>\n",
       "      <td>5.72922</td>\n",
       "      <td>6.91738</td>\n",
       "      <td>6.35952</td>\n",
       "      <td>6.13216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100022</th>\n",
       "      <td>SV2B</td>\n",
       "      <td>6.40638</td>\n",
       "      <td>6.53990</td>\n",
       "      <td>2.94554</td>\n",
       "      <td>5.62157</td>\n",
       "      <td>6.12992</td>\n",
       "      <td>5.64204</td>\n",
       "      <td>6.80579</td>\n",
       "      <td>5.74520</td>\n",
       "      <td>6.05809</td>\n",
       "      <td>...</td>\n",
       "      <td>6.06607</td>\n",
       "      <td>5.03564</td>\n",
       "      <td>5.23585</td>\n",
       "      <td>4.78694</td>\n",
       "      <td>2.93856</td>\n",
       "      <td>4.82587</td>\n",
       "      <td>4.44784</td>\n",
       "      <td>5.80114</td>\n",
       "      <td>5.78344</td>\n",
       "      <td>5.36149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100056</th>\n",
       "      <td>RBPMS2</td>\n",
       "      <td>1.88171</td>\n",
       "      <td>1.43777</td>\n",
       "      <td>1.47388</td>\n",
       "      <td>1.50057</td>\n",
       "      <td>1.42719</td>\n",
       "      <td>1.41453</td>\n",
       "      <td>1.31007</td>\n",
       "      <td>1.50026</td>\n",
       "      <td>1.58497</td>\n",
       "      <td>...</td>\n",
       "      <td>1.87270</td>\n",
       "      <td>1.42304</td>\n",
       "      <td>1.35702</td>\n",
       "      <td>1.89157</td>\n",
       "      <td>1.98907</td>\n",
       "      <td>2.01289</td>\n",
       "      <td>1.26051</td>\n",
       "      <td>2.84686</td>\n",
       "      <td>1.42486</td>\n",
       "      <td>1.76015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100074</th>\n",
       "      <td>AVEN</td>\n",
       "      <td>9.16313</td>\n",
       "      <td>8.79248</td>\n",
       "      <td>9.41351</td>\n",
       "      <td>9.75378</td>\n",
       "      <td>8.85106</td>\n",
       "      <td>9.07236</td>\n",
       "      <td>9.14032</td>\n",
       "      <td>9.32616</td>\n",
       "      <td>9.34733</td>\n",
       "      <td>...</td>\n",
       "      <td>9.71164</td>\n",
       "      <td>9.03423</td>\n",
       "      <td>9.42655</td>\n",
       "      <td>9.18300</td>\n",
       "      <td>9.36121</td>\n",
       "      <td>9.40767</td>\n",
       "      <td>9.31643</td>\n",
       "      <td>9.35973</td>\n",
       "      <td>8.86756</td>\n",
       "      <td>8.94563</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>E1A_r60_n11</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E1A_r60_n9</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EQC</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NegativeControl</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pro25G</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41108 rows × 109 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                IDENTIFIER  GSM1068478  GSM1068479  GSM1068481  GSM1068482  \\\n",
       "ID_REF                                                                       \n",
       "A_23_P100001       FAM174B    11.76500    11.15030    11.82380    10.96300   \n",
       "A_23_P100011         AP3S2     6.68845     6.29378     5.77885     6.20746   \n",
       "A_23_P100022          SV2B     6.40638     6.53990     2.94554     5.62157   \n",
       "A_23_P100056        RBPMS2     1.88171     1.43777     1.47388     1.50057   \n",
       "A_23_P100074          AVEN     9.16313     8.79248     9.41351     9.75378   \n",
       "...                    ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11        CONTROL         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9         CONTROL         NaN         NaN         NaN         NaN   \n",
       "EQC                CONTROL         NaN         NaN         NaN         NaN   \n",
       "NegativeControl    CONTROL         NaN         NaN         NaN         NaN   \n",
       "Pro25G             CONTROL         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068483  GSM1068486  GSM1068487  GSM1068488  GSM1068490  \\\n",
       "ID_REF                                                                        \n",
       "A_23_P100001       11.63470    11.07970    11.30700    11.17300    11.01220   \n",
       "A_23_P100011        4.78520     5.38453     5.88975     6.39307     6.00136   \n",
       "A_23_P100022        6.12992     5.64204     6.80579     5.74520     6.05809   \n",
       "A_23_P100056        1.42719     1.41453     1.31007     1.50026     1.58497   \n",
       "A_23_P100074        8.85106     9.07236     9.14032     9.32616     9.34733   \n",
       "...                     ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11             NaN         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9              NaN         NaN         NaN         NaN         NaN   \n",
       "EQC                     NaN         NaN         NaN         NaN         NaN   \n",
       "NegativeControl         NaN         NaN         NaN         NaN         NaN   \n",
       "Pro25G                  NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 ...  GSM1068529  GSM1068530  GSM1068534  GSM1068536  \\\n",
       "ID_REF           ...                                                   \n",
       "A_23_P100001     ...    11.46210    11.13460    11.53310    11.30700   \n",
       "A_23_P100011     ...     6.53933     5.77446     5.84170     5.98821   \n",
       "A_23_P100022     ...     6.06607     5.03564     5.23585     4.78694   \n",
       "A_23_P100056     ...     1.87270     1.42304     1.35702     1.89157   \n",
       "A_23_P100074     ...     9.71164     9.03423     9.42655     9.18300   \n",
       "...              ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11      ...         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9       ...         NaN         NaN         NaN         NaN   \n",
       "EQC              ...         NaN         NaN         NaN         NaN   \n",
       "NegativeControl  ...         NaN         NaN         NaN         NaN   \n",
       "Pro25G           ...         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068541  GSM1068553  GSM1068554  GSM1068558  GSM1068559  \\\n",
       "ID_REF                                                                        \n",
       "A_23_P100001       11.03410    10.68630    11.13070    11.19560    11.13110   \n",
       "A_23_P100011        5.76583     5.92124     5.72922     6.91738     6.35952   \n",
       "A_23_P100022        2.93856     4.82587     4.44784     5.80114     5.78344   \n",
       "A_23_P100056        1.98907     2.01289     1.26051     2.84686     1.42486   \n",
       "A_23_P100074        9.36121     9.40767     9.31643     9.35973     8.86756   \n",
       "...                     ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11             NaN         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9              NaN         NaN         NaN         NaN         NaN   \n",
       "EQC                     NaN         NaN         NaN         NaN         NaN   \n",
       "NegativeControl         NaN         NaN         NaN         NaN         NaN   \n",
       "Pro25G                  NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068564  \n",
       "ID_REF                       \n",
       "A_23_P100001       10.79130  \n",
       "A_23_P100011        6.13216  \n",
       "A_23_P100022        5.36149  \n",
       "A_23_P100056        1.76015  \n",
       "A_23_P100074        8.94563  \n",
       "...                     ...  \n",
       "E1A_r60_n11             NaN  \n",
       "E1A_r60_n9              NaN  \n",
       "EQC                     NaN  \n",
       "NegativeControl         NaN  \n",
       "Pro25G                  NaN  \n",
       "\n",
       "[41108 rows x 109 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# metadata = list(range(160)) + [41269] and instead of header and skipfooter -> skiprows=metadata\n",
    "soft = pd.read_csv('GDS5037.soft', sep=\"\\s+\" ,header=160, skipfooter=1, engine='python')\n",
    "df = pd.DataFrame(soft)\n",
    "df_full = df.set_index('ID_REF')\n",
    "df_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c4e64e34-f101-42f4-8ea1-822d7fd45639",
   "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>IDENTIFIER</th>\n",
       "      <th>GSM1068478</th>\n",
       "      <th>GSM1068479</th>\n",
       "      <th>GSM1068481</th>\n",
       "      <th>GSM1068482</th>\n",
       "      <th>GSM1068483</th>\n",
       "      <th>GSM1068486</th>\n",
       "      <th>GSM1068487</th>\n",
       "      <th>GSM1068488</th>\n",
       "      <th>GSM1068490</th>\n",
       "      <th>...</th>\n",
       "      <th>GSM1068529</th>\n",
       "      <th>GSM1068530</th>\n",
       "      <th>GSM1068534</th>\n",
       "      <th>GSM1068536</th>\n",
       "      <th>GSM1068541</th>\n",
       "      <th>GSM1068553</th>\n",
       "      <th>GSM1068554</th>\n",
       "      <th>GSM1068558</th>\n",
       "      <th>GSM1068559</th>\n",
       "      <th>GSM1068564</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID_REF</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>A_23_P100001</th>\n",
       "      <td>FAM174B</td>\n",
       "      <td>11.76500</td>\n",
       "      <td>11.15030</td>\n",
       "      <td>11.82380</td>\n",
       "      <td>10.96300</td>\n",
       "      <td>11.63470</td>\n",
       "      <td>11.07970</td>\n",
       "      <td>11.30700</td>\n",
       "      <td>11.17300</td>\n",
       "      <td>11.01220</td>\n",
       "      <td>...</td>\n",
       "      <td>11.46210</td>\n",
       "      <td>11.13460</td>\n",
       "      <td>11.53310</td>\n",
       "      <td>11.30700</td>\n",
       "      <td>11.03410</td>\n",
       "      <td>10.68630</td>\n",
       "      <td>11.13070</td>\n",
       "      <td>11.19560</td>\n",
       "      <td>11.13110</td>\n",
       "      <td>10.79130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100011</th>\n",
       "      <td>AP3S2</td>\n",
       "      <td>6.68845</td>\n",
       "      <td>6.29378</td>\n",
       "      <td>5.77885</td>\n",
       "      <td>6.20746</td>\n",
       "      <td>4.78520</td>\n",
       "      <td>5.38453</td>\n",
       "      <td>5.88975</td>\n",
       "      <td>6.39307</td>\n",
       "      <td>6.00136</td>\n",
       "      <td>...</td>\n",
       "      <td>6.53933</td>\n",
       "      <td>5.77446</td>\n",
       "      <td>5.84170</td>\n",
       "      <td>5.98821</td>\n",
       "      <td>5.76583</td>\n",
       "      <td>5.92124</td>\n",
       "      <td>5.72922</td>\n",
       "      <td>6.91738</td>\n",
       "      <td>6.35952</td>\n",
       "      <td>6.13216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100022</th>\n",
       "      <td>SV2B</td>\n",
       "      <td>6.40638</td>\n",
       "      <td>6.53990</td>\n",
       "      <td>2.94554</td>\n",
       "      <td>5.62157</td>\n",
       "      <td>6.12992</td>\n",
       "      <td>5.64204</td>\n",
       "      <td>6.80579</td>\n",
       "      <td>5.74520</td>\n",
       "      <td>6.05809</td>\n",
       "      <td>...</td>\n",
       "      <td>6.06607</td>\n",
       "      <td>5.03564</td>\n",
       "      <td>5.23585</td>\n",
       "      <td>4.78694</td>\n",
       "      <td>2.93856</td>\n",
       "      <td>4.82587</td>\n",
       "      <td>4.44784</td>\n",
       "      <td>5.80114</td>\n",
       "      <td>5.78344</td>\n",
       "      <td>5.36149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100056</th>\n",
       "      <td>RBPMS2</td>\n",
       "      <td>1.88171</td>\n",
       "      <td>1.43777</td>\n",
       "      <td>1.47388</td>\n",
       "      <td>1.50057</td>\n",
       "      <td>1.42719</td>\n",
       "      <td>1.41453</td>\n",
       "      <td>1.31007</td>\n",
       "      <td>1.50026</td>\n",
       "      <td>1.58497</td>\n",
       "      <td>...</td>\n",
       "      <td>1.87270</td>\n",
       "      <td>1.42304</td>\n",
       "      <td>1.35702</td>\n",
       "      <td>1.89157</td>\n",
       "      <td>1.98907</td>\n",
       "      <td>2.01289</td>\n",
       "      <td>1.26051</td>\n",
       "      <td>2.84686</td>\n",
       "      <td>1.42486</td>\n",
       "      <td>1.76015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A_23_P100074</th>\n",
       "      <td>AVEN</td>\n",
       "      <td>9.16313</td>\n",
       "      <td>8.79248</td>\n",
       "      <td>9.41351</td>\n",
       "      <td>9.75378</td>\n",
       "      <td>8.85106</td>\n",
       "      <td>9.07236</td>\n",
       "      <td>9.14032</td>\n",
       "      <td>9.32616</td>\n",
       "      <td>9.34733</td>\n",
       "      <td>...</td>\n",
       "      <td>9.71164</td>\n",
       "      <td>9.03423</td>\n",
       "      <td>9.42655</td>\n",
       "      <td>9.18300</td>\n",
       "      <td>9.36121</td>\n",
       "      <td>9.40767</td>\n",
       "      <td>9.31643</td>\n",
       "      <td>9.35973</td>\n",
       "      <td>8.86756</td>\n",
       "      <td>8.94563</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>E1A_r60_n11</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E1A_r60_n9</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EQC</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NegativeControl</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pro25G</th>\n",
       "      <td>CONTROL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>41108 rows × 109 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                IDENTIFIER  GSM1068478  GSM1068479  GSM1068481  GSM1068482  \\\n",
       "ID_REF                                                                       \n",
       "A_23_P100001       FAM174B    11.76500    11.15030    11.82380    10.96300   \n",
       "A_23_P100011         AP3S2     6.68845     6.29378     5.77885     6.20746   \n",
       "A_23_P100022          SV2B     6.40638     6.53990     2.94554     5.62157   \n",
       "A_23_P100056        RBPMS2     1.88171     1.43777     1.47388     1.50057   \n",
       "A_23_P100074          AVEN     9.16313     8.79248     9.41351     9.75378   \n",
       "...                    ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11        CONTROL         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9         CONTROL         NaN         NaN         NaN         NaN   \n",
       "EQC                CONTROL         NaN         NaN         NaN         NaN   \n",
       "NegativeControl    CONTROL         NaN         NaN         NaN         NaN   \n",
       "Pro25G             CONTROL         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068483  GSM1068486  GSM1068487  GSM1068488  GSM1068490  \\\n",
       "ID_REF                                                                        \n",
       "A_23_P100001       11.63470    11.07970    11.30700    11.17300    11.01220   \n",
       "A_23_P100011        4.78520     5.38453     5.88975     6.39307     6.00136   \n",
       "A_23_P100022        6.12992     5.64204     6.80579     5.74520     6.05809   \n",
       "A_23_P100056        1.42719     1.41453     1.31007     1.50026     1.58497   \n",
       "A_23_P100074        8.85106     9.07236     9.14032     9.32616     9.34733   \n",
       "...                     ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11             NaN         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9              NaN         NaN         NaN         NaN         NaN   \n",
       "EQC                     NaN         NaN         NaN         NaN         NaN   \n",
       "NegativeControl         NaN         NaN         NaN         NaN         NaN   \n",
       "Pro25G                  NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 ...  GSM1068529  GSM1068530  GSM1068534  GSM1068536  \\\n",
       "ID_REF           ...                                                   \n",
       "A_23_P100001     ...    11.46210    11.13460    11.53310    11.30700   \n",
       "A_23_P100011     ...     6.53933     5.77446     5.84170     5.98821   \n",
       "A_23_P100022     ...     6.06607     5.03564     5.23585     4.78694   \n",
       "A_23_P100056     ...     1.87270     1.42304     1.35702     1.89157   \n",
       "A_23_P100074     ...     9.71164     9.03423     9.42655     9.18300   \n",
       "...              ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11      ...         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9       ...         NaN         NaN         NaN         NaN   \n",
       "EQC              ...         NaN         NaN         NaN         NaN   \n",
       "NegativeControl  ...         NaN         NaN         NaN         NaN   \n",
       "Pro25G           ...         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068541  GSM1068553  GSM1068554  GSM1068558  GSM1068559  \\\n",
       "ID_REF                                                                        \n",
       "A_23_P100001       11.03410    10.68630    11.13070    11.19560    11.13110   \n",
       "A_23_P100011        5.76583     5.92124     5.72922     6.91738     6.35952   \n",
       "A_23_P100022        2.93856     4.82587     4.44784     5.80114     5.78344   \n",
       "A_23_P100056        1.98907     2.01289     1.26051     2.84686     1.42486   \n",
       "A_23_P100074        9.36121     9.40767     9.31643     9.35973     8.86756   \n",
       "...                     ...         ...         ...         ...         ...   \n",
       "E1A_r60_n11             NaN         NaN         NaN         NaN         NaN   \n",
       "E1A_r60_n9              NaN         NaN         NaN         NaN         NaN   \n",
       "EQC                     NaN         NaN         NaN         NaN         NaN   \n",
       "NegativeControl         NaN         NaN         NaN         NaN         NaN   \n",
       "Pro25G                  NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "                 GSM1068564  \n",
       "ID_REF                       \n",
       "A_23_P100001       10.79130  \n",
       "A_23_P100011        6.13216  \n",
       "A_23_P100022        5.36149  \n",
       "A_23_P100056        1.76015  \n",
       "A_23_P100074        8.94563  \n",
       "...                     ...  \n",
       "E1A_r60_n11             NaN  \n",
       "E1A_r60_n9              NaN  \n",
       "EQC                     NaN  \n",
       "NegativeControl         NaN  \n",
       "Pro25G                  NaN  \n",
       "\n",
       "[41108 rows x 109 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "probes = pd.read_table('43-probes.ids', header=None)[0]\n",
    "subjects = pd.read_table('43-subjects.ids', header=None)[0]\n",
    "df = df_full.loc[probes,subjects]\n",
    "df_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cb904a90-7a83-493c-bcf9-2038f4d03262",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GSM1068478    7.542535\n",
       "GSM1068486    7.351364\n",
       "GSM1068492    7.225399\n",
       "GSM1068498    7.546109\n",
       "GSM1068505    7.590863\n",
       "GSM1068512    7.378594\n",
       "GSM1068520    7.522257\n",
       "GSM1068480    7.349102\n",
       "GSM1068501    7.618670\n",
       "GSM1068516    7.508493\n",
       "GSM1068458    7.548477\n",
       "GSM1068468    7.492289\n",
       "GSM1068477    7.528315\n",
       "GSM1068467    7.548450\n",
       "GSM1068528    7.602219\n",
       "GSM1068537    7.547033\n",
       "GSM1068543    7.490493\n",
       "GSM1068548    7.527154\n",
       "GSM1068555    7.465526\n",
       "GSM1068562    7.610374\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#subject mean\n",
    "df.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a86516b3-89ce-43eb-8799-57e37779edb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID_REF\n",
       "A_23_P100011     6.039319\n",
       "A_23_P102083     1.739160\n",
       "A_23_P104323    11.228080\n",
       "A_23_P106562     9.091581\n",
       "A_23_P108673     5.824867\n",
       "A_23_P110686     2.219179\n",
       "A_23_P112646     4.890992\n",
       "A_23_P115223    11.610360\n",
       "A_23_P117582    11.169150\n",
       "A_23_P119617     9.018067\n",
       "A_23_P121716     9.735072\n",
       "A_23_P124084     9.119484\n",
       "A_23_P126613     7.079437\n",
       "A_23_P12884      7.355830\n",
       "A_23_P130948     1.560550\n",
       "A_23_P133146    11.141010\n",
       "A_23_P135079     8.843013\n",
       "A_23_P137634     3.109135\n",
       "A_23_P139704    10.610100\n",
       "A_23_P14184      8.609328\n",
       "dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#probe means\n",
    "df.mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "108de7f4-9f3e-4c20-82c3-4b542f6c34a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID_REF\n",
       "A_23_P104323     MGMT\n",
       "A_23_P115223     HAX1\n",
       "A_23_P117582     JDP2\n",
       "A_23_P133146    H2AFZ\n",
       "A_23_P139704    DUSP6\n",
       "Name: IDENTIFIER, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many probes are there with a mean greater than 10.0? Which genes do they correspond to?\n",
    "df = df.loc[df.mean(axis=1) > 10]\n",
    "ids = df.index.values\n",
    "identefiers = df_full.loc[ids].IDENTIFIER\n",
    "identefiers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04874635-b8ae-4e3d-8ae1-9a27b83428ff",
   "metadata": {},
   "source": [
    "<h2>4.</h2>\n",
    "The files 44-helsinki.csv and 44-espoo.csv contain daily numbers of cyclists spotted on selected streets in Helsinki and Espoo. Load the files and merge the data into a single data frame.\n",
    "\n",
    "* For how many days were observations made in total?\n",
    "\n",
    "* How many observation days were there for each street?\n",
    "\n",
    "* On how many days were all streets observed simultaneously?\n",
    "\n",
    "* Which street was the busiest in terms of the total number of cyclists?\n",
    "\n",
    "* Filter out the dates which have one or more missing values. Does this affect your conclusion about the busiest street? Why or why not?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "87e1a5a6-27a9-4663-878e-28953fb5b11d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3134, 4) (2714, 3)\n"
     ]
    },
    {
     "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>Gallen-Kallela</th>\n",
       "      <th>Länsiväylä</th>\n",
       "      <th>Länsituulenkuja</th>\n",
       "      <th>Eteläesplanadi</th>\n",
       "      <th>Kaivokatu</th>\n",
       "      <th>Kuusisaarentie</th>\n",
       "      <th>Merikannontie</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</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>2014-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>129.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>526.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>546.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>259.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-01-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>230.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1939.0</td>\n",
       "      <td>3120.0</td>\n",
       "      <td>1405.0</td>\n",
       "      <td>2180.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1775.0</td>\n",
       "      <td>2706.0</td>\n",
       "      <td>1185.0</td>\n",
       "      <td>1801.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1657.0</td>\n",
       "      <td>2770.0</td>\n",
       "      <td>1201.0</td>\n",
       "      <td>1876.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1397.0</td>\n",
       "      <td>2058.0</td>\n",
       "      <td>1123.0</td>\n",
       "      <td>1659.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-07-31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>1919.0</td>\n",
       "      <td>1347.0</td>\n",
       "      <td>2125.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3134 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Gallen-Kallela  Länsiväylä  Länsituulenkuja  Eteläesplanadi  \\\n",
       "date                                                                      \n",
       "2014-01-01             NaN         NaN              NaN           129.0   \n",
       "2014-01-02             NaN         NaN              NaN           526.0   \n",
       "2014-01-03             NaN         NaN              NaN           546.0   \n",
       "2014-01-04             NaN         NaN              NaN           259.0   \n",
       "2014-01-05             NaN         NaN              NaN           230.0   \n",
       "...                    ...         ...              ...             ...   \n",
       "2022-07-27             NaN         NaN              NaN          1939.0   \n",
       "2022-07-28             NaN         NaN              NaN          1775.0   \n",
       "2022-07-29             NaN         NaN              NaN          1657.0   \n",
       "2022-07-30             NaN         NaN              NaN          1397.0   \n",
       "2022-07-31             NaN         NaN              NaN          1269.0   \n",
       "\n",
       "            Kaivokatu  Kuusisaarentie  Merikannontie  \n",
       "date                                                  \n",
       "2014-01-01        NaN             NaN            NaN  \n",
       "2014-01-02        NaN             NaN            NaN  \n",
       "2014-01-03        NaN             NaN            NaN  \n",
       "2014-01-04        NaN             NaN            NaN  \n",
       "2014-01-05        NaN             NaN            NaN  \n",
       "...               ...             ...            ...  \n",
       "2022-07-27     3120.0          1405.0         2180.0  \n",
       "2022-07-28     2706.0          1185.0         1801.0  \n",
       "2022-07-29     2770.0          1201.0         1876.0  \n",
       "2022-07-30     2058.0          1123.0         1659.0  \n",
       "2022-07-31     1919.0          1347.0         2125.0  \n",
       "\n",
       "[3134 rows x 7 columns]"
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import helsinki and espoo data\n",
    "helsinki = pd.DataFrame(pd.read_csv('44-helsinki.csv', sep=\",\" ,header=0, index_col=0))\n",
    "espoo = pd.DataFrame(pd.read_csv('44-espoo.csv', sep=\",\" ,header=0, index_col=0))\n",
    "print(helsinki.shape, espoo.shape)\n",
    "\n",
    "#right join data on date\n",
    "rm = pd.merge(espoo, helsinki, how=\"right\", on=[\"date\"])\n",
    "# rm = pd.concat([helsinki, espoo], axis=1)\n",
    "rm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "f884494a-d17a-4b39-9abf-ae7eb9eaeae7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date               3134\n",
       "Gallen-Kallela     1459\n",
       "Länsiväylä         1460\n",
       "Länsituulenkuja    2209\n",
       "Eteläesplanadi     3072\n",
       "Kaivokatu          1722\n",
       "Kuusisaarentie     2640\n",
       "Merikannontie      2843\n",
       "dtype: int64"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Total observations 3134\n",
    "\n",
    "# Observations for each street \n",
    "rm.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "25d215dd-1604-4de8-8204-f52bbdc78ae0",
   "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>date</th>\n",
       "      <th>Gallen-Kallela</th>\n",
       "      <th>Länsiväylä</th>\n",
       "      <th>Länsituulenkuja</th>\n",
       "      <th>Eteläesplanadi</th>\n",
       "      <th>Kaivokatu</th>\n",
       "      <th>Kuusisaarentie</th>\n",
       "      <th>Merikannontie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>803.0</td>\n",
       "      <td>1944.0</td>\n",
       "      <td>2776.0</td>\n",
       "      <td>1973.0</td>\n",
       "      <td>3736.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-06-19</td>\n",
       "      <td>1145.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>2533.0</td>\n",
       "      <td>3770.0</td>\n",
       "      <td>2076.0</td>\n",
       "      <td>2842.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-06-21</td>\n",
       "      <td>911.0</td>\n",
       "      <td>1279.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>2125.0</td>\n",
       "      <td>3167.0</td>\n",
       "      <td>1519.0</td>\n",
       "      <td>2365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-06-22</td>\n",
       "      <td>176.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>275.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>707.0</td>\n",
       "      <td>314.0</td>\n",
       "      <td>599.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-06-23</td>\n",
       "      <td>681.0</td>\n",
       "      <td>704.0</td>\n",
       "      <td>329.0</td>\n",
       "      <td>1052.0</td>\n",
       "      <td>1431.0</td>\n",
       "      <td>1029.0</td>\n",
       "      <td>1823.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>2022-06-15</td>\n",
       "      <td>1559.0</td>\n",
       "      <td>1911.0</td>\n",
       "      <td>2773.0</td>\n",
       "      <td>2611.0</td>\n",
       "      <td>4373.0</td>\n",
       "      <td>2337.0</td>\n",
       "      <td>3397.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1396</th>\n",
       "      <td>2022-06-16</td>\n",
       "      <td>1490.0</td>\n",
       "      <td>1671.0</td>\n",
       "      <td>2579.0</td>\n",
       "      <td>2569.0</td>\n",
       "      <td>4438.0</td>\n",
       "      <td>2212.0</td>\n",
       "      <td>3223.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>2022-06-17</td>\n",
       "      <td>1347.0</td>\n",
       "      <td>1531.0</td>\n",
       "      <td>2674.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>3971.0</td>\n",
       "      <td>1827.0</td>\n",
       "      <td>2979.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1398</th>\n",
       "      <td>2022-06-18</td>\n",
       "      <td>259.0</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>979.0</td>\n",
       "      <td>342.0</td>\n",
       "      <td>566.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1399</th>\n",
       "      <td>2022-06-19</td>\n",
       "      <td>1258.0</td>\n",
       "      <td>1301.0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>1317.0</td>\n",
       "      <td>2134.0</td>\n",
       "      <td>1486.0</td>\n",
       "      <td>2487.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1400 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  Gallen-Kallela  Länsiväylä  Länsituulenkuja  Eteläesplanadi  \\\n",
       "0     2018-06-17             0.0         0.0            803.0          1944.0   \n",
       "1     2018-06-19          1145.0         0.0           1075.0          2533.0   \n",
       "2     2018-06-21           911.0      1279.0            999.0          2125.0   \n",
       "3     2018-06-22           176.0       193.0            275.0           519.0   \n",
       "4     2018-06-23           681.0       704.0            329.0          1052.0   \n",
       "...          ...             ...         ...              ...             ...   \n",
       "1395  2022-06-15          1559.0      1911.0           2773.0          2611.0   \n",
       "1396  2022-06-16          1490.0      1671.0           2579.0          2569.0   \n",
       "1397  2022-06-17          1347.0      1531.0           2674.0          2400.0   \n",
       "1398  2022-06-18           259.0       275.0           1110.0           600.0   \n",
       "1399  2022-06-19          1258.0      1301.0           1928.0          1317.0   \n",
       "\n",
       "      Kaivokatu  Kuusisaarentie  Merikannontie  \n",
       "0        2776.0          1973.0         3736.0  \n",
       "1        3770.0          2076.0         2842.0  \n",
       "2        3167.0          1519.0         2365.0  \n",
       "3         707.0           314.0          599.0  \n",
       "4        1431.0          1029.0         1823.0  \n",
       "...         ...             ...            ...  \n",
       "1395     4373.0          2337.0         3397.0  \n",
       "1396     4438.0          2212.0         3223.0  \n",
       "1397     3971.0          1827.0         2979.0  \n",
       "1398      979.0           342.0          566.0  \n",
       "1399     2134.0          1486.0         2487.0  \n",
       "\n",
       "[1400 rows x 8 columns]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = rm.dropna();\n",
    "df = df.reset_index(drop=True)\n",
    "df\n",
    "# Observations with all 1400"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "ced2e8b4-c52c-4da1-be78-aba5066042dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Merikannontie      4839490.0\n",
       "Eteläesplanadi     3677532.0\n",
       "Kaivokatu          3675674.0\n",
       "Kuusisaarentie     2823869.0\n",
       "Länsituulenkuja    2037502.0\n",
       "Länsiväylä         1324149.0\n",
       "Gallen-Kallela     1107151.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_sum = rm.sum().sort_values(ascending=False)\n",
    "col_sum\n",
    "# Merikannontie was busiest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "b3973cd9-7b78-44ca-aaee-515cf6f12fcb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GSM1068478    56.16660\n",
       "GSM1068486    57.44120\n",
       "GSM1068492    55.02850\n",
       "GSM1068498    55.15540\n",
       "GSM1068505    54.79190\n",
       "GSM1068512    54.50550\n",
       "GSM1068520    54.32463\n",
       "GSM1068480    56.52050\n",
       "GSM1068501    55.27480\n",
       "GSM1068516    55.05304\n",
       "GSM1068458    55.43840\n",
       "GSM1068468    55.23094\n",
       "GSM1068477    58.04440\n",
       "GSM1068467    56.19320\n",
       "GSM1068528    58.78780\n",
       "GSM1068537    55.59500\n",
       "GSM1068543    56.36850\n",
       "GSM1068548    54.39480\n",
       "GSM1068555    56.34530\n",
       "GSM1068562    54.51360\n",
       "dtype: float64"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_sum = df.sum().sort_values(ascending=False)\n",
    "col_sum\n",
    "# Kaivokatu was busiest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cdb938a-4f21-43c6-987f-63d54f9d72d6",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}