Convert Bikes Usage To Json.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parsing bikes usage into json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from os.path import join\n",
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load csv"
   ]
  },
  {
   "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>interval_start</th>\n",
       "      <th>interval_end</th>\n",
       "      <th>bikes_in_use</th>\n",
       "      <th>bikes_total</th>\n",
       "      <th>bikes_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-07-01 00:00:00</td>\n",
       "      <td>2019-07-01 00:15:00</td>\n",
       "      <td>32</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.016763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01 00:15:00</td>\n",
       "      <td>2019-07-01 00:30:00</td>\n",
       "      <td>71</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.037192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-01 00:30:00</td>\n",
       "      <td>2019-07-01 00:45:00</td>\n",
       "      <td>84</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.044002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-01 00:45:00</td>\n",
       "      <td>2019-07-01 01:00:00</td>\n",
       "      <td>78</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.040859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-01 01:00:00</td>\n",
       "      <td>2019-07-01 01:15:00</td>\n",
       "      <td>65</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.034049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2971</td>\n",
       "      <td>2019-07-31 22:45:00</td>\n",
       "      <td>2019-07-31 23:00:00</td>\n",
       "      <td>152</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.079623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2972</td>\n",
       "      <td>2019-07-31 23:00:00</td>\n",
       "      <td>2019-07-31 23:15:00</td>\n",
       "      <td>123</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.064432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2973</td>\n",
       "      <td>2019-07-31 23:15:00</td>\n",
       "      <td>2019-07-31 23:30:00</td>\n",
       "      <td>100</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.052383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2974</td>\n",
       "      <td>2019-07-31 23:30:00</td>\n",
       "      <td>2019-07-31 23:45:00</td>\n",
       "      <td>99</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.051860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2975</td>\n",
       "      <td>2019-07-31 23:45:00</td>\n",
       "      <td>2019-08-01 00:00:00</td>\n",
       "      <td>52</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.027239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2976 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           interval_start         interval_end  bikes_in_use  bikes_total  \\\n",
       "0     2019-07-01 00:00:00  2019-07-01 00:15:00            32         1909   \n",
       "1     2019-07-01 00:15:00  2019-07-01 00:30:00            71         1909   \n",
       "2     2019-07-01 00:30:00  2019-07-01 00:45:00            84         1909   \n",
       "3     2019-07-01 00:45:00  2019-07-01 01:00:00            78         1909   \n",
       "4     2019-07-01 01:00:00  2019-07-01 01:15:00            65         1909   \n",
       "...                   ...                  ...           ...          ...   \n",
       "2971  2019-07-31 22:45:00  2019-07-31 23:00:00           152         1909   \n",
       "2972  2019-07-31 23:00:00  2019-07-31 23:15:00           123         1909   \n",
       "2973  2019-07-31 23:15:00  2019-07-31 23:30:00           100         1909   \n",
       "2974  2019-07-31 23:30:00  2019-07-31 23:45:00            99         1909   \n",
       "2975  2019-07-31 23:45:00  2019-08-01 00:00:00            52         1909   \n",
       "\n",
       "      bikes_percentage  \n",
       "0             0.016763  \n",
       "1             0.037192  \n",
       "2             0.044002  \n",
       "3             0.040859  \n",
       "4             0.034049  \n",
       "...                ...  \n",
       "2971          0.079623  \n",
       "2972          0.064432  \n",
       "2973          0.052383  \n",
       "2974          0.051860  \n",
       "2975          0.027239  \n",
       "\n",
       "[2976 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(join(\"bikes_usage\", \"historia_przejazdow_2019-07.csv\"), parse_dates=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove unnecessary columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\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>interval_start</th>\n",
       "      <th>bikes_in_use</th>\n",
       "      <th>bikes_total</th>\n",
       "      <th>bikes_percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-07-01 00:00:00</td>\n",
       "      <td>32</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.016763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01 00:15:00</td>\n",
       "      <td>71</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.037192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-01 00:30:00</td>\n",
       "      <td>84</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.044002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-01 00:45:00</td>\n",
       "      <td>78</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.040859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.034049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2971</td>\n",
       "      <td>2019-07-31 22:45:00</td>\n",
       "      <td>152</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.079623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2972</td>\n",
       "      <td>2019-07-31 23:00:00</td>\n",
       "      <td>123</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.064432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2973</td>\n",
       "      <td>2019-07-31 23:15:00</td>\n",
       "      <td>100</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.052383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2974</td>\n",
       "      <td>2019-07-31 23:30:00</td>\n",
       "      <td>99</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.051860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2975</td>\n",
       "      <td>2019-07-31 23:45:00</td>\n",
       "      <td>52</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.027239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2976 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          interval_start  bikes_in_use  bikes_total  bikes_percentage\n",
       "0    2019-07-01 00:00:00            32         1909          0.016763\n",
       "1    2019-07-01 00:15:00            71         1909          0.037192\n",
       "2    2019-07-01 00:30:00            84         1909          0.044002\n",
       "3    2019-07-01 00:45:00            78         1909          0.040859\n",
       "4    2019-07-01 01:00:00            65         1909          0.034049\n",
       "...                  ...           ...          ...               ...\n",
       "2971 2019-07-31 22:45:00           152         1909          0.079623\n",
       "2972 2019-07-31 23:00:00           123         1909          0.064432\n",
       "2973 2019-07-31 23:15:00           100         1909          0.052383\n",
       "2974 2019-07-31 23:30:00            99         1909          0.051860\n",
       "2975 2019-07-31 23:45:00            52         1909          0.027239\n",
       "\n",
       "[2976 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[[\"interval_start\", \"bikes_in_use\", \"bikes_total\", \"bikes_percentage\"]]\n",
    "# df = df.rename(columns={\"interval_start\":\"s\",\"rental_place\":\"o\",\"return_place\":\"d\",\"number_of_trips\":\"c\"})\n",
    "df['interval_start']= pd.to_datetime(df['interval_start']) \n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add day column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>interval_start</th>\n",
       "      <th>bikes_in_use</th>\n",
       "      <th>bikes_total</th>\n",
       "      <th>bikes_percentage</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-07-01 00:00:00</td>\n",
       "      <td>32</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.016763</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01 00:15:00</td>\n",
       "      <td>71</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.037192</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-01 00:30:00</td>\n",
       "      <td>84</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.044002</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-01 00:45:00</td>\n",
       "      <td>78</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.040859</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.034049</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2971</td>\n",
       "      <td>2019-07-31 22:45:00</td>\n",
       "      <td>152</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.079623</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2972</td>\n",
       "      <td>2019-07-31 23:00:00</td>\n",
       "      <td>123</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.064432</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2973</td>\n",
       "      <td>2019-07-31 23:15:00</td>\n",
       "      <td>100</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.052383</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2974</td>\n",
       "      <td>2019-07-31 23:30:00</td>\n",
       "      <td>99</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.051860</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2975</td>\n",
       "      <td>2019-07-31 23:45:00</td>\n",
       "      <td>52</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.027239</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2976 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          interval_start  bikes_in_use  bikes_total  bikes_percentage  day\n",
       "0    2019-07-01 00:00:00            32         1909          0.016763    1\n",
       "1    2019-07-01 00:15:00            71         1909          0.037192    1\n",
       "2    2019-07-01 00:30:00            84         1909          0.044002    1\n",
       "3    2019-07-01 00:45:00            78         1909          0.040859    1\n",
       "4    2019-07-01 01:00:00            65         1909          0.034049    1\n",
       "...                  ...           ...          ...               ...  ...\n",
       "2971 2019-07-31 22:45:00           152         1909          0.079623   31\n",
       "2972 2019-07-31 23:00:00           123         1909          0.064432   31\n",
       "2973 2019-07-31 23:15:00           100         1909          0.052383   31\n",
       "2974 2019-07-31 23:30:00            99         1909          0.051860   31\n",
       "2975 2019-07-31 23:45:00            52         1909          0.027239   31\n",
       "\n",
       "[2976 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"day\"] = df[\"interval_start\"].dt.day\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Change hour to a \"minute in day\" form"
   ]
  },
  {
   "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>interval_start</th>\n",
       "      <th>bikes_in_use</th>\n",
       "      <th>bikes_total</th>\n",
       "      <th>bikes_percentage</th>\n",
       "      <th>day</th>\n",
       "      <th>minute_in_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-07-01 00:00:00</td>\n",
       "      <td>32</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.016763</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01 00:15:00</td>\n",
       "      <td>71</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.037192</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-01 00:30:00</td>\n",
       "      <td>84</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.044002</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-01 00:45:00</td>\n",
       "      <td>78</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.040859</td>\n",
       "      <td>1</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-01 01:00:00</td>\n",
       "      <td>65</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.034049</td>\n",
       "      <td>1</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\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",
       "      <td>2971</td>\n",
       "      <td>2019-07-31 22:45:00</td>\n",
       "      <td>152</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.079623</td>\n",
       "      <td>31</td>\n",
       "      <td>1365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2972</td>\n",
       "      <td>2019-07-31 23:00:00</td>\n",
       "      <td>123</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.064432</td>\n",
       "      <td>31</td>\n",
       "      <td>1380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2973</td>\n",
       "      <td>2019-07-31 23:15:00</td>\n",
       "      <td>100</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.052383</td>\n",
       "      <td>31</td>\n",
       "      <td>1395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2974</td>\n",
       "      <td>2019-07-31 23:30:00</td>\n",
       "      <td>99</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.051860</td>\n",
       "      <td>31</td>\n",
       "      <td>1410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2975</td>\n",
       "      <td>2019-07-31 23:45:00</td>\n",
       "      <td>52</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.027239</td>\n",
       "      <td>31</td>\n",
       "      <td>1425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2976 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          interval_start  bikes_in_use  bikes_total  bikes_percentage  day  \\\n",
       "0    2019-07-01 00:00:00            32         1909          0.016763    1   \n",
       "1    2019-07-01 00:15:00            71         1909          0.037192    1   \n",
       "2    2019-07-01 00:30:00            84         1909          0.044002    1   \n",
       "3    2019-07-01 00:45:00            78         1909          0.040859    1   \n",
       "4    2019-07-01 01:00:00            65         1909          0.034049    1   \n",
       "...                  ...           ...          ...               ...  ...   \n",
       "2971 2019-07-31 22:45:00           152         1909          0.079623   31   \n",
       "2972 2019-07-31 23:00:00           123         1909          0.064432   31   \n",
       "2973 2019-07-31 23:15:00           100         1909          0.052383   31   \n",
       "2974 2019-07-31 23:30:00            99         1909          0.051860   31   \n",
       "2975 2019-07-31 23:45:00            52         1909          0.027239   31   \n",
       "\n",
       "      minute_in_day  \n",
       "0                 0  \n",
       "1                15  \n",
       "2                30  \n",
       "3                45  \n",
       "4                60  \n",
       "...             ...  \n",
       "2971           1365  \n",
       "2972           1380  \n",
       "2973           1395  \n",
       "2974           1410  \n",
       "2975           1425  \n",
       "\n",
       "[2976 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"minute_in_day\"] = df[\"interval_start\"].dt.hour*60 + df[\"interval_start\"].dt.minute\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count the number of days in this month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "days_in_month = df[\"interval_start\"].dt.daysinmonth.max()\n",
    "days_in_month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove unnecessary columns and rename rest  with {\"bikes_in_use\":\"bu\", \"bikes_total\":\"bt\", \"bikes_percentage\":\"bp\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>day</th>\n",
       "      <th>minute_in_day</th>\n",
       "      <th>bu</th>\n",
       "      <th>bt</th>\n",
       "      <th>bp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>32</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.016763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>71</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.037192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>84</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.044002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>45</td>\n",
       "      <td>78</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.040859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>60</td>\n",
       "      <td>65</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.034049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2971</td>\n",
       "      <td>31</td>\n",
       "      <td>1365</td>\n",
       "      <td>152</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.079623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2972</td>\n",
       "      <td>31</td>\n",
       "      <td>1380</td>\n",
       "      <td>123</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.064432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2973</td>\n",
       "      <td>31</td>\n",
       "      <td>1395</td>\n",
       "      <td>100</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.052383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2974</td>\n",
       "      <td>31</td>\n",
       "      <td>1410</td>\n",
       "      <td>99</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.051860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2975</td>\n",
       "      <td>31</td>\n",
       "      <td>1425</td>\n",
       "      <td>52</td>\n",
       "      <td>1909</td>\n",
       "      <td>0.027239</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2976 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      day  minute_in_day   bu    bt        bp\n",
       "0       1              0   32  1909  0.016763\n",
       "1       1             15   71  1909  0.037192\n",
       "2       1             30   84  1909  0.044002\n",
       "3       1             45   78  1909  0.040859\n",
       "4       1             60   65  1909  0.034049\n",
       "...   ...            ...  ...   ...       ...\n",
       "2971   31           1365  152  1909  0.079623\n",
       "2972   31           1380  123  1909  0.064432\n",
       "2973   31           1395  100  1909  0.052383\n",
       "2974   31           1410   99  1909  0.051860\n",
       "2975   31           1425   52  1909  0.027239\n",
       "\n",
       "[2976 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[[\"day\", \"minute_in_day\", \"bikes_in_use\", \"bikes_total\", \"bikes_percentage\"]]\n",
    "\n",
    "df = df.rename(columns={\"bikes_in_use\":\"bu\", \"bikes_total\":\"bt\", \"bikes_percentage\":\"bp\"})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example for a single day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: [{'bu': 74, 'bt': 1909, 'bp': 0.03876375065479309}],\n",
       " 15: [{'bu': 82, 'bt': 1909, 'bp': 0.042954426401257205}],\n",
       " 30: [{'bu': 57, 'bt': 1909, 'bp': 0.029858564693556838}],\n",
       " 45: [{'bu': 44, 'bt': 1909, 'bp': 0.023048716605552647}],\n",
       " 60: [{'bu': 37, 'bt': 1909, 'bp': 0.019381875327396544}],\n",
       " 75: [{'bu': 38, 'bt': 1909, 'bp': 0.01990570979570456}],\n",
       " 90: [{'bu': 36, 'bt': 1909, 'bp': 0.018858040859088532}],\n",
       " 105: [{'bu': 43, 'bt': 1909, 'bp': 0.022524882137244632}],\n",
       " 120: [{'bu': 41, 'bt': 1909, 'bp': 0.0214772132006286}],\n",
       " 135: [{'bu': 38, 'bt': 1909, 'bp': 0.01990570979570456}],\n",
       " 150: [{'bu': 29, 'bt': 1909, 'bp': 0.015191199580932424}],\n",
       " 165: [{'bu': 23, 'bt': 1909, 'bp': 0.012048192771084338}],\n",
       " 180: [{'bu': 23, 'bt': 1909, 'bp': 0.012048192771084338}],\n",
       " 195: [{'bu': 26, 'bt': 1909, 'bp': 0.013619696176008382}],\n",
       " 210: [{'bu': 26, 'bt': 1909, 'bp': 0.013619696176008382}],\n",
       " 225: [{'bu': 25, 'bt': 1909, 'bp': 0.013095861707700369}],\n",
       " 240: [{'bu': 22, 'bt': 1909, 'bp': 0.011524358302776323}],\n",
       " 255: [{'bu': 24, 'bt': 1909, 'bp': 0.012572027239392351}],\n",
       " 270: [{'bu': 32, 'bt': 1909, 'bp': 0.01676270298585647}],\n",
       " 285: [{'bu': 41, 'bt': 1909, 'bp': 0.0214772132006286}],\n",
       " 300: [{'bu': 59, 'bt': 1909, 'bp': 0.03090623363017287}],\n",
       " 315: [{'bu': 66, 'bt': 1909, 'bp': 0.03457307490832897}],\n",
       " 330: [{'bu': 64, 'bt': 1909, 'bp': 0.03352540597171294}],\n",
       " 345: [{'bu': 54, 'bt': 1909, 'bp': 0.028287061288632794}],\n",
       " 360: [{'bu': 61, 'bt': 1909, 'bp': 0.031953902566788886}],\n",
       " 375: [{'bu': 87, 'bt': 1909, 'bp': 0.04557359874279728}],\n",
       " 390: [{'bu': 128, 'bt': 1909, 'bp': 0.06705081194342588}],\n",
       " 405: [{'bu': 158, 'bt': 1909, 'bp': 0.08276584599266631}],\n",
       " 420: [{'bu': 180, 'bt': 1909, 'bp': 0.09429020429544263}],\n",
       " 435: [{'bu': 213, 'bt': 1909, 'bp': 0.11157674174960712}],\n",
       " 450: [{'bu': 273, 'bt': 1909, 'bp': 0.143006809848088}],\n",
       " 465: [{'bu': 281, 'bt': 1909, 'bp': 0.14719748559455212}],\n",
       " 480: [{'bu': 246, 'bt': 1909, 'bp': 0.1288632792037716}],\n",
       " 495: [{'bu': 230, 'bt': 1909, 'bp': 0.12048192771084335}],\n",
       " 510: [{'bu': 241, 'bt': 1909, 'bp': 0.12624410686223153}],\n",
       " 525: [{'bu': 235, 'bt': 1909, 'bp': 0.12310110005238345}],\n",
       " 540: [{'bu': 157, 'bt': 1909, 'bp': 0.0822420115243583}],\n",
       " 555: [{'bu': 145, 'bt': 1909, 'bp': 0.07595599790466212}],\n",
       " 570: [{'bu': 134, 'bt': 1909, 'bp': 0.07019381875327396}],\n",
       " 585: [{'bu': 146, 'bt': 1909, 'bp': 0.07647983237297014}],\n",
       " 600: [{'bu': 130, 'bt': 1909, 'bp': 0.0680984808800419}],\n",
       " 615: [{'bu': 124, 'bt': 1909, 'bp': 0.06495547407019382}],\n",
       " 630: [{'bu': 109, 'bt': 1909, 'bp': 0.0570979570455736}],\n",
       " 645: [{'bu': 120, 'bt': 1909, 'bp': 0.06286013619696176}],\n",
       " 660: [{'bu': 112, 'bt': 1909, 'bp': 0.05866946045049765}],\n",
       " 675: [{'bu': 117, 'bt': 1909, 'bp': 0.06128863279203772}],\n",
       " 690: [{'bu': 132, 'bt': 1909, 'bp': 0.06914614981665794}],\n",
       " 705: [{'bu': 134, 'bt': 1909, 'bp': 0.07019381875327396}],\n",
       " 720: [{'bu': 162, 'bt': 1909, 'bp': 0.08486118386589837}],\n",
       " 735: [{'bu': 159, 'bt': 1909, 'bp': 0.08328968046097432}],\n",
       " 750: [{'bu': 146, 'bt': 1909, 'bp': 0.07647983237297014}],\n",
       " 765: [{'bu': 167, 'bt': 1909, 'bp': 0.08748035620743845}],\n",
       " 780: [{'bu': 146, 'bt': 1909, 'bp': 0.07647983237297014}],\n",
       " 795: [{'bu': 159, 'bt': 1909, 'bp': 0.08328968046097432}],\n",
       " 810: [{'bu': 170, 'bt': 1909, 'bp': 0.08905185961236249}],\n",
       " 825: [{'bu': 175, 'bt': 1909, 'bp': 0.09167103195390257}],\n",
       " 840: [{'bu': 192, 'bt': 1909, 'bp': 0.10057621791513882}],\n",
       " 855: [{'bu': 176, 'bt': 1909, 'bp': 0.0921948664222106}],\n",
       " 870: [{'bu': 216, 'bt': 1909, 'bp': 0.11314824515453115}],\n",
       " 885: [{'bu': 249, 'bt': 1909, 'bp': 0.13043478260869565}],\n",
       " 900: [{'bu': 319, 'bt': 1909, 'bp': 0.16710319539025667}],\n",
       " 915: [{'bu': 339, 'bt': 1909, 'bp': 0.17757988475641698}],\n",
       " 930: [{'bu': 351, 'bt': 1909, 'bp': 0.18386589837611314}],\n",
       " 945: [{'bu': 334, 'bt': 1909, 'bp': 0.1749607124148769}],\n",
       " 960: [{'bu': 442, 'bt': 1909, 'bp': 0.2315348349921425}],\n",
       " 975: [{'bu': 508, 'bt': 1909, 'bp': 0.26610790990047145}],\n",
       " 990: [{'bu': 496, 'bt': 1909, 'bp': 0.2598218962807753}],\n",
       " 1005: [{'bu': 434, 'bt': 1909, 'bp': 0.2273441592456784}],\n",
       " 1020: [{'bu': 436, 'bt': 1909, 'bp': 0.2283918281822944}],\n",
       " 1035: [{'bu': 457, 'bt': 1909, 'bp': 0.2393923520167627}],\n",
       " 1050: [{'bu': 434, 'bt': 1909, 'bp': 0.2273441592456784}],\n",
       " 1065: [{'bu': 419, 'bt': 1909, 'bp': 0.21948664222105813}],\n",
       " 1080: [{'bu': 407, 'bt': 1909, 'bp': 0.213200628601362}],\n",
       " 1095: [{'bu': 377, 'bt': 1909, 'bp': 0.19748559455212156}],\n",
       " 1110: [{'bu': 391, 'bt': 1909, 'bp': 0.20481927710843373}],\n",
       " 1125: [{'bu': 396, 'bt': 1909, 'bp': 0.2074384494499738}],\n",
       " 1140: [{'bu': 382, 'bt': 1909, 'bp': 0.2001047668936616}],\n",
       " 1155: [{'bu': 369, 'bt': 1909, 'bp': 0.1932949188056574}],\n",
       " 1170: [{'bu': 379, 'bt': 1909, 'bp': 0.1985332634887376}],\n",
       " 1185: [{'bu': 395, 'bt': 1909, 'bp': 0.2069146149816658}],\n",
       " 1200: [{'bu': 364, 'bt': 1909, 'bp': 0.19067574646411733}],\n",
       " 1215: [{'bu': 331, 'bt': 1909, 'bp': 0.17338920900995286}],\n",
       " 1230: [{'bu': 339, 'bt': 1909, 'bp': 0.17757988475641698}],\n",
       " 1245: [{'bu': 283, 'bt': 1909, 'bp': 0.14824515453116816}],\n",
       " 1260: [{'bu': 269, 'bt': 1909, 'bp': 0.14091147197485596}],\n",
       " 1275: [{'bu': 247, 'bt': 1909, 'bp': 0.1293871136720796}],\n",
       " 1290: [{'bu': 218, 'bt': 1909, 'bp': 0.1141959140911472}],\n",
       " 1305: [{'bu': 202, 'bt': 1909, 'bp': 0.10581456259821896}],\n",
       " 1320: [{'bu': 197, 'bt': 1909, 'bp': 0.10319539025667887}],\n",
       " 1335: [{'bu': 169, 'bt': 1909, 'bp': 0.08852802514405447}],\n",
       " 1350: [{'bu': 182, 'bt': 1909, 'bp': 0.09533787323205868}],\n",
       " 1365: [{'bu': 152, 'bt': 1909, 'bp': 0.07962283918281822}],\n",
       " 1380: [{'bu': 123, 'bt': 1909, 'bp': 0.0644316396018858}],\n",
       " 1395: [{'bu': 100, 'bt': 1909, 'bp': 0.05238344683080147}],\n",
       " 1410: [{'bu': 99, 'bt': 1909, 'bp': 0.05185961236249345}],\n",
       " 1425: [{'bu': 52, 'bt': 1909, 'bp': 0.027239392352016764}]}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df[df.day==31])[[\"minute_in_day\", \"bu\", \"bt\", \"bp\"]].groupby('minute_in_day').apply(lambda g: g[[\"bu\", \"bt\", \"bp\"]].to_dict(orient='records')).to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loop for each day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "month_dict = {}\n",
    "\n",
    "for day in range(1, days_in_month+1):\n",
    "    dict_for_current_day = (df[df.day==day])[[\"minute_in_day\", \"bu\", \"bt\", \"bp\"]].groupby('minute_in_day').apply(lambda g: g[[\"bu\", \"bt\", \"bp\"]].to_dict(orient='records')).to_dict()\n",
    "    \n",
    "    month_dict[day] = dict_for_current_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save as json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "with open('07bikes.json', 'w') as fp:\n",
    "    json.dump(month_dict, fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}