TDLR: Python program, amely csv-fájlt olvas, és egy felhasználó által definiált config.json fájlt használ az SQL utasítások futtatásához. > az adatokon. A felhasználási példa a következő lenne:

python3 main.py --source ships.csv --config hamburg_bremerhaven

Motiváció

A gépi tanulást szeretnénk használni egy hajó becsült érkezésének előrejelzésére (ez a példa a Bremerhaven és Hamburg közötti útvonalra). Nagy adatkészlettel rendelkezünk gépi tanulási modellünk betanításához. Adatkészletünk a számítógépek által (a hajókon) pár percenként továbbított helyzet- és útinformációkat tartalmaz:

ships.csv:
TripID (ID of the trip)
StartTime (Ttrip start)
StartLatitude (Latitude at the start of the trip)
StartLongitude (Longitude at the start of the trip)
EndTime (Trip end)
EndLatitude (Latitude at the end of the trip)
EndLongitude (Longitude at the end of the trip)
PositionID (Id of the transmitted position)
Time (Current time of transmission)
Latitude (Current Latitude)
Longitude (Current Longitude)

Mivel minket csak ateljesés normál utak érdekelnek, amelyek Bremerhavenből Hamburgba költöznek, a nem kívánt, hiányos és abnormális utakat ki kell szűrni.

Import és érvek

Szűrés konfigurációkkal

Ebben a példában olyan hajókat keresünk, amelyek szokásos módon haladnak Bremerhavenből Hamburgba, mi van, ha legközelebb Felixstowe-ból Rotterdamba tartó hajókra lesz szükségünk? Ezért kell az adattisztításhoz konfigurációkat használnunk, amelyek így nézhetnek ki:

(Újrafelhasználható) szűrők:

  • area_restrictions.sql az adott területet elhagyó utazások kiszűréséhez
  • start_harbor.sql, hogy kiszűrje azokat az utazásokat, amelyek a választott kikötőhelyünk területén kívül indulnak (ez a példa: Bremerhaven)
  • Az end_harbor.sql a kiválasztott végkikötőn kívül végződő utak kiszűréséhez (ez a példa: Hamburg)

Ezenkívül egy új „travel_time” oszlopot szeretnénk az adatainkhoz hozzáfűzni, amely tartalmazza a kezdési időpont és a befejezési időpont közötti különbséget. Ezt a lehetőséget hívjuk

  • addcol_travel_time.sql

Tehát a Bremerhaven_Hamburg konfigurációs fájlja így nézhet ki:

Config file made for Bremerhaven_Hamburg
{
     "area_restrictions" :
     {
          "north" : 54.58,
          "south" : 52.5, 
          "east" : 11.0,
          "west" : 6.5
     },
     "start_harbor" :
     {
          "latitude" : 53.59,
          "longitude" : 8.5,
          "latTolerance" : 0.1,
          "lonTolerance" : 0.1
     },
     "end_harbor" :
     {
          "latitude" : 53.53,
          "longitude" : 9.98,
          "latTolerance" : 0.1,
          "lonTolerance" : 0.1
     },
     "addcol_travel_time": {}
}

A következő fejezetben a szűrőink sql-utasításait fogjuk összeállítani úgy, hogy a konfigurációs fájlban megadott paramétereket tudjuk használni.

Az SQL utasítások

Az exmaple konfigurációs fájlban a szűrőket paraméterekkel láttuk el, amelyeket a python programunkban elemzünk, és az általunk készített sql fájlokkal együtt használjuk őket:

-- sql/area_restrictions.sql
-- area_restrictions: {north, south, east, west}
-- Delete trips which move outside of restricted area
DELETE FROM
    DATA
WHERE
    TripID IN (
        SELECT
            TripID
        FROM
            DATA,
            var
        WHERE
            Latitude > var.north
            OR Latitude < var.south
            OR Longitude > var.east
            OR Longitude < var.west
        GROUP BY
            TripID
    );
-- sql/addcol_travel_time.sql
-- Add new column that shows the duration of the trip in HH:MM format 

ALTER TABLE DATA ADD COLUMN TravelTime DATETIME;
UPDATE  DATA SET TravelTime = time(strftime('%s', EndTime) - strftime('%s', StartTime),'unixepoch');

Az utolsó szűrő, amelyet alkalmazni fogunk, a start_harbor.sql, amely kiszűri azokat az utakat, amelyek nem a választott kikötőben (ebben az esetben Bremerhavenben) indulnak. A szélességi és hosszúsági értékeket használjuk, és toleranciát adunk a pontatlan mérések figyelembevételéhez:

-- sql/start_harbor.sql
-- start_harbor: {latitude, longitude, latTolerance, lonTolerance}
-- Delete trips which start outside the chosen harbor

sDELETE FROM
 DATA
WHERE
 TripID IN (
 SELECT
 TripID
 FROM
 DATA,
 var
 WHERE
 Latitude BETWEEN var.latitude - var.latTolerance AND var.latitude + var.latTolerance
 AND Longitude BETWEEN var.longitude - var.lonTolerance AND var.longitude + var.lonTolerance
 GROUP BY
 TripID
 );

Hasonlóképpen az end_harbor.sql szűrőt alkalmazzuk az olyan utazások eltávolítására, amelyek nem a mi választott célunkban (ebben az esetben Hamburgban) végződnek:

Mostanra elkészítettük az összes szükséges SQL utasítást az adatok szűréséhez és egy új oszlop létrehozásához az utazási időhöz. A következő fejezetben látni fogjuk, hogyan használhatjuk ezeket a szűrőket Python programunkban.

A Python program

A Python program beolvassa a ships.csv fájlt, és alkalmazza a konfigurációs fájlban megadott szűrőket. Ezután a szűrt adatokat egy új csv-fájlba írja, amelyet gépi tanulási célokra használhat fel. Íme egy példa a program futtatására:
python3 main.py — forrás ships.csv — config hamburg_bremerhaven
A program két argumentumot vesz fel: a forrásfájlt (ebben az esetben ships.csv) és a config-ot. fájl (ebben az esetben hamburg_bremerhaven.json). A program először beolvassa a konfigurációs fájlt, és elemzi az egyes szűrők paramétereit. Ezután alkalmazza a szűrőket az adatokra, és létrehozza az utazási idő új oszlopát. Végül a szűrt adatokat egy új csv fájlba írja. Az eredményül kapott csv-fájl gépi tanulási célokra használható egy hajó hamburgi érkezési idejének előrejelzésére.

Következtetés

Ebben a cikkben bemutattuk, hogyan lehet SQL-utasításokkal szűrni a hajópozíciók és utak adatkészletét, és hogyan hozhat létre egy Python-programot, amely alkalmazza ezeket a szűrőket a konfigurációs fájlban megadott paraméterekkel. A nem kívánt utazások kiszűrésével és az utazási idő új oszlopának létrehozásával az adatkészletet gépi tanulási célokra készítettük elő. Ez csak egy példa arra, hogy az SQL és a Python hogyan használható együtt adatok előfeldolgozására a gépi tanuláshoz.