def inspect_continuous_var(varName, conn):
try:
#Min, Max, Mean
sql = '''
select min(?var?*1.) min_?var?,
round(avg(?var?*1.),0) mean_?var?,
max(?var?*1.) max_?var?
from bank
'''.replace('?var?', varName)
print('Statistics for: ' + varName)
Eu.run(sql, conn)
#Min, Max, Mean by class type
sql = '''
select y,count(*) n_people,
min(?var?*1.) min_?var?,
round(avg(?var?*1.),0) mean_?var?,
max(?var?*1.) max_?var?
from bank
group by y
'''.replace('?var?', varName)
print('Statistics for: ' + varName + ', grouped by y')
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
def inspect_continuous_var(varName, conn):
'''
Exercise: Fix this function so that
it fetches information for 'varName'
instead of 'age'.
'''
try:
#Min, Max, Mean
sql = '''
select min(?varN?*1.) min_?varN?,
round(avg(?varN?*1.),0) mean_?varN?,
max(?varN?*1.) max_?varN?
from bank
'''
sql = sql.replace('?varN?', varName)
print('Statistics for: ' + varName)
Eu.run(sql, conn)
#Min, Max, Mean by class type
sql = '''
select y,count(*) n_people,
min(?varN?*1.) min_?varN?,
round(avg(?varN?*1.),0) mean_?varN?,
max(?varN?*1.) max_?varN?
from bank
group by y
'''
sql = sql.replace('?varN?', varName)
print('Statistics for: ' + varName + ', grouped by y')
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
def main():
conn = Eu.connection(data_work)
try:
inspect_continuous_var(varName='age', conn=conn)
inspect_continuous_var(
varName='duration',
conn=conn) #duration in sec of last contact in current campaign
#Exercise : Find other continous variables (Use Data Dictionary on Synopsis Page).
#Exercise : Call inspection on those variables.
inspect_continuous_var(
varName='pdays',
conn=conn) #days since last contacted in this campaign
inspect_continuous_var(varName='previous', conn=conn) # no of contacts
#inspect_continuous_var(varName='cons_conf_idx',conn=conn)#confidence index
#inspect_continuous_var(varName='cons_price_idx',conn=conn)#consumer price
#inspect_continuous_var(varName='emp_var_rate',conn=conn)# employment variation rate
#inspect_continuous_var(varName='nr_employed',conn=conn) # no of employees
except Exception as err:
Eu.print_error(err)
finally:
conn.close()
def main():
conn = Eu.connection(data_work)
try:
sql = '''
SELECT sql FROM sqlite_master
WHERE tbl_name = 'bank' AND type = 'table'
'''
#Eu.run(sql,conn)
##A look at the 'age'
sql = '''
select age*1. from bank limit 10
'''
#Eu.run(sql,conn)
###Average age in sample
sql = '''
select avg(1.*age) mean_age from bank
'''
#Eu.run(sql,conn)
####Min, Max, Mean
sql = '''
select min(age*1.) min_age,
round(avg(age*1.),0) mean_age,
max(age*1.) max_age
from bank
'''
#Eu.run(sql,conn)
#####Min, Max, Mean by class type
sql = '''
select y,count(*) n_people,
min(age*1.) min_age,
round(avg(age*1.),0) mean_age,
max(age*1.) max_age
from bank
group by y
'''
Eu.run(sql, conn)
# Exercise: Fix the function
inspect_continuous_var(varName='duration', conn=conn)
inspect_continuous_var(varName='age', conn=conn)
except Exception as err:
Eu.print_error(err)
finally:
conn.close()
def data_ingestion():
'''
This function reads CSV file
and loads it into a SQLITE
database in Table form.
'''
try:
csvfile = data_folder + 'bank_dataset.csv'
pcsv = Eu.pycsv_reader(csvfile)
#Dump into the database. Specify the tablename,sqlite filename etc
pcsv.to_database(tabName='bank', database=data_work, verbose=False)
except Exception as err:
Eu.print_error(err)
finally:
pcsv.close()
def inspect_job(conn):
try:
#A look at the 'marital'
sql = '''
select distinct job from bank
'''
Eu.run(sql, conn)
#Order the results by descending order of people
sql = '''
select job,count(*) n_people from bank
group by job order by n_people desc
'''
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
def inspect_categorical_var(varName, conn):
'''
Informal Inspection of Variables
Parameters:
varName : Variable to be inspected.
conn : Connection to sqlite database.
'''
try:
sql = '''
select ?varN?, count(*) n_people from bank
group by ?varN?
'''
sql = sql.replace('?varN?', varName)
print(sql)
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
def main():
conn = Eu.connection(data_work)
try:
#Call data_ingestion to create a working database.
data_ingestion()
#Check the column names
sql = '''
SELECT sql FROM sqlite_master
WHERE tbl_name = 'bank' AND type = 'table'
'''
Eu.run(sql, conn)
#A look at the column: 'marital'
sql = '''
select distinct marital
from bank
'''
Eu.run(sql, conn)
#Count how many entries for each value of marital
sql = '''
SELECT marital,
COUNT(*) n_people
FROM bank
GROUP by marital
'''
Eu.run(sql, conn)
#Order the results by descending order of people
sql = '''
SELECT marital,
COUNT(*) n_people
FROM bank
GROUP by marital
ORDER by n_people desc
'''
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
finally:
conn.close()
def main():
conn = Eu.connection(data_work)
try:
sql = '''
SELECT sql
FROM sqlite_master
WHERE tbl_name = 'bank' AND type = 'table'
'''
Eu.run(sql, conn)
#Calling inspect marital function
inspect_marital(conn)
#Exercise: Following function needs fixing
inspect_job(conn)
except Exception as err:
Eu.print_error(err)
finally:
conn.close()
def main():
conn = Eu.connection(data_work)
try:
sql = '''
SELECT sql FROM sqlite_master
WHERE tbl_name = 'bank' AND type = 'table'
'''
Eu.run(sql, conn)
#Calling inspect marital function
inspect_marital(conn)
#Calling inspect marital function
inspect_job(conn)
#Calling inspect categorical function
inspect_categorical_var(varName='job', conn=conn)
except Exception as err:
Eu.print_error(err)
finally:
conn.close()
def inspect_marital(conn):
'''
This function examines the variable 'marital'
'''
try:
#A look at the 'marital'
sql = '''
select distinct marital
from bank
'''
Eu.run(sql, conn)
#Order the results by descending order of people
sql = '''
select marital,
count(*) n_people
from bank
group by marital
order by n_people desc
'''
Eu.run(sql, conn)
except Exception as err:
Eu.print_error(err)
