def get_value_prop_with_conditions(column: str, con_column: str, con_value):
'''
Return the number of values of a certain column with conditions
------
Args:
column(str): column's header
con_column(str): conditional column's header
con_value(any type): conditional column's value
Output:
res(dict): e.g. {'operator001': 47, 'operator002': 26, 'operator003': 26}
'''
conn = sql_connection(sqlite_file)
c = conn.cursor()
c.execute(
"SELECT DISTINCT {} FROM transactions WHERE {} = ? ORDER BY {}".format(
column, con_column, column), (con_value, ))
unique_values = c.fetchall()
res = {}
for (value, ) in unique_values:
c.execute(
"SELECT COUNT(*) FROM transactions WHERE {} = ? AND {} = ?".format(
column, con_column), (value, con_value))
res.update({'{}'.format(value): c.fetchone()[0]})
return res
def get_distinct_value(column: str):
'''
Return distinct values of a column
------
Args:
column(str): column's header
Output:
(list): e.g. [('operator001',), ('operator002',), ('operator003',)]
'''
conn = sql_connection(sqlite_file)
c = conn.cursor()
c.execute("SELECT DISTINCT {} FROM transactions ORDER BY {}".format(
column, column))
return c.fetchall()
def query_rows(column, value):
'''
Query all rows in the table by a certain column,
turn the result into DataFrame
------
Args:
column(str): the name of the column to query
value(str): query by this value
Output:
query_result(df)
'''
conn = sql_connection(sqlite_file)
return pd.read_sql_query(
'SELECT * FROM transactions WHERE {} = (?)'.format(column),
conn,
params=(value, ))
def get_value_prop(column: str):
'''
Return the number of values of a certain column
------
Args:
column(str): column's header
Output:
res(dict): e.g. {'operator001': 47, 'operator002': 26, 'operator003': 26}
'''
conn = sql_connection(sqlite_file)
c = conn.cursor()
c.execute("SELECT DISTINCT {} FROM transactions ORDER BY {}".format(
column, column))
unique_values = c.fetchall()
res = {}
for value in unique_values:
c.execute(
"SELECT COUNT(*) FROM transactions WHERE {} = ?".format(column),
value)
res.update({'{}'.format(value[0]): c.fetchone()[0]})
return res
def heatmap_helper():
'''
Return parameters for building the heatmap
------
Args:
None
Output:
whole_op_list(list): a list of distinct operators' monikers
whole_vm_list(list/matrix): a matrix of vm_ids of all vending machines in an order for heatmap visualization
whole_tx_list(list/matrix): a matrix of transaction amounts of all vending machines in an order for heatmap visualization
'''
conn = sql_connection(sqlite_file)
c = conn.cursor()
c.execute("SELECT DISTINCT operator FROM transactions ORDER BY operator")
distinct_ops = c.fetchall()
whole_op_list = []
whole_vm_list = []
whole_tx_list = []
for (op, ) in distinct_ops:
whole_op_list.append(moniker_name_converter(op))
vm_list = []
tx_list = []
for unit in vm_units:
if unit['operator']['moniker'] == op:
vm = unit['moniker']
vm_id = unit['vm_id']
vm_list.append(vm_id)
c.execute(
"SELECT COUNT(*) FROM transactions WHERE operator = ? AND vending_machine = ?",
(op, vm))
tx_list.append(c.fetchone()[0])
whole_vm_list.append(vm_list)
whole_tx_list.append(tx_list)
return whole_op_list, whole_vm_list, whole_tx_list