departure_stats-between-merge.py

#!/usr/bin/env python3.9
# -*- coding: utf-8 -*-

import pandas

names = ["a", "a_ifopt", "b", "b_ifopt", "count"]

vorher = pandas.read_csv("dep_stats_vorher.csv", header=None, names=names, sep=";", encoding="utf-8", dtype=str)
nachher = pandas.read_csv("dep_stats_nachher.csv", header=None, names=names, sep=";", encoding="utf-8", dtype=str)

vorher['key'] = vorher[['a_ifopt', 'b_ifopt']].apply(lambda x: '~'.join(sorted(x)), axis=1)
nachher['key'] = nachher[['a_ifopt', 'b_ifopt']].apply(lambda x: '~'.join(sorted(x)), axis=1)

merged = pandas.merge(vorher, nachher, on="key", how="outer")
merged['a_y'].fillna(merged['a_x'], inplace=True)
merged['a_ifopt_y'].fillna(merged['a_ifopt_x'], inplace=True)
merged['b_y'].fillna(merged['b_x'], inplace=True)
merged['b_ifopt_y'].fillna(merged['b_ifopt_x'], inplace=True)
merged['count_x'].fillna(0, inplace=True)
merged['count_y'].fillna(0, inplace=True)

merged.drop([col for col in merged.columns if col.endswith('_x') and col != 'count_x'], axis=1, inplace=True)
merged.rename(columns={col: col[:-2] for col in merged.columns if col.endswith('_y') and col != 'count_y'}, inplace=True)
merged = merged[merged['a_ifopt'] != merged['b_ifopt']]
merged = merged[['key', 'a', 'a_ifopt', 'b', 'b_ifopt', 'count_x', 'count_y']]

stops = pandas.read_csv("stops.csv", header='infer', sep=";", encoding="utf-8", dtype=str)

merged['a_x'] = merged['a_ifopt'].apply(lambda m_a_ifopt: stops[stops['stopifopt'] == m_a_ifopt]['X'].iloc[0])
merged['a_y'] = merged['a_ifopt'].apply(lambda m_a_ifopt: stops[stops['stopifopt'] == m_a_ifopt]['Y'].iloc[0])
merged['b_x'] = merged['b_ifopt'].apply(lambda m_b_ifopt: stops[stops['stopifopt'] == m_b_ifopt]['X'].iloc[0])
merged['b_y'] = merged['b_ifopt'].apply(lambda m_b_ifopt: stops[stops['stopifopt'] == m_b_ifopt]['Y'].iloc[0])

merged.to_csv("merged.csv", sep=";", encoding="utf-8")

stops.py

#!/usr/bin/env python3.9
# -*- coding: utf-8 -*-

from csv import writer
from sqlalchemy.orm import joinedload

from DINO2 import Database
from DINO2.model import *

db = Database()
session = db.Session()

rows = [("version", "stopid", "stopname", "placename", "stopifopt", "X", "Y")]
q = session.query(location.Stop)

for stop in q.all():
    rows.append((stop.version_id, stop.id, stop.name, stop.place, stop.ifopt, stop.pos_x, stop.pos_y))

with open("stops.csv", 'w', encoding='utf-8') as f:
    writer(f, delimiter=";", lineterminator='\n').writerows(rows)