Dataset to Pivot table (time series dataset)

[grijken]

Hi Everyone, I have ran into problem during my graduation internship. I need to study the impact of occupancy data (data found below) on energy forecasts. The problem is that the data they provided me with is formatted in a very unusual way. I would like to create a pivot time table from 2020-01-01 01:00 updating every hour until 2020-12-31 23:00. Can someone explain the best way to go about this? I don't mind using Python or another programming language to do this. ("personen"=occupants and bezoekers can be deleted)

Thank you!

 

[awoohaw]

Can you ask your instructors to provide the data in a flat file?

If that can't happen. I suggest using power query to unpivot your data

You've posted an image which gives a view of your scenario, but the forum must manually recreate your data. Can you please post a table or use the xl2bb add in (preferred, link to the add in is below).

Thanks, and welcome to the forum.

 

[awoohaw]

Here is a power query statement based on your image.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type any}, {"Category", type text}, {"7:00", Int64.Type}, {"9:00", Int64.Type}, {"10:00", Int64.Type}, {"11:00", Int64.Type}, {"13:00", Int64.Type}, {"14:00", Int64.Type}, {"15:00", Int64.Type}, {"16:00", Int64.Type}, {"17:00", Int64.Type}, {"18:00", Int64.Type}, {"18:30", Int64.Type}, {"19:00", Int64.Type}, {"21:00", Int64.Type}, {"23:00", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null and [Date] <> "Week 2" and [Date] <> "Week 3" and [Date] <> "Week Total" and [Date] <> "Week-Gemiddelde")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Date", "Category"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Hour"}, {"Value", "Count"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Hour", type time}, {"Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Hour", "Category", "Count"})
in
    #"Reordered Columns"