6-structure_en.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial, you will learn how to store and process structured data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to deal with a complicate data processing tasks?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Never solve problems manually"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The people do data have one principle:\n",
    "\n",
    "<div class=\"alert alert-info\"><p>   Unless someone put the knife on your neck, don't do any  match, copy, paste and other mechanical process for each piece of data manually\n",
    "    </p></div>\n",
    "   \n",
    "    \n",
    "When we encounter any complicate problems, we will try our best to change them into programming problems that can be solved"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How should data processing tasks be decomposed?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When encountering a data processing task, the first and the most important thing we should consider is:\n",
    "\n",
    "    What are the inputs and outputs of this data processing task?\n",
    "   \n",
    "More specifically, what kind of data do we have? How is the data stored? What results are we going to get? What is the form of the resulting data?\n",
    "<img src=\"resource/6/2_en.png\"  style=\"width:800px\">  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After understanding the input and output, the next step is to divide the task into several small tasks:\n",
    "<img src=\"resource/6/3_en.png\"  style=\"width:800px\">  \n",
    "The output of each small task is the input of the next task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Store data as a data table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In data processing task, the most commonly used way is to store data in the form of data table, that is structured data.\n",
    "\n",
    "The data stored in the form of tables can be easily processed by SQL or Python pandas and other tools with high processing efficiency.\n",
    "\n",
    "Moreover, the advantage of data tables is that each column can be added with one dimension of information.\n",
    "\n",
    "In other words, if there are multiple tables of similar structure, you can concat the tables into one table, and add a column indicating which table the data comes from"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data processing task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-08T12:13:18.391971Z",
     "start_time": "2019-09-08T12:13:18.387370Z"
    }
   },
   "source": [
    "For example:\n",
    "\n",
    "Suppose there is a batch of subway travel data, the data form is as follows:\n",
    "\n",
    "<img src=\"resource/6/1.jpg\" style=\"width:800px\">\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "In this, each row of data represents a passenger's trip, and each number represents a different subway station. A trip will pass through a series of subway stations\n",
    "\n",
    "From this Metro travel data, our data processing task is:\n",
    "\n",
    ">Extract the transfer volume of each metro transfer station\n",
    "\n",
    "That is to say, if metro station 1 is connected to line 1, line 2 and line 3, we want to get the flow of interchange between these three lines through station 1.\n",
    "\n",
    "If we want to get all transfer volume of all transfer stations in the city, how to calculate?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A possible solution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Input\n",
    "The input we already know is the data form above\n",
    "\n",
    "However, the input data is not a very perfect structured data, because the length of each row is different, and a row contains information of multiple trajectory points.\n",
    "\n",
    "The input we expect is the table data with perfect structured, like this:\n",
    "\n",
    "<img src=\"resource/6/5_en.png\" style=\"width:300px\">\n",
    "\n",
    "The advantage of data table is that we can add a column to store the information with a new dimension.\n",
    "\n",
    "We don't need to save multiple tables for each person's travel trajectory. Instead, we add a column as the trip number to distinguish different trips\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Output\n",
    "What is the output? Very simple, the transfer volume in all directions of all transfer stations in the city\n",
    "\n",
    "So, in order to contain this information, how should we design the output data table?\n",
    "\n",
    "The information to be included is, at which station, from which line, transfer to which line, and how many people\n",
    "\n",
    "<img src=\"resource/6/4_en.png\"  style=\"width:300px\">  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decompose the task\n",
    "\n",
    "After understanding the input and output, the task can be decomposed into the following four tasks:\n",
    "\n",
    ">Task 1: reorganize the original input into perfect structured data to get Table 1  \n",
    "Task 2: Generate Table 2 from Table 1, and arrange them in order, to see which metro lines each trip has traveled through  \n",
    "Task 3: Generate Table 3 from Table 2, collect the transferring information, to see each trip is transferring from which line to which line at which station.   \n",
    "Task 4: Generate Table 4 from Table 3, aggregate\n",
    "\n",
    "<img src=\"resource/6/6_en.png\"  style=\"width:1000px\">  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Home work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-03-02T10:29:41.650178Z",
     "start_time": "2020-03-02T10:29:41.643196Z"
    }
   },
   "source": [
    "## Dynamic repeatability of buslines "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a bus line, suppose that there are $n$ bus stations,then there will be $C^2_n=\\frac{n(n-1)}{2}$ station pairs. \n",
    "If most of the station pairs of this bus line are the same as the station pairs of the other lines, then it is a huge waste of the public tansit resource, since its service can be replace by the other bus line. Here, we define the **Dynamic repeatability of buslines** as:\n",
    "$$D_r=\\frac{\\sum_{i<j}{r_{ij}}}{\\frac{n(n-1)}{2}}$$  \n",
    "where $r_{ij}= 1$ if there are other buslines connecting station $i$ and $j$;$r_{ij}= 0$ if there is no another busline connecting station $i$ and $j$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-08T15:43:58.260726Z",
     "start_time": "2019-09-08T15:43:58.183013Z"
    }
   },
   "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>linename</th>\n",
       "      <th>stationname</th>\n",
       "      <th>stationgeo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>650路(锦程文丰公交场站-凤凰山脚)</td>\n",
       "      <td>['锦程文丰公交场站', '明士达公司', '鸿桥工业园西', '鸿桥工业园', '三洋部件...</td>\n",
       "      <td>['113.78947398104066,22.728276040706554', '113...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>650路(凤凰山脚-锦程文丰公交场站)</td>\n",
       "      <td>['凤凰山脚', '凤凰第二工业区', '凤凰台湾街', '凤凰社区', '凤凰广场', '...</td>\n",
       "      <td>['113.8559999718322,22.68862002580157', '113.8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>m502a线(龙西公交总站-龙西公交总站)</td>\n",
       "      <td>['龙西公交总站', '添利工业园', '瓦窑坑', '五联社区', '崇和学校', '美信...</td>\n",
       "      <td>['114.25368997182035,22.759529012563924', '114...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>高快巴士39路(华富路②-坂田风门坳总站)</td>\n",
       "      <td>['华富路②', '宏杨学校', '坂田地铁站', '扬马市场', '金洲嘉丽园', '坂田...</td>\n",
       "      <td>['114.08788496287927,22.548925018156375', '114...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>高快巴士39路(坂田风门坳总站-华富路②)</td>\n",
       "      <td>['坂田风门坳总站', '岗头市场', '华为基地', '华为单身公寓北', '万科城', ...</td>\n",
       "      <td>['114.0748459685757,22.675367010065482', '114....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                linename                                        stationname  \\\n",
       "0    650路(锦程文丰公交场站-凤凰山脚)  ['锦程文丰公交场站', '明士达公司', '鸿桥工业园西', '鸿桥工业园', '三洋部件...   \n",
       "1    650路(凤凰山脚-锦程文丰公交场站)  ['凤凰山脚', '凤凰第二工业区', '凤凰台湾街', '凤凰社区', '凤凰广场', '...   \n",
       "2  m502a线(龙西公交总站-龙西公交总站)  ['龙西公交总站', '添利工业园', '瓦窑坑', '五联社区', '崇和学校', '美信...   \n",
       "3  高快巴士39路(华富路②-坂田风门坳总站)  ['华富路②', '宏杨学校', '坂田地铁站', '扬马市场', '金洲嘉丽园', '坂田...   \n",
       "4  高快巴士39路(坂田风门坳总站-华富路②)  ['坂田风门坳总站', '岗头市场', '华为基地', '华为单身公寓北', '万科城', ...   \n",
       "\n",
       "                                          stationgeo  \n",
       "0  ['113.78947398104066,22.728276040706554', '113...  \n",
       "1  ['113.8559999718322,22.68862002580157', '113.8...  \n",
       "2  ['114.25368997182035,22.759529012563924', '114...  \n",
       "3  ['114.08788496287927,22.548925018156375', '114...  \n",
       "4  ['114.0748459685757,22.675367010065482', '114....  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read data\n",
    "import pandas as pd\n",
    "f = open(r'data-sample/busline.csv')\n",
    "busline = pd.read_csv(f)\n",
    "f.close()\n",
    "busline.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-08T15:44:54.038437Z",
     "start_time": "2019-09-08T15:44:54.033903Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2113"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(busline)"
   ]
  }
 ],
 "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.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "204.109px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}