def query_accounts_by_company_id(insurer_ids, bank_name, account_type): """ get all bank accounts for each insurer in a list of insurer ids """ session, connection = connect_bank(bank_name) accounts_query = session.query( Insurer.insurer_id, Insurer.customer_id, Account.account_id ).outerjoin( Account, Insurer.customer_id == Account.customer_id ).filter( Account.account_type == account_type ).statement accounts = pd.read_sql( accounts_query, connection ) accounts = accounts[accounts['insurer_id'].isin(insurer_ids)] connection.close() return accounts
def query_accounts_by_person_id(person_ids, bank_name, account_type): """ get all the accounts for each person id in a list of person ids """ session, connection = connect_bank(bank_name) accounts_query = session.query( Person.person_id, Person.customer_id, Account.account_id ).outerjoin( Account, Person.customer_id == Account.customer_id ).filter( Account.account_type == account_type ).statement accounts = pd.read_sql( accounts_query, connection ) accounts = accounts[accounts['person_id'].isin(person_ids)] connection.close() return accounts
def query_customers_by_person_id(person_ids, bank_name): """ get corresponding customer ids for each person id in a list of person ids """ session, connection = connect_bank(bank_name) customer_query = session.query(Person).statement customers = pd.read_sql(customer_query, connection) customers = customers[customers['person_id'].isin(person_ids)] customers = customers['customer_id'] connection.close() return customers
def query_population_wealth(): """ get wealth for each person in the population """ population = query_population() person_ids = population['person_id'] banks = query_banks() pop_wealth = pd.DataFrame() for index, row in banks.iterrows(): bank_name = row['bank_name'] session, connection = connect_bank(bank_name) debit_query = session.query( Transaction.debit_account, func.sum(Transaction.transaction_amount)).group_by( Transaction.debit_account).statement debit_amounts = pd.read_sql(debit_query, connection) debit_amounts.columns = ['account_id', 'debits'] credit_query = session.query( Transaction.credit_account, func.sum(Transaction.transaction_amount)).group_by( Transaction.credit_account).statement credit_amounts = pd.read_sql(credit_query, connection) credit_amounts.columns = ['account_id', 'credits'] person_accounts = query_accounts_by_person_id(person_ids, bank_name, 'cash') wealth = person_accounts.merge(debit_amounts, on='account_id', how='left') wealth = wealth.merge(credit_amounts, on='account_id', how='left') wealth['debits'] = wealth['debits'].fillna(0) wealth['credits'] = wealth['credits'].fillna(0) wealth = wealth[['person_id', 'account_id', 'debits', 'credits']] wealth = wealth.groupby(['person_id', 'account_id'])[['debits', 'credits' ]].agg('sum').reset_index() wealth['wealth'] = wealth['debits'] - wealth['credits'] wealth = wealth[['person_id', 'wealth']] wealth = wealth.groupby(['person_id' ])['wealth'].agg('sum').reset_index() pop_wealth = pop_wealth.append(wealth) connection.close() pop_wealth = pop_wealth.groupby(['person_id' ])['wealth'].agg('sum').reset_index() return pop_wealth
def query_person_wealth(person_id): """ returns the wealth for one person """ banks = query_banks() person_wealth = pd.DataFrame() for index, row in banks.iterrows(): bank_name = row['bank_name'] session, connection = connect_bank(bank_name) person_accounts = query_accounts_by_person_id(person_id, bank_name, 'cash') account_list = list(person_accounts['account_id']) debit_query = session.query( Transaction.debit_account.label('account_id'), func.sum(Transaction.transaction_amount).label('debits')).group_by( Transaction.debit_account).filter( Transaction.debit_account.in_(account_list)).statement debit_amounts = pd.read_sql(debit_query, connection) credit_query = session.query( Transaction.credit_account.label('account_id'), func.sum( Transaction.transaction_amount).label('credits')).group_by( Transaction.credit_account).filter( Transaction.debit_account.in_(account_list)).statement credit_amounts = pd.read_sql(credit_query, connection) wealth = person_accounts.merge(debit_amounts, on='account_id', how='left') wealth = wealth.merge(credit_amounts, on='account_id', how='left') wealth['debits'] = wealth['debits'].fillna(0) wealth['credits'] = wealth['credits'].fillna(0) wealth = wealth[['person_id', 'account_id', 'debits', 'credits']] wealth = wealth.groupby(['person_id', 'account_id'])[['debits', 'credits' ]].agg('sum').reset_index() wealth['wealth'] = wealth['debits'] - wealth['credits'] wealth = wealth[['person_id', 'wealth']] wealth = wealth.groupby(['person_id' ])['wealth'].agg('sum').reset_index() person_wealth = person_wealth.append(wealth) connection.close() person_wealth = person_wealth.groupby( ['person_id'])['wealth'].agg('sum').reset_index() return person_wealth