Monthly transactions ii

# Table: Transactions
# 
# +----------------+---------+
# | Column Name    | Type    |
# +----------------+---------+
# | id             | int     |
# | country        | varchar |
# | state          | enum    |
# | amount         | int     |
# | trans_date     | date    |
# +----------------+---------+
# id is the column of unique values of this table.
# The table has information about incoming transactions.
# The state column is an ENUM (category) of type ["approved", "declined"].
# 
# Table: Chargebacks
# 
# +----------------+---------+
# | Column Name    | Type    |
# +----------------+---------+
# | trans_id       | int     |
# | trans_date     | date    |
# +----------------+---------+
# Chargebacks contains basic information regarding incoming chargebacks from some transactions placed in Transactions table.
# trans_id is a foreign key (reference column) to the id column of Transactions table.
# Each chargeback corresponds to a transaction made previously even if they were not approved.
# 
#  
# 
# Write a solution to find for each month and country: the number of approved transactions and their total amount, the number of chargebacks, and their total amount.
# 
# Note: In your solution, given the month and country, ignore rows with all zeros.
# 
# Return the result table in any order.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Transactions table:
# +-----+---------+----------+--------+------------+
# | id  | country | state    | amount | trans_date |
# +-----+---------+----------+--------+------------+
# | 101 | US      | approved | 1000   | 2019-05-18 |
# | 102 | US      | declined | 2000   | 2019-05-19 |
# | 103 | US      | approved | 3000   | 2019-06-10 |
# | 104 | US      | declined | 4000   | 2019-06-13 |
# | 105 | US      | approved | 5000   | 2019-06-15 |
# +-----+---------+----------+--------+------------+
# Chargebacks table:
# +----------+------------+
# | trans_id | trans_date |
# +----------+------------+
# | 102      | 2019-05-29 |
# | 101      | 2019-06-30 |
# | 105      | 2019-09-18 |
# +----------+------------+
# Output:
# +---------+---------+----------------+-----------------+------------------+-------------------+
# | month   | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
# +---------+---------+----------------+-----------------+------------------+-------------------+
# | 2019-05 | US      | 1              | 1000            | 1                | 2000              |
# | 2019-06 | US      | 2              | 8000            | 1                | 1000              |
# | 2019-09 | US      | 0              | 0               | 1                | 5000              |
# +---------+---------+----------------+-----------------+------------------+-------------------+

import pandas as pd

def monthly_transactions(transactions: pd.DataFrame, chargebacks: pd.DataFrame) -> pd.DataFrame:
  # Approved transactions
  approved = transactions[transactions['state'] == 'approved'].copy()
  approved['month'] = approved['trans_date'].dt.strftime('%Y-%m')
  approved_agg = approved.groupby(['month', 'country']).agg(
    approved_count=('id', 'count'),
    approved_amount=('amount', 'sum')
  ).reset_index()

  # Chargebacks: join with transactions to get country and amount, use chargeback's trans_date for month
  cb = chargebacks.merge(transactions[['id', 'country', 'amount']], left_on='trans_id', right_on='id')
  cb['month'] = cb['trans_date'].dt.strftime('%Y-%m')
  cb_agg = cb.groupby(['month', 'country']).agg(
    chargeback_count=('trans_id', 'count'),
    chargeback_amount=('amount', 'sum')
  ).reset_index()

  # Merge
  result = approved_agg.merge(cb_agg, on=['month', 'country'], how='outer').fillna(0)
  for col in ['approved_count', 'approved_amount', 'chargeback_count', 'chargeback_amount']:
    result[col] = result[col].astype(int)

  # Remove rows with all zeros
  result = result[
    (result['approved_count'] != 0) | (result['approved_amount'] != 0) |
    (result['chargeback_count'] != 0) | (result['chargeback_amount'] != 0)
  ]

  return result[['month', 'country', 'approved_count', 'approved_amount', 'chargeback_count', 'chargeback_amount']]