Hours lost by day

reshaping

Hours lost by day

PagerDuty Pandas Interview Question

A service that loses an hour every day and a service that loses four hours in one bad afternoon need different fixes: the first is rotting, the second had an accident. A weekly total cannot tell them apart, so PagerDuty's reliability review tracks the hours each service lost on each day.

Using the incidents DataFrame, return one row per service. Keep service as a column, then one column per day, named 2024-10-01, 2024-10-02, 2024-10-03 and 2024-10-04, holding the hours lost on that day rounded to 2 decimal places. A service with nothing on a day shows 0. Assign the answer to result.

Asked of

  • Data Analyst
  • Analytics Engineer
  • Data Engineer
  • Data Scientist

incidentsDataFrame20 rows

Column NameType
incident_idint64
servicestr
started_atstr
resolved_atstr
severitystr

incidentsExample Input

incident_idservicestarted_atresolved_atseverity
6001checkout-api2024-10-01 02:10:002024-10-01 03:40:00SEV2
6002checkout-api2024-10-01 03:05:002024-10-01 04:25:00SEV3
6003checkout-api2024-10-01 06:00:002024-10-01 06:45:00SEV1
6004checkout-api2024-10-02 09:30:002024-10-02 10:15:00SEV3
6005checkout-api2024-10-02 10:10:002024-10-02 11:50:00SEV2
6006search-api2024-10-01 01:00:002024-10-01 01:35:00SEV3
6007search-api2024-10-01 05:20:002024-10-01 08:05:00SEV1
6008search-api2024-10-01 07:15:002024-10-01 07:50:00SEV3
6009search-api2024-10-02 14:00:002024-10-02 14:20:00SEV3
6010search-api2024-10-03 11:45:002024-10-03 13:10:00SEV2
6020checkout-api2024-10-04 07:00:002024-10-04 07:25:00SEV3

Example Output

service2024-10-012024-10-022024-10-032024-10-04
checkout-api3.582.4200.42
search-api3.920.331.420

Explanation

The checkout service lost 3.58 hours on the first day and 2.42 on the second, then nothing on the third, which shows as 0 rather than an empty cell. The search service lost time on three of the four days.

The example above is a small slice of the data. Your code runs against the full DataFrames.