Load_txt_files_V2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PeMS数据为美国加州政府的公开数据，单个文件中包含的是一千余个监测站点的单日数据（24小时*每小时12条数据）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''导入包'''\n",
    "import pandas as pd\n",
    "import numpy as np \n",
    "import csv \n",
    "import os "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.单个txt文件解析测试"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(349344, 52)\n"
     ]
    }
   ],
   "source": [
    "'''读入txt文件，并将长字符串拆分'''\n",
    "txtfile = list()\n",
    "with open('data_test/d03_text_station_5min_2020_01_01.txt','r') as f:\n",
    "    res = f.readlines()\n",
    "    for one_line in res:\n",
    "        # 可以使用[:-1]来去除行末的换行符，并按逗号拆分\n",
    "        txtfile.append(one_line[:-1].split(','))\n",
    "txtfile = np.array(txtfile)\n",
    "print(txtfile.shape)"
   ]
  },
  {
   "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>time</th>\n",
       "      <th>station</th>\n",
       "      <th>Total_Flow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>308511</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>308512</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>311831</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>311832</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>311844</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  time station Total_Flow\n",
       "0  01/01/2020 00:00:00  308511          3\n",
       "1  01/01/2020 00:00:00  308512         37\n",
       "2  01/01/2020 00:00:00  311831          0\n",
       "3  01/01/2020 00:00:00  311832         10\n",
       "4  01/01/2020 00:00:00  311844          3"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''将数据导入pandas的DataFrame'''\n",
    "df = pd.DataFrame(data={'time' : txtfile[:, 0],  \n",
    "                        'station' :  txtfile[:, 1],\n",
    "                        'Total_Flow' : txtfile[:, 9]})\n",
    "# 查看数据\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>station</th>\n",
       "      <th>Total_Flow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>349344</td>\n",
       "      <td>349344</td>\n",
       "      <td>349344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>288</td>\n",
       "      <td>1213</td>\n",
       "      <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>01/01/2020 21:00:00</td>\n",
       "      <td>314060</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>1213</td>\n",
       "      <td>288</td>\n",
       "      <td>34414</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       time station Total_Flow\n",
       "count                349344  349344     349344\n",
       "unique                  288    1213        632\n",
       "top     01/01/2020 21:00:00  314060           \n",
       "freq                   1213     288      34414"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''查看数据统计信息'''\n",
    "df.describe()"
   ]
  },
  {
   "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>time</th>\n",
       "      <th>station</th>\n",
       "      <th>Total_Flow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1212</td>\n",
       "      <td>01/01/2020 00:00:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2425</td>\n",
       "      <td>01/01/2020 00:05:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3638</td>\n",
       "      <td>01/01/2020 00:10:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4851</td>\n",
       "      <td>01/01/2020 00:15:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6064</td>\n",
       "      <td>01/01/2020 00:20:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7277</td>\n",
       "      <td>01/01/2020 00:25:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8490</td>\n",
       "      <td>01/01/2020 00:30:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9703</td>\n",
       "      <td>01/01/2020 00:35:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10916</td>\n",
       "      <td>01/01/2020 00:40:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12129</td>\n",
       "      <td>01/01/2020 00:45:00</td>\n",
       "      <td>3013051</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      time  station Total_Flow\n",
       "1212   01/01/2020 00:00:00  3013051          0\n",
       "2425   01/01/2020 00:05:00  3013051          2\n",
       "3638   01/01/2020 00:10:00  3013051         13\n",
       "4851   01/01/2020 00:15:00  3013051          9\n",
       "6064   01/01/2020 00:20:00  3013051         11\n",
       "7277   01/01/2020 00:25:00  3013051         11\n",
       "8490   01/01/2020 00:30:00  3013051         19\n",
       "9703   01/01/2020 00:35:00  3013051         11\n",
       "10916  01/01/2020 00:40:00  3013051         10\n",
       "12129  01/01/2020 00:45:00  3013051         24"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''将数据表按站点、时间进行排序'''\n",
    "# 所得内容为：第一个站点0-24点车流信息、第二个站点0-24点车流信息。。。以此类推，共计1213个站点。\n",
    "sort_df = df.sort_values(by = ['station', 'time'])\n",
    "sort_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(349344,)\n"
     ]
    }
   ],
   "source": [
    "'''保留唯一有用的车流量列，将空值替换为0，并存为整形'''\n",
    "df_array=sort_df.values\n",
    "data = list()\n",
    "for i in df_array:\n",
    "    Total_Flow = int(i[2]) if len(i[2]) != 0 else 0\n",
    "    data.append(Total_Flow)\n",
    "data = np.array(data)\n",
    "print(data.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.批量数据处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "单日数据处理的中间结果中，已经形成了以站点为第一排序对象，以时间为第二排序对象的数据表，多日数据处理应在此基础上，按天的顺序将各个站点的数据进行拼接。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''将txt中的数据读取出来存到数组里'''\n",
    "def txt2array(filedir,station):\n",
    "    txtfile = list()\n",
    "    with open(filedir, 'r') as f:\n",
    "        res = f.readlines()\n",
    "        for one_line in res:\n",
    "            # 可以使用[:-1]来去除行末的换行符，并按逗号拆分\n",
    "            txtfile.append(one_line[:-1].split(','))\n",
    "    txtfile = np.array(txtfile)\n",
    "    return txtfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''对数组中原始数据进行筛选、排序，以列表形式返回'''\n",
    "# 原始数据中既有‘’的空字符串，又有Nan的空值，都需要替换为0\n",
    "def array2list(array, station):\n",
    "    # 转入pandas中方便进行排序、筛选操作\n",
    "    df = pd.DataFrame(data={'time' : array[:, 0],  'station' :  array[:, 1],  'Total_Flow' : array[:, 9]})\n",
    "    \n",
    "    # 查找指定站点的数据\n",
    "    this_station = df[df['station'].isin([str(station)])]\n",
    "    # 将Nan填充为0\n",
    "    this_station = this_station.fillna(value=0)\n",
    "    # 因为原始数据总体是按时间排序的，所以这里其实不用再排一次了\n",
    "    # new_df = this_station.sort_values(by = 'time')\n",
    "    \n",
    "    this_station_array = this_station.values\n",
    "    \n",
    "    # 将‘’填充为0\n",
    "    data = list()\n",
    "    for i in this_station_array:\n",
    "        time = i[0]\n",
    "        station = i[1]\n",
    "        dasum = i[2] if len(i[2]) != 0 else 0\n",
    "        data.append([time, station, dasum])\n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''main函数，输入为数据文件夹路径及所要的站点'''\n",
    "def main(data_dir,station):\n",
    "    Station = list()\n",
    "    # 找到txt文件路径\n",
    "    data_files_dir_list = os.listdir(data_dir)\n",
    "    # 遍历txt文件\n",
    "    for one_dir in data_files_dir_list:\n",
    "        print('开始处理：', one_dir)\n",
    "        filedir = os.path.join(data_dir, one_dir)\n",
    "        \n",
    "        # 实际处理部分\n",
    "        txtfile_array = txt2array(filedir,station)\n",
    "        this_station_list = array2list(txtfile_array,station)\n",
    "        Station = Station + this_station_list\n",
    "        \n",
    "    Station = np.array(Station)\n",
    "    print('共计：', Station.shape[0], '条数据')\n",
    "    \n",
    "    # 将处理好的数据保存至CSV文件\n",
    "    save_data_dir = data_dir + '/' + str(station) + '.csv'\n",
    "    with open(save_data_dir, 'w', newline='') as csvfile: \n",
    "        writer = csv.writer(csvfile)\n",
    "        writer.writerow(['time', 'station', 'Total_Flow'])\n",
    "        for i in Station:\n",
    "            writer.writerow(i)\n",
    "    print('ALL Done！')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "开始处理： d03_text_station_5min_2020_01_01.txt\n",
      "开始处理： d03_text_station_5min_2020_01_02.txt\n",
      "开始处理： d03_text_station_5min_2020_01_03.txt\n",
      "开始处理： d03_text_station_5min_2020_01_04.txt\n",
      "开始处理： d03_text_station_5min_2020_01_05.txt\n",
      "开始处理： d03_text_station_5min_2020_01_06.txt\n",
      "开始处理： d03_text_station_5min_2020_01_07.txt\n",
      "开始处理： d03_text_station_5min_2020_01_08.txt\n",
      "开始处理： d03_text_station_5min_2020_01_09.txt\n",
      "开始处理： d03_text_station_5min_2020_01_10.txt\n",
      "开始处理： d03_text_station_5min_2020_01_11.txt\n",
      "开始处理： d03_text_station_5min_2020_01_12.txt\n",
      "开始处理： d03_text_station_5min_2020_01_13.txt\n",
      "开始处理： d03_text_station_5min_2020_01_14.txt\n",
      "开始处理： d03_text_station_5min_2020_01_15.txt\n",
      "开始处理： d03_text_station_5min_2020_01_16.txt\n",
      "开始处理： d03_text_station_5min_2020_01_17.txt\n",
      "开始处理： d03_text_station_5min_2020_01_18.txt\n",
      "开始处理： d03_text_station_5min_2020_01_19.txt\n",
      "开始处理： d03_text_station_5min_2020_01_20.txt\n",
      "开始处理： d03_text_station_5min_2020_01_21.txt\n",
      "开始处理： d03_text_station_5min_2020_01_22.txt\n",
      "开始处理： d03_text_station_5min_2020_01_23.txt\n",
      "开始处理： d03_text_station_5min_2020_01_24.txt\n",
      "开始处理： d03_text_station_5min_2020_01_25.txt\n",
      "开始处理： d03_text_station_5min_2020_01_26.txt\n",
      "开始处理： d03_text_station_5min_2020_01_27.txt\n",
      "开始处理： d03_text_station_5min_2020_01_28.txt\n",
      "开始处理： d03_text_station_5min_2020_01_29.txt\n",
      "开始处理： d03_text_station_5min_2020_01_30.txt\n",
      "开始处理： d03_text_station_5min_2020_01_31.txt\n",
      "共计： 8928 条数据\n",
      "ALL Done！\n"
     ]
    }
   ],
   "source": [
    "data_dir = 'data_test'\n",
    "main(data_dir, 3013051)"
   ]
  },
  {
   "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": 2
}