Working with Timestamp in Mulitindex

Working with Timestamp in Mulitindex - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Working with Timestamp in Mulitindex (/thread-32126.html)

Working with Timestamp in Mulitindex - krischanb - Jan-22-2021

Hi,
I imported a csv containing from three different temprature sensors values taken each 10 minutes. The point, where I am stuck right now is the Multiindex of my dataframe. I contains the Sensorname and the timestamp (YYYY-MM-DD HH:MM:SS. What I now would like to to do is to evaluate a certain timeperiod of the day, eg from 10pm to 6 am the next morning. As well as gaining the max min and mean values of the day.
Do I have to split the timestamp index into two separate ones or is there a way to to parse the timestamp for evalutaion?

Please see attached my code:

import pandas as pd

LOG_FILENAME = "D:/Python/temperaturlog.txt"
UNIT = "°C"


def main():
    data = pd.read_csv(
        LOG_FILENAME,
        delimiter=";",
        index_col=["Sensor","Zeitpunkt"],
        parse_dates={"Zeitpunkt": ["Datum","Uhrzeit"]},
        decimal=",",
        encoding="utf-8",
        dayfirst=True,
    )
    if (data["Einheit"] != UNIT).any():
        raise ValueError(f"expected unit {UNIT!r} for all records")

    df=pd.DataFrame(data)


    df_pivot = df.pivot_table(
        values="Temperatur",
        index="Zeitpunkt",
        columns="Sensor",
        aggfunc=["max","min","mean"]
        )

    
    ser_unstacked=df_pivot.unstack()
    df_unstacked=ser_unstacked.unstack()
    df_unstacked.index.names=["Werte","Sensor"]
    df_reorderd = df_unstacked.reorder_levels(["Sensor","Werte"])
    df_sorted = df_reorderd.sort_index(axis=0)
    

    print(df_sorted)


if __name__ == "__main__":
    main()

df.index shows:

Quote:MultiIndex([( 'Schwarz', '2020-12-01 11:40:02'),
('Draussen1', '2020-12-01 11:40:03'),
( 'Blau', '2020-12-01 11:40:04'),
( 'Schwarz', '2020-12-01 11:50:02'),
('Draussen1', '2020-12-01 11:50:02'),
( 'Blau', '2020-12-01 11:50:03'),
( 'Schwarz', '2020-12-01 12:00:02'),
('Draussen1', '2020-12-01 12:00:03'),
( 'Blau', '2020-12-01 12:00:04'),
( 'Schwarz', '2020-12-01 12:10:02'),
...
names=['Sensor', 'Zeitpunkt'], length=16044)

Following the first few lines from the logfile:

Quote:Sensor;Datum;Uhrzeit;Temperatur;Einheit
Schwarz;01.12.2020;11:40:02; 18,50;°C
Draussen1;01.12.2020;11:40:03; 4,69;°C
Blau;01.12.2020;11:40:04; 18,31;°C
Schwarz;01.12.2020;11:50:02; 18,50;°C
Draussen1;01.12.2020;11:50:02; 4,75;°C
Blau;01.12.2020;11:50:03; 18,31;°C
Schwarz;01.12.2020;12:00:02; 18,50;°C
Draussen1;01.12.2020;12:00:03; 4,94;°C

Best regards
krischanb