{
 "cells": [
 {
 "cell_type": "code",
 "execution_count": 195,
 "metadata": {},
 "outputs": [
 {
 "name": "stdout",
 "output_type": "stream",
 "text": [
 " custID accountID tenure_mo account_type\n",
 "0 1 1 2 BusiNESS\n",
 "1 1 2 3 CONSUMER\n",
 "2 2 1 4 consumer\n",
 "3 2 2 4 BUSINESS\n",
 "4 2 3 5 BuSIness\n",
 "5 3 1 6 CONSUmer\n",
 "6 3 2 6 consumer\n",
 "7 4 1 6 CONSUMER\n",
 "8 4 2 7 BUSINESS\n",
 " custID cust_age\n",
 "0 1 20\n",
 "1 2 35\n",
 "2 3 50\n",
 "3 4 85\n"
 ]
 }
 ],
 "source": [
 "#The lines of code below display two real-world data sets: the first contains customer ID and their\n",
 "#corresponding accounts IDs, along with account tenure in months and account type (business or \n",
 "#consumer). The second contains customer IDs and their corresponding age in years.\n",
 "\n",
 "import pandas as pd\n",
 "df1=pd.DataFrame({'custID':[1,1,2,2,2,3,3,4,4],\n",
 " 'accountID':[1,2,1,2,3,1,2,1,2],\n",
 " 'tenure_mo':[2,3,4,4,5,6,6,6,7],\n",
 " 'account_type':['BusiNESS','CONSUMER',\n",
 " 'consumer',\n",
 " 'BUSINESS',\n",
 " 'BuSIness',\n",
 " 'CONSUmer',\n",
 " 'consumer',\n",
 " 'CONSUMER',\n",
 " 'BUSINESS']},columns=['custID','accountID','tenure_mo','account_type'])\n",
 "print(df1)\n",
 "df2=pd.DataFrame({'custID':[1,2,3,4],\n",
 " 'cust_age':[20,35,50,85]},columns=['custID','cust_age'])\n",
 "print(df2)"
 ]
 },
 {
 "cell_type": "code",
 "execution_count": 4,
 "metadata": {
 "collapsed": true
 },
 "outputs": [],
 "source": [
 "#Question 1: \n",
 "#using pivot tables, write code to display the number of consumer accounts for each customer\n"
 ]
 },
 {
 "cell_type": "code",
 "execution_count": 196,
 "metadata": {},
 "outputs": [
 {
 "data": {
 "text/html": [
 "<div>\n",
 "<style scoped>\n",
 " .dataframe tbody tr th:only-of-type {\n",
 " vertical-align: middle;\n",
 " }\n",
 "\n",
 " .dataframe tbody tr th {\n",
 " vertical-align: top;\n",
 " }\n",
 "\n",
 " .dataframe thead th {\n",
 " text-align: right;\n",
 " }\n",
 "</style>\n",
 "<table border=\"1\" class=\"dataframe\">\n",
 " <thead>\n",
 " <tr style=\"text-align: right;\">\n",
 " <th></th>\n",
 " <th>num_cons_accounts</th>\n",
 " </tr>\n",
 " <tr>\n",
 " <th>custID</th>\n",
 " <th></th>\n",
 " </tr>\n",
 " </thead>\n",
 " <tbody>\n",
 " <tr>\n",
 " <th>1</th>\n",
 " <td>1</td>\n",
 " </tr>\n",
 " <tr>\n",
 " <th>2</th>\n",
 " <td>1</td>\n",
 " </tr>\n",
 " <tr>\n",
 " <th>3</th>\n",
 " <td>2</td>\n",
 " </tr>\n",
 " <tr>\n",
 " <th>4</th>\n",
 " <td>1</td>\n",
 " </tr>\n",
 " </tbody>\n",
 "</table>\n",
 "</div>"
 ],
 "text/plain": [
 " num_cons_accounts\n",
 "custID \n",
 "1 1\n",
 "2 1\n",
 "3 2\n",
 "4 1"
 ]
 },
 "execution_count": 196,
 "metadata": {},
 "output_type": "execute_result"
 }
 ],
 "source": [
 "#your code goes here..desired output below\n",
 "df1['account_type'] = df1['account_type'].apply(lambda x:x.lower())\n",
 "data = pd.pivot_table(df1, columns=['account_type'], index=['custID'], aggfunc='count')\n",
 "data = pd.DataFrame(data['accountID']['consumer'])\n",
 "data.columns = ['num_cons_accounts']\n",
 "data['num_cons_accounts'] = data['num_cons_accounts'].astype(int)\n",
 "data"
 ]
 },
 {
 "cell_type": "code",
 "execution_count": null,
 "metadata": {
 "collapsed": true
 },
 "outputs": [],
 "source": [
 "#Question 2: \n",
 "#using group by methods (not pivot table), write code to display a list of only those customer IDs that\n",
 "#have at least 1 business account and that are under 50 years of age.\n"
 ]
 },
 {
 "cell_type": "code",
 "execution_count": 197,
 "metadata": {},
 "outputs": [
 {
 "data": {
 "text/plain": [
 "[1, 2]"
 ]
 },
 "execution_count": 197,
 "metadata": {},
 "output_type": "execute_result"
 }
 ],
 "source": [
 "#your code goes here: \n",
 "#desired output below\n",
 "df1['account_type'] = df1['account_type'].apply(lambda x:x.lower())\n",
 "data = df1.groupby('account_type')['custID'].unique()\n",
 "data = pd.DataFrame(data).reset_index()\n",
 "data = data[data['account_type'] == 'business']\n",
 "data = pd.DataFrame(data['custID'][0].tolist(), columns=['custID'])\n",
 "data = data.merge(df2, on='custID')\n",
 "data[data['cust_age']<50]['custID'].tolist()"
 ]
 }
 ],
 "metadata": {
 "kernelspec": {
 "display_name": "Python 3",
 "language": "python",
 "name": "python3"
 },
 "language_info": {
 "codemirror_mode": {
 "name": "ipython",
 "version": 3
 },
 "file_extension": ".py",
 "mimetype": "text/x-python",
 "name": "python",
 "nbconvert_exporter": "python",
 "pygments_lexer": "ipython3",
 "version": "3.7.3"
 }
 },
 "nbformat": 4,
 "nbformat_minor": 2
 }