Daten nach Datum aufsummieren

[viechdokter]

Hallo, ich bin relativ neu in Python und experimentiere gerade mit dem Lesen und Auswerten von Daten aus Excel-Tabellen mit openpyxl, insbesondere aus der Corona-Datenbank des RKI. Dabei gibt es eine Spalte für das Datum einer Meldung, eine andere für die Altersklasse, aus der die Meldung kommt, und eine weitere Spalte, in der die Fallzahlen stehen. Zum Beispiel:

Datum Altersklasse Fälle

2020/11/23 A35-50 2
2020/11/24 A35-50 1
.
.
.
2020/11/23 A60-70 1
usw.

Da die Tabelle nach Altersklassen geordnet ist, kann es am selben Tag in verschiedenen Altersklassen Fälle geben. Ein Datum kann also mehrfach verstreut in der Tabelle erscheinen. Ich möchte jetzt gerne die Fallzahlen eines einzelnen Tages aufsummieren, um sagen zu können, wieviele Fälle es an diesem Tag (z.B. 23.11.2020) insgesamt gegeben hat.

Ich weiß inzwischen, wie ich aus einer beliebigen Zelle mit openpyxl einen Wert auslesen kann, zum Beispiel habe ich einmal die Gesamtzahl der gemeldeten Fälle ausrechnen lassen und sie auch mal (mit datetime) automatisch einem Wochentag zuordnen lassen. Ich weiß nur nicht, wie ich Python beibringe, daß es, wenn ein Datum mehrfach vorkommt, die Fallzahlen dieses Tages aufaddiert und und diesem Datum zuordnet. Falls jemand eine Idee hat und mich auf den richtigen Weg schubsen kann, würde ich mich sehr freuen. Bitte auf Anfängernieveau. Ich kenne mich noch nicht mit Klassen, Objekten und ähnlichem aus. Vielen Dank schonmal!

[sparrow]

Daten in einer solchen Struktur verarbeitet man in Python in der Regl unter Zuhilfename der Bibliothek "Pandas", die liest auch gleich Excel-Dateien soviel ich weiß.
Die Lernkurve ist etwas steil, aber es lohnt sich.
Das hier iste in guter Punkt zum Starten.

[viechdokter]

Danke, Sparrow, das sieht allerdings wirklich kompliziert aus. Ich hatte gehofft, dass es irgendwie auf Anfängerniveau mit "gehe alle Zeilen durch, schaue, ob sich Daten in der Datumsspalte wiederholen und addiere die zugehörigen Fallzahlen aus der anderen Spalte in einer Variablen" geht.Aber ich bekomme nicht ganz meinen Kopf da herum, wie ich das anfange...

[Sirius3]

Jeder hält das Neue erstmal für kompliziert. Du musst bei Deienem Ansatz openpyxl kennen, wissen wie man an die Zelleninhalte kommt. Header prüfen, ein defaultdict erstellen, in einer Schleife alle Zeilen durchgehen und Werte addieren.
Bei Pandas ist das Abstraktionsniveau eben höher, und Du musst nur read_excel und groupby kenne.

[viechdokter]

Ich möchte es erst einmal ohne Pandas probieren, behalte diese Möglichkeit jedoch im Hinterkopf. Ich habe noch einmal über das Problem nachgedacht. Ich will versuchen, ein dictionary mit allen möglichen Datumsangaben von Anfangsdatum bis Enddatum als keys und anfangs einer 0 jeweils als value zu generieren. Dann in einer for-Schleife über alle keys zu iterieren und sie mit den Datumsangaben im Worksheet zu vergleichen. Wenn beide Datumsangaben übereinstimmen, soll die in derselben Zeile stehende Fallzahl zum value des entsprechenden keys im dictionary dazu addiert werden. Dann müsste man doch am Ende ein dictionary mit allen Fällen auf die Tage verteilt erhalten, oder?

[sparrow]

Wie es geht hat Sirius3 doch im Detail erklärt.
Mit einem defaultdict kannst du Anfangswerte definieren.

Ich würde es trotzdem mit Pandas machen.
Die investierte Zeit lohnt sich wirklich. Und dann sind es 3 Zeilen.

[viechdokter]

Ja, sorry, ich bin halt noch Neuling. Ich habe gestern über defaultdicts nachgelesen, doch leider wird in den meisten Python-Erklärtexten schon zuviel anderes Wissen um Begriffe vorausgesetzt. Das hat mich etwas verwirrt. Vielen "älteren" Pythonnutzern reichen ein, zwei Schlagworte aus, um ein Konzept zu beschreiben. Als Neuling fehlt einem da häufig Kontext. Also habe ich mir versucht, das ganze oben etwas genauer zu verdeutlichen.

[__blackjack__]

@viechdokter: Wenn man kein `collections.defaultdict` verwendet, kommt man um einen zusätzlichen Durchgang über die Daten herum wenn man `dict.get()` mit zweitem Argument verwendet: ``date_to_total[date] = date_to_total.get(date, 0) + value``. Wenn es das Datum noch nicht als Schlüssel gibt, wird 0 zurückgegeben, ansonsten der Wert der schon im Wörterbuch hinterlegt ist.

[viechdokter]

So... immer noch nicht der beste Pythonian Way, aber immerhin tut das Programm, was ich von ihm will:

Code: Alles auswählen

# Import `load_workbook` module from `openpyxl`
from openpyxl import load_workbook
import datetime
import matplotlib.pyplot as plt

#-------------------------------------------------------
# A progam to analyse data from the RKI corona database
# (c) viechdokter 2021
# ------------------------------------------------------
#
from openpyxl import load_workbook
import datetime
import matplotlib.pyplot as plt
#
# First we want to create a dictionary with all dates from beginning to end
# later we put the case counts into the dictionary
# create empty lists and dictionary to fill with data
print('... creating dictionary...')
date_list = []
null_list = []
date_dictionary = {}
# set a starting point
start_date = '01/01/20'
# create a datetime object of the starting date
start_date = datetime.datetime.strptime(start_date, "%m/%d/%y")
# fill a list with dates from starting date through a range of days (2 years)
for x in range (0,730):
    q = (start_date + datetime.timedelta (days = x))
    # print (q)
    # re-convert to string (STRingFromTIME function)
    q = q.strftime('%Y/%m/%d 00:00:00+00')
    # put the dates into a list
    date_list.append(q)
    # fill the other list with as many zeros as the other list has dates
    null_list.append(0)
# zip the two lists together to create a dictionary with the dates as keys and zeros as start values
date_dictionary=dict(zip(date_list, null_list))
#-------------------------------------------------------------
# now to the real world data
# Load in the workbook
print('... loading worksheet...')
wb = load_workbook('RKI_COVID19_Niedersachsen.xlsx')

# Get a sheet by name
sheet = wb['RKI_COVID19_Niedersachsen']

# Print the sheet title
print('Sheet Title:',sheet.title)

#------------------------------------------------------------
# adding cases per date
print('... adding up cases...')
# go through all rows of the worksheet from row 2 on (row 1 are the headers)
for i in range(2, sheet.max_row):
    # only Brunswick data (column 5)
    if sheet.cell(row=i, column=5).value == 'SK Braunschweig':
        # go through dictionary
        for key,value in date_dictionary.items():
            # if date in key of dictionary equals date in current row (column 9)
            if key == (sheet.cell(row=i, column=9).value):
                cases = (sheet.cell(row=i, column=15).value)
                # add cases (column 15) to the value in the dictionary
                date_dictionary[key] = date_dictionary[key] + sheet.cell(row=i, column = 15).value

# graphics cases per day
print('...creating plot...')
my_dictionary = date_dictionary.items()
x, y = zip(*my_dictionary)

plt.figure(figsize= (25, 5))
plt.scatter(x, y, color='red', s=1)
plt.xlabel('Datum')
plt.ylabel('Fallzahlen')
plt.title("Corona-Fallzahlen nach Meldetag")
plt.show()

Es ist immer noch der "unschöne" Umweg über strptime ->strftime drin, weil ich dem datetime-Objekt noch nicht ganz traue, aber das schaue ich mir noch einmal genauer an. Auch ein paar unschöne Variablennamen bitte ich zu verzeihen. Da kann ich noch etwas "refactor"ieren. Aber zum Schluß kommt eine Grafik der Tageszahlen heraus, die die verschiedenen Wellen zeigt. Da das jetzt klappt, kann ich die Daten nach allen möglichen Richtungen auswerten... Danke nochmal an alle, die mir hier Unterstützung gegeben haben. Den Hinweisen zum defaultdict und Pandas und Snoop werde ich weiter nachgehen.

[Sirius3]

Warum hast Du alle Importe doppelt?
Was ist der Sinn einer date_list und einer null_luist, wenn Du daraus ein Wörterbuch baust, das Du auch direkt erzeugen könntest:

Code: Alles auswählen

start_date = '01/01/20'
start_date = datetime.datetime.strptime(start_date, "%m/%d/%y")
date_dictionary = {}
for day in range (730):
    date = (start_date + datetime.timedelta (days=day))
    date_dictionary[f'{date:%Y/%m/%d 00:00:00+00}'] = 0

Wörterbücher haben den Vorteil, dass man direkt auf Schlüssel zugreifen kann, die For-Schleife über Items ist daher absoluter Quatsch.
Mit einem defaultdict spart man sich, wie schon geschrieben, auch die ganze Initialisierung, so dass das hier übrigbleibt:

Code: Alles auswählen

from collections import defaultdict
from openpyxl import load_workbook
import matplotlib.pyplot as plt

cases_per_day = defaultdict(int)
workbook = load_workbook('RKI_COVID19_Niedersachsen.xlsx')
sheet = workbook['RKI_COVID19_Niedersachsen']
for row in range(2, sheet.max_row):
    if sheet.cell(row=row, column=5).value == 'SK Braunschweig':
        date = sheet.cell(row=row, column=9).value
        cases = sheet.cell(row=row, column=15).value
        cases_per_day[date] += cases

x, y = zip(*sorted(cases_per_day.items()))
plt.figure(figsize=(25, 5))
plt.scatter(x, y, color='red', s=1)
plt.xlabel('Datum')
plt.ylabel('Fallzahlen')
plt.title("Corona-Fallzahlen nach Meldetag")
plt.show()

[viechdokter]

Wow, das ist viel kürzer und sieht dabei noch elegant aus. Danke! Ich frage mich nur, was passiert, wenn in der Excel-Tabelle ein Tag nicht vorkommt, weil es an diesem Tag keinen einzigen Fall gegeben hat? So wie ich das sehe, hast du in deinem cases_per_day defaultdict nur die Daten aus der Tabelle genommen, nicht ALLE Tage. Auch in deiner Grafik kommen Null-Fall-Tage nicht vor. Das war der Grund, weshalb ich mir vorher ein Null-Dictionary mit allen Tagen zusammengezipt hatte.

Die doppelten Importe stehen so in meinem Programm nicht drin. Die sind beim Kopieren in den Editor mit reingerutscht...

[__blackjack__]

@viechdokter: *Du* hast ja einen Scatterplot gewählt, dass heisst IMHO das da Werte fehlen beziehungsweise 0-Werte nicht geplottet werden. Sonst wäre das IMHO kein Scatterplot. Sondern ein normaler Plot, möglicherweise ohne Linie zwischen den Markern, ein Stemplot, oder ein Säulendiagramm.

[viechdokter]

Naja, vielleicht sollte ich ein Balkendiagramm daraus machen. Ein Liniendiagramm macht bei Tagesdaten keinen Sinn, finde ich. Zuviele Hin-und-her-Striche. Aber es wäre schon wichtig, Nuller-Tage zu sehen.

[__blackjack__]

@viechdokter: Was meinst Du mit Hin- und Her-Striche? Und ich schrob ja auch extra gegebenenfalls ohne Linie zwischen den Markern. Hier mal mit grauen, dünnen Strichen und kleinen roten Markern:

[viechdokter]

Ja stimmt. Mit den richtigen Farben mag es gehen. Zwei Fragen allerdings:

1. Wie hast du das mit den Monatszahlen auf der x-Achse hinbekommen? Bei mir stehen nur die Daten der Tage wild übereinander.
2. Wie geht das mit den unterschiedlichen Farben für Punkte und Striche? Ist das Pyplot oder wieder irgendeine andere Bibliothek?

[__blackjack__]

@viechdokter: Also wild übereinander sollten die da nicht stehen. Es sei denn Du fütterst da einen Haufen Zeichenketten rein statt `datetime`-Objekte.

Das ist Matplotlib. Hier ist der Code, der allerdings die CSV-Datei vom RKI verarbeitet — wer kommt denn bitte auf die perverse Idee *die* Anzahl von Datensätzen in eine Exceltabelle zu packen?

Code: Alles auswählen

#!/usr/bin/env python3
from pathlib import Path

import pandas as pd
from matplotlib import pyplot as plt
from matplotlib.dates import DateFormatter, MonthLocator, YearLocator

SELF_PATH = Path(__file__).parent
CSV_FILE_PATH = SELF_PATH / "RKI_COVID19_Niedersachsen.csv"


def main():
    data = pd.read_csv(
        "RKI_COVID19_Niedersachsen.csv",
        parse_dates=["Meldedatum", "Refdatum"],
        infer_datetime_format=True,
    )
    data = (
        data[data["Landkreis"] == "SK Braunschweig"][
            ["Meldedatum", "AnzahlFall"]
        ]
        .groupby("Meldedatum")
        .sum()
    )
    figure, ax = plt.subplots(figsize=(10, 4))
    ax.plot(
        data.index,
        data["AnzahlFall"],
        "o-",
        markerfacecolor="red",
        markeredgecolor="red",
        markersize=1,
        linewidth=0.5,
        color="gray",
    )
    ax.xaxis.set_major_locator(YearLocator())
    ax.xaxis.set_minor_locator(MonthLocator())
    ax.xaxis.set_minor_formatter(DateFormatter("%m"))
    for label in ax.xaxis.get_minorticklabels():
        label.set_name("Roboto Condensed")
    ax.set_ylabel("Fallzahlen")
    ax.set_title("Corona-Fallzahlen nach Meldetag")
    for side in ["top", "right"]:
        ax.spines[side].set_visible(False)
    figure.tight_layout()
    plt.show()


if __name__ == "__main__":
    main()

[viechdokter]

wer kommt denn bitte auf die perverse Idee *die* Anzahl von Datensätzen in eine Exceltabelle zu packen?

Das war dann wohl ich...

[viechdokter]

@__blackjack __ Dein Plot ist beeindruckend! Ich glaube, ich werde ein ganzes Wochenende brauchen, um alleine deine Grafikanweisungen durchzuarbeiten. Danke für die vielen Denkanstöße und danke, dass du dich so für einen Rookie reingekniet hast!

[viechdokter]

Hier ist ein Ausschnitt aus dem Balkendiagramm mit einem Null-Tag.

[viechdokter]

Und hier ist nach etlichen Versuchen mit Matplotlib eine grafische Auswertung der Fallzahlen nach Altersklassen. Still way to go, aber die Lernkurve der letzten Tage war für mich als Neuling schon ziemlich steil... Danke nochmal an alle hier!