3.1.read_write_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"./images/shouke_logo.png\"\n",
    "     style=\"float: right\"\n",
    "     width=100\n",
    "     style=\"padding-bottom:30px;\"/>\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "<table style=\"float:center;\">\n",
    "    <tr>\n",
    "        <td>\n",
    "            <img src='./images/python-logo.png'width=130>\n",
    "        </td>\n",
    "        <td>\n",
    "            <img src='./images/pandas-logo.png'width=150>\n",
    "        </td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "<h1 style='text-align: center;'>Reading Data and Saving DataFrame</h1>\n",
    "<h2 style='text-align: center;'>Shouke Wei, Ph.D. Professor</h2>\n",
    "<h3 style='text-align: center;'>Email: shouke.wei@gmail.com</h3>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Objective\n",
    "- how to read data into Pandas Dataframe\n",
    "- how to save Pandas DataFrame as a .CSV file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Import required packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pandas Version: 1.3.4\n"
     ]
    }
   ],
   "source": [
    "print(f'Pandas Version: {pd.__version__}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Read data into DataFrame\n",
    "### (1) Read local .CSV file "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./data/gdp_china.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": false
   },
   "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>Province</th>\n",
       "      <th>GDP ranking</th>\n",
       "      <th>Year</th>\n",
       "      <th>GDP (x10^8CNY)</th>\n",
       "      <th>population (x10^4 person)</th>\n",
       "      <th>fixed assets investment (x10^8 CNY)</th>\n",
       "      <th>total imports and exports (CNY)</th>\n",
       "      <th>fiscal expenditure （x10^9CNY)</th>\n",
       "      <th>urban disposal income per capita (CNY）</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>1</td>\n",
       "      <td>2000</td>\n",
       "      <td>10741.25</td>\n",
       "      <td>8650</td>\n",
       "      <td>3145.13</td>\n",
       "      <td>140814657</td>\n",
       "      <td>1080.32</td>\n",
       "      <td>9761.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>1</td>\n",
       "      <td>2001</td>\n",
       "      <td>12039.25</td>\n",
       "      <td>8733</td>\n",
       "      <td>3484.43</td>\n",
       "      <td>146097010</td>\n",
       "      <td>1321.33</td>\n",
       "      <td>10415.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>1</td>\n",
       "      <td>2002</td>\n",
       "      <td>13502.42</td>\n",
       "      <td>8842</td>\n",
       "      <td>3850.78</td>\n",
       "      <td>183016893</td>\n",
       "      <td>1521.08</td>\n",
       "      <td>11137.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>1</td>\n",
       "      <td>2003</td>\n",
       "      <td>15844.64</td>\n",
       "      <td>8963</td>\n",
       "      <td>4813.20</td>\n",
       "      <td>234673452</td>\n",
       "      <td>1695.63</td>\n",
       "      <td>12380.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>1</td>\n",
       "      <td>2004</td>\n",
       "      <td>18864.62</td>\n",
       "      <td>9111</td>\n",
       "      <td>5870.02</td>\n",
       "      <td>295589873</td>\n",
       "      <td>1852.95</td>\n",
       "      <td>13627.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Province  GDP ranking   Year    GDP (x10^8CNY)  population (x10^4 person)  \\\n",
       "0  Guangdong             1    2000        10741.25                       8650   \n",
       "1  Guangdong             1    2001        12039.25                       8733   \n",
       "2  Guangdong             1    2002        13502.42                       8842   \n",
       "3  Guangdong             1    2003        15844.64                       8963   \n",
       "4  Guangdong             1    2004        18864.62                       9111   \n",
       "\n",
       "   fixed assets investment (x10^8 CNY)  total imports and exports (CNY)  \\\n",
       "0                              3145.13                        140814657   \n",
       "1                              3484.43                        146097010   \n",
       "2                              3850.78                        183016893   \n",
       "3                              4813.20                        234673452   \n",
       "4                              5870.02                        295589873   \n",
       "\n",
       "   fiscal expenditure （x10^9CNY)  urban disposal income per capita (CNY）  \n",
       "0                        1080.32                                 9761.57  \n",
       "1                        1321.33                                10415.19  \n",
       "2                        1521.08                                11137.20  \n",
       "3                        1695.63                                12380.43  \n",
       "4                        1852.95                                13627.65  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (2) Read online data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "url ='https://raw.githubusercontent.com/WEIAI-TECH/python-data-analysis-modelling_beginner/main/data/gdp_china_clean.csv'\n",
    "df2 = pd.read_csv(url)"
   ]
  },
  {
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Table: Social Economic Development Data of First 5 Big Provinces by GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <th>Province</th>\n",
       "      <th>GDP ranking</th>\n",
       "      <th>Year</th>\n",
       "      <th>GDP (x10^12CNY)</th>\n",
       "      <th>population (x10^7 person)</th>\n",
       "      <th>fixed assets investment (x10^12 CNY)</th>\n",
       "      <th>total imports and exports (x10^8CNY)</th>\n",
       "      <th>fiscal expenditure(x10^12CNY)</th>\n",
       "      <td>urban disposal income per capita (x10^4CNY)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <th>Guangdong</th>\n",
       "      <th>First</th>\n",
       "      <th>2000</th>\n",
       "      <th>1.074125</th>\n",
       "      <th>8.65</th>\n",
       "      <th>0.314513</th>\n",
       "      <th>1.40814657</th>\n",
       "      <th>0.108032</th>\n",
       "      <td>0.976157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>Guangdong</th>\n",
       "      <th>First</th>\n",
       "      <th>2001</th>\n",
       "      <th>1.203925</th>\n",
       "      <th>8.733</th>\n",
       "      <th>0.348443</th>\n",
       "      <th>14.609701</th>\n",
       "      <th>0.132133</th>\n",
       "      <td>1.041519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>Guangdong</th>\n",
       "      <th>First</th>\n",
       "      <th>2002</th>\n",
       "      <th>1.350242</th>\n",
       "      <th>8.842</th>\n",
       "      <th>0.385078</th>\n",
       "      <th>1.83016893</th>\n",
       "      <th>0.152108</th>\n",
       "      <td>1.11372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>Guangdong</th>\n",
       "      <th>First</th>\n",
       "      <th>2003</th>\n",
       "      <th>1.584464</th>\n",
       "      <th>8.963</th>\n",
       "      <th>0.48132</th>\n",
       "      <th>2.34673452</th>\n",
       "      <th>0.169563</th>\n",
       "      <td>1.238043</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                                                                                    Table: Social Economic Development Data of First 5 Big Provinces by GDP\n",
       "NaN Province  GDP ranking  Year   GDP (x10^12CNY) population (x10^7 person) fixed assets investment (x10^12 CNY) total imports and exports (x10^8CNY) fiscal expenditure(x10^12CNY)        urban disposal income per capita (x10^4CNY)                     \n",
       "0   Guangdong First        2000   1.074125        8.65                      0.314513                             1.40814657                           0.108032                                                                0.976157                     \n",
       "1   Guangdong First        2001   1.203925        8.733                     0.348443                             14.609701                            0.132133                                                                1.041519                     \n",
       "2   Guangdong First        2002   1.350242        8.842                     0.385078                             1.83016893                           0.152108                                                                 1.11372                     \n",
       "3   Guangdong First        2003   1.584464        8.963                     0.48132                              2.34673452                           0.169563                                                                1.238043                     "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Table: Social Economic Development Data of First 5 Big Provinces by GDP \\t\\t\\t\\t\\t\\t</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Gongdong  Statistical Yearbooks (2001</th>\n",
       "      <th>2002-2019)\\t\\t\\t\\t\\t</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jiangsu  Statistical Yearbooks (2001</th>\n",
       "      <th>2002-2019)\\t\\t\\t\\t\\t</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shangdong Statistical Yearbooks (2001</th>\n",
       "      <th>2002-2019)\\t\\t\\t\\t\\t</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zhejiang Statistical Yearbooks (2001</th>\n",
       "      <th>2002-2019)\\t\\t\\t\\t\\t</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Henan  Statistical Yearbooks (2001</th>\n",
       "      <th>2002-2019)\\t\\t\\t\\t\\t</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                    Table: Social Economic Development Data of First 5 Big Provinces by GDP \\t\\t\\t\\t\\t\\t\n",
       "Gongdong  Statistical Yearbooks (2001  2002-2019)\\t\\t\\t\\t\\t NaN NaN NaN NaN NaN NaN                                                NaN                                  \n",
       "Jiangsu  Statistical Yearbooks (2001   2002-2019)\\t\\t\\t\\t\\t NaN NaN NaN NaN NaN NaN                                                NaN                                  \n",
       "Shangdong Statistical Yearbooks (2001  2002-2019)\\t\\t\\t\\t\\t NaN NaN NaN NaN NaN NaN                                                NaN                                  \n",
       "Zhejiang Statistical Yearbooks (2001   2002-2019)\\t\\t\\t\\t\\t NaN NaN NaN NaN NaN NaN                                                NaN                                  \n",
       "Henan  Statistical Yearbooks (2001     2002-2019)\\t\\t\\t\\t\\t NaN NaN NaN NaN NaN NaN                                                NaN                                  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a number of pandas commands to read and write other data formats, such as:\n",
    "```python\n",
    "pd.read_excel('filename.xlsx',sheet_name='Sheet1', index_col=None, na_values=['NA'])\n",
    "pd.read_stata('filename.dta')\n",
    "pd.read_sas('filename.sas7bdat')\n",
    "pd.read_hdf('filename.h5','df')\n",
    "\n",
    "...\n",
    "```\n",
    "Note: The above command has many optional arguments to fine-tune the data import process.\n",
    "\n",
    "More information can be refers to https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (3) Skip rows from top"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(url,skiprows = 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Province</th>\n",
       "      <th>GDP ranking</th>\n",
       "      <th>Year</th>\n",
       "      <th>GDP (x10^12CNY)</th>\n",
       "      <th>population (x10^7 person)</th>\n",
       "      <th>fixed assets investment (x10^12 CNY)</th>\n",
       "      <th>total imports and exports (x10^8CNY)</th>\n",
       "      <th>fiscal expenditure(x10^12CNY)</th>\n",
       "      <th>urban disposal income per capita (x10^4CNY)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>First</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>1.074125</td>\n",
       "      <td>8.650</td>\n",
       "      <td>0.314513</td>\n",
       "      <td>1.408147</td>\n",
       "      <td>0.108032</td>\n",
       "      <td>0.976157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>First</td>\n",
       "      <td>2001.0</td>\n",
       "      <td>1.203925</td>\n",
       "      <td>8.733</td>\n",
       "      <td>0.348443</td>\n",
       "      <td>14.609701</td>\n",
       "      <td>0.132133</td>\n",
       "      <td>1.041519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>First</td>\n",
       "      <td>2002.0</td>\n",
       "      <td>1.350242</td>\n",
       "      <td>8.842</td>\n",
       "      <td>0.385078</td>\n",
       "      <td>1.830169</td>\n",
       "      <td>0.152108</td>\n",
       "      <td>1.113720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>First</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>1.584464</td>\n",
       "      <td>8.963</td>\n",
       "      <td>0.481320</td>\n",
       "      <td>2.346735</td>\n",
       "      <td>0.169563</td>\n",
       "      <td>1.238043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Guangdong</td>\n",
       "      <td>First</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>1.886462</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.587002</td>\n",
       "      <td>2.955899</td>\n",
       "      <td>0.185295</td>\n",
       "      <td>1.362765</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Province GDP ranking   Year    GDP (x10^12CNY)  population (x10^7 person)  \\\n",
       "0  Guangdong        First  2000.0         1.074125                      8.650   \n",
       "1  Guangdong        First  2001.0         1.203925                      8.733   \n",
       "2  Guangdong        First  2002.0         1.350242                      8.842   \n",
       "3  Guangdong        First  2003.0         1.584464                      8.963   \n",
       "4  Guangdong        First  2004.0         1.886462                        NaN   \n",
       "\n",
       "   fixed assets investment (x10^12 CNY)  total imports and exports (x10^8CNY)  \\\n",
       "0                              0.314513                              1.408147   \n",
       "1                              0.348443                             14.609701   \n",
       "2                              0.385078                              1.830169   \n",
       "3                              0.481320                              2.346735   \n",
       "4                              0.587002                              2.955899   \n",
       "\n",
       "   fiscal expenditure(x10^12CNY)  urban disposal income per capita (x10^4CNY)  \n",
       "0                       0.108032                                     0.976157  \n",
       "1                       0.132133                                     1.041519  \n",
       "2                       0.152108                                     1.113720  \n",
       "3                       0.169563                                     1.238043  \n",
       "4                       0.185295                                     1.362765  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (4) Skip rows from bottom / footer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(url,skipfooter =7,engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "scrolled": true
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Table: Social Economic Development Data of First 5 Big Provinces by GDP \\t\\t\\t\\t\\t\\t</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Henan</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Fifth</th>\n",
       "      <th>2014</th>\n",
       "      <th>3.493824</th>\n",
       "      <th>9.436</th>\n",
       "      <th>3.078217</th>\n",
       "      <th>0.39911129</th>\n",
       "      <th>0.602869</th>\n",
       "      <td>2.367206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <th>3.700216</th>\n",
       "      <th>9.48</th>\n",
       "      <th>3.566035</th>\n",
       "      <th>0.45953484</th>\n",
       "      <th>0.679935</th>\n",
       "      <td>2.557561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <th>4.047179</th>\n",
       "      <th>9.532</th>\n",
       "      <th>4.041509</th>\n",
       "      <th>0.47138464</th>\n",
       "      <th>0.745374</th>\n",
       "      <td>2.723292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <th>4.455283</th>\n",
       "      <th>9.392</th>\n",
       "      <th>4.44969</th>\n",
       "      <th>0.00052339</th>\n",
       "      <th>0.821552</th>\n",
       "      <td>2.95579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <th>4.805586</th>\n",
       "      <th>9.36</th>\n",
       "      <th>4.45096</th>\n",
       "      <th>0.55117</th>\n",
       "      <th>0.921773</th>\n",
       "      <td>3.18742</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             Table: Social Economic Development Data of First 5 Big Provinces by GDP \\t\\t\\t\\t\\t\\t\n",
       "Henan Fifth 2014 3.493824 9.436 3.078217 0.39911129 0.602869                                           2.367206                                  \n",
       "            2015 3.700216 9.48  3.566035 0.45953484 0.679935                                           2.557561                                  \n",
       "            2016 4.047179 9.532 4.041509 0.47138464 0.745374                                           2.723292                                  \n",
       "            2017 4.455283 9.392 4.44969  0.00052339 0.821552                                            2.95579                                  \n",
       "            2018 4.805586 9.36  4.45096  0.55117    0.921773                                            3.18742                                  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_csv(url,skiprows=2,skipfooter =7,engine='python')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>Province</th>\n",
       "      <th>GDP ranking</th>\n",
       "      <th>Year</th>\n",
       "      <th>GDP (x10^12CNY)</th>\n",
       "      <th>population (x10^7 person)</th>\n",
       "      <th>fixed assets investment (x10^12 CNY)</th>\n",
       "      <th>total imports and exports (x10^8CNY)</th>\n",
       "      <th>fiscal expenditure(x10^12CNY)</th>\n",
       "      <th>urban disposal income per capita (x10^4CNY)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Henan</td>\n",
       "      <td>Fifth</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.493824</td>\n",
       "      <td>9.436</td>\n",
       "      <td>3.078217</td>\n",
       "      <td>0.399111</td>\n",
       "      <td>0.602869</td>\n",
       "      <td>2.367206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Henan</td>\n",
       "      <td>Fifth</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.700216</td>\n",
       "      <td>9.480</td>\n",
       "      <td>3.566035</td>\n",
       "      <td>0.459535</td>\n",
       "      <td>0.679935</td>\n",
       "      <td>2.557561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Henan</td>\n",
       "      <td>Fifth</td>\n",
       "      <td>2016</td>\n",
       "      <td>4.047179</td>\n",
       "      <td>9.532</td>\n",
       "      <td>4.041509</td>\n",
       "      <td>0.471385</td>\n",
       "      <td>0.745374</td>\n",
       "      <td>2.723292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Henan</td>\n",
       "      <td>Fifth</td>\n",
       "      <td>2017</td>\n",
       "      <td>4.455283</td>\n",
       "      <td>9.392</td>\n",
       "      <td>4.449690</td>\n",
       "      <td>0.000523</td>\n",
       "      <td>0.821552</td>\n",
       "      <td>2.955790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Henan</td>\n",
       "      <td>Fifth</td>\n",
       "      <td>2018</td>\n",
       "      <td>4.805586</td>\n",
       "      <td>9.360</td>\n",
       "      <td>4.450960</td>\n",
       "      <td>0.551170</td>\n",
       "      <td>0.921773</td>\n",
       "      <td>3.187420</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Province GDP ranking   Year    GDP (x10^12CNY)  population (x10^7 person)  \\\n",
       "90    Henan        Fifth    2014         3.493824                      9.436   \n",
       "91    Henan        Fifth    2015         3.700216                      9.480   \n",
       "92    Henan        Fifth    2016         4.047179                      9.532   \n",
       "93    Henan        Fifth    2017         4.455283                      9.392   \n",
       "94    Henan        Fifth    2018         4.805586                      9.360   \n",
       "\n",
       "    fixed assets investment (x10^12 CNY)  \\\n",
       "90                              3.078217   \n",
       "91                              3.566035   \n",
       "92                              4.041509   \n",
       "93                              4.449690   \n",
       "94                              4.450960   \n",
       "\n",
       "    total imports and exports (x10^8CNY)  fiscal expenditure(x10^12CNY)  \\\n",
       "90                              0.399111                       0.602869   \n",
       "91                              0.459535                       0.679935   \n",
       "92                              0.471385                       0.745374   \n",
       "93                              0.000523                       0.821552   \n",
       "94                              0.551170                       0.921773   \n",
       "\n",
       "    urban disposal income per capita (x10^4CNY)  \n",
       "90                                     2.367206  \n",
       "91                                     2.557561  \n",
       "92                                     2.723292  \n",
       "93                                     2.955790  \n",
       "94                                     3.187420  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3 Save DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.to_csv('./data/gdp_china_clean.csv', index=False)"
   ]
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}