3 Join Frames

In order to explore our database, we decided to combine the different tables. This was a two-step process:

1. Aggregate every table but the transactions in which the granularity was the account, the clients were agregated to show only the account owner and the number of the dependants in the account.

2. Next step was to join this new table with Transactions, creating a big dataset with all the data. The granularity for this is the single transaction.

With this two new structures we were able to study the data and draw some ideas and conclusions to help the manager.

3.1 Join frames without transactions:

#Base frame accounts 
frame_no_transaction <- account %>%
  #Join accounts
  left_join(loan, by = 'account_id') %>%
  #Join Demographic
  left_join(demographic, by = 'district_id') %>%
  #Join disposition
  left_join(
    dplyr::filter(disposition, type == 'OWNER' ) %>% 
      left_join(disposition %>% group_by(account_id) %>% 
                  dplyr::summarise(dependents = n()-1), by = 'account_id')
    , by = 'account_id') %>%
  #Join client
  left_join(client,by = 'client_id') %>%
  #Join card
  left_join(card, by = 'disp_id') %>%
  #Join transactions 
  left_join(
    transaction %>% group_by(account_id) %>%
      dplyr::summarise(amount_transactions = sum(amount)),
    by = 'account_id')

3.2 Join frames with transactions:

# Join previous frame with transactions
frame <- transaction %>%
  left_join(frame_no_transaction, by = 'account_id')