untitled_data_wrangling_snippet_1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0dafa875",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "594d0c82",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = pd.read_csv('data/table_1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9704caea",
   "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>col_1</th>\n",
       "      <th>col_2</th>\n",
       "      <th>col_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>b</td>\n",
       "      <td>3</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b</td>\n",
       "      <td>5</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>c</td>\n",
       "      <td>1</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>c</td>\n",
       "      <td>2</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>c</td>\n",
       "      <td>4</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>c</td>\n",
       "      <td>5</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   col_1  col_2  col_3\n",
       "0      a      1     10\n",
       "1      a      2     20\n",
       "2      a      3     30\n",
       "3      a      4     40\n",
       "4      a      5     50\n",
       "5      b      1     60\n",
       "6      b      2     70\n",
       "7      b      3     80\n",
       "8      b      4     90\n",
       "9      b      5    100\n",
       "10     c      1    110\n",
       "11     c      2    120\n",
       "12     c      3    130\n",
       "13     c      4    140\n",
       "14     c      5    150"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#たとえば、col_1を日付、col_2を時間帯、col_3を日・時間帯ごとのある指標の値と考える\n",
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "811e7f5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = pd.read_csv('data/table_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "18b436ac",
   "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>col_1</th>\n",
       "      <th>col_a</th>\n",
       "      <th>col_b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col_1  col_a  col_b\n",
       "0     a      1      4\n",
       "1     b      2      3\n",
       "2     c      3      5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#col_1はdf_1と同じく日付、col_aは日ごとの開始時間帯、col_bは日ごとの終了時間帯と考える\n",
    "df_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "47f1363e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#df_2で日ごとの開始時間帯から終了時間帯までの条件に該当するcol_3の値をdf_1からとってくる\n",
    "#まず、df_1のcol_2の値を列にしてcol_3の値を持たせる\n",
    "#さらに、各行でcol_3の値をリストにして1列にまとめる（col_3_value_list）\n",
    "df_1 = df_1.pivot(index='col_1', columns='col_2', values='col_3').reset_index()\n",
    "df_1['col_3_value_list'] = df_1.apply(lambda x: [x[1], x[2], x[3], x[4], x[5]], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3f65f4d5",
   "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>col_2</th>\n",
       "      <th>col_1</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>col_3_value_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>[10, 20, 30, 40, 50]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>100</td>\n",
       "      <td>[60, 70, 80, 90, 100]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>130</td>\n",
       "      <td>140</td>\n",
       "      <td>150</td>\n",
       "      <td>[110, 120, 130, 140, 150]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "col_2 col_1    1    2    3    4    5           col_3_value_list\n",
       "0         a   10   20   30   40   50       [10, 20, 30, 40, 50]\n",
       "1         b   60   70   80   90  100      [60, 70, 80, 90, 100]\n",
       "2         c  110  120  130  140  150  [110, 120, 130, 140, 150]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dbe66183",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_1とdf_2を結合し、col_a、col_bとcol_3_value_listを対応させる\n",
    "df_merged = pd.merge(df_1, df_2, on='col_1', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9acc4e40",
   "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>col_1</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>col_3_value_list</th>\n",
       "      <th>col_a</th>\n",
       "      <th>col_b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>[10, 20, 30, 40, 50]</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>100</td>\n",
       "      <td>[60, 70, 80, 90, 100]</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>130</td>\n",
       "      <td>140</td>\n",
       "      <td>150</td>\n",
       "      <td>[110, 120, 130, 140, 150]</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col_1    1    2    3    4    5           col_3_value_list  col_a  col_b\n",
       "0     a   10   20   30   40   50       [10, 20, 30, 40, 50]      1      4\n",
       "1     b   60   70   80   90  100      [60, 70, 80, 90, 100]      2      3\n",
       "2     c  110  120  130  140  150  [110, 120, 130, 140, 150]      3      5"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f5457930",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#col_a、col_bをリストのインデックスにして条件に該当するcol_3_value_listの値を抽出する\n",
    "df_merged['extract_from_col_3_value_list'] = df_merged.apply(lambda x: x['col_3_value_list'][x['col_a']-1:x['col_b']], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "defafee6",
   "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>col_1</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>col_3_value_list</th>\n",
       "      <th>col_a</th>\n",
       "      <th>col_b</th>\n",
       "      <th>extract_from_col_3_value_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>40</td>\n",
       "      <td>50</td>\n",
       "      <td>[10, 20, 30, 40, 50]</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>[10, 20, 30, 40]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>60</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>90</td>\n",
       "      <td>100</td>\n",
       "      <td>[60, 70, 80, 90, 100]</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>[70, 80]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>130</td>\n",
       "      <td>140</td>\n",
       "      <td>150</td>\n",
       "      <td>[110, 120, 130, 140, 150]</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>[130, 140, 150]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  col_1    1    2    3    4    5           col_3_value_list  col_a  col_b  \\\n",
       "0     a   10   20   30   40   50       [10, 20, 30, 40, 50]      1      4   \n",
       "1     b   60   70   80   90  100      [60, 70, 80, 90, 100]      2      3   \n",
       "2     c  110  120  130  140  150  [110, 120, 130, 140, 150]      3      5   \n",
       "\n",
       "  extract_from_col_3_value_list  \n",
       "0              [10, 20, 30, 40]  \n",
       "1                      [70, 80]  \n",
       "2               [130, 140, 150]  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}