Lösungen

> import duckdb
> import pandas as pd
>
> con = duckdb.connect(":memory:")
> con.register("kohorte", pd.read_csv("https://datascience.medizin.dev/data/kohorte.csv"))
> con.register("labor", pd.read_csv("https://datascience.medizin.dev/data/labor.csv"))
> con.register("vitalwerte", pd.read_csv("https://datascience.medizin.dev/data/vitalwerte.csv"))
> 

result = con.execute("""
    SELECT patient_id, alter, aufnahmegrund, crp_mg_l, sofa_score
    FROM kohorte
    WHERE crp_mg_l > 100 AND sofa_score < 5
    ORDER BY crp_mg_l DESC
""").df()
print(len(result), "patients")
print(result["aufnahmegrund"].value_counts())

-- pure SQL variant: count per admission reason directly in the query
SELECT aufnahmegrund, COUNT(*) AS n
FROM kohorte
WHERE crp_mg_l > 100 AND sofa_score < 5
GROUP BY aufnahmegrund
ORDER BY n DESC;

SELECT
    raucherstatus,
    COUNT(*)                              AS anzahl,
    ROUND(AVG(verweildauer_tage), 1)     AS verweildauer_mittel,
    ROUND(MEDIAN(crp_mg_l), 1)           AS crp_median,
    ROUND(AVG(verstorben_30d) * 100, 1) AS mortalitaet_pct
FROM kohorte
GROUP BY raucherstatus
ORDER BY mortalitaet_pct DESC;

result = con.execute("""
    SELECT raucherstatus,
           COUNT(*) AS anzahl,
           ROUND(AVG(verweildauer_tage), 1)     AS verweildauer_mittel,
           ROUND(MEDIAN(crp_mg_l), 1)           AS crp_median,
           ROUND(AVG(verstorben_30d) * 100, 1) AS mortalitaet_pct
    FROM kohorte
    GROUP BY raucherstatus
    ORDER BY mortalitaet_pct DESC
""").df()
print(result)

-- patients without a creatinine value
SELECT COUNT(*) AS ohne_kreatinin
FROM kohorte AS k
LEFT JOIN labor AS l ON k.patient_id = l.patient_id
WHERE l.kreatinin_mg_dl IS NULL;

-- creatinine > 2.0 mg/dl
SELECT COUNT(*) AS kreatinin_hoch
FROM kohorte AS k
LEFT JOIN labor AS l ON k.patient_id = l.patient_id
WHERE l.kreatinin_mg_dl > 2.0;

result = con.execute("""
    SELECT
        COUNT(*) FILTER (WHERE l.kreatinin_mg_dl IS NULL)    AS ohne_kreatinin,
        COUNT(*) FILTER (WHERE l.kreatinin_mg_dl > 2.0)      AS kreatinin_hoch
    FROM kohorte AS k
    LEFT JOIN labor AS l ON k.patient_id = l.patient_id
""").df()
print(result)

SELECT
    k.aufnahmegrund,
    COUNT(l.laktat_mmol_l)            AS n_laktat,
    ROUND(AVG(l.laktat_mmol_l), 2)   AS laktat_mittel
FROM kohorte AS k
LEFT JOIN labor AS l ON k.patient_id = l.patient_id
WHERE l.laktat_mmol_l IS NOT NULL
GROUP BY k.aufnahmegrund
HAVING COUNT(l.laktat_mmol_l) >= 30
ORDER BY laktat_mittel DESC;

SELECT
    k.patient_id,
    k.aufnahmegrund,
    l.laktat_mmol_l,
    v.herzfrequenz AS hf_tag1
FROM kohorte AS k
LEFT JOIN labor      AS l ON k.patient_id = l.patient_id
LEFT JOIN vitalwerte AS v ON k.patient_id = v.patient_id AND v.tag = 1
WHERE k.diabetes = 1
  AND l.laktat_mmol_l IS NOT NULL
  AND v.herzfrequenz IS NOT NULL
LIMIT 20;

result = con.execute("""
    SELECT COUNT(*) AS n_vollstaendig
    FROM kohorte AS k
    LEFT JOIN labor      AS l ON k.patient_id = l.patient_id
    LEFT JOIN vitalwerte AS v ON k.patient_id = v.patient_id AND v.tag = 1
    WHERE k.diabetes = 1
      AND l.laktat_mmol_l IS NOT NULL
      AND v.herzfrequenz  IS NOT NULL
""").df()
print(result)

WITH schwer_krank AS (
    SELECT patient_id, 'schwer' AS gruppe
    FROM kohorte WHERE sofa_score >= 7
),
rest AS (
    SELECT patient_id, 'leicht' AS gruppe
    FROM kohorte WHERE sofa_score < 7
),
alle AS (
    SELECT * FROM schwer_krank
    UNION ALL
    SELECT * FROM rest
)
SELECT
    a.gruppe,
    COUNT(*)                           AS n,
    ROUND(MEDIAN(l.kreatinin_mg_dl), 2) AS kreatinin_median,
    ROUND(MEDIAN(l.laktat_mmol_l), 2)   AS laktat_median
FROM alle AS a
LEFT JOIN labor AS l ON a.patient_id = l.patient_id
GROUP BY a.gruppe
ORDER BY kreatinin_median DESC;