Question about finding max value in dataset

Hi, v-yalanwu-msft 

It probibly is a bit confusing. Therefore i will make a better description of what i'am trying to do.

I have two sets of data:

- 1 set of data consists of about 200 meters with quarterly (electricity) or hourly data (gas) for a whole year

- 1 set of data consists of about 100 buildings with caracteristics of these building including the meters which are part of these buildings. in many cases a building has 1 electricity and 1 gas meter, but sometimes a building has for instance two electricity meters.

What I want to do with this data is:

1. sum up the amount of electricity used in an hour with the gas used in an hour. Herefor I'm looking for the maximum use of electricity in every hour of the year, which i will mutiply times four. And I am looking for the maximum amount of gas used in every hour of the year. I have made a new column in my datasheet in which I have converted quarters into hours (so 0:00, 0:15, 0:30 and 0:45 are now all 0:00). Because I have 200 different meters I have made a new column in which I have grouped the name of the meter, the date and the timestamp (full hours).  Based upon this data I have created a new table in which I have used the function groupby so that I have left 200 (meters) x 8760 (hours per year) unique datapoints.

This table I want to expend with the max value for electricity consumption per hour and the max value for gas consumption per hour.

This looks as following:

datasheet

What I want to create in a new table looks like:

2. My second database contains the data from which I know which meter belongs to which building. Based upon this knowledge I want to expand my former new table with the data of which building belongs to a specific meter. So the table looks like this: This is what I have allready build, lacking the maximum value 🙂

From this table I have created a new table in which I used the groupby formula another time but now to create new unique variables containing the name of the building and the date and hourly timestamp (Building001 01-01-2021 00:00, etc..) In this table I want to sum up the max consumption values for electricity and gas per building.

So for instance:

I hope this clarifies what I am trying to do with the data.

Thanks in advance,

Rinto