{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Päivitetty 2024-02-08 / Aki Taanila\n"
     ]
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "print(f'Päivitetty {datetime.now().date()} / Aki Taanila')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Datojen yhdistäminen vierekkäin tai allekkain\n",
    "\n",
    "Vierekkäin yhdistettävissä datoissa täytyy molemmissa olla indeksi, jonka arvojen perusteella rivit asettuvat kohdakkain.\n",
    "\n",
    "Allekkain yhdistettävissä datoissa saman nimiset muuttujat asettuvat kohdakkain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Yhdistäminen vierekkäin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>nro</th>\n",
       "      <th>sukup</th>\n",
       "      <th>ikä</th>\n",
       "      <th>perhe</th>\n",
       "      <th>koulutus</th>\n",
       "      <th>palveluv</th>\n",
       "      <th>palkka</th>\n",
       "      <th>johto</th>\n",
       "      <th>työtov</th>\n",
       "      <th>työymp</th>\n",
       "      <th>palkkat</th>\n",
       "      <th>työteht</th>\n",
       "      <th>työterv</th>\n",
       "      <th>lomaosa</th>\n",
       "      <th>kuntosa</th>\n",
       "      <th>hieroja</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3587</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2963</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1989</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2144</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.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",
       "      <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>77</th>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1598</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1638</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2612</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>35</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2808</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>82</td>\n",
       "      <td>2</td>\n",
       "      <td>35</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>82 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    nro  sukup  ikä  perhe  koulutus  palveluv  palkka  johto  työtov  työymp  \\\n",
       "0     1      1   38      1       1.0      22.0    3587      3     3.0       3   \n",
       "1     2      1   29      2       2.0      10.0    2963      1     5.0       2   \n",
       "2     3      1   30      1       1.0       7.0    1989      3     4.0       1   \n",
       "3     4      1   36      2       1.0      14.0    2144      3     3.0       3   \n",
       "4     5      1   24      1       2.0       4.0    2183      2     3.0       2   \n",
       "..  ...    ...  ...    ...       ...       ...     ...    ...     ...     ...   \n",
       "77   78      1   22      1       3.0       0.0    1598      4     4.0       4   \n",
       "78   79      1   33      1       1.0       2.0    1638      1     3.0       2   \n",
       "79   80      1   27      1       2.0       7.0    2612      3     4.0       3   \n",
       "80   81      1   35      2       2.0      16.0    2808      3     4.0       3   \n",
       "81   82      2   35      2       3.0      15.0    2183      3     4.0       4   \n",
       "\n",
       "    palkkat  työteht  työterv  lomaosa  kuntosa  hieroja  \n",
       "0         3        3      NaN      NaN      NaN      NaN  \n",
       "1         1        3      NaN      NaN      NaN      NaN  \n",
       "2         1        3      1.0      NaN      NaN      NaN  \n",
       "3         3        3      1.0      NaN      NaN      NaN  \n",
       "4         1        2      1.0      NaN      NaN      NaN  \n",
       "..      ...      ...      ...      ...      ...      ...  \n",
       "77        3        4      NaN      1.0      1.0      NaN  \n",
       "78        1        2      1.0      NaN      NaN      NaN  \n",
       "79        3        3      1.0      NaN      1.0      NaN  \n",
       "80        3        3      NaN      NaN      NaN      NaN  \n",
       "81        3        4      1.0      NaN      NaN      NaN  \n",
       "\n",
       "[82 rows x 16 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Avaan yhdistettävät datat\n",
    "df1 = pd.read_excel('https://taanila.fi/data1.xlsx')\n",
    "df2 = pd.read_excel('https://taanila.fi/data1_s.xlsx')\n",
    "\n",
    "# Data, johon yhdistetään sairaspoissaolot\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>nro</th>\n",
       "      <th>sairaspoissaolot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>46</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>47</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>48</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>49</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>50</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   nro  sairaspoissaolot\n",
       "0    6                20\n",
       "1    7                 1\n",
       "2    8                 7\n",
       "3   45                 5\n",
       "4   46                 4\n",
       "5   47                 2\n",
       "6   48                 0\n",
       "7   49                 5\n",
       "8   50                 0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sairaspoissaolot\n",
    "# nro-sarakkeen tiedot vastaavat toisiaan: esim. nro 6 vastaa henkilöä, \n",
    "# jonka nro on 6 myös df1:ssä\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Siirrän nro-muuttujan arvot indeksiin\n",
    "df1 = df1.set_index('nro')\n",
    "df2 = df2.set_index('nro')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>sukup</th>\n",
       "      <th>ikä</th>\n",
       "      <th>perhe</th>\n",
       "      <th>koulutus</th>\n",
       "      <th>palveluv</th>\n",
       "      <th>palkka</th>\n",
       "      <th>johto</th>\n",
       "      <th>työtov</th>\n",
       "      <th>työymp</th>\n",
       "      <th>palkkat</th>\n",
       "      <th>työteht</th>\n",
       "      <th>työterv</th>\n",
       "      <th>lomaosa</th>\n",
       "      <th>kuntosa</th>\n",
       "      <th>hieroja</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nro</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>1</th>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3587</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2963</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1989</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2144</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.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",
       "      <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>78</th>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1598</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1638</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2612</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>1</td>\n",
       "      <td>35</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2808</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>2</td>\n",
       "      <td>35</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>82 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     sukup  ikä  perhe  koulutus  palveluv  palkka  johto  työtov  työymp  \\\n",
       "nro                                                                         \n",
       "1        1   38      1       1.0      22.0    3587      3     3.0       3   \n",
       "2        1   29      2       2.0      10.0    2963      1     5.0       2   \n",
       "3        1   30      1       1.0       7.0    1989      3     4.0       1   \n",
       "4        1   36      2       1.0      14.0    2144      3     3.0       3   \n",
       "5        1   24      1       2.0       4.0    2183      2     3.0       2   \n",
       "..     ...  ...    ...       ...       ...     ...    ...     ...     ...   \n",
       "78       1   22      1       3.0       0.0    1598      4     4.0       4   \n",
       "79       1   33      1       1.0       2.0    1638      1     3.0       2   \n",
       "80       1   27      1       2.0       7.0    2612      3     4.0       3   \n",
       "81       1   35      2       2.0      16.0    2808      3     4.0       3   \n",
       "82       2   35      2       3.0      15.0    2183      3     4.0       4   \n",
       "\n",
       "     palkkat  työteht  työterv  lomaosa  kuntosa  hieroja  \n",
       "nro                                                        \n",
       "1          3        3      NaN      NaN      NaN      NaN  \n",
       "2          1        3      NaN      NaN      NaN      NaN  \n",
       "3          1        3      1.0      NaN      NaN      NaN  \n",
       "4          3        3      1.0      NaN      NaN      NaN  \n",
       "5          1        2      1.0      NaN      NaN      NaN  \n",
       "..       ...      ...      ...      ...      ...      ...  \n",
       "78         3        4      NaN      1.0      1.0      NaN  \n",
       "79         1        2      1.0      NaN      NaN      NaN  \n",
       "80         3        3      1.0      NaN      1.0      NaN  \n",
       "81         3        3      NaN      NaN      NaN      NaN  \n",
       "82         3        4      1.0      NaN      NaN      NaN  \n",
       "\n",
       "[82 rows x 15 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Varmistan, että nro:n siirto indeksiin onnnistui\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>sairaspoissaolot</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nro</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sairaspoissaolot\n",
       "nro                  \n",
       "6                  20\n",
       "7                   1\n",
       "8                   7\n",
       "45                  5\n",
       "46                  4\n",
       "47                  2\n",
       "48                  0\n",
       "49                  5\n",
       "50                  0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Varmistan, että nro:n siirto indeksiin onnnistui\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Yhdistän df1 ja df2 vierekkäin (axis=1 merkitsee vierekkäin yhdistämistä)\n",
    "df3 = pd.concat([df1, df2], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>sukup</th>\n",
       "      <th>ikä</th>\n",
       "      <th>perhe</th>\n",
       "      <th>koulutus</th>\n",
       "      <th>palveluv</th>\n",
       "      <th>palkka</th>\n",
       "      <th>johto</th>\n",
       "      <th>työtov</th>\n",
       "      <th>työymp</th>\n",
       "      <th>palkkat</th>\n",
       "      <th>työteht</th>\n",
       "      <th>työterv</th>\n",
       "      <th>lomaosa</th>\n",
       "      <th>kuntosa</th>\n",
       "      <th>hieroja</th>\n",
       "      <th>sairaspoissaolot</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nro</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>1</th>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3587</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</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>2</th>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2963</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</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>3</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1989</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2144</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1910</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2066</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2066</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2768</td>\n",
       "      <td>2</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2106</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sukup  ikä  perhe  koulutus  palveluv  palkka  johto  työtov  työymp  \\\n",
       "nro                                                                         \n",
       "1        1   38      1       1.0      22.0    3587      3     3.0       3   \n",
       "2        1   29      2       2.0      10.0    2963      1     5.0       2   \n",
       "3        1   30      1       1.0       7.0    1989      3     4.0       1   \n",
       "4        1   36      2       1.0      14.0    2144      3     3.0       3   \n",
       "5        1   24      1       2.0       4.0    2183      2     3.0       2   \n",
       "6        2   31      2       2.0      14.0    1910      4     4.0       5   \n",
       "7        1   49      1       2.0      16.0    2066      3     5.0       4   \n",
       "8        1   55      1       1.0       0.0    2066      3     5.0       3   \n",
       "9        1   40      2       1.0      23.0    2768      2     4.0       4   \n",
       "10       1   33      1       1.0      16.0    2106      3     2.0       1   \n",
       "\n",
       "     palkkat  työteht  työterv  lomaosa  kuntosa  hieroja  sairaspoissaolot  \n",
       "nro                                                                          \n",
       "1          3        3      NaN      NaN      NaN      NaN               NaN  \n",
       "2          1        3      NaN      NaN      NaN      NaN               NaN  \n",
       "3          1        3      1.0      NaN      NaN      NaN               NaN  \n",
       "4          3        3      1.0      NaN      NaN      NaN               NaN  \n",
       "5          1        2      1.0      NaN      NaN      NaN               NaN  \n",
       "6          2        4      1.0      1.0      NaN      NaN              20.0  \n",
       "7          2        2      NaN      NaN      1.0      NaN               1.0  \n",
       "8          1        3      1.0      NaN      NaN      NaN               7.0  \n",
       "9          2        4      NaN      1.0      NaN      NaN               NaN  \n",
       "10         1        1      1.0      NaN      NaN      NaN               NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Varmistan yhdistämisen onnistumisen \n",
    "df3.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Yhdistäminen allekkain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>sukup</th>\n",
       "      <th>ikä</th>\n",
       "      <th>perhe</th>\n",
       "      <th>koulutus</th>\n",
       "      <th>palveluv</th>\n",
       "      <th>palkka</th>\n",
       "      <th>johto</th>\n",
       "      <th>työtov</th>\n",
       "      <th>työymp</th>\n",
       "      <th>palkkat</th>\n",
       "      <th>työteht</th>\n",
       "      <th>työterv</th>\n",
       "      <th>lomaosa</th>\n",
       "      <th>kuntosa</th>\n",
       "      <th>hieroja</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nro</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>83</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2800</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2500</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3500</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4600</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>3900</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2400</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sukup  ikä  perhe  koulutus  palveluv  palkka  johto  työtov  työymp  \\\n",
       "nro                                                                         \n",
       "83       2   31      2         3         1    2800      4       4       4   \n",
       "84       1   26      1         2         0    2500      5       4       3   \n",
       "85       1   32      2         2         0    2500      5       3       2   \n",
       "86       1   40      1         2         0    2500      3       4       3   \n",
       "87       1   31      1         4         1    3500      4       4       3   \n",
       "88       1   28      1         4         0    4600      4       3       2   \n",
       "89       1   29      1         4         0    3900      4       3       3   \n",
       "90       2   31      2         2         0    2400      4       3       2   \n",
       "\n",
       "     palkkat  työteht  työterv  lomaosa  kuntosa  hieroja  \n",
       "nro                                                        \n",
       "83         3        3        1      NaN      NaN      1.0  \n",
       "84         2        5        1      NaN      NaN      NaN  \n",
       "85         2        3        1      NaN      1.0      NaN  \n",
       "86         2        3        1      NaN      1.0      NaN  \n",
       "87         3        3        1      NaN      1.0      NaN  \n",
       "88         4        2        1      NaN      NaN      NaN  \n",
       "89         4        3        1      NaN      NaN      NaN  \n",
       "90         2        3        1      NaN      1.0      NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data1_add.xlsx sisältää tietoja, jotka haluan lisätä df3:n perään\n",
    "df4 = pd.read_excel('http://taanila.fi/data1_add.xlsx')\n",
    "\n",
    "# Siirrän nro-muuttujan arvot indeksiin\n",
    "df4 = df4.set_index('nro')\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>sukup</th>\n",
       "      <th>ikä</th>\n",
       "      <th>perhe</th>\n",
       "      <th>koulutus</th>\n",
       "      <th>palveluv</th>\n",
       "      <th>palkka</th>\n",
       "      <th>johto</th>\n",
       "      <th>työtov</th>\n",
       "      <th>työymp</th>\n",
       "      <th>palkkat</th>\n",
       "      <th>työteht</th>\n",
       "      <th>työterv</th>\n",
       "      <th>lomaosa</th>\n",
       "      <th>kuntosa</th>\n",
       "      <th>hieroja</th>\n",
       "      <th>sairaspoissaolot</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nro</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>1</th>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3587</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</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>2</th>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2963</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</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>3</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1989</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2144</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2183</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</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",
       "      <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>86</th>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2500</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3500</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4600</td>\n",
       "      <td>4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>1</td>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3900</td>\n",
       "      <td>4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2400</td>\n",
       "      <td>4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     sukup  ikä  perhe  koulutus  palveluv  palkka  johto  työtov  työymp  \\\n",
       "nro                                                                         \n",
       "1        1   38      1       1.0      22.0    3587      3     3.0       3   \n",
       "2        1   29      2       2.0      10.0    2963      1     5.0       2   \n",
       "3        1   30      1       1.0       7.0    1989      3     4.0       1   \n",
       "4        1   36      2       1.0      14.0    2144      3     3.0       3   \n",
       "5        1   24      1       2.0       4.0    2183      2     3.0       2   \n",
       "..     ...  ...    ...       ...       ...     ...    ...     ...     ...   \n",
       "86       1   40      1       2.0       0.0    2500      3     4.0       3   \n",
       "87       1   31      1       4.0       1.0    3500      4     4.0       3   \n",
       "88       1   28      1       4.0       0.0    4600      4     3.0       2   \n",
       "89       1   29      1       4.0       0.0    3900      4     3.0       3   \n",
       "90       2   31      2       2.0       0.0    2400      4     3.0       2   \n",
       "\n",
       "     palkkat  työteht  työterv  lomaosa  kuntosa  hieroja  sairaspoissaolot  \n",
       "nro                                                                          \n",
       "1          3        3      NaN      NaN      NaN      NaN               NaN  \n",
       "2          1        3      NaN      NaN      NaN      NaN               NaN  \n",
       "3          1        3      1.0      NaN      NaN      NaN               NaN  \n",
       "4          3        3      1.0      NaN      NaN      NaN               NaN  \n",
       "5          1        2      1.0      NaN      NaN      NaN               NaN  \n",
       "..       ...      ...      ...      ...      ...      ...               ...  \n",
       "86         2        3      1.0      NaN      1.0      NaN               NaN  \n",
       "87         3        3      1.0      NaN      1.0      NaN               NaN  \n",
       "88         4        2      1.0      NaN      NaN      NaN               NaN  \n",
       "89         4        3      1.0      NaN      NaN      NaN               NaN  \n",
       "90         2        3      1.0      NaN      1.0      NaN               NaN  \n",
       "\n",
       "[90 rows x 16 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Yhdistän df3 ja df4 allekkain\n",
    "df5 = pd.concat([df3, df4])\n",
    "df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  Luettelo muistiossa käytetyistä funktioista ja ominaisuuksista\n",
    "\n",
    "* **pd.read_excel()** datan lukeminen Excel-tiedostosta\n",
    "* **df.set_index()** sarakkeen siirtäminen indeksiin\n",
    "* **pd.concat()** datojen yhdistäminen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lisätietoa\n",
    "\n",
    "- https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html\n",
    "    \n",
    "Data-analytiikka Pythonilla https://tilastoapu.wordpress.com/python/"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}