text: # Advanced data problems!!
  # In this chapter, you’ll dive into more advanced data cleaning problems, such as ensuring that weights are all written in kilograms instead of pounds. You’ll also gain invaluable skills that will help you verify that values have been added correctly and that missing values don’t negatively impact your analyses.
  
  
  # Uniform currencies
  # In this exercise and throughout this chapter, you will be working with a retail banking dataset stored in the banking DataFrame. The dataset contains data on the amount of money stored in accounts, their currency, amount invested, account opening date and last transaction date that were consolidated from American and European branches.
  
  # You are tasked with understanding the average account size and how investments vary by the size of account, however in order to produce this analysis accurately, you first need to unify the currency amount into dollars. The pandas package has been imported as pd, and the banking DataFrame is in your environment.
  
  # Find values of acct_cur that are equal to 'euro'
  acct_eu = banking['acct_cur'] == 'euro'
  
  # Convert acct_amount where it is in euro to dollars
  banking.loc[acct_eu, 'acct_amount'] = banking.loc[acct_eu,'acct_amount'] * 1.1
  
  # Unify acct_cur column by changing 'euro' values to 'dollar'
  banking.loc[acct_eu, 'acct_cur'] = 'dollar'
  
  # Print unique values of acct_cur
  assert banking['acct_cur'].unique() == 'dollar'
  
  # Uniform dates
  # After having unified the currencies of your different account amounts, you want to add a temporal dimension to your analysis and see how customers have been investing their money given the size of their account over each year. The account_opened column represents when customers opened their accounts and is a good proxy for segmenting customer activity and investment over time.
  
  # However, since this data was consolidated from multiple sources, you need to make sure that all dates are of the same format. You will do so by converting this column into a datetime object, while making sure that the format is inferred and potentially incorrect formats are set to missing. The banking DataFrame is in your environment and pandas was imported as pd.
  
  
  # Print the header of account_opened
  print(banking['account_opened'].head())
  
  
  # How's our data integrity?
  # New data has been merged into the banking DataFrame that contains details on how investments in the inv_amount column are allocated across four different funds A, B, C and D.
  
  # Furthermore, the age and birthdays of customers are now stored in the age and birth_date columns respectively.
  
  # You want to understand how customers of different age groups invest. However, you want to first make sure the data you're analyzing is correct. You will do so by cross field checking values of inv_amount and age against the amount invested in different funds and customers' birthdays. Both pandas and datetime have been imported as pd and dt respectively.
  
  
  # Store fund columns to sum against
  fund_columns = ['fund_A', 'fund_B', 'fund_C', 'fund_D']
  
  # Find rows where fund_columns row sum == inv_amount
  inv_equ = banking[fund_columns].sum(axis = 1) == banking['inv_amount']
  
  # Store consistent and inconsistent data
  consistent_inv = banking[inv_equ]
  inconsistent_inv = banking[~inv_equ]
  
  # Store consistent and inconsistent data
  print("Number of inconsistent investments: ", inconsistent_inv.shape[0])
  
  
  # Missing investors
  # Dealing with missing data is one of the most common tasks in data science. There are a variety of types of missingness, as well as a variety of types of solutions to missing data.
  
  # You just received a new version of the banking DataFrame containing data on the amount held and invested for new and existing customers. However, there are rows with missing inv_amount values.
  
  # You know for a fact that most customers below 25 do not have investment accounts yet, and suspect it could be driving the missingness. The pandas, missingno and matplotlib.pyplot packages have been imported as pd, msno and plt respectively. The banking DataFrame is in your environment.
  
  # Print number of missing values in banking
  print(banking.isna().sum())
  
  # Visualize missingness matrix
  msno.matrix(banking)
  plt.show()
  
  
  # Follow the money
  # In this exercise, you're working with another version of the banking DataFrame that contains missing values for both the cust_id column and the acct_amount column.
  
  # You want to produce analysis on how many unique customers the bank has, the average amount held by customers and more. You know that rows with missing cust_id don't really help you, and that on average acct_amount is usually 5 times the amount of inv_amount.
  
  # In this exercise, you will drop rows of banking with missing cust_ids, and impute missing values of acct_amount with some domain knowledge.
  
  # Drop missing values of cust_id
  banking_fullid = banking.dropna(subset = ['cust_id'])
  
  # Compute estimated acct_amount
  acct_imp = banking_fullid['inv_amount']*5
  
  # Impute missing acct_amount with corresponding acct_imp
  banking_imputed = banking_fullid.fillna({'acct_amount':acct_imp})
  
  # Print number of missing values
  print(banking_imputed.isna().sum())
  
  
  