Calculate parking fees and duration

# Table: ParkingTransactions
# 
# +--------------+-----------+
# | Column Name  | Type      |
# +--------------+-----------+
# | lot_id       | int       |
# | car_id       | int       |
# | entry_time   | datetime  |
# | exit_time    | datetime  |
# | fee_paid     | decimal   |
# +--------------+-----------+
# (lot_id, car_id, entry_time) is the primary key (combination of columns with unique values) for this table.
# Each row of this table contains the ID of the parking lot, the ID of the car, the entry and exit times, and the fee paid for the parking duration.
# 
# Write a solution to find the total parking fee paid by each car across all parking lots, and the average hourly fee (rounded to 2 decimal places) paid by each car. Also, find the parking lot where each car spent the most total time.
# 
# Return the result table ordered by car_id in ascending  order.
# 
# Note: Test cases are generated in such a way that an individual car cannot be in multiple parking lots at the same time.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# ParkingTransactions table:
# +--------+--------+---------------------+---------------------+----------+
# | lot_id | car_id | entry_time          | exit_time           | fee_paid |
# +--------+--------+---------------------+---------------------+----------+
# | 1      | 1001   | 2023-06-01 08:00:00 | 2023-06-01 10:30:00 | 5.00     |
# | 1      | 1001   | 2023-06-02 11:00:00 | 2023-06-02 12:45:00 | 3.00     |
# | 2      | 1001   | 2023-06-01 10:45:00 | 2023-06-01 12:00:00 | 6.00     |
# | 2      | 1002   | 2023-06-01 09:00:00 | 2023-06-01 11:30:00 | 4.00     |
# | 3      | 1001   | 2023-06-03 07:00:00 | 2023-06-03 09:00:00 | 4.00     |
# | 3      | 1002   | 2023-06-02 12:00:00 | 2023-06-02 14:00:00 | 2.00     |
# +--------+--------+---------------------+---------------------+----------+
# Output:
# +--------+----------------+----------------+---------------+
# | car_id | total_fee_paid | avg_hourly_fee | most_time_lot |
# +--------+----------------+----------------+---------------+
# | 1001   | 18.00          | 2.40           | 1             |
# | 1002   | 6.00           | 1.33           | 2             |
# +--------+----------------+----------------+---------------+
# Explanation:
# For car ID 1001:
# From 2023-06-01 08:00:00 to 2023-06-01 10:30:00 in lot 1: 2.5 hours, fee 5.00
# From 2023-06-02 11:00:00 to 2023-06-02 12:45:00 in lot 1: 1.75 hours, fee 3.00
# From 2023-06-01 10:45:00 to 2023-06-01 12:00:00 in lot 2: 1.25 hours, fee 6.00
# From 2023-06-03 07:00:00 to 2023-06-03 09:00:00 in lot 3: 2 hours, fee 4.00
# Total fee paid: 18.00, total hours: 7.5, average hourly fee: 2.40, most time spent in lot 1: 4.25 hours.
# For car ID 1002:
# From 2023-06-01 09:00:00 to 2023-06-01 11:30:00 in lot 2: 2.5 hours, fee 4.00
# From 2023-06-02 12:00:00 to 2023-06-02 14:00:00 in lot 3: 2 hours, fee 2.00
# Total fee paid: 6.00, total hours: 4.5, average hourly fee: 1.33, most time spent in lot 2: 2.5 hours.
# Note: Output table is ordered by car_id in ascending order.

import pandas as pd

def calculate_fees_and_duration(parking_transactions: pd.DataFrame) -> pd.DataFrame:
  parking_transactions['entry_time'] = pd.to_datetime(parking_transactions['entry_time'])
  parking_transactions['exit_time'] = pd.to_datetime(parking_transactions['exit_time'])
  parking_transactions['hours'] = (parking_transactions['exit_time'] - parking_transactions['entry_time']).dt.total_seconds() / 3600
  total_fee = parking_transactions.groupby('car_id')['fee_paid'].sum().reset_index(name='total_fee_paid')
  total_hours = parking_transactions.groupby('car_id')['hours'].sum().reset_index(name='total_hours')
  lot_hours = parking_transactions.groupby(['car_id', 'lot_id'])['hours'].sum().reset_index()
  most_time = lot_hours.sort_values(['car_id', 'hours', 'lot_id'], ascending=[True, False, True]).drop_duplicates('car_id', keep='first')[['car_id', 'lot_id']]
  most_time.columns = ['car_id', 'most_time_lot']
  result = total_fee.merge(total_hours, on='car_id').merge(most_time, on='car_id')
  result['avg_hourly_fee'] = round(result['total_fee_paid'] / result['total_hours'], 2)
  return result[['car_id', 'total_fee_paid', 'avg_hourly_fee', 'most_time_lot']].sort_values('car_id').reset_index(drop=True)