def countgroup(table: Arrable, groupOn: list):
"""
groups 'Arrable' by 'groupOn' and counts the number of rows containing a value for 'to_count' in each group
returns arrable with two columns, 'groupOn' and new 'countgroup' columns
"""
# all_groups = _groupby(table, groupOn)
orig_groupOn = groupOn.copy()
group_col_name = "countgroup"
final_columns = table.get_col_names().append(group_col_name)
all_groups = [table]
while groupOn:
next_group_col = groupOn.pop()
print("nextgroup call = ", next_group_col)
new_all_groups = []
for group in all_groups:
new_group = _groupby(group, next_group_col)
new_all_groups.extend(new_group)
all_groups = new_all_groups
list_of_counts = []
for group in all_groups:
list_of_counts.append(len(group.get_rows()))
arrable_rows = []
group_col_name = "countgroup"
final_columns = table.get_col_names().append(group_col_name)
for i, group in enumerate(all_groups):
group.get_rows()[0].update({group_col_name: str(list_of_counts[i])})
arrable_rows.append(group.get_rows()[0])
final_arrable = Arrable().init_from_arrable(final_columns, arrable_rows)
orig_groupOn.append("countgroup")
result = project(final_arrable, orig_groupOn)
return result
def sumgroup(table: Arrable, to_add: str, groupOn: list):
"""
groups 'Arrable' by 'groupOn' and compute the sum of column 'to_add' for each group
returns arrable with two columns, 'groupOn' and new 'sumgroup' columns
"""
orig_groupOn = groupOn.copy()
group_col_name = "sumgroup"
final_columns = table.get_col_names().append(group_col_name)
all_groups = [table]
while groupOn:
next_group_col = groupOn.pop()
print("nextgroup call = ", next_group_col)
new_all_groups = []
for group in all_groups:
new_group = _groupby(group, next_group_col)
new_all_groups.extend(new_group)
all_groups = new_all_groups
list_of_sums = [0] * len(all_groups)
# generate list of sums, one for each group
for index, group in enumerate(all_groups):
for row in group.get_rows():
list_of_sums[index] += int(row[to_add])
# append sumgroup as new column and then append row to new final arrable
arrable_rows = []
for i, group in enumerate(all_groups):
group.get_rows()[0].update({group_col_name: str(list_of_sums[i])})
arrable_rows.append(group.get_rows()[0])
final_arrable = Arrable().init_from_arrable(final_columns, arrable_rows)
orig_groupOn.append("sumgroup")
result = project(final_arrable, orig_groupOn)
return result
def count(table: Arrable):
"""
counts the number of rows containing a value in the specified column 'col_name'
returns int
"""
result = [{"count": len(table.get_rows())}]
col_name = ["count"]
return Arrable().init_from_arrable(col_name, result)
def test_concat(self):
arr1 = Arrable().import_from_file("test_input1")
arr2 = Arrable().import_from_file("test_input1_to_concat")
result = q.concat(arr1, arr2)
# print("arr1 num rows = <", len(arr1.get_rows()), ">\n")
# print("arr2 num rows = <", len(arr2.get_rows()), ">\n")
# print("concat arr num rows = <", len(result.get_rows()), ">\n")
self.assertEqual(len(result.get_rows()), 9)
def _rename_fields_in_table(table: Arrable, table_name):
rows = []
for row in table.get_rows():
newRow = {}
for col in table.get_col_names():
new_col = ''.join([table_name, "_",
col]) # Turn all "col"s to "A_col"
newRow[new_col] = row[col]
rows.append(newRow)
return rows
def _groupby(fromTable: Arrable, groupOn: str):
"""
Returns an arrable per distinct column value as an intermediate step.
To be concatted.
"""
res = defaultdict(list)
groupOn = groupOn.strip()
for row in fromTable.get_rows():
res[row[groupOn]].append(row)
return list([
Arrable().init_from_arrable(fromTable.get_col_names(), groupArr)
for groupArr in res.values()
])
def project(fromTable: Arrable, columns: list):
"""
filters columns specified in params (*arg)
from the specified arrable (fromTable)
returns: Arrable
"""
# columns = list(args)
print("COLUMNS = ", columns)
result = []
for (j, row) in enumerate(fromTable.get_rows()):
new_row = {col: row[col] for col in columns}
result.append(new_row)
newArrable = Arrable().init_from_arrable(columns, result)
return newArrable
def _get_converted_col_tables_for_join(tableA: Arrable, A_name: str,
tableB: Arrable, B_name: str):
renamedArows = _rename_fields_in_table(tableA, A_name)
renamedBrows = _rename_fields_in_table(tableB, B_name)
colsA = list(
map(lambda col: ''.join([A_name, "_", col]), tableA.get_col_names()))
colsB = list(
map(lambda col: ''.join([B_name, "_", col]), tableB.get_col_names()))
newA = Arrable().init_from_arrable(colsA, renamedArows, tableA.pk)
newB = Arrable().init_from_arrable(colsB, renamedBrows, tableB.pk)
return newA, newB
def test_import_from_file_no_head(self):
arr = Arrable().import_from_file("test_input1", header=False)
self.assertEqual([0, 1, 2, 3, 4, 5, 6, 7], arr.get_col_names())
self.assertEqual(
{
0: '36',
1: '14',
2: '2',
3: '38',
4: '49',
5: '15',
6: 'moderate',
' ': 1,
7: 'hey'
},
arr.get_rows()[1])
def test_mov_avg(self):
arr = Arrable().import_from_file("sales1")
res = q.moving_avg(arr, "qty", 2)
rows = res.get_rows()
self.assertEqual(rows[0], 15)
self.assertEqual(rows[1], 23)
self.assertEqual(rows[-1], 12)
def avg(table: Arrable, col_name: str):
"""
computes average of all values in a specified column
does not check for proper data type - will add later
returns int
"""
sum_elts = 0
num_elts = 0
for i, row in enumerate(table.get_rows()):
num_elts += 1
sum_elts += float(row[col_name])
if num_elts == 0:
num_elts = 1
result = [{"avg": sum_elts / num_elts}]
return Arrable().init_from_arrable(["avg"], result)
def moving_op(table: Arrable, col_name: str, sliding_window: int, op,
op_name: str):
newArr = Arrable().init_from_arrable([op_name], [])
stubs = []
for stub in range(0, sliding_window):
slice = table.get_slice(0, stub + 1)
newArr = concat(newArr, op(slice, col_name))
for i, row in enumerate(table.get_rows()):
if i + sliding_window - 1 <= len(table.get_rows()) - 1:
slice = table.get_slice(i,
i + sliding_window) # make this the slice
# print(newArr.get_col_names())
# print(op(slice, col_name).get_rows())
newArr = concat(newArr, op(slice, col_name))
return newArr
def test_import_from_file(self):
arr = Arrable().import_from_file("test_input1")
self.assertEqual([
'saleid', 'itemid', 'customerid', 'storeid', 'time', 'qty',
'pricerange', 'join'
], arr.get_col_names())
self.assertEqual(
arr.get_rows()[0], {
' ': 0,
'saleid': '36',
'itemid': '14',
'join': 'hey',
'customerid': '2',
'storeid': '38',
'time': '49',
'qty': '15',
'pricerange': 'moderate'
})
def test_project(self):
arr = Arrable().import_from_file("test_input1")
result = q.project(arr, "saleid", "itemid", "time")
self.assertEqual({
'saleid': '36',
'itemid': '14',
'time': '49'
},
result.get_rows()[0])
def test_select_math_asymm(self):
arr = Arrable().import_from_file("test_input1")
res = q.select(arr,
"itemid,customerid",
where="(itemid=14) and (customerid*2<5)")
self.assertEqual([{
' ': 0,
'customerid': '2',
'itemid': '14'
}], res.get_rows())
def sort(
fromTable: Arrable, *args: str
): # args can also be None for base case, returning the table itself (recursion)
"""
Order in reverse order so that the earlier columns have higher priority
"""
if not list(args):
return fromTable
orderedPreference = list(args)
colToOrderOn = orderedPreference.pop()
# print(colToOrderOn)
sorted_table_rows = sorted(
fromTable.get_rows(),
key=lambda row: int(row[colToOrderOn])
if row[colToOrderOn].isnumeric() else row[colToOrderOn])
# [print(row) for row in sorted_table_rows]
# print("\n")
newArr = Arrable().init_from_arrable(fromTable.get_col_names(),
sorted_table_rows)
return sort(newArr, *orderedPreference)
def _some(table: Arrable, col_name: str):
"""
function name due to unclear instructions in hw specification. will include other namings to hedge our bets.
computes the sum of all values in a specified column
does not check for proper data type - will add later
returns int
"""
result = 0
for i, row in enumerate(table.get_rows()):
result += int(row[col_name])
return float(result)
def test_join(self):
a = Arrable().import_from_file("test_input1")
b = Arrable().import_from_file("test_input2")
res = q.join(a, "a", b, "b", "a_join = b_j_oin")
self.assertEqual(res.get_rows(), [{
'a_saleid': '36',
'a_itemid': '14',
'a_customerid': '2',
'a_storeid': '38',
'a_time': '49',
'a_qty': '15',
'a_pricerange': 'moderate',
'a_join': 'hey',
'b_saleid': '3506',
'b_I': '13517',
'b_C': '16566',
'b_S': '45',
'b_T': '73',
'b_Q': '19',
'b_P': 'expensive',
'b_j_oin': 'hey'
}])
def join(tableA: Arrable, A_name: str, tableB: Arrable, B_name: str,
where: str):
"""
The where string expects a pre-parsed string where "Table.Column" has already become "Table_Column".
e.g.
join(R, S, "R_price = S_cost and R_cost = S_price")
"""
renamed_cols_tableA, renamed_cols_tableB = _get_converted_col_tables_for_join(
tableA, A_name, tableB, B_name)
joined_cols = renamed_cols_tableA.get_col_names(
) + renamed_cols_tableB.get_col_names() # Concat the lists
where = where.replace(".", "_")
where = WherePredicates(where)
res = []
done = len(tableA.get_rows()) * len(tableB.get_rows())
# print(done)
prog = 0
progress_bar = progressbar.ProgressBar(max_value=100).start()
for Arow in renamed_cols_tableA.get_rows():
intermediate_cartesian = []
# print(prog/done)
progress_bar.update((prog / done) * 100)
for Brow in renamed_cols_tableB.get_rows():
joined_row = {**Arow, **Brow}
intermediate_cartesian.append(joined_row)
prog += 1
for cart_row in intermediate_cartesian:
if where.isMatch(cart_row, joined_cols):
res.append(cart_row)
progress_bar.finish()
newArr = Arrable().init_from_arrable(joined_cols, res)
return newArr
def select(fromTable: Arrable, cols: str, where: str):
"""
e.g.
select(R, R.get_cols_names(), "(time > 50) or (qty < 30)")
select(R, R.get_cols_names(), "qty = 5")
select(R, R.get_cols_names(), "itemid = 7")
"""
orig_cols = fromTable.get_col_names()
cols = cols.strip().split(",")
where = WherePredicates(where)
res = []
for i, row in enumerate(fromTable.get_rows()):
if where.isMatch(row, orig_cols):
val = {entry: row[entry] for entry in cols}
val[" "] = i
res.append(val)
newArr = Arrable().init_from_arrable(cols, res)
return newArr
def concat(table1: Arrable, table2: Arrable):
"""
concats two arrables
returns arrable
"""
if table1.get_col_names() != table2.get_col_names():
print("Table schemas don't match.")
return
col_names = table1.get_col_names()
concated = table1.get_rows() + table2.get_rows()
result = Arrable().init_from_arrable(col_names, concated)
return result
def test_avggroup(self):
arr = Arrable().import_from_file("test_input1")
result = q.avggroup(arr, "qty", "time")
self.assertEqual(result.get_rows(), [{
'avggroup': '15.0',
'time': '49'
}, {
'avggroup': '31.0',
'time': '46'
}, {
'avggroup': '14.0',
'time': '81'
}, {
'avggroup': '43.0',
'time': '67'
}, {
'avggroup': '1.0',
'time': '35'
}])
def test_sumgroup(self):
arr = Arrable().import_from_file("test_input1")
result = q.sumgroup(arr, "qty", "time")
self.assertEqual(result.get_rows(), [{
'sumgroup': '15',
'time': '49'
}, {
'sumgroup': '31',
'time': '46'
}, {
'sumgroup': '14',
'time': '81'
}, {
'sumgroup': '86',
'time': '67'
}, {
'sumgroup': '1',
'time': '35'
}])
def test_groupby(self):
arr = Arrable().import_from_file("test_input1")
resList = q._groupby(arr, "customerid")
self.assertEqual([len(res) for res in resList], [3, 1, 1, 1])
def test_select_multiple_word(self):
arr = Arrable().import_from_file("test_input1")
res = q.select(arr, "itemid", where="(itemid=14) and (saleid=36)")
self.assertEqual([{'itemid': '14', ' ': 0}], res.get_rows())
def test_select_multiple_comma(self):
arr = Arrable().import_from_file("sales1")
res = q.select(arr, "itemid", where="itemid=14,saleid=36")
self.assertEqual([{'itemid': '14', ' ': 0}], res.get_rows())
def test_output(self):
arr = Arrable().import_from_file("test_input1")
arr.output_to_file("test_out_file")
def test_sort_by_columns_single(self):
arr = Arrable().import_from_file("test_input1")
res = q.sort(arr, "qty")
def test_import_from_file_pk(self):
arr = Arrable().import_from_file("sales1", pk="itemid")
def test_sort_by_columns_multiple(self):
arr = Arrable().import_from_file("test_input1")
res = q.sort(arr, "saleid", "qty", "pricerange")
# print(res.get_rows(), [{' ': 0, 'saleid': '36', 'itemid': '14', 'customerid': '2', 'storeid': '38', 'time': '49', 'qty': '15', 'pricerange': 'moderate', 'join': 'hey'}, {' ': 1, 'saleid': '784', 'itemid': '90', 'customerid': '182', 'storeid': '97', 'time': '46', 'qty': '31', 'pricerange': 'moderate', 'join': '1'}, {' ': 5, 'saleid': '951', 'itemid': '102', 'customerid': '116', 'storeid': '45', 'time': '35', 'qty': '1', 'pricerange': 'outrageous', 'join': 'wop'}, {' ': 2, 'saleid': '801', 'itemid': '117', 'customerid': '2', 'storeid': '43', 'time': '81', 'qty': '14', 'pricerange': 'outrageous', 'join': 'g'}, {' ': 3, 'saleid': '905', 'itemid': '79', 'customerid': '119', 'storeid': '81', 'time': '67', 'qty': '44', 'pricerange': 'outrageous', 'join': 'n'}, {' ': 4, 'saleid': '227', 'itemid': '68', 'customerid': '2', 'storeid': '66', 'time': '67', 'qty': '42', 'pricerange': 'supercheap', 'join': 'lol'}])
self.assertEqual(res.get_rows(), [{
' ': 0,
'saleid': '36',
'itemid': '14',
'customerid': '2',
'storeid': '38',
'time': '49',
'qty': '15',
'pricerange': 'moderate',
'join': 'hey'
}, {
' ': 1,
'saleid': '784',
'itemid': '90',
'customerid': '182',
'storeid': '97',
'time': '46',
'qty': '31',
'pricerange': 'moderate',
'join': '1'
}, {
' ': 5,
'saleid': '951',
'itemid': '102',
'customerid': '116',
'storeid': '45',
'time': '35',
'qty': '1',
'pricerange': 'outrageous',
'join': 'wop'
}, {
' ': 2,
'saleid': '801',
'itemid': '117',
'customerid': '2',
'storeid': '43',
'time': '81',
'qty': '14',
'pricerange': 'outrageous',
'join': 'g'
}, {
' ': 3,
'saleid': '905',
'itemid': '79',
'customerid': '119',
'storeid': '81',
'time': '67',
'qty': '44',
'pricerange': 'outrageous',
'join': 'n'
}, {
' ': 4,
'saleid': '227',
'itemid': '68',
'customerid': '2',
'storeid': '66',
'time': '67',
'qty': '42',
'pricerange': 'supercheap',
'join': 'lol'
}])