{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" point_d Year day_number hour minute second 100th_of_a_second \\\n",
"28 101 17 32 0 3 39 84 \n",
"79 101 17 32 0 10 47 86 \n",
"97 101 17 32 0 13 52 99 \n",
"124 101 17 32 0 18 53 89 \n",
"140 101 17 32 0 23 44 99 \n",
"... ... ... ... ... ... ... ... \n",
"71536 101 17 32 23 24 41 99 \n",
"71554 101 17 32 23 27 38 13 \n",
"71621 101 17 32 23 37 30 71 \n",
"71729 101 17 32 23 51 39 99 \n",
"71745 101 17 32 23 53 43 99 \n",
"\n",
" length_m lane direction vehicle_class speed_km/h \\\n",
"28 14.0 5 2 4 80 \n",
"79 13.2 2 1 4 80 \n",
"97 14.4 5 2 4 84 \n",
"124 13.8 5 2 4 76 \n",
"140 15.6 5 2 4 87 \n",
"... ... ... ... ... ... \n",
"71536 13.4 1 1 4 76 \n",
"71554 14.0 5 2 4 86 \n",
"71621 16.6 5 2 4 78 \n",
"71729 14.2 5 2 4 82 \n",
"71745 15.4 5 2 4 89 \n",
"\n",
" faulty_0=valid_1=incorrect total_time interval jonoalku \n",
"28 0 21984 4125 0 \n",
"79 0 64786 8189 0 \n",
"97 0 83299 1684 0 \n",
"124 0 113389 2774 0 \n",
"140 0 142499 3643 0 \n",
"... ... ... ... ... \n",
"71536 0 8428199 2702 0 \n",
"71554 0 8445813 4749 0 \n",
"71621 0 8505071 5957 0 \n",
"71729 0 8589999 3669 0 \n",
"71745 0 8602399 2756 0 \n",
"\n",
"[829 rows x 16 columns]\n",
" vehicle_class\n",
"hour \n",
"0 7\n",
"1 7\n",
"2 2\n",
"3 5\n",
"4 8\n",
"5 13\n",
"6 38\n",
"7 33\n",
"8 36\n",
"9 75\n",
"10 31\n",
"11 26\n",
"12 36\n",
"13 75\n",
"14 73\n",
"15 76\n",
"16 54\n",
"17 40\n",
"18 51\n",
"19 34\n",
"20 25\n",
"21 69\n",
"22 9\n",
"23 6\n"
]
},
{
"data": {
"text/plain": [
"<Figure size 640x480 with 1 Axes>"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"\n",
"import matplotlib.pyplot as plt\n",
"\n",
"year = 2020\n",
"areaID = \"01\"\n",
"lamID = 101\n",
"\n",
"\n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"names = [\"point_d\", \"Year\", \"day_number\", \"hour\", \"minute\", \"second\", \"100th_of_a_second\", \"length_m\",\"lane\", \"direction\", \"vehicle_class\", \"speed_km/h\", \"faulty_0=valid_1=incorrect\", \"total_time\", \"interval\",\"jonoalku\"]\n",
"\n",
"\n",
"df = pd.read_csv(\"https://aineistot.vayla.fi/lam/rawdata/2017/01/lamraw_101_17_32.csv\", names=names, sep = \";\")\n",
"\n",
"#print(df.head())\n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"\n",
"df1 = df[df[\"vehicle_class\"]==4]\n",
"print(df1)\n",
"\n",
"df2 = df1.groupby(\"hour\")[\"vehicle_class\"].count()\n",
"\n",
"df3= pd.DataFrame(df2)\n",
"print(df3)\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"\n",
"df3.plot()\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"hour vehicle_class\n",
"0 1 178\n",
" 2 4\n",
" 3 26\n",
" 4 3\n",
" 5 0\n",
" 6 0\n",
" 7 0\n",
"1 1 130\n",
" 2 9\n",
" 3 17\n",
" 4 7\n",
" 5 0\n",
" 6 0\n",
" 7 0\n",
"2 1 73\n",
" 2 8\n",
" 3 2\n",
" 4 0\n",
" 5 0\n",
" 6 0\n",
" 7 0\n",
"3 1 53\n",
" 2 3\n",
" 3 0\n",
" 4 4\n",
"dtype: int64\n",
"{\"[0,1]\":178,\"[0,2]\":4,\"[0,3]\":26,\"[0,4]\":3,\"[0,5]\":0,\"[0,6]\":0,\"[0,7]\":0,\"[1,1]\":130,\"[1,2]\":9,\"[1,3]\":17,\"[1,4]\":7,\"[1,5]\":0,\"[1,6]\":0,\"[1,7]\":0,\"[2,1]\":73,\"[2,2]\":8,\"[2,3]\":2,\"[2,4]\":0,\"[2,5]\":0,\"[2,6]\":0,\"[2,7]\":0,\"[3,1]\":53,\"[3,2]\":3,\"[3,3]\":0,\"[3,4]\":4,\"[3,5]\":0,\"[3,6]\":1,\"[3,7]\":0,\"[4,1]\":44,\"[4,2]\":3,\"[4,3]\":1,\"[4,4]\":0,\"[4,5]\":0,\"[4,6]\":0,\"[4,7]\":0,\"[5,1]\":118,\"[5,2]\":5,\"[5,3]\":1,\"[5,4]\":1,\"[5,5]\":2,\"[5,6]\":0,\"[5,7]\":0,\"[6,1]\":347,\"[6,2]\":8,\"[6,3]\":5,\"[6,4]\":20,\"[6,5]\":4,\"[6,6]\":2,\"[6,7]\":1,\"[7,1]\":389,\"[7,2]\":4,\"[7,3]\":8,\"[7,4]\":60,\"[7,5]\":6,\"[7,6]\":3,\"[7,7]\":1,\"[8,1]\":526,\"[8,2]\":12,\"[8,3]\":5,\"[8,4]\":44,\"[8,5]\":8,\"[8,6]\":0,\"[8,7]\":2,\"[9,1]\":978,\"[9,2]\":9,\"[9,3]\":4,\"[9,4]\":28,\"[9,5]\":5,\"[9,6]\":5,\"[9,7]\":1,\"[10,1]\":1602,\"[10,2]\":6,\"[10,3]\":4,\"[10,4]\":8,\"[10,5]\":2,\"[10,6]\":6,\"[10,7]\":2,\"[11,1]\":2039,\"[11,2]\":13,\"[11,3]\":3,\"[11,4]\":18,\"[11,5]\":5,\"[11,6]\":10,\"[11,7]\":0,\"[12,1]\":2493,\"[12,2]\":11,\"[12,3]\":5,\"[12,4]\":11,\"[12,5]\":4,\"[12,6]\":8,\"[12,7]\":2,\"[13,1]\":2589,\"[13,2]\":8,\"[13,3]\":4,\"[13,4]\":23,\"[13,5]\":7,\"[13,6]\":6,\"[13,7]\":0,\"[14,1]\":2721,\"[14,2]\":8,\"[14,3]\":7,\"[14,4]\":21,\"[14,5]\":5,\"[14,6]\":10,\"[14,7]\":0,\"[15,1]\":2680,\"[15,2]\":7,\"[15,3]\":5,\"[15,4]\":15,\"[15,5]\":2,\"[15,6]\":9,\"[15,7]\":3,\"[16,1]\":2462,\"[16,2]\":5,\"[16,3]\":3,\"[16,4]\":4,\"[16,5]\":2,\"[16,6]\":11,\"[16,7]\":0,\"[17,1]\":2259,\"[17,2]\":10,\"[17,3]\":6,\"[17,4]\":2,\"[17,5]\":1,\"[17,6]\":10,\"[17,7]\":2,\"[18,1]\":1677,\"[18,2]\":7,\"[18,3]\":6,\"[18,4]\":2,\"[18,5]\":1,\"[18,6]\":6,\"[18,7]\":0,\"[19,1]\":1177,\"[19,2]\":7,\"[19,3]\":3,\"[19,4]\":2,\"[19,5]\":1,\"[19,6]\":5,\"[19,7]\":0,\"[20,1]\":982,\"[20,2]\":6,\"[20,3]\":9,\"[20,4]\":2,\"[20,5]\":2,\"[20,6]\":3,\"[20,7]\":0,\"[21,1]\":720,\"[21,2]\":3,\"[21,3]\":6,\"[21,4]\":3,\"[21,5]\":0,\"[21,6]\":1,\"[21,7]\":0,\"[22,1]\":446,\"[22,2]\":0,\"[22,3]\":5,\"[22,4]\":4,\"[22,5]\":1,\"[22,6]\":1,\"[22,7]\":0,\"[23,1]\":338,\"[23,2]\":3,\"[23,3]\":12,\"[23,4]\":11,\"[23,5]\":1,\"[23,6]\":3,\"[23,7]\":0}\n",
"11.04.2020\n"
]
}
],
"source": [
"#%%time\n",
"\n",
"import datetime\n",
"\n",
"year = \"2020\"\n",
"areaID = \"01\"\n",
"lamID = \"101\"\n",
"shortYear = year[-2:]\n",
"startDayNumber = \"101\"\n",
"endDayNumber = \"\"\n",
"vehicle_class =[4]\n",
"\n",
"output = pd.DataFrame()\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"if(len(endDayNumber)!=0):\n",
" start = int(startDayNumber)\n",
" end = int(endDayNumber)\n",
" duration = end - start\n",
" print(duration)\n",
" \n",
" \n",
" \n",
" vehicleTotalNumberInOneDay = []\n",
" date = []\n",
" \n",
" for x in range(duration):\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{start}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, start=start)\n",
" print(url)\n",
" \n",
" csv = pd.read_csv(url, names=names, sep = \";\")\n",
" df = csv[[\"vehicle_class\"]==vehicle_class[0]]\n",
" vehicleTotalNumberInOneDay.append(df[\"vehicle_class\"].count())\n",
" \n",
" print(vehicleTotalNumberInOneDay)\n",
" \n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=start)\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" date.append(dt)\n",
" print(date)\n",
" \n",
" start +=1\n",
"\n",
"\n",
" output[\"vehicle_number\"] = vehicleTotalNumberInOneDay\n",
" output[\"time\"] = date\n",
" output.set_index(\"time\")\n",
"\n",
"else:\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{startDayNumber}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, startDayNumber=startDayNumber)\n",
" \n",
" allData = pd.read_csv(url, names=names, sep = \";\")\n",
" #df = allData[allData[\"vehicle_class\"]==vehicle_class[0]]\n",
" \n",
" df = allData.groupby([\"hour\",\"vehicle_class\"])[\"vehicle_class\"].count().unstack(fill_value=0).stack()\n",
" print(df.head(25))\n",
" \n",
" outjson = df.to_json()\n",
" print(outjson)\n",
"\n",
" #df1 = df.groupby(df[\"hour\"])[\"vehicle_class\"].count()\n",
"\n",
" #output[\"vehicle_number\"]= df1[\"vehicle_class\"]\n",
" #output[\"time\"] = df2.index.values\n",
" \n",
" #print(output)\n",
" # getting date from day number\n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=int(startDayNumber))\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" print(dt)\n",
" \n",
" \n",
"\n",
"\n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"\n",
"#output.plot()\n",
"#plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"in else!\n",
"11.04.2020\n",
" vehicle_number avg_speed_km_h\n",
"hour vehicle_class direction \n",
"0 1 1 107 84.373832\n",
" 2 71 83.943662\n",
" 2 1 2 85.000000\n",
" 2 2 81.500000\n",
" 3 1 12 77.833333\n",
" 2 14 79.642857\n",
" 4 1 3 82.000000\n",
"1 1 1 76 88.026316\n",
" 2 54 85.000000\n",
" 2 1 5 78.200000\n",
" 2 4 78.000000\n",
" 3 1 11 78.000000\n",
" 2 6 78.000000\n",
" 4 1 4 76.750000\n",
" 2 3 81.666667\n",
"2 1 1 31 82.129032\n",
" 2 42 86.642857\n",
" 2 1 5 70.800000\n",
" 2 3 76.000000\n",
" 3 1 2 71.500000\n",
"3 1 1 32 77.343750\n",
" 2 21 80.238095\n",
" 2 1 2 21.500000\n",
" 2 1 82.000000\n",
" 4 1 2 26.500000\n",
"{\"vehicle_number\":{\"[0,1,1]\":107,\"[0,1,2]\":71,\"[0,2,1]\":2,\"[0,2,2]\":2,\"[0,3,1]\":12,\"[0,3,2]\":14,\"[0,4,1]\":3,\"[1,1,1]\":76,\"[1,1,2]\":54,\"[1,2,1]\":5,\"[1,2,2]\":4,\"[1,3,1]\":11,\"[1,3,2]\":6,\"[1,4,1]\":4,\"[1,4,2]\":3,\"[2,1,1]\":31,\"[2,1,2]\":42,\"[2,2,1]\":5,\"[2,2,2]\":3,\"[2,3,1]\":2,\"[3,1,1]\":32,\"[3,1,2]\":21,\"[3,2,1]\":2,\"[3,2,2]\":1,\"[3,4,1]\":2,\"[3,4,2]\":2,\"[3,6,1]\":1,\"[4,1,1]\":21,\"[4,1,2]\":23,\"[4,2,1]\":2,\"[4,2,2]\":1,\"[4,3,2]\":1,\"[5,1,1]\":49,\"[5,1,2]\":69,\"[5,2,1]\":3,\"[5,2,2]\":2,\"[5,3,2]\":1,\"[5,4,2]\":1,\"[5,5,2]\":2,\"[6,1,1]\":90,\"[6,1,2]\":257,\"[6,2,1]\":5,\"[6,2,2]\":3,\"[6,3,1]\":1,\"[6,3,2]\":4,\"[6,4,1]\":1,\"[6,4,2]\":19,\"[6,5,2]\":4,\"[6,6,2]\":2,\"[6,7,1]\":1,\"[7,1,1]\":152,\"[7,1,2]\":237,\"[7,2,1]\":1,\"[7,2,2]\":3,\"[7,3,1]\":2,\"[7,3,2]\":6,\"[7,4,1]\":2,\"[7,4,2]\":58,\"[7,5,2]\":6,\"[7,6,2]\":3,\"[7,7,2]\":1,\"[8,1,1]\":250,\"[8,1,2]\":276,\"[8,2,1]\":3,\"[8,2,2]\":9,\"[8,3,1]\":2,\"[8,3,2]\":3,\"[8,4,1]\":8,\"[8,4,2]\":36,\"[8,5,2]\":8,\"[8,7,2]\":2,\"[9,1,1]\":559,\"[9,1,2]\":419,\"[9,2,1]\":4,\"[9,2,2]\":5,\"[9,3,1]\":3,\"[9,3,2]\":1,\"[9,4,1]\":16,\"[9,4,2]\":12,\"[9,5,1]\":3,\"[9,5,2]\":2,\"[9,6,1]\":1,\"[9,6,2]\":4,\"[9,7,1]\":1,\"[10,1,1]\":934,\"[10,1,2]\":668,\"[10,2,1]\":4,\"[10,2,2]\":2,\"[10,3,1]\":1,\"[10,3,2]\":3,\"[10,4,1]\":3,\"[10,4,2]\":5,\"[10,5,1]\":2,\"[10,6,1]\":2,\"[10,6,2]\":4,\"[10,7,1]\":1,\"[10,7,2]\":1,\"[11,1,1]\":1151,\"[11,1,2]\":888,\"[11,2,1]\":5,\"[11,2,2]\":8,\"[11,3,1]\":2,\"[11,3,2]\":1,\"[11,4,1]\":2,\"[11,4,2]\":16,\"[11,5,2]\":5,\"[11,6,1]\":2,\"[11,6,2]\":8,\"[12,1,1]\":1418,\"[12,1,2]\":1075,\"[12,2,1]\":6,\"[12,2,2]\":5,\"[12,3,1]\":2,\"[12,3,2]\":3,\"[12,4,2]\":11,\"[12,5,1]\":1,\"[12,5,2]\":3,\"[12,6,1]\":4,\"[12,6,2]\":4,\"[12,7,2]\":2,\"[13,1,1]\":1443,\"[13,1,2]\":1146,\"[13,2,1]\":5,\"[13,2,2]\":3,\"[13,3,1]\":2,\"[13,3,2]\":2,\"[13,4,1]\":1,\"[13,4,2]\":22,\"[13,5,2]\":7,\"[13,6,1]\":2,\"[13,6,2]\":4,\"[14,1,1]\":1423,\"[14,1,2]\":1298,\"[14,2,1]\":2,\"[14,2,2]\":6,\"[14,3,1]\":1,\"[14,3,2]\":6,\"[14,4,2]\":21,\"[14,5,1]\":2,\"[14,5,2]\":3,\"[14,6,1]\":5,\"[14,6,2]\":5,\"[15,1,1]\":1376,\"[15,1,2]\":1304,\"[15,2,1]\":2,\"[15,2,2]\":5,\"[15,3,1]\":3,\"[15,3,2]\":2,\"[15,4,1]\":9,\"[15,4,2]\":6,\"[15,5,2]\":2,\"[15,6,1]\":7,\"[15,6,2]\":2,\"[15,7,1]\":2,\"[15,7,2]\":1,\"[16,1,1]\":1217,\"[16,1,2]\":1245,\"[16,2,1]\":1,\"[16,2,2]\":4,\"[16,3,1]\":1,\"[16,3,2]\":2,\"[16,4,1]\":4,\"[16,5,1]\":2,\"[16,6,1]\":6,\"[16,6,2]\":5,\"[17,1,1]\":1067,\"[17,1,2]\":1192,\"[17,2,1]\":2,\"[17,2,2]\":8,\"[17,3,1]\":4,\"[17,3,2]\":2,\"[17,4,2]\":2,\"[17,5,2]\":1,\"[17,6,1]\":3,\"[17,6,2]\":7,\"[17,7,2]\":2,\"[18,1,1]\":766,\"[18,1,2]\":911,\"[18,2,1]\":2,\"[18,2,2]\":5,\"[18,3,1]\":3,\"[18,3,2]\":3,\"[18,4,2]\":2,\"[18,5,2]\":1,\"[18,6,1]\":6,\"[19,1,1]\":491,\"[19,1,2]\":686,\"[19,2,1]\":2,\"[19,2,2]\":5,\"[19,3,1]\":2,\"[19,3,2]\":1,\"[19,4,2]\":2,\"[19,5,2]\":1,\"[19,6,1]\":3,\"[19,6,2]\":2,\"[20,1,1]\":459,\"[20,1,2]\":523,\"[20,2,1]\":4,\"[20,2,2]\":2,\"[20,3,2]\":9,\"[20,4,2]\":2,\"[20,5,1]\":1,\"[20,5,2]\":1,\"[20,6,1]\":1,\"[20,6,2]\":2,\"[21,1,1]\":326,\"[21,1,2]\":394,\"[21,2,1]\":2,\"[21,2,2]\":1,\"[21,3,1]\":4,\"[21,3,2]\":2,\"[21,4,2]\":3,\"[21,6,1]\":1,\"[22,1,1]\":216,\"[22,1,2]\":230,\"[22,3,1]\":3,\"[22,3,2]\":2,\"[22,4,1]\":2,\"[22,4,2]\":2,\"[22,5,2]\":1,\"[22,6,2]\":1,\"[23,1,1]\":180,\"[23,1,2]\":158,\"[23,2,1]\":3,\"[23,3,1]\":4,\"[23,3,2]\":8,\"[23,4,1]\":11,\"[23,5,2]\":1,\"[23,6,1]\":1,\"[23,6,2]\":2},\"avg_speed_km_h\":{\"[0,1,1]\":84.3738317757,\"[0,1,2]\":83.9436619718,\"[0,2,1]\":85.0,\"[0,2,2]\":81.5,\"[0,3,1]\":77.8333333333,\"[0,3,2]\":79.6428571429,\"[0,4,1]\":82.0,\"[1,1,1]\":88.0263157895,\"[1,1,2]\":85.0,\"[1,2,1]\":78.2,\"[1,2,2]\":78.0,\"[1,3,1]\":78.0,\"[1,3,2]\":78.0,\"[1,4,1]\":76.75,\"[1,4,2]\":81.6666666667,\"[2,1,1]\":82.1290322581,\"[2,1,2]\":86.6428571429,\"[2,2,1]\":70.8,\"[2,2,2]\":76.0,\"[2,3,1]\":71.5,\"[3,1,1]\":77.34375,\"[3,1,2]\":80.2380952381,\"[3,2,1]\":21.5,\"[3,2,2]\":82.0,\"[3,4,1]\":26.5,\"[3,4,2]\":61.0,\"[3,6,1]\":18.0,\"[4,1,1]\":82.9523809524,\"[4,1,2]\":85.0869565217,\"[4,2,1]\":75.5,\"[4,2,2]\":84.0,\"[4,3,2]\":75.0,\"[5,1,1]\":84.9387755102,\"[5,1,2]\":84.9130434783,\"[5,2,1]\":86.3333333333,\"[5,2,2]\":78.0,\"[5,3,2]\":82.0,\"[5,4,2]\":78.0,\"[5,5,2]\":81.0,\"[6,1,1]\":84.5666666667,\"[6,1,2]\":87.5447470817,\"[6,2,1]\":81.8,\"[6,2,2]\":80.3333333333,\"[6,3,1]\":72.0,\"[6,3,2]\":77.25,\"[6,4,1]\":89.0,\"[6,4,2]\":79.6842105263,\"[6,5,2]\":77.25,\"[6,6,2]\":77.0,\"[6,7,1]\":22.0,\"[7,1,1]\":85.5328947368,\"[7,1,2]\":86.4599156118,\"[7,2,1]\":92.0,\"[7,2,2]\":82.3333333333,\"[7,3,1]\":77.5,\"[7,3,2]\":71.0,\"[7,4,1]\":77.0,\"[7,4,2]\":80.1206896552,\"[7,5,2]\":80.8333333333,\"[7,6,2]\":66.0,\"[7,7,2]\":78.0,\"[8,1,1]\":84.844,\"[8,1,2]\":84.0217391304,\"[8,2,1]\":79.0,\"[8,2,2]\":86.1111111111,\"[8,3,1]\":77.0,\"[8,3,2]\":79.6666666667,\"[8,4,1]\":77.125,\"[8,4,2]\":79.8888888889,\"[8,5,2]\":80.125,\"[8,7,2]\":67.0,\"[9,1,1]\":83.1162790698,\"[9,1,2]\":82.2076372315,\"[9,2,1]\":66.5,\"[9,2,2]\":85.4,\"[9,3,1]\":77.6666666667,\"[9,3,2]\":84.0,\"[9,4,1]\":80.5,\"[9,4,2]\":81.75,\"[9,5,1]\":78.6666666667,\"[9,5,2]\":75.0,\"[9,6,1]\":82.0,\"[9,6,2]\":81.25,\"[9,7,1]\":81.0,\"[10,1,1]\":81.9068522484,\"[10,1,2]\":82.6526946108,\"[10,2,1]\":60.25,\"[10,2,2]\":74.5,\"[10,3,1]\":78.0,\"[10,3,2]\":84.3333333333,\"[10,4,1]\":75.6666666667,\"[10,4,2]\":76.4,\"[10,5,1]\":78.5,\"[10,6,1]\":86.5,\"[10,6,2]\":68.5,\"[10,7,1]\":87.0,\"[10,7,2]\":96.0,\"[11,1,1]\":82.2076455256,\"[11,1,2]\":82.6497747748,\"[11,2,1]\":72.2,\"[11,2,2]\":83.5,\"[11,3,1]\":79.5,\"[11,3,2]\":83.0,\"[11,4,1]\":81.0,\"[11,4,2]\":76.75,\"[11,5,2]\":81.4,\"[11,6,1]\":81.0,\"[11,6,2]\":82.0,\"[12,1,1]\":81.9407616361,\"[12,1,2]\":82.5348837209,\"[12,2,1]\":81.8333333333,\"[12,2,2]\":83.0,\"[12,3,1]\":79.5,\"[12,3,2]\":80.0,\"[12,4,2]\":77.6363636364,\"[12,5,1]\":55.0,\"[12,5,2]\":86.6666666667,\"[12,6,1]\":78.5,\"[12,6,2]\":80.5,\"[12,7,2]\":71.5,\"[13,1,1]\":82.2134442134,\"[13,1,2]\":81.9363001745,\"[13,2,1]\":67.6,\"[13,2,2]\":87.3333333333,\"[13,3,1]\":76.5,\"[13,3,2]\":47.5,\"[13,4,1]\":80.0,\"[13,4,2]\":74.2272727273,\"[13,5,2]\":78.8571428571,\"[13,6,1]\":75.5,\"[13,6,2]\":80.25,\"[14,1,1]\":83.1546029515,\"[14,1,2]\":81.8998459168,\"[14,2,1]\":76.5,\"[14,2,2]\":66.5,\"[14,3,1]\":80.0,\"[14,3,2]\":68.6666666667,\"[14,4,2]\":79.1904761905,\"[14,5,1]\":84.5,\"[14,5,2]\":76.0,\"[14,6,1]\":77.8,\"[14,6,2]\":80.0,\"[15,1,1]\":83.207122093,\"[15,1,2]\":81.25,\"[15,2,1]\":76.5,\"[15,2,2]\":70.2,\"[15,3,1]\":81.3333333333,\"[15,3,2]\":64.5,\"[15,4,1]\":78.6666666667,\"[15,4,2]\":79.3333333333,\"[15,5,2]\":90.5,\"[15,6,1]\":80.8571428571,\"[15,6,2]\":82.0,\"[15,7,1]\":87.0,\"[15,7,2]\":79.0,\"[16,1,1]\":83.3845521775,\"[16,1,2]\":81.3100401606,\"[16,2,1]\":77.0,\"[16,2,2]\":81.0,\"[16,3,1]\":79.0,\"[16,3,2]\":76.5,\"[16,4,1]\":76.5,\"[16,5,1]\":83.0,\"[16,6,1]\":66.1666666667,\"[16,6,2]\":81.2,\"[17,1,1]\":82.9896907216,\"[17,1,2]\":82.1736577181,\"[17,2,1]\":45.5,\"[17,2,2]\":90.25,\"[17,3,1]\":65.25,\"[17,3,2]\":72.5,\"[17,4,2]\":73.0,\"[17,5,2]\":83.0,\"[17,6,1]\":80.0,\"[17,6,2]\":75.2857142857,\"[17,7,2]\":83.5,\"[18,1,1]\":82.911227154,\"[18,1,2]\":81.2294182217,\"[18,2,1]\":82.5,\"[18,2,2]\":89.2,\"[18,3,1]\":78.6666666667,\"[18,3,2]\":69.0,\"[18,4,2]\":78.0,\"[18,5,2]\":83.0,\"[18,6,1]\":77.8333333333,\"[19,1,1]\":83.6639511202,\"[19,1,2]\":82.1545189504,\"[19,2,1]\":51.5,\"[19,2,2]\":86.0,\"[19,3,1]\":68.0,\"[19,3,2]\":71.0,\"[19,4,2]\":77.5,\"[19,5,2]\":63.0,\"[19,6,1]\":81.3333333333,\"[19,6,2]\":80.5,\"[20,1,1]\":83.3093681917,\"[20,1,2]\":82.4531548757,\"[20,2,1]\":77.0,\"[20,2,2]\":84.0,\"[20,3,2]\":64.2222222222,\"[20,4,2]\":76.0,\"[20,5,1]\":46.0,\"[20,5,2]\":79.0,\"[20,6,1]\":75.0,\"[20,6,2]\":89.5,\"[21,1,1]\":83.1656441718,\"[21,1,2]\":82.0482233503,\"[21,2,1]\":79.0,\"[21,2,2]\":40.0,\"[21,3,1]\":69.75,\"[21,3,2]\":81.0,\"[21,4,2]\":85.6666666667,\"[21,6,1]\":82.0,\"[22,1,1]\":81.837962963,\"[22,1,2]\":84.0608695652,\"[22,3,1]\":73.3333333333,\"[22,3,2]\":76.5,\"[22,4,1]\":82.5,\"[22,4,2]\":75.5,\"[22,5,2]\":75.0,\"[22,6,2]\":69.0,\"[23,1,1]\":82.9388888889,\"[23,1,2]\":81.4873417722,\"[23,2,1]\":78.3333333333,\"[23,3,1]\":70.5,\"[23,3,2]\":78.875,\"[23,4,1]\":77.2727272727,\"[23,5,2]\":75.0,\"[23,6,1]\":83.0,\"[23,6,2]\":86.0}}\n"
]
}
],
"source": [
"\n",
"# getting count number for each vehicle class, avarage speed for each class and direction\n",
"\n",
"import datetime\n",
"\n",
"\n",
"year = \"2020\"\n",
"areaID = \"01\"\n",
"lamID = \"101\"\n",
"shortYear = year[-2:]\n",
"startDayNumber = \"101\"\n",
"endDayNumber = \"\"\n",
"vehicle_class =[4]\n",
"\n",
"output = pd.DataFrame()\n",
"\n",
"\n",
"\n",
"if(len(endDayNumber)!=0):\n",
" start = int(startDayNumber)\n",
" end = int(endDayNumber)\n",
" duration = (end+1) - start\n",
" print(duration)\n",
" \n",
" \n",
" \n",
" vehicleTotalNumberInOneDay = pd.DataFrame()\n",
" date = []\n",
" \n",
" for x in range(duration):\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{start}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, start=start)\n",
" csv = pd.read_csv(url, names=names, sep = \";\")\n",
" \n",
" #getting the day from sequectial day number\n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=start)\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" date.append(dt)\n",
" print(date)\n",
" \n",
" \n",
" #temp = csv.groupby([\"vehicle_class\"])[\"vehicle_class\"].count()\n",
" temp = csv.groupby([\"vehicle_class\",\"direction\"]).agg({\"vehicle_class\":\"count\", \"speed_km/h\":\"mean\"})\\\n",
" .rename(columns={'vehicle_class':'vehicle_number','speed_km/h':'avg_speed_km_h'})\n",
" \n",
" #changing indexes to create uniqe indexs containing date and vehicle class\n",
" indexv = temp.index.values.tolist() \n",
" newindex = []\n",
" for i in range(len(indexv)):\n",
" a= list(indexv[i])\n",
" a[0] = dt+\"_\"+str(a[0])\n",
" newindex.append(tuple(a))\n",
" \n",
" #print(newindex)\n",
" \n",
" \n",
" #setting the new index to dataframe\n",
" temp[\"date_vehicleclass_direction\"]= newindex\n",
" temp.set_index(\"date_vehicleclass_direction\", inplace= True)\n",
" \n",
" vehicleTotalNumberInOneDay= vehicleTotalNumberInOneDay.append(temp)\n",
" \n",
" #print(vehicleTotalNumberInOneDay)\n",
" \n",
"\n",
" \n",
" start +=1\n",
" \n",
" \n",
" output= vehicleTotalNumberInOneDay\n",
" #output[\"date\"] = date\n",
" #output.set_index(\"date\", inplace = True)\n",
" print(output)\n",
" outputJson = output.to_json()\n",
" print(outputJson)\n",
"\n",
"else:\n",
" print(\"in else!\")\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{startDayNumber}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, startDayNumber=startDayNumber)\n",
" \n",
" allData = pd.read_csv(url, names=names, sep = \";\")\n",
" #df = allData[allData[\"vehicle_class\"]==vehicle_class[0]]\n",
" \n",
" # getting date from day number\n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=int(startDayNumber))\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" print(dt)\n",
" \n",
" df = allData.groupby([\"hour\",\"vehicle_class\",\"direction\"]).agg({\"vehicle_class\": \"count\", \"speed_km/h\":\"mean\"})\\\n",
" .rename(columns={'vehicle_class':'vehicle_number','speed_km/h':'avg_speed_km_h'})\n",
" \n",
" print(df.head(25))\n",
" \n",
" outjson = df.to_json()\n",
" print(outjson)\n",
" \n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"#output.plot()\n",
"#plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"in else!\n",
"09.01.2020\n",
" vehicle_number avg_speed_km_h\n",
"hour vehicle_class direction \n",
"0 1 1 103 82.83\n",
" 2 81 81.14\n",
" 2 1 3 69.00\n",
" 2 3 83.33\n",
" 3 1 9 72.44\n",
" 2 13 79.69\n",
" 4 1 5 84.00\n",
" 5 1 1 77.00\n",
"1 1 1 58 82.60\n",
" 2 40 84.45\n",
" 2 1 3 74.33\n",
" 2 4 79.75\n",
" 3 1 11 79.82\n",
" 2 8 80.00\n",
" 4 1 3 77.00\n",
" 2 2 80.00\n",
"2 1 1 35 83.09\n",
" 2 27 82.04\n",
" 2 1 5 80.20\n",
" 2 2 86.50\n",
" 3 1 1 73.00\n",
" 4 2 1 80.00\n",
" 5 1 1 82.00\n",
" 2 2 83.00\n",
"3 1 1 35 82.80\n",
"{\"vehicle_number\":{\"[0,1,1]\":103,\"[0,1,2]\":81,\"[0,2,1]\":3,\"[0,2,2]\":3,\"[0,3,1]\":9,\"[0,3,2]\":13,\"[0,4,1]\":5,\"[0,5,1]\":1,\"[1,1,1]\":58,\"[1,1,2]\":40,\"[1,2,1]\":3,\"[1,2,2]\":4,\"[1,3,1]\":11,\"[1,3,2]\":8,\"[1,4,1]\":3,\"[1,4,2]\":2,\"[2,1,1]\":35,\"[2,1,2]\":27,\"[2,2,1]\":5,\"[2,2,2]\":2,\"[2,3,1]\":1,\"[2,4,2]\":1,\"[2,5,1]\":1,\"[2,5,2]\":2,\"[3,1,1]\":35,\"[3,1,2]\":23,\"[3,2,2]\":1,\"[3,4,2]\":2,\"[4,1,1]\":51,\"[4,1,2]\":63,\"[4,2,1]\":1,\"[4,2,2]\":6,\"[4,3,2]\":3,\"[4,5,2]\":1,\"[4,6,2]\":2,\"[5,1,1]\":133,\"[5,1,2]\":349,\"[5,2,1]\":6,\"[5,2,2]\":14,\"[5,3,1]\":6,\"[5,3,2]\":10,\"[5,4,1]\":1,\"[5,4,2]\":9,\"[5,5,1]\":1,\"[5,5,2]\":2,\"[5,6,2]\":1,\"[5,7,2]\":2,\"[6,1,1]\":546,\"[6,1,2]\":1666,\"[6,2,1]\":15,\"[6,2,2]\":37,\"[6,3,1]\":10,\"[6,3,2]\":27,\"[6,4,1]\":5,\"[6,4,2]\":14,\"[6,5,1]\":3,\"[6,5,2]\":9,\"[6,6,1]\":1,\"[6,6,2]\":7,\"[6,7,1]\":1,\"[6,7,2]\":1,\"[7,1,1]\":1404,\"[7,1,2]\":3130,\"[7,2,1]\":26,\"[7,2,2]\":50,\"[7,3,1]\":15,\"[7,3,2]\":24,\"[7,4,1]\":8,\"[7,4,2]\":36,\"[7,5,1]\":6,\"[7,5,2]\":7,\"[7,6,1]\":4,\"[7,6,2]\":5,\"[7,7,1]\":1,\"[7,7,2]\":3,\"[8,1,1]\":2234,\"[8,1,2]\":3451,\"[8,2,1]\":43,\"[8,2,2]\":35,\"[8,3,1]\":21,\"[8,3,2]\":23,\"[8,4,1]\":50,\"[8,4,2]\":24,\"[8,5,1]\":10,\"[8,5,2]\":11,\"[8,6,1]\":5,\"[8,6,2]\":4,\"[8,7,1]\":1,\"[8,7,2]\":1,\"[9,1,1]\":1422,\"[9,1,2]\":2613,\"[9,2,1]\":53,\"[9,2,2]\":51,\"[9,3,1]\":17,\"[9,3,2]\":25,\"[9,4,1]\":39,\"[9,4,2]\":28,\"[9,5,1]\":12,\"[9,5,2]\":12,\"[9,6,1]\":3,\"[9,6,2]\":7,\"[9,7,1]\":4,\"[9,7,2]\":2,\"[10,1,1]\":1202,\"[10,1,2]\":1533,\"[10,2,1]\":55,\"[10,2,2]\":40,\"[10,3,1]\":9,\"[10,3,2]\":6,\"[10,4,1]\":27,\"[10,4,2]\":9,\"[10,5,1]\":7,\"[10,5,2]\":9,\"[10,6,1]\":5,\"[10,6,2]\":7,\"[10,7,1]\":2,\"[10,7,2]\":2,\"[11,1,1]\":1445,\"[11,1,2]\":1479,\"[11,2,1]\":56,\"[11,2,2]\":35,\"[11,3,1]\":5,\"[11,3,2]\":7,\"[11,4,1]\":4,\"[11,4,2]\":14,\"[11,5,1]\":4,\"[11,5,2]\":6,\"[11,6,1]\":4,\"[11,6,2]\":4,\"[11,7,1]\":1,\"[11,7,2]\":2,\"[12,1,1]\":1457,\"[12,1,2]\":1493,\"[12,2,1]\":50,\"[12,2,2]\":22,\"[12,3,1]\":5,\"[12,3,2]\":8,\"[12,4,1]\":2,\"[12,4,2]\":25,\"[12,5,1]\":3,\"[12,5,2]\":9,\"[12,6,2]\":1,\"[13,1,1]\":1676,\"[13,1,2]\":1360,\"[13,2,1]\":46,\"[13,2,2]\":31,\"[13,3,1]\":6,\"[13,3,2]\":8,\"[13,4,1]\":4,\"[13,4,2]\":38,\"[13,5,1]\":5,\"[13,5,2]\":9,\"[13,6,1]\":2,\"[13,6,2]\":3,\"[13,7,1]\":1,\"[13,7,2]\":1,\"[14,1,1]\":2197,\"[14,1,2]\":1437,\"[14,2,1]\":48,\"[14,2,2]\":27,\"[14,3,1]\":17,\"[14,3,2]\":31,\"[14,4,1]\":38,\"[14,4,2]\":31,\"[14,5,1]\":11,\"[14,5,2]\":5,\"[14,6,1]\":2,\"[14,6,2]\":2,\"[14,7,1]\":1,\"[14,7,2]\":4,\"[15,1,1]\":3346,\"[15,1,2]\":1771,\"[15,2,1]\":33,\"[15,2,2]\":29,\"[15,3,1]\":19,\"[15,3,2]\":23,\"[15,4,1]\":26,\"[15,4,2]\":23,\"[15,5,1]\":4,\"[15,5,2]\":2,\"[15,6,1]\":9,\"[15,6,2]\":8,\"[15,7,1]\":1,\"[15,7,2]\":1,\"[16,1,1]\":3777,\"[16,1,2]\":2300,\"[16,2,1]\":29,\"[16,2,2]\":10,\"[16,3,1]\":22,\"[16,3,2]\":22,\"[16,4,1]\":59,\"[16,4,2]\":31,\"[16,5,1]\":5,\"[16,5,2]\":3,\"[16,6,1]\":3,\"[16,6,2]\":4,\"[16,7,1]\":4,\"[16,7,2]\":1,\"[17,1,1]\":3102,\"[17,1,2]\":2076,\"[17,2,1]\":15,\"[17,2,2]\":12,\"[17,3,1]\":23,\"[17,3,2]\":22,\"[17,4,1]\":6,\"[17,4,2]\":20,\"[17,5,1]\":2,\"[17,5,2]\":8,\"[17,6,1]\":2,\"[17,6,2]\":5,\"[17,7,1]\":1,\"[17,7,2]\":1,\"[18,1,1]\":2115,\"[18,1,2]\":1513,\"[18,2,1]\":10,\"[18,2,2]\":13,\"[18,3,1]\":16,\"[18,3,2]\":12,\"[18,4,1]\":2,\"[18,4,2]\":20,\"[18,5,2]\":8,\"[18,6,1]\":1,\"[18,6,2]\":2,\"[18,7,1]\":2,\"[18,7,2]\":1,\"[19,1,1]\":1611,\"[19,1,2]\":1186,\"[19,2,1]\":9,\"[19,2,2]\":11,\"[19,3,1]\":5,\"[19,3,2]\":5,\"[19,4,1]\":2,\"[19,4,2]\":10,\"[19,5,1]\":1,\"[19,5,2]\":4,\"[19,6,1]\":2,\"[19,6,2]\":4,\"[19,7,2]\":1,\"[20,1,1]\":1221,\"[20,1,2]\":1002,\"[20,2,1]\":4,\"[20,2,2]\":7,\"[20,3,1]\":2,\"[20,3,2]\":5,\"[20,4,2]\":11,\"[20,5,1]\":1,\"[20,5,2]\":2,\"[20,6,1]\":1,\"[20,6,2]\":1,\"[21,1,1]\":1154,\"[21,1,2]\":583,\"[21,2,1]\":7,\"[21,2,2]\":5,\"[21,3,1]\":5,\"[21,3,2]\":1,\"[21,4,1]\":81,\"[21,4,2]\":5,\"[21,5,1]\":7,\"[21,5,2]\":3,\"[21,6,1]\":1,\"[21,6,2]\":1,\"[21,7,1]\":2,\"[22,1,1]\":449,\"[22,1,2]\":288,\"[22,2,1]\":9,\"[22,2,2]\":4,\"[22,3,1]\":3,\"[22,3,2]\":3,\"[22,4,1]\":8,\"[22,4,2]\":1,\"[22,5,2]\":3,\"[22,7,2]\":2,\"[23,1,1]\":215,\"[23,1,2]\":135,\"[23,2,1]\":2,\"[23,2,2]\":2,\"[23,3,1]\":3,\"[23,3,2]\":9,\"[23,4,2]\":1,\"[23,5,1]\":2},\"avg_speed_km_h\":{\"[0,1,1]\":82.83,\"[0,1,2]\":81.14,\"[0,2,1]\":69.0,\"[0,2,2]\":83.33,\"[0,3,1]\":72.44,\"[0,3,2]\":79.69,\"[0,4,1]\":84.0,\"[0,5,1]\":77.0,\"[1,1,1]\":82.6,\"[1,1,2]\":84.45,\"[1,2,1]\":74.33,\"[1,2,2]\":79.75,\"[1,3,1]\":79.82,\"[1,3,2]\":80.0,\"[1,4,1]\":77.0,\"[1,4,2]\":80.0,\"[2,1,1]\":83.09,\"[2,1,2]\":82.04,\"[2,2,1]\":80.2,\"[2,2,2]\":86.5,\"[2,3,1]\":73.0,\"[2,4,2]\":80.0,\"[2,5,1]\":82.0,\"[2,5,2]\":83.0,\"[3,1,1]\":82.8,\"[3,1,2]\":84.48,\"[3,2,2]\":76.0,\"[3,4,2]\":81.0,\"[4,1,1]\":80.41,\"[4,1,2]\":81.51,\"[4,2,1]\":87.0,\"[4,2,2]\":82.5,\"[4,3,2]\":75.0,\"[4,5,2]\":79.0,\"[4,6,2]\":82.5,\"[5,1,1]\":83.08,\"[5,1,2]\":84.72,\"[5,2,1]\":81.33,\"[5,2,2]\":76.93,\"[5,3,1]\":76.83,\"[5,3,2]\":70.5,\"[5,4,1]\":74.0,\"[5,4,2]\":83.0,\"[5,5,1]\":76.0,\"[5,5,2]\":83.0,\"[5,6,2]\":77.0,\"[5,7,2]\":84.5,\"[6,1,1]\":81.23,\"[6,1,2]\":82.82,\"[6,2,1]\":76.67,\"[6,2,2]\":80.27,\"[6,3,1]\":73.5,\"[6,3,2]\":73.22,\"[6,4,1]\":72.4,\"[6,4,2]\":76.14,\"[6,5,1]\":79.67,\"[6,5,2]\":84.0,\"[6,6,1]\":22.0,\"[6,6,2]\":79.86,\"[6,7,1]\":25.0,\"[6,7,2]\":81.0,\"[7,1,1]\":78.4,\"[7,1,2]\":77.62,\"[7,2,1]\":76.88,\"[7,2,2]\":76.5,\"[7,3,1]\":74.6,\"[7,3,2]\":78.33,\"[7,4,1]\":73.5,\"[7,4,2]\":73.42,\"[7,5,1]\":70.5,\"[7,5,2]\":68.86,\"[7,6,1]\":79.75,\"[7,6,2]\":73.8,\"[7,7,1]\":68.0,\"[7,7,2]\":68.67,\"[8,1,1]\":76.27,\"[8,1,2]\":75.49,\"[8,2,1]\":71.07,\"[8,2,2]\":75.03,\"[8,3,1]\":72.48,\"[8,3,2]\":72.17,\"[8,4,1]\":70.12,\"[8,4,2]\":75.33,\"[8,5,1]\":72.7,\"[8,5,2]\":70.82,\"[8,6,1]\":74.2,\"[8,6,2]\":78.75,\"[8,7,1]\":77.0,\"[8,7,2]\":73.0,\"[9,1,1]\":80.13,\"[9,1,2]\":77.34,\"[9,2,1]\":75.28,\"[9,2,2]\":75.41,\"[9,3,1]\":76.71,\"[9,3,2]\":76.12,\"[9,4,1]\":77.82,\"[9,4,2]\":76.36,\"[9,5,1]\":76.25,\"[9,5,2]\":76.75,\"[9,6,1]\":76.33,\"[9,6,2]\":74.0,\"[9,7,1]\":77.75,\"[9,7,2]\":59.5,\"[10,1,1]\":80.19,\"[10,1,2]\":79.77,\"[10,2,1]\":79.2,\"[10,2,2]\":77.32,\"[10,3,1]\":71.78,\"[10,3,2]\":74.83,\"[10,4,1]\":74.0,\"[10,4,2]\":77.11,\"[10,5,1]\":73.43,\"[10,5,2]\":69.0,\"[10,6,1]\":76.2,\"[10,6,2]\":77.14,\"[10,7,1]\":75.0,\"[10,7,2]\":76.0,\"[11,1,1]\":80.44,\"[11,1,2]\":79.85,\"[11,2,1]\":79.23,\"[11,2,2]\":75.77,\"[11,3,1]\":74.6,\"[11,3,2]\":69.0,\"[11,4,1]\":76.5,\"[11,4,2]\":77.64,\"[11,5,1]\":59.25,\"[11,5,2]\":77.0,\"[11,6,1]\":75.0,\"[11,6,2]\":78.5,\"[11,7,1]\":72.0,\"[11,7,2]\":84.0,\"[12,1,1]\":80.89,\"[12,1,2]\":80.16,\"[12,2,1]\":78.22,\"[12,2,2]\":80.36,\"[12,3,1]\":71.8,\"[12,3,2]\":78.38,\"[12,4,1]\":81.0,\"[12,4,2]\":77.4,\"[12,5,1]\":59.0,\"[12,5,2]\":78.89,\"[12,6,2]\":73.0,\"[13,1,1]\":79.76,\"[13,1,2]\":79.63,\"[13,2,1]\":77.48,\"[13,2,2]\":78.84,\"[13,3,1]\":78.33,\"[13,3,2]\":78.25,\"[13,4,1]\":82.25,\"[13,4,2]\":77.71,\"[13,5,1]\":77.0,\"[13,5,2]\":75.11,\"[13,6,1]\":77.0,\"[13,6,2]\":83.0,\"[13,7,1]\":69.0,\"[13,7,2]\":73.0,\"[14,1,1]\":80.27,\"[14,1,2]\":79.58,\"[14,2,1]\":75.67,\"[14,2,2]\":79.07,\"[14,3,1]\":75.88,\"[14,3,2]\":78.71,\"[14,4,1]\":73.66,\"[14,4,2]\":78.97,\"[14,5,1]\":78.82,\"[14,5,2]\":82.4,\"[14,6,1]\":74.5,\"[14,6,2]\":75.0,\"[14,7,1]\":28.0,\"[14,7,2]\":74.75,\"[15,1,1]\":76.41,\"[15,1,2]\":78.74,\"[15,2,1]\":74.67,\"[15,2,2]\":77.52,\"[15,3,1]\":69.05,\"[15,3,2]\":77.0,\"[15,4,1]\":68.85,\"[15,4,2]\":76.52,\"[15,5,1]\":47.5,\"[15,5,2]\":78.5,\"[15,6,1]\":71.78,\"[15,6,2]\":70.25,\"[15,7,1]\":75.0,\"[15,7,2]\":82.0,\"[16,1,1]\":69.22,\"[16,1,2]\":77.75,\"[16,2,1]\":64.79,\"[16,2,2]\":82.9,\"[16,3,1]\":73.68,\"[16,3,2]\":76.36,\"[16,4,1]\":61.86,\"[16,4,2]\":70.94,\"[16,5,1]\":55.8,\"[16,5,2]\":58.67,\"[16,6,1]\":47.0,\"[16,6,2]\":82.25,\"[16,7,1]\":67.0,\"[16,7,2]\":81.0,\"[17,1,1]\":74.68,\"[17,1,2]\":77.29,\"[17,2,1]\":68.4,\"[17,2,2]\":75.33,\"[17,3,1]\":74.7,\"[17,3,2]\":77.41,\"[17,4,1]\":69.17,\"[17,4,2]\":72.8,\"[17,5,1]\":71.0,\"[17,5,2]\":77.25,\"[17,6,1]\":72.5,\"[17,6,2]\":79.0,\"[17,7,1]\":58.0,\"[17,7,2]\":71.0,\"[18,1,1]\":78.88,\"[18,1,2]\":79.68,\"[18,2,1]\":76.1,\"[18,2,2]\":68.92,\"[18,3,1]\":77.19,\"[18,3,2]\":79.33,\"[18,4,1]\":69.0,\"[18,4,2]\":75.9,\"[18,5,2]\":78.62,\"[18,6,1]\":68.0,\"[18,6,2]\":79.0,\"[18,7,1]\":63.0,\"[18,7,2]\":81.0,\"[19,1,1]\":79.55,\"[19,1,2]\":80.07,\"[19,2,1]\":70.89,\"[19,2,2]\":75.55,\"[19,3,1]\":74.4,\"[19,3,2]\":76.2,\"[19,4,1]\":81.5,\"[19,4,2]\":80.0,\"[19,5,1]\":71.0,\"[19,5,2]\":60.25,\"[19,6,1]\":73.0,\"[19,6,2]\":77.0,\"[19,7,2]\":60.0,\"[20,1,1]\":80.21,\"[20,1,2]\":81.18,\"[20,2,1]\":80.5,\"[20,2,2]\":78.71,\"[20,3,1]\":68.0,\"[20,3,2]\":76.0,\"[20,4,2]\":81.36,\"[20,5,1]\":41.0,\"[20,5,2]\":87.0,\"[20,6,1]\":73.0,\"[20,6,2]\":70.0,\"[21,1,1]\":81.34,\"[21,1,2]\":81.87,\"[21,2,1]\":80.86,\"[21,2,2]\":78.8,\"[21,3,1]\":76.0,\"[21,3,2]\":83.0,\"[21,4,1]\":77.32,\"[21,4,2]\":81.2,\"[21,5,1]\":81.0,\"[21,5,2]\":73.33,\"[21,6,1]\":79.0,\"[21,6,2]\":81.0,\"[21,7,1]\":77.0,\"[22,1,1]\":81.5,\"[22,1,2]\":81.73,\"[22,2,1]\":74.56,\"[22,2,2]\":74.25,\"[22,3,1]\":80.0,\"[22,3,2]\":71.67,\"[22,4,1]\":76.75,\"[22,4,2]\":83.0,\"[22,5,2]\":61.67,\"[22,7,2]\":80.5,\"[23,1,1]\":82.96,\"[23,1,2]\":81.59,\"[23,2,1]\":69.5,\"[23,2,2]\":70.0,\"[23,3,1]\":83.67,\"[23,3,2]\":79.0,\"[23,4,2]\":72.0,\"[23,5,1]\":78.0}}\n"
]
}
],
"source": [
"# getting count number for each vehicle class, avarage speed for each class and direction\n",
"\n",
"import datetime\n",
"\n",
"\n",
"year = \"2020\"\n",
"areaID = \"01\"\n",
"lamID = \"101\"\n",
"shortYear = year[-2:]\n",
"startDayNumber = \"8\"\n",
"endDayNumber = \"\"\n",
"vehicle_class =[4]\n",
"\n",
"output = pd.DataFrame()\n",
"\n",
"\n",
"\n",
"if(len(endDayNumber)!=0):\n",
" start = int(startDayNumber)\n",
" end = int(endDayNumber)\n",
" duration = (end+1) - start\n",
" print(duration)\n",
" \n",
" \n",
" \n",
" vehicleTotalNumberInOneDay = pd.DataFrame()\n",
" date = []\n",
" \n",
" for x in range(duration):\n",
" print(start)\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{start}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, start=start)\n",
" csv = pd.read_csv(url, names=names, sep = \";\")\n",
" \n",
" #getting the day from sequectial day number\n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=(start-1))\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" date.append(dt)\n",
" print(date)\n",
" \n",
" \n",
" #temp = csv.groupby([\"vehicle_class\"])[\"vehicle_class\"].count()\n",
" temp = csv.groupby([\"vehicle_class\",\"direction\"]).agg({\"vehicle_class\":\"count\", \"speed_km/h\":\"mean\"})\\\n",
" .rename(columns={'vehicle_class':'vehicle_number','speed_km/h':'avg_speed_km_h'})\n",
" \n",
" #changing indexes to create uniqe indexs containing date and vehicle class\n",
" indexv = temp.index.values.tolist() \n",
" newindex = []\n",
" for i in range(len(indexv)):\n",
" a= list(indexv[i])\n",
" a[0] = dt+\"_\"+str(a[0])\n",
" newindex.append(tuple(a))\n",
" \n",
" #print(newindex)\n",
" \n",
" \n",
" #setting the new index to dataframe\n",
" temp[\"date_vehicleclass_direction\"]= newindex\n",
" temp.set_index(\"date_vehicleclass_direction\", inplace= True)\n",
" \n",
" vehicleTotalNumberInOneDay= vehicleTotalNumberInOneDay.append(temp)\n",
" \n",
" #print(vehicleTotalNumberInOneDay)\n",
" \n",
"\n",
" \n",
" start +=1\n",
" \n",
" \n",
" output= vehicleTotalNumberInOneDay\n",
" #output[\"date\"] = date\n",
" #output.set_index(\"date\", inplace = True)\n",
" print(output)\n",
" outputJson = output.to_json()\n",
" print(outputJson)\n",
"\n",
"else:\n",
" print(\"in else!\")\n",
" url = \"https://aineistot.vayla.fi/lam/rawdata/{year}/{areaID}/lamraw_{lamID}_{shortYear}_{startDayNumber}.csv\".format(year=year,areaID=areaID,lamID=lamID,shortYear = shortYear, startDayNumber=startDayNumber)\n",
" \n",
" allData = pd.read_csv(url, names=names, sep = \";\")\n",
" #df = allData[allData[\"vehicle_class\"]==vehicle_class[0]]\n",
" \n",
" # getting date from day number\n",
" dt = datetime.datetime(int(year),1,1)\n",
" dtdelta = datetime.timedelta(days=int(startDayNumber))\n",
" dt = dt + dtdelta\n",
" dt=dt.strftime('%d.%m.%Y')\n",
" print(dt)\n",
" \n",
" df = allData.groupby([\"hour\",\"vehicle_class\",\"direction\"]).agg({\"vehicle_class\": \"count\", \"speed_km/h\":\"mean\"})\\\n",
" .rename(columns={'vehicle_class':'vehicle_number','speed_km/h':'avg_speed_km_h'})\n",
" df['avg_speed_km_h'] = df['avg_speed_km_h'].astype(float).round(1)\n",
" print(df.head(25))\n",
" \n",
" outjson = df.to_json()\n",
" print(outjson)\n",
" \n",
"\n",
"#print(\"\\n-----------------------------------------\\n\")\n",
"\n",
"#output.plot()\n",
"#plt.show()"
]
}
],
"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
}