Parse xls files from http://www.stadtklima-stuttgart.de/index.php?luft_messdaten_download

stadtklima-parser.py

from collections import defaultdict
import datetime

import pandas as pd


def data(filename='/home/mfk/Halbstd-Werte-Stuttgart-Mitte-SZ_2015.xls', sheetname='Okt. 2015'):
    raw_data = pd.read_excel(
        io=filename,
        sheetname=sheetname,
        header=(6, 7),
        index_col=None,
    )

    # Replace `NaN` with None
    data_with_none = raw_data.where((pd.notnull(raw_data)), None)

    # Convert row to dict; include only lines with date
    data = ((index, row.to_dict())
            for index, row in data_with_none.iterrows()
            if pd.notnull(index))

    return data


def date_from_raw(date, time):
    return datetime.datetime(
        date.year,
        date.month,
        date.day,
        time.hour,
        time.minute,
        time.second,
    )


for raw_date, row in data():
    values = defaultdict(dict)

    for item in row.items():
        if item[0][1] == 'Uhrzeit':
            date = date_from_raw(raw_date, item[1])
            continue

        values[item[0][1]].update({
            item[0][0]: item[1],
        })

    print(date, values)  # TODO: push to api