import numpy as np
import pandas as pd
import geopandas as gpd
from geopandas import GeoDataFrame, read_file
import shapely as shp
from shapely.geometry import Point, LineString, Polygon
from datetime import datetime, timedelta
import movingpandas as mpd
import hvplot.pandas
# import folium
import contextily as cx
import matplotlib.pyplot as plt
import warnings
warnings.filterwarnings('ignore')
print(f'MovingPandas version {mpd.__version__}')
print(f'GeoPandas version {gpd.__version__}')
print(f'Shapely version {shp.__version__}')
MovingPandas version 0.8.rc1 GeoPandas version 0.9.0 Shapely version 1.7.1
taskai = gpd.read_file('B:\\...Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\gis\\stops.gpkg')
ax = taskai.plot(figsize=(10, 10), alpha=0.5, edgecolor='k')
taskai.head(3) # 1427 eilutes
OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POINT (568773.727 6056951.539) |
1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POINT (583463.063 6065751.175) |
2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POINT (578433.855 6059672.066) |
taskai.drop_duplicates('stop_name', inplace=True)
taskai.reset_index(inplace=True)
taskai.head(3)
# rezultatas - 706 stoteles
index | OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POINT (568773.727 6056951.539) |
1 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POINT (583463.063 6065751.175) |
2 | 2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POINT (578433.855 6059672.066) |
# Sukuriame atskira buferini sluoksni/lentele, kuriame priskirsime buferi
buf = taskai
buf.head(3)
index | OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POINT (568773.727 6056951.539) |
1 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POINT (583463.063 6065751.175) |
2 | 2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POINT (578433.855 6059672.066) |
# buf sluoksniui/lentai paskaiciuojame 500 metru buferi
buf['buffer_geometry'] = buf.geometry.buffer(500)
buf.head(3)
index | OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | geometry | buffer_geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POINT (568773.727 6056951.539) | POLYGON ((569273.727 6056951.539, 569271.320 6... |
1 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POINT (583463.063 6065751.175) | POLYGON ((583963.063 6065751.175, 583960.655 6... |
2 | 2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POINT (578433.855 6059672.066) | POLYGON ((578933.855 6059672.066, 578931.448 6... |
# istriname taskinio sluoksnio GEOMETRY stulpeli, kad nesumaisytu pandoms plotinant
buf = buf.drop(columns=['geometry'])
buf.reset_index(inplace=True)
buf
level_0 | index | OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | buffer_geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POLYGON ((569273.727 6056951.539, 569271.320 6... |
1 | 1 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POLYGON ((583963.063 6065751.175, 583960.655 6... |
2 | 2 | 2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POLYGON ((578933.855 6059672.066, 578931.448 6... |
3 | 3 | 3 | 4 | 16291 | None | Geležinio Vilko st. | None | 54.75762 | 25.27183 | https://www.stops.lt/vilnius/index.html#stop/2145 | None | None | POLYGON ((582360.929 6069776.295, 582358.521 6... |
4 | 4 | 5 | 6 | 8258 | None | Kaimelio žiedas | None | 54.73141 | 25.24430 | https://www.stops.lt/vilnius/index.html#stop/3890 | None | None | POLYGON ((580640.799 6066827.662, 580638.391 6... |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
701 | 701 | 1413 | 1414 | 814 | None | Panorama | None | 54.69915 | 25.25704 | https://www.stops.lt/vilnius/index.html#stop/0317 | None | None | POLYGON ((581525.598 6063252.164, 581523.190 6... |
702 | 702 | 1415 | 1416 | 6397 | None | Švietimo akademija | None | 54.69830 | 25.26121 | https://www.stops.lt/vilnius/index.html#stop/0318 | None | None | POLYGON ((581796.071 6063162.392, 581793.663 6... |
703 | 703 | 1421 | 1422 | 632 | None | Džocharo Dudajevo skv. | None | 54.69583 | 25.25609 | https://www.stops.lt/vilnius/index.html#stop/0309 | None | None | POLYGON ((581470.978 6062881.596, 581468.570 6... |
704 | 704 | 1423 | 1424 | 1116 | None | Vytauto st. | None | 54.69117 | 25.25643 | https://www.stops.lt/vilnius/index.html#stop/0305 | None | None | POLYGON ((581502.176 6062363.392, 581499.768 6... |
705 | 705 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | None | POLYGON ((582502.179 6063868.678, 582499.772 6... |
706 rows × 13 columns
# plotinam buferius
ax = buf['buffer_geometry'].plot(figsize=(20, 20))
# plotinam buferius su stoteliu taskais
ax = buf['buffer_geometry'].plot(figsize=(15,15), color = 'lightblue')
taskai['geometry'].plot(ax = ax, color = 'red')
plt.title('300 m buferis')
Text(0.5, 1.0, '300 m buferis')
# Isikeliu 2021 metu preliminarius gyventoju surasymo duomenis gride ir Vilniaus ribas
grid2011 = gpd.read_file('B:\\...\\Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\grid2021\\grid2011.gpkg')
vilnius = gpd.read_file('B:\\...\\Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\grid2021\\Vilnius.gpkg')
gx = grid2011.plot(figsize=(7, 7), alpha=0.5, edgecolor='k')
vilnius['geometry'].plot(ax = gx, color = 'lightgreen', alpha = 0.5)
buf['buffer_geometry'].plot(ax = gx, color = 'yellow')
plt.title('Gyventoju surasymo tinklelis, Vilniaus miesto ribos, stoteliu buferiai')
Text(0.5, 1.0, 'Gyventoju surasymo tinklelis, Vilniaus miesto ribos, stoteliu buferiai')
grid2011.head(5)
OBJECTID | GRID_ID | GYV_SK | VYRU_SK | MOTERU_SK | AMZIUS0_14 | AMZIUS15_6 | AMZIUS65 | AUKSTASIS | AUKSTESNYS | ... | GYV_TANKIS | BUSTU_SK | BUTAS1 | BUTAS2 | BUTAS_ | VANDUO | VONIA | wc | GYV_BUSTAS | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 12794 | 100x100mX56690Y60549 | < 10 | < 10 | 0 | 0 | < 10 | 0 | 0 | 0 | ... | Duomenys neteikiami | < 4 | < 4 | 0 | 0 | 0 | 0 | 0 | <= 3 | MULTIPOLYGON (((566900.000 6054991.045, 566900... |
1 | 12795 | 100x100mX56730Y60551 | < 10 | < 10 | < 10 | < 10 | < 10 | 0 | 0 | 0 | ... | Duomenys neteikiami | < 4 | < 4 | 0 | 0 | 0 | 0 | 0 | <= 3 | MULTIPOLYGON (((567300.000 6055191.045, 567300... |
2 | 12796 | 100x100mX56800Y60548 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | < 4 | 0 | < 4 | 0 | 0 | 0 | 0 | 0 | MULTIPOLYGON (((568000.000 6054891.045, 568000... |
3 | 12797 | 100x100mX56860Y60543 | 16 | < 10 | < 10 | < 10 | 10 - 19 | < 10 | < 10 | 0 | ... | 1600 | < 4 | < 4 | 0 | 0 | 0 | 0 | 0 | <= 5 | MULTIPOLYGON (((568600.000 6054391.045, 568600... |
4 | 12798 | 100x100mX56860Y60549 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ... | 0 | < 4 | < 4 | 0 | 0 | 0 | 0 | 0 | 0 | MULTIPOLYGON (((568600.000 6054991.045, 568600... |
5 rows × 38 columns
buf = buf.set_geometry('buffer_geometry')
# vykdant Intersect, mete klaida apie jau egzistuojanti Level_0 indexa. Ji nudropinau
buf.drop(['level_0'], axis = 1, inplace = True)
buf
index | OBJECTID | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | parent_station | buffer_geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 1 | 9483 | None | 1-asis Lentvaris | None | 54.64437 | 25.06552 | https://www.stops.lt/vilnius/index.html#stop/3906 | None | None | POLYGON ((569273.727 6056951.539, 569271.320 6... |
1 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | None | POLYGON ((583963.063 6065751.175, 583960.655 6... |
2 | 2 | 3 | 9102 | None | Bijūnų st. | None | 54.66740 | 25.21588 | https://www.stops.lt/vilnius/index.html#stop/1631 | None | None | POLYGON ((578933.855 6059672.066, 578931.448 6... |
3 | 3 | 4 | 16291 | None | Geležinio Vilko st. | None | 54.75762 | 25.27183 | https://www.stops.lt/vilnius/index.html#stop/2145 | None | None | POLYGON ((582360.929 6069776.295, 582358.521 6... |
4 | 5 | 6 | 8258 | None | Kaimelio žiedas | None | 54.73141 | 25.24430 | https://www.stops.lt/vilnius/index.html#stop/3890 | None | None | POLYGON ((580640.799 6066827.662, 580638.391 6... |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
701 | 1413 | 1414 | 814 | None | Panorama | None | 54.69915 | 25.25704 | https://www.stops.lt/vilnius/index.html#stop/0317 | None | None | POLYGON ((581525.598 6063252.164, 581523.190 6... |
702 | 1415 | 1416 | 6397 | None | Švietimo akademija | None | 54.69830 | 25.26121 | https://www.stops.lt/vilnius/index.html#stop/0318 | None | None | POLYGON ((581796.071 6063162.392, 581793.663 6... |
703 | 1421 | 1422 | 632 | None | Džocharo Dudajevo skv. | None | 54.69583 | 25.25609 | https://www.stops.lt/vilnius/index.html#stop/0309 | None | None | POLYGON ((581470.978 6062881.596, 581468.570 6... |
704 | 1423 | 1424 | 1116 | None | Vytauto st. | None | 54.69117 | 25.25643 | https://www.stops.lt/vilnius/index.html#stop/0305 | None | None | POLYGON ((581502.176 6062363.392, 581499.768 6... |
705 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | None | POLYGON ((582502.179 6063868.678, 582499.772 6... |
706 rows × 12 columns
susikirtimas = gpd.overlay(buf, grid2011, how='intersection')
susikirtimas.head(3)
index | OBJECTID_1 | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | ... | GYV_TANKIS | BUSTU_SK | BUTAS1 | BUTAS2 | BUTAS_ | VANDUO | VONIA | wc | GYV_BUSTAS | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582989.955 6065591.045, 582984.593 6... |
1 | 141 | 142 | 5839 | None | Naujakiemio st. | None | 54.72387 | 25.28144 | https://www.stops.lt/vilnius/index.html#stop/2012 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582913.370 6065691.045, 582901.597 6... |
2 | 1329 | 1330 | 5846 | None | Jono Kazlausko st. | None | 54.72145 | 25.28502 | https://www.stops.lt/vilnius/index.html#stop/2014 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582900.000 6065591.045, 582900.000 6... |
3 rows × 49 columns
list(susikirtimas.columns)
['index', 'OBJECTID_1', 'stop_id', 'stop_code', 'stop_name', 'stop_desc', 'stop_lat', 'stop_lon', 'stop_url', 'location_type', 'parent_station', 'OBJECTID_2', 'GRID_ID', 'GYV_SK', 'VYRU_SK', 'MOTERU_SK', 'AMZIUS0_14', 'AMZIUS15_6', 'AMZIUS65', 'AUKSTASIS', 'AUKSTESNYS', 'VIDURINIS', 'PAGRINDINI', 'DARBAS', 'VERSLAS', 'PENSIJA', 'ISLAIKOMAS', 'SUSITUOKE', 'ISSITUOKE', 'NASLIAI', 'NESUSITUOK', 'LT_K', 'POL_K', 'RU_K', 'KITA_K', 'EN_MK', 'POL_MK', 'RU_MK', 'KITA_MK', 'GYV_TANKIS', 'BUSTU_SK', 'BUTAS1', 'BUTAS2', 'BUTAS_', 'VANDUO', 'VONIA', 'wc', 'GYV_BUSTAS', 'geometry']
#ax = vilnius['geometry'].plot( color = 'red', alpha = 0.5)
susikirtimas.plot(figsize=(15, 15), alpha=0.5, edgecolor='k')
#buf['buffer_geometry'].plot(ax = gx, color = 'yellow')
<AxesSubplot:>
# sluoksnio eksportas i GIS formata
import fiona
susikirtimas.to_file('susikirtim34.geojson', driver='GeoJSON')
# "susikirtimas" gyventoju surasymo duomenyse pasitaike reiksmiu "<5",
# kas veliau trukdys atlikti statistikos skaiciavimus. Reikalimgas duomenu pravalymas
# 1. Susikuriu susikirtimo sluoksnio kopija, kurioje vykdysiu valymus
susikirtimas2 = susikirtimas
susikirtimas2.head(3)
index | OBJECTID_1 | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | ... | GYV_TANKIS | BUSTU_SK | BUTAS1 | BUTAS2 | BUTAS_ | VANDUO | VONIA | wc | GYV_BUSTAS | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582989.955 6065591.045, 582984.593 6... |
1 | 141 | 142 | 5839 | None | Naujakiemio st. | None | 54.72387 | 25.28144 | https://www.stops.lt/vilnius/index.html#stop/2012 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582913.370 6065691.045, 582901.597 6... |
2 | 1329 | 1330 | 5846 | None | Jono Kazlausko st. | None | 54.72145 | 25.28502 | https://www.stops.lt/vilnius/index.html#stop/2014 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582900.000 6065591.045, 582900.000 6... |
3 rows × 49 columns
list(susikirtimas2.columns)
['index', 'OBJECTID_1', 'stop_id', 'stop_code', 'stop_name', 'stop_desc', 'stop_lat', 'stop_lon', 'stop_url', 'location_type', 'parent_station', 'OBJECTID_2', 'GRID_ID', 'GYV_SK', 'VYRU_SK', 'MOTERU_SK', 'AMZIUS0_14', 'AMZIUS15_6', 'AMZIUS65', 'AUKSTASIS', 'AUKSTESNYS', 'VIDURINIS', 'PAGRINDINI', 'DARBAS', 'VERSLAS', 'PENSIJA', 'ISLAIKOMAS', 'SUSITUOKE', 'ISSITUOKE', 'NASLIAI', 'NESUSITUOK', 'LT_K', 'POL_K', 'RU_K', 'KITA_K', 'EN_MK', 'POL_MK', 'RU_MK', 'KITA_MK', 'GYV_TANKIS', 'BUSTU_SK', 'BUTAS1', 'BUTAS2', 'BUTAS_', 'VANDUO', 'VONIA', 'wc', 'GYV_BUSTAS', 'geometry']
# paziuriu kiekvieno stulpelio unikalius.
susikirtimas2['MOTERU_SK'].value_counts().sort_values(ascending = False)
# rezultate matosi, kad yra neskaitines reiksmes, pvz <10, 10-19 ir pan.
< 10 10177 10 - 19 3202 0 1469 20 - 29 1440 30 - 39 857 50 - 59 841 40 - 49 788 60 - 69 720 80 - 89 619 70 - 79 618 90 - 99 529 110 - 119 484 100 - 109 445 130 - 139 435 120 - 129 390 140 - 149 288 150 - 159 264 180 - 189 261 160 - 169 214 190 - 199 213 170 - 179 184 210 - 219 174 200 - 209 164 220 - 229 129 230 - 239 118 250 - 259 106 240 - 249 101 270 - 279 94 260 - 269 79 300 - 309 59 280 - 289 56 290 - 299 55 330 - 339 36 350 - 359 36 320 - 329 31 340 - 349 23 360 - 369 18 310 - 319 17 380 - 389 13 420 - 429 11 430 - 439 8 410 - 419 8 400 - 409 8 370 - 379 8 590 - 599 7 390 - 399 6 510 - 519 5 450 - 459 4 Name: MOTERU_SK, dtype: int64
# pakeisime duomenis: <10 -> 10, is 10-19 -> 14 (vidutine reiksme truncate-inta)
keitimui = susikirtimas2
keitimui
index | OBJECTID_1 | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | ... | GYV_TANKIS | BUSTU_SK | BUTAS1 | BUTAS2 | BUTAS_ | VANDUO | VONIA | wc | GYV_BUSTAS | geometry | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582989.955 6065591.045, 582984.593 6... |
1 | 141 | 142 | 5839 | None | Naujakiemio st. | None | 54.72387 | 25.28144 | https://www.stops.lt/vilnius/index.html#stop/2012 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582913.370 6065691.045, 582901.597 6... |
2 | 1329 | 1330 | 5846 | None | Jono Kazlausko st. | None | 54.72145 | 25.28502 | https://www.stops.lt/vilnius/index.html#stop/2014 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582900.000 6065591.045, 582900.000 6... |
3 | 1337 | 1338 | 5127 | None | Verkių st. | None | 54.71687 | 25.29360 | https://www.stops.lt/vilnius/index.html#stop/0807 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((582965.410 6065591.045, 582992.131 6... |
4 | 1343 | 1344 | 5022 | None | Autobusų parkas | None | 54.72092 | 25.29589 | https://www.stops.lt/vilnius/index.html#stop/5177 | None | ... | Duomenys neteikiami | < 4 | 0 | 0 | < 4 | 0 | 0 | 0 | <= 3 | POLYGON ((583000.000 6065600.920, 582994.504 6... |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
25807 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 1300 | 6 | 0 | < 4 | 4 | 4 | < 4 | 4 | 2 | POLYGON ((582000.000 6064091.045, 582000.000 6... |
25808 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | Duomenys neteikiami | < 4 | < 4 | 0 | 0 | 0 | < 4 | < 4 | <= 3 | POLYGON ((582100.000 6063891.045, 582100.000 6... |
25809 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 3100 | 9 | < 4 | 6 | 0 | 6 | 6 | 6 | 3 | POLYGON ((582100.000 6064191.045, 582100.000 6... |
25810 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 3500 | 12 | < 4 | 4 | 5 | 4 | 11 | 6 | 3 | POLYGON ((582200.000 6064091.045, 582200.000 6... |
25811 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 5600 | 15 | 10 | 0 | 5 | 13 | 13 | 13 | 4 | POLYGON ((582268.898 6064291.045, 582279.964 6... |
25812 rows × 49 columns
def keitimasm(row):
if '-' in row['MOTERU_SK']:
sp = row['MOTERU_SK'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['MOTERU_SK']:
rep = row['MOTERU_SK'].replace('<','')
return int(rep)
else:
return int(row['MOTERU_SK'])
#pritaikau funkcija moteru stulpeliui
keitimui['MOTERU_SK2'] = keitimui.apply(keitimasm, axis = 'columns')
# paziuriu ar pavyko pakeisti:
keitimui.loc[:, ['MOTERU_SK', 'MOTERU_SK2']]
MOTERU_SK | MOTERU_SK2 | |
---|---|---|
0 | 0 | 0.0 |
1 | 0 | 0.0 |
2 | 0 | 0.0 |
3 | 0 | 0.0 |
4 | 0 | 0.0 |
... | ... | ... |
25807 | < 10 | 10.0 |
25808 | < 10 | 10.0 |
25809 | 10 - 19 | 14.0 |
25810 | 20 - 29 | 24.0 |
25811 | 20 - 29 | 24.0 |
25812 rows × 2 columns
# moterims
def keitimasm(row):
if '-' in row['MOTERU_SK']:
sp = row['MOTERU_SK'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['MOTERU_SK']:
rep = row['MOTERU_SK'].replace('<','')
return int(rep)
else:
return int(row['MOTERU_SK'])
susikirtimas2['MOTERU_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
# gyventoju skaiciaus stulpeliui
def keitimasm(row):
if '-' in row['GYV_SK']:
sp = row['GYV_SK'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['GYV_SK']:
rep = row['GYV_SK'].replace('<','')
return int(rep)
else:
return int(row['GYV_SK'])
susikirtimas2['GYV_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
# vyrams
def keitimasm(row):
if '-' in row['VYRU_SK']:
sp = row['VYRU_SK'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['VYRU_SK']:
rep = row['VYRU_SK'].replace('<','')
return int(rep)
else:
return int(row['VYRU_SK'])
susikirtimas2['VYRU_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
# AMZIUS0_14
def keitimasm(row):
if '-' in row['AMZIUS0_14']:
sp = row['AMZIUS0_14'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['AMZIUS0_14']:
rep = row['AMZIUS0_14'].replace('<','')
return int(rep)
else:
return int(row['AMZIUS0_14'])
susikirtimas2['AMZIUS0_14_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
# AMZIUS15_6
def keitimasm(row):
if '-' in row['AMZIUS15_6']:
sp = row['AMZIUS15_6'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['AMZIUS15_6']:
rep = row['AMZIUS15_6'].replace('<','')
return int(rep)
else:
return int(row['AMZIUS15_6'])
susikirtimas2['AMZIUS15_6_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
# AMZIUS65
def keitimasm(row):
if '-' in row['AMZIUS65']:
sp = row['AMZIUS65'].split()
vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
return vidurkis
elif '<' in row['AMZIUS65']:
rep = row['AMZIUS65'].replace('<','')
return int(rep)
else:
return int(row['AMZIUS65'])
susikirtimas2['AMZIUS65_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
susikirtimas2
index | OBJECTID_1 | stop_id | stop_code | stop_name | stop_desc | stop_lat | stop_lon | stop_url | location_type | ... | VONIA | wc | GYV_BUSTAS | geometry | MOTERU_SK2 | GYV_SK2 | VYRU_SK2 | AMZIUS0_14_2 | AMZIUS15_6_2 | AMZIUS65_2 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 | 2 | 9560 | None | AP vartai | None | 54.72120 | 25.29556 | https://www.stops.lt/vilnius/index.html#stop/0018 | None | ... | 0 | 0 | <= 3 | POLYGON ((582989.955 6065591.045, 582984.593 6... | 0.0 | 10 | 10.0 | 0.0 | 10.0 | 0.0 |
1 | 141 | 142 | 5839 | None | Naujakiemio st. | None | 54.72387 | 25.28144 | https://www.stops.lt/vilnius/index.html#stop/2012 | None | ... | 0 | 0 | <= 3 | POLYGON ((582913.370 6065691.045, 582901.597 6... | 0.0 | 10 | 10.0 | 0.0 | 10.0 | 0.0 |
2 | 1329 | 1330 | 5846 | None | Jono Kazlausko st. | None | 54.72145 | 25.28502 | https://www.stops.lt/vilnius/index.html#stop/2014 | None | ... | 0 | 0 | <= 3 | POLYGON ((582900.000 6065591.045, 582900.000 6... | 0.0 | 10 | 10.0 | 0.0 | 10.0 | 0.0 |
3 | 1337 | 1338 | 5127 | None | Verkių st. | None | 54.71687 | 25.29360 | https://www.stops.lt/vilnius/index.html#stop/0807 | None | ... | 0 | 0 | <= 3 | POLYGON ((582965.410 6065591.045, 582992.131 6... | 0.0 | 10 | 10.0 | 0.0 | 10.0 | 0.0 |
4 | 1343 | 1344 | 5022 | None | Autobusų parkas | None | 54.72092 | 25.29589 | https://www.stops.lt/vilnius/index.html#stop/5177 | None | ... | 0 | 0 | <= 3 | POLYGON ((583000.000 6065600.920, 582994.504 6... | 0.0 | 10 | 10.0 | 0.0 | 10.0 | 0.0 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
25807 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | < 4 | 4 | 2 | POLYGON ((582000.000 6064091.045, 582000.000 6... | 10.0 | 13 | 10.0 | 0.0 | 10.0 | 10.0 |
25808 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | < 4 | < 4 | <= 3 | POLYGON ((582100.000 6063891.045, 582100.000 6... | 10.0 | 10 | 10.0 | 0.0 | 10.0 | 10.0 |
25809 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 6 | 6 | 3 | POLYGON ((582100.000 6064191.045, 582100.000 6... | 14.0 | 31 | 14.0 | 10.0 | 24.0 | 10.0 |
25810 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 11 | 6 | 3 | POLYGON ((582200.000 6064091.045, 582200.000 6... | 24.0 | 35 | 14.0 | 10.0 | 24.0 | 10.0 |
25811 | 1425 | 1426 | 5462 | None | Linkmenų st. | None | 54.70453 | 25.27236 | https://www.stops.lt/vilnius/index.html#stop/0702 | None | ... | 13 | 13 | 4 | POLYGON ((582268.898 6064291.045, 582279.964 6... | 24.0 | 56 | 24.0 | 14.0 | 34.0 | 10.0 |
25812 rows × 55 columns
list(susikirtimas2.columns)
['index', 'OBJECTID_1', 'stop_id', 'stop_code', 'stop_name', 'stop_desc', 'stop_lat', 'stop_lon', 'stop_url', 'location_type', 'parent_station', 'OBJECTID_2', 'GRID_ID', 'GYV_SK', 'VYRU_SK', 'MOTERU_SK', 'AMZIUS0_14', 'AMZIUS15_6', 'AMZIUS65', 'AUKSTASIS', 'AUKSTESNYS', 'VIDURINIS', 'PAGRINDINI', 'DARBAS', 'VERSLAS', 'PENSIJA', 'ISLAIKOMAS', 'SUSITUOKE', 'ISSITUOKE', 'NASLIAI', 'NESUSITUOK', 'LT_K', 'POL_K', 'RU_K', 'KITA_K', 'EN_MK', 'POL_MK', 'RU_MK', 'KITA_MK', 'GYV_TANKIS', 'BUSTU_SK', 'BUTAS1', 'BUTAS2', 'BUTAS_', 'VANDUO', 'VONIA', 'wc', 'GYV_BUSTAS', 'geometry', 'MOTERU_SK2', 'GYV_SK2', 'VYRU_SK2', 'AMZIUS0_14_2', 'AMZIUS15_6_2', 'AMZIUS65_2']
eksportui_intersectas = susikirtimas2.loc[:, ['index',
'stop_id',
'stop_code',
'stop_name',
'stop_desc',
'stop_lat',
'stop_lon',
'stop_url',
'GRID_ID',
'GYV_SK',
'VYRU_SK',
'MOTERU_SK',
'AMZIUS0_14',
'AMZIUS15_6',
'AMZIUS65',
'AUKSTASIS',
'AUKSTESNYS',
'VIDURINIS',
'PAGRINDINI',
'DARBAS',
'VERSLAS',
'PENSIJA',
'geometry',
'MOTERU_SK2',
'GYV_SK2',
'VYRU_SK2',
'AMZIUS0_14_2',
'AMZIUS15_6_2',
'AMZIUS65_2']]
# issieksportuoju kaip csv isidejimui i Power BI.
eksportui_intersectas.to_csv('susikirtimas3.csv')
# https://youtu.be/PHxYNGo8NcI
# Prisijungiu prie duomenu is MYSQL DB ir atsidarau lentele "gridas_klasifikavimui"
import mysql.connector
import pandas as pd
mydb = mysql.connector.connect(
host = 'localhost',
port = '3317',
user = 'root',
password = '***********'
)
cursor = mydb.cursor()
cursor.execute('USE projektas')
df = pd.read_sql('SELECT GRID_ID, mokyklos, universitetas, darzeliai, gydymo_istaiga, prioritetas from gridas_klasifikavimui', con = mydb)
df
GRID_ID | mokyklos | universitetas | darzeliai | gydymo_istaiga | prioritetas | |
---|---|---|---|---|---|---|
0 | 100x100mX57780Y60605 | 1 | 1 | 1 | 1 | 4 |
1 | 100x100mX57790Y60601 | 1 | 1 | 1 | 1 | 4 |
2 | 100x100mX57790Y60605 | 1 | 1 | 1 | 1 | 4 |
3 | 100x100mX57790Y60606 | 1 | 1 | 1 | 1 | 4 |
4 | 100x100mX57800Y60601 | 1 | 1 | 1 | 1 | 4 |
... | ... | ... | ... | ... | ... | ... |
12625 | 100x100mX59540Y60623 | 0 | 0 | 0 | 0 | 0 |
12626 | 100x100mX59540Y60624 | 0 | 0 | 0 | 0 | 0 |
12627 | 100x100mX59540Y60625 | 0 | 0 | 0 | 0 | 0 |
12628 | 100x100mX59540Y60627 | 0 | 0 | 0 | 0 | 0 |
12629 | 100x100mX59540Y60628 | 0 | 0 | 0 | 0 | 0 |
12630 rows × 6 columns
# independable variable
inputs = df.drop(['prioritetas','GRID_ID'], axis = 'columns')
target = df['prioritetas']
inputs.head(3)
mokyklos | universitetas | darzeliai | gydymo_istaiga | |
---|---|---|---|---|
0 | 1 | 1 | 1 | 1 |
1 | 1 | 1 | 1 | 1 |
2 | 1 | 1 | 1 | 1 |
target.head(3)
0 4 1 4 2 4 Name: prioritetas, dtype: int64
#train
from sklearn import tree
model = tree.DecisionTreeClassifier()
model.fit(inputs, target)
DecisionTreeClassifier()
# score
model.score(inputs, target)
1.0
# predict
model.predict([[0,0,0,0]])
array([0], dtype=int64)
model.predict([[0,0,1,0]])
array([1], dtype=int64)
model.predict([[1,1,0,0]])
array([2], dtype=int64)
model.predict([[1,1,1,0]])
array([3], dtype=int64)
model.predict([[1,1,1,1]])
array([4], dtype=int64)
prediction = model.predict(inputs)
df['Prediction'] = prediction
df
GRID_ID | mokyklos | universitetas | darzeliai | gydymo_istaiga | prioritetas | Prediction | |
---|---|---|---|---|---|---|---|
0 | 100x100mX57780Y60605 | 1 | 1 | 1 | 1 | 4 | 4 |
1 | 100x100mX57790Y60601 | 1 | 1 | 1 | 1 | 4 | 4 |
2 | 100x100mX57790Y60605 | 1 | 1 | 1 | 1 | 4 | 4 |
3 | 100x100mX57790Y60606 | 1 | 1 | 1 | 1 | 4 | 4 |
4 | 100x100mX57800Y60601 | 1 | 1 | 1 | 1 | 4 | 4 |
... | ... | ... | ... | ... | ... | ... | ... |
12625 | 100x100mX59540Y60623 | 0 | 0 | 0 | 0 | 0 | 0 |
12626 | 100x100mX59540Y60624 | 0 | 0 | 0 | 0 | 0 | 0 |
12627 | 100x100mX59540Y60625 | 0 | 0 | 0 | 0 | 0 | 0 |
12628 | 100x100mX59540Y60627 | 0 | 0 | 0 | 0 | 0 | 0 |
12629 | 100x100mX59540Y60628 | 0 | 0 | 0 | 0 | 0 | 0 |
12630 rows × 7 columns
df.to_csv('prediction.csv')