def transform_xls(hires_and_promotions_excel, separations_excel, exempt_roster_excel, output_file): hires_and_promotions = petl.io.xls \ .fromxls(hires_and_promotions_excel, sheet='Data') \ .rename(column_map_shared) separations = petl.io.xls \ .fromxls(separations_excel, sheet='Data') \ .rename({**column_map_shared, **column_map_separations}) def dedup_separations(payroll_number, rows): rows_sorted = sorted(rows, key=lambda x: x['termination_date']) return rows_sorted[-1] separations_deduped = petl.rowreduce(separations, 'payroll_number', dedup_separations) exempt_roster = petl.io.xls \ .fromxls(exempt_roster_excel, sheet='Data') \ .rename(column_map_roster) merged = petl.mergesort(hires_and_promotions, separations_deduped, exempt_roster, key='payroll_number') def dedup_merged(payroll_number, rows): rows_sorted = sorted(rows, key=lambda x: x['latest_start_date']) if len(rows_sorted) == 1: return rows_sorted[-1] merged_row = [] for i in range(0, len(rows_sorted[0]) - 1): if (rows_sorted[0][i] == '' or rows_sorted[0][i] == None ) and rows_sorted[1][i] != '' and rows_sorted[1][i] != None: merged_row.append(rows_sorted[1][i]) elif (rows_sorted[1][i] == '' or rows_sorted[1][i] == None ) and rows_sorted[0][i] != '' and rows_sorted[0][i] != None: merged_row.append(rows_sorted[0][i]) elif rows_sorted[0][i] == rows_sorted[1][i]: merged_row.append(rows_sorted[0][i]) else: merged_row.append( rows_sorted[1][i]) ## take latest value by start date return merged_row merged_deduped = petl.rowreduce(merged, 'payroll_number', dedup_merged) petl.tocsv(merged_deduped, source=output_file)
# rowreduce table1 = [['foo', 'bar'], ['a', 3], ['a', 7], ['b', 2], ['b', 1], ['b', 9], ['c', 4]] from petl import rowreduce, look look(table1) def sumbar(key, rows): return [key, sum(row[1] for row in rows)] table2 = rowreduce(table1, key='foo', reducer=sumbar, fields=['foo', 'barsum']) look(table2) # recordreduce table1 = [['foo', 'bar'], ['a', 3], ['a', 7], ['b', 2], ['b', 1], ['b', 9], ['c', 4]] from petl import recordreduce, look look(table1)
# rowreduce table1 = [['foo', 'bar'], ['a', 3], ['a', 7], ['b', 2], ['b', 1], ['b', 9], ['c', 4]] from petl import rowreduce, look look(table1) def sumbar(key, rows): return [key, sum(row[1] for row in rows)] table2 = rowreduce(table1, key='foo', reducer=sumbar, fields=['foo', 'barsum']) look(table2) # recordreduce table1 = [['foo', 'bar'], ['a', 3], ['a', 7], ['b', 2], ['b', 1], ['b', 9], ['c', 4]] from petl import recordreduce, look look(table1)
def reduce_rows(self, columns, reduce_func, headers, presorted=False, **kwargs): """ Group rows by a column or columns, then reduce the groups to a single row. Based on the `rowreduce petl function <https://petl.readthedocs.io/en/stable/transform.html#petl.transform.reductions.rowreduce>`_. For example, the output from the query to get a table's definition is returned as one component per row. The `reduce_rows` method can be used to reduce all those to a single row containg the entire query. .. code-block:: python >>> ddl = rs.query(sql_to_get_table_ddl) >>> ddl.table +--------------+--------------+----------------------------------------------------+ | schemaname | tablename | ddl | +==============+==============+====================================================+ | 'db_scratch' | 'state_fips' | '--DROP TABLE db_scratch.state_fips;' | +--------------+--------------+----------------------------------------------------+ | 'db_scratch' | 'state_fips' | 'CREATE TABLE IF NOT EXISTS db_scratch.state_fips' | +--------------+--------------+----------------------------------------------------+ | 'db_scratch' | 'state_fips' | '(' | +--------------+--------------+----------------------------------------------------+ | 'db_scratch' | 'state_fips' | '\\tstate VARCHAR(1024) ENCODE RAW' | +--------------+--------------+----------------------------------------------------+ | 'db_scratch' | 'state_fips' | '\\t,stusab VARCHAR(1024) ENCODE RAW' | +--------------+--------------+----------------------------------------------------+ >>> reducer_fn = lambda columns, rows: [ ... f"{columns[0]}.{columns[1]}", ... '\\n'.join([row[2] for row in rows])] >>> ddl.reduce_rows( ... ['schemaname', 'tablename'], ... reducer_fn, ... ['tablename', 'ddl'], ... presorted=True) >>> ddl.table +-------------------------+-----------------------------------------------------------------------+ | tablename | ddl | +=========================+=======================================================================+ | 'db_scratch.state_fips' | '--DROP TABLE db_scratch.state_fips;\\nCREATE TABLE IF NOT EXISTS | | | db_scratch.state_fips\\n(\\n\\tstate VARCHAR(1024) ENCODE RAW\\n\\t | | | ,db_scratch.state_fips\\n(\\n\\tstate VARCHAR(1024) ENCODE RAW | | | \\n\\t,stusab VARCHAR(1024) ENCODE RAW\\n\\t,state_name | | | VARCHAR(1024) ENCODE RAW\\n\\t,statens VARCHAR(1024) ENCODE | | | RAW\\n)\\nDISTSTYLE EVEN\\n;' | +-------------------------+-----------------------------------------------------------------------+ `Args:` columns: list The column(s) by which to group the rows. reduce_func: fun The function by which to reduce the rows. Should take the 2 arguments, the columns list and the rows list and return a list. `reducer(columns: list, rows: list) -> list;` headers: list The list of headers for modified table. The length of `headers` should match the length of the list returned by the reduce function. presorted: bool If false, the row will be sorted. `Returns:` `Parsons Table` and also updates self """ # noqa: E501,E261 self.table = petl.rowreduce(self.table, columns, reduce_func, header=headers, presorted=presorted, **kwargs) return self
def reducer_execute(c, **kwargs): r = c() if 'addfields' in kwargs: r = etl.addfields(r, kwargs['addfields']) kwargs = filter_keys(kwargs, ("key", "reducer", "header")) return etl.rowreduce(r, **kwargs)