Identify Date with Highest Total kWh Consumption Using Pandas

meter_readings

+----------+------------+---------------+---------+
| meter_id | timestamp  | kwh_consumed  | city    |
+----------+------------+---------------+---------+
| 1001     | 2023-01-01 | 15.2          | Austin  |
| 1001     | 2023-01-02 | 18.0          | Austin  |
| 1002     | 2023-01-01 | 22.5          | Dallas  |
| 1002     | 2023-01-02 | 20.1          | Dallas  |
| 1003     | 2023-01-01 | 10.7          | Houston |
+----------+------------+---------------+---------+

Scenario

Analyst needs to summarize smart-meter readings for an operations report.

Question

Using pandas, aggregate total kwh_consumed for each date and return the date with the highest total consumption.

Hints

groupby on timestamp, sum, use idxmax or sort_values.

Find date with highest total kWh consumption

Given the meter_readings table of smart-meter data, write an SQL query to aggregate the total kwh_consumed for each date (timestamp) across all meters, and return the single date with the highest total consumption along with that total.

Table meter_readings

column	type	constraints
meter_id	INTEGER
timestamp	DATE
kwh_consumed	DECIMAL(10,1)
city	VARCHAR(50)

Sample data:

city	kwh_consumed	meter_id	timestamp
Austin	15.2	1001	2023-01-01
Austin	18	1001	2023-01-02
Dallas	22.5	1002	2023-01-01
Dallas	20.1	1002	2023-01-02
Houston	10.7	1003	2023-01-01

Expected output

A single row containing the date (timestamp) with the highest summed kWh across all meters and the corresponding total kWh consumed on that date.

timestamp	total_kwh_consumed
2023-01-01	48.4

Hints

1. GROUP BY timestamp and SUM kwh_consumed
2. ORDER BY the summed value in descending order and LIMIT 1