def test_outerjoin_2():

    table1 = (('id', 'colour'),
              (1, 'blue'),
              (2, 'red'),
              (3, 'purple'))
    table2 = (('id', 'shape'),
              (0, 'pentagon'),
              (1, 'circle'),
              (3, 'square'),
              (4, 'ellipse'),
              (5, 'triangle'))
    table3 = outerjoin(table1, table2, key='id')
    expect3 = (('id', 'colour', 'shape'),
               (0, None, 'pentagon'),
               (1, 'blue', 'circle'),
               (2, 'red', None),
               (3, 'purple', 'square'),
               (4, None, 'ellipse'),
               (5, None, 'triangle'))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice

    # natural join
    table4 = outerjoin(table1, table2)
    expect4 = expect3
    ieq(expect4, table4)
Exemplo n.º 2
0
def test_outerjoin_novaluefield():
    table1 = (('id', 'colour'),
              (0, 'black'),
              (1, 'blue'),
              (2, 'red'),
              (3, 'purple'),
              (5, 'yellow'),
              (7, 'white'))
    table2 = (('id', 'shape'),
              (1, 'circle'),
              (3, 'square'),
              (4, 'ellipse'))
    expect = (('id', 'colour', 'shape'),
              (0, 'black', None),
              (1, 'blue', 'circle'),
              (2, 'red', None),
              (3, 'purple', 'square'),
              (4, None, 'ellipse'),
              (5, 'yellow', None),
              (7, 'white', None))
    actual = outerjoin(table1, table2, key='id')
    ieq(expect, actual)
    actual = outerjoin(cut(table1, 'id'), table2, key='id')
    ieq(cut(expect, 'id', 'shape'), actual)
    actual = outerjoin(table1, cut(table2, 'id'), key='id')
    ieq(cut(expect, 'id', 'colour'), actual)
    actual = outerjoin(cut(table1, 'id'), cut(table2, 'id'), key='id')
    ieq(cut(expect, 'id'), actual)
Exemplo n.º 3
0
def test_outerjoin_novaluefield():
    table1 = (('id', 'colour'), (0, 'black'), (1, 'blue'), (2, 'red'),
              (3, 'purple'), (5, 'yellow'), (7, 'white'))
    table2 = (('id', 'shape'), (1, 'circle'), (3, 'square'), (4, 'ellipse'))
    expect = (('id', 'colour', 'shape'), (0, 'black', None),
              (1, 'blue', 'circle'), (2, 'red', None), (3, 'purple', 'square'),
              (4, None, 'ellipse'), (5, 'yellow', None), (7, 'white', None))
    actual = outerjoin(table1, table2, key='id')
    ieq(expect, actual)
    actual = outerjoin(cut(table1, 'id'), table2, key='id')
    ieq(cut(expect, 'id', 'shape'), actual)
    actual = outerjoin(table1, cut(table2, 'id'), key='id')
    ieq(cut(expect, 'id', 'colour'), actual)
    actual = outerjoin(cut(table1, 'id'), cut(table2, 'id'), key='id')
    ieq(cut(expect, 'id'), actual)
Exemplo n.º 4
0
 def transform_resource(self, source, target):
     if isinstance(self.__resource, str):
         self.__resource = source.package.get_resource(self.__resource)
     self.__resource.infer(only_sample=True)
     view1 = source.to_petl()
     view2 = self.__resource.to_petl()
     if self.__mode == "inner":
         join = petl.hashjoin if self.__hash else petl.join
         target.data = join(view1, view2, self.__field_name)
     elif self.__mode == "left":
         leftjoin = petl.hashleftjoin if self.__hash else petl.leftjoin
         target.data = leftjoin(view1, view2, self.__field_name)
     elif self.__mode == "right":
         rightjoin = petl.hashrightjoin if self.__hash else petl.rightjoin
         target.data = rightjoin(view1, view2, self.__field_name)
     elif self.__mode == "outer":
         target.data = petl.outerjoin(view1, view2, self.__field_name)
     elif self.__mode == "cross":
         target.data = petl.crossjoin(view1, view2)
     elif self.__mode == "anti":
         antijoin = petl.hashantijoin if self.__hash else petl.antijoin
         target.data = antijoin(view1, view2, self.__field_name)
     if self.__mode not in ["anti"]:
         for field in self.__resource.schema.fields:
             if field.name != self.__field_name:
                 target.schema.fields.append(field.to_copy())
Exemplo n.º 5
0
 def transform_resource(self, resource):
     target = resource
     source = self.get("resource")
     field_name = self.get("fieldName")
     use_hash = self.get("useHash")
     mode = self.get("mode")
     if isinstance(source, str):
         source = target.package.get_resource(source)
     source.infer()
     view1 = target.to_petl()
     view2 = source.to_petl()
     if mode not in ["negate"]:
         for field in source.schema.fields:
             if field.name != field_name:
                 target.schema.fields.append(field.to_copy())
     if mode == "inner":
         join = petl.hashjoin if use_hash else petl.join
         resource.data = join(view1, view2, field_name)
     elif mode == "left":
         leftjoin = petl.hashleftjoin if use_hash else petl.leftjoin
         resource.data = leftjoin(view1, view2, field_name)
     elif mode == "right":
         rightjoin = petl.hashrightjoin if use_hash else petl.rightjoin
         resource.data = rightjoin(view1, view2, field_name)
     elif mode == "outer":
         resource.data = petl.outerjoin(view1, view2, field_name)
     elif mode == "cross":
         resource.data = petl.crossjoin(view1, view2)
     elif mode == "negate":
         antijoin = petl.hashantijoin if use_hash else petl.antijoin
         resource.data = antijoin(view1, view2, field_name)
def test_outerjoin_multiple():

    table1 = (('id', 'color', 'cost'),
              (1, 'blue', 12),
              (1, 'red', 8),
              (2, 'yellow', 15),
              (2, 'orange', 5),
              (3, 'purple', 4),
              (4, 'chartreuse', 42))

    table2 = (('id', 'shape', 'size'),
              (1, 'circle', 'big'),
              (2, 'square', 'tiny'),
              (2, 'square', 'big'),
              (3, 'ellipse', 'small'),
              (3, 'ellipse', 'tiny'),
              (5, 'didodecahedron', 3.14159265))

    actual = outerjoin(table1, table2, key='id')
    expect = (('id', 'color', 'cost', 'shape', 'size'),
              (1, 'blue', 12, 'circle', 'big'),
              (1, 'red', 8, 'circle', 'big'),
              (2, 'yellow', 15, 'square', 'tiny'),
              (2, 'yellow', 15, 'square', 'big'),
              (2, 'orange', 5, 'square', 'tiny'),
              (2, 'orange', 5, 'square', 'big'),
              (3, 'purple', 4, 'ellipse', 'small'),
              (3, 'purple', 4, 'ellipse', 'tiny'),
              (4, 'chartreuse', 42, None, None),
              (5, None, None, 'didodecahedron', 3.14159265))

    ieq(expect, actual)
Exemplo n.º 7
0
def test_outerjoin_fieldorder():

    table1 = (('colour', 'id'), ('blue', 1), ('red', 2), ('purple', 3))
    table2 = (('id', 'shape'), (1, 'circle'), (3, 'square'), (4, 'ellipse'))
    table3 = outerjoin(table1, table2, key='id')
    expect3 = (('colour', 'id', 'shape'), ('blue', 1, 'circle'),
               ('red', 2, None), ('purple', 3, 'square'), (None, 4, 'ellipse'))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 8
0
 def process_join(self):
     hpa_merged_table = (petl.outerjoin(
         self.hpa_normal_table,
         self.hpa_rna_table,
         key='gene',
         presorted=True).addfield(
             'expression',
             lambda rec: format_expression_with_rna(rec)).cut('expression'))
     return hpa_merged_table
Exemplo n.º 9
0
def test_outerjoin_empty():

    table1 = (('id', 'colour'), (0, 'black'), (1, 'blue'), (2, 'red'),
              (3, 'purple'), (5, 'yellow'), (7, 'white'))
    table2 = (('id', 'shape'), )
    table3 = outerjoin(table1, table2, key='id')
    expect3 = (('id', 'colour', 'shape'), (0, 'black', None),
               (1, 'blue', None), (2, 'red', None), (3, 'purple', None),
               (5, 'yellow', None), (7, 'white', None))
    ieq(expect3, table3)
Exemplo n.º 10
0
def join(data, strategy, source_left, source_right, destination, key_left,
         key_right, prefix_left, prefix_right, presorted, buffersize, tempdir,
         cache, missing):
    """Perform a join on two data tables."""
    source_left = data.get(source_left)
    source_right = data.get(source_right)

    kwargs = {}
    if key_left == key_right:
        kwargs['key'] = key_left
    else:
        kwargs['lkey'] = key_left
        kwargs['rkey'] = key_right

    if presorted is True:
        kwargs['presorted'] = presorted

    if buffersize is not None:
        kwargs['buffersize'] = buffersize

    if tempdir:
        kwargs['tempdir'] = tempdir

    if 'anti' not in strategy:
        if prefix_left is not None:
            kwargs['lprefix'] = prefix_left
        if prefix_right is not None:
            kwargs['rprefix'] = prefix_right

    if strategy not in ['join', 'antijoin', 'hashjoin', 'hashantijoin']:
        kwargs['missing'] = missing

    if strategy == 'join':
        o = petl.join(source_left, source_right, **kwargs)
    elif strategy == 'leftjoin':
        o = petl.leftjoin(source_left, source_right, **kwargs)
    elif strategy == 'lookupjoin':
        o = petl.lookupjoin(source_left, source_right, **kwargs)
    elif strategy == 'rightjoin':
        o = petl.rightjoin(source_left, source_right, **kwargs)
    elif strategy == 'outerjoin':
        o = petl.outerjoin(source_left, source_right, **kwargs)
    elif strategy == 'antijoin':
        o = petl.antijoin(source_left, source_right, **kwargs)
    elif strategy == 'hashjoin':
        o = petl.antijoin(source_left, source_right, **kwargs)
    elif strategy == 'hashleftjoin':
        o = petl.hashleftjoin(source_left, source_right, **kwargs)
    elif strategy == 'hashlookupjoin':
        o = petl.hashlookupjoin(source_left, source_right, **kwargs)
    elif strategy == 'hashrightjoin':
        o = petl.hashrightjoin(source_left, source_right, **kwargs)

    data.set(destination, o)
Exemplo n.º 11
0
def test_outerjoin_prefix():

    table1 = (('id', 'colour'), (0, 'black'), (1, 'blue'), (2, 'red'),
              (3, 'purple'), (5, 'yellow'), (7, 'white'))
    table2 = (('id', 'shape'), (1, 'circle'), (3, 'square'), (4, 'ellipse'))
    table3 = outerjoin(table1, table2, key='id', lprefix='l_', rprefix='r_')
    expect3 = (('l_id', 'l_colour', 'r_shape'), (0, 'black', None), (1, 'blue',
                                                                     'circle'),
               (2, 'red', None), (3, 'purple', 'square'), (4, None, 'ellipse'),
               (5, 'yellow', None), (7, 'white', None))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 12
0
def test_outerjoin_2():

    table1 = (("id", "colour"), (1, "blue"), (2, "red"), (3, "purple"))
    table2 = (("id", "shape"), (0, "pentagon"), (1, "circle"), (3, "square"), (4, "ellipse"), (5, "triangle"))
    table3 = outerjoin(table1, table2, key="id")
    expect3 = (
        ("id", "colour", "shape"),
        (0, None, "pentagon"),
        (1, "blue", "circle"),
        (2, "red", None),
        (3, "purple", "square"),
        (4, None, "ellipse"),
        (5, None, "triangle"),
    )
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice

    # natural join
    table4 = outerjoin(table1, table2)
    expect4 = expect3
    ieq(expect4, table4)
Exemplo n.º 13
0
def load_into_warehouse():
    api = WarehouseConnector()

    odoo = extract_odoo()
    backend = extract_backend()
    planday = extract_planday()

    # Make a flat table to compare
    odoo_with_backend = outerjoin(odoo, backend, lkey='backend_username', rkey='backend_username')
    odoo_with_planday = outerjoin(odoo, planday, key='salary_id')

    odoo_with_backend = odoo_with_backend.addfield('etl_timestamp', timestamp)
    odoo_with_planday = odoo_with_planday.addfield('etl_timestamp', timestamp)

    write_to_log(odoo_with_backend, 'odoo_with_backend', 'outerjoin')
    write_to_log(odoo_with_planday, 'odoo_with_planday', 'outerjoin')

    odoo_with_backend.toxlsx(ODOO_WITH_BACKEND_FILENAME)
    odoo_with_planday.toxlsx(ODOO_WITH_PLANDAY_FILENAME)

    odoo_with_planday.appenddb(api.db, 'sync_police_uk_drivers_planday_vs_odoo', schema='tableau')
    odoo_with_backend.appenddb(api.db, 'sync_police_uk_drivers_backend_vs_odoo', schema='tableau')
Exemplo n.º 14
0
def test_outerjoin_lrkey():

    table1 = (('id', 'colour'), (0, 'black'), (1, 'blue'), (2, 'red'),
              (3, 'purple'), (5, 'yellow'), (7, 'white'))
    table2 = (('identifier', 'shape'), (1, 'circle'), (3, 'square'),
              (4, 'ellipse'))
    table3 = outerjoin(table1, table2, lkey='id', rkey='identifier')
    expect3 = (('id', 'colour', 'shape'), (0, 'black', None), (1, 'blue',
                                                               'circle'),
               (2, 'red', None), (3, 'purple', 'square'), (4, None, 'ellipse'),
               (5, 'yellow', None), (7, 'white', None))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 15
0
def test_outerjoin_fieldorder():

    table1 = (("colour", "id"), ("blue", 1), ("red", 2), ("purple", 3))
    table2 = (("id", "shape"), (1, "circle"), (3, "square"), (4, "ellipse"))
    table3 = outerjoin(table1, table2, key="id")
    expect3 = (
        ("colour", "id", "shape"),
        ("blue", 1, "circle"),
        ("red", 2, None),
        ("purple", 3, "square"),
        (None, 4, "ellipse"),
    )
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 16
0
def test_outerjoin_empty():

    table1 = (("id", "colour"), (0, "black"), (1, "blue"), (2, "red"), (3, "purple"), (5, "yellow"), (7, "white"))
    table2 = (("id", "shape"),)
    table3 = outerjoin(table1, table2, key="id")
    expect3 = (
        ("id", "colour", "shape"),
        (0, "black", None),
        (1, "blue", None),
        (2, "red", None),
        (3, "purple", None),
        (5, "yellow", None),
        (7, "white", None),
    )
    ieq(expect3, table3)
Exemplo n.º 17
0
def test_outerjoin_lrkey():

    table1 = (("id", "colour"), (0, "black"), (1, "blue"), (2, "red"), (3, "purple"), (5, "yellow"), (7, "white"))
    table2 = (("identifier", "shape"), (1, "circle"), (3, "square"), (4, "ellipse"))
    table3 = outerjoin(table1, table2, lkey="id", rkey="identifier")
    expect3 = (
        ("id", "colour", "shape"),
        (0, "black", None),
        (1, "blue", "circle"),
        (2, "red", None),
        (3, "purple", "square"),
        (4, None, "ellipse"),
        (5, "yellow", None),
        (7, "white", None),
    )
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 18
0
def test_outerjoin_prefix():

    table1 = (("id", "colour"), (0, "black"), (1, "blue"), (2, "red"), (3, "purple"), (5, "yellow"), (7, "white"))
    table2 = (("id", "shape"), (1, "circle"), (3, "square"), (4, "ellipse"))
    table3 = outerjoin(table1, table2, key="id", lprefix="l_", rprefix="r_")
    expect3 = (
        ("l_id", "l_colour", "r_shape"),
        (0, "black", None),
        (1, "blue", "circle"),
        (2, "red", None),
        (3, "purple", "square"),
        (4, None, "ellipse"),
        (5, "yellow", None),
        (7, "white", None),
    )
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 19
0
def test_outerjoin_fieldorder():

    table1 = (('colour', 'id'),
              ('blue', 1),
              ('red', 2),
              ('purple', 3))
    table2 = (('id', 'shape'),
              (1, 'circle'),
              (3, 'square'),
              (4, 'ellipse'))
    table3 = outerjoin(table1, table2, key='id')
    expect3 = (('colour', 'id', 'shape'),
               ('blue', 1, 'circle'),
               ('red', 2, None),
               ('purple', 3, 'square'),
               (None, 4, 'ellipse'))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 20
0
def test_outerjoin_empty():

    table1 = (('id', 'colour'),
              (0, 'black'),
              (1, 'blue'),
              (2, 'red'),
              (3, 'purple'),
              (5, 'yellow'),
              (7, 'white'))
    table2 = (('id', 'shape'),)
    table3 = outerjoin(table1, table2, key='id')
    expect3 = (('id', 'colour', 'shape'),
               (0, 'black', None),
               (1, 'blue', None),
               (2, 'red', None),
               (3, 'purple', None),
               (5, 'yellow', None),
               (7, 'white', None))
    ieq(expect3, table3)
Exemplo n.º 21
0
def join_execute(cl, cr, join, **kwargs):
    cl, cr = cl(), cr()
    if 'addLfields' in kwargs:
        cl = etl.addfields(cl, kwargs['addLfields'])
    if 'addRfields' in kwargs:
        cr = etl.addfields(cr, kwargs['addRfields'])
    args = cl, cr
    if join == Join.UNION:
        c = etl.crossjoin(*args)
    else:
        kwargs = filter_keys(kwargs,
                             ("key", "lkey", "rkey", "missing", "presorted",
                              "buffersize", "tempdir", "cache"))
        if join == Join.INNER:
            c = etl.join(*args, **kwargs)
        elif join == Join.LEFT:
            c = etl.leftjoin(*args, **kwargs)
        elif join == Join.RIGHT:
            c = etl.rightjoin(*args, **kwargs)
        elif join == Join.FULL:
            c = etl.outerjoin(*args, **kwargs)
    return c
Exemplo n.º 22
0
def test_outerjoin_prefix():

    table1 = (('id', 'colour'),
              (0, 'black'),
              (1, 'blue'),
              (2, 'red'),
              (3, 'purple'),
              (5, 'yellow'),
              (7, 'white'))
    table2 = (('id', 'shape'),
              (1, 'circle'),
              (3, 'square'),
              (4, 'ellipse'))
    table3 = outerjoin(table1, table2, key='id', lprefix='l_', rprefix='r_')
    expect3 = (('l_id', 'l_colour', 'r_shape'),
               (0, 'black', None),
               (1, 'blue', 'circle'),
               (2, 'red', None),
               (3, 'purple', 'square'),
               (4, None, 'ellipse'),
               (5, 'yellow', None),
               (7, 'white', None))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 23
0
def test_outerjoin_lrkey():

    table1 = (('id', 'colour'),
              (0, 'black'),
              (1, 'blue'),
              (2, 'red'),
              (3, 'purple'),
              (5, 'yellow'),
              (7, 'white'))
    table2 = (('identifier', 'shape'),
              (1, 'circle'),
              (3, 'square'),
              (4, 'ellipse'))
    table3 = outerjoin(table1, table2, lkey='id', rkey='identifier')
    expect3 = (('id', 'colour', 'shape'),
               (0, 'black', None),
               (1, 'blue', 'circle'),
               (2, 'red', None),
               (3, 'purple', 'square'),
               (4, None, 'ellipse'),
               (5, 'yellow', None),
               (7, 'white', None))
    ieq(expect3, table3)
    ieq(expect3, table3)  # check twice
Exemplo n.º 24
0
    # create petl table from column arrays and rename the columns
    exchangeRates = petl.fromcolumns([BOCDates, BOCRates],
                                     header=['date', 'rate'])

    # print (exchangeRates)

    # load expense document
    try:
        expenses = petl.io.xlsx.fromxlsx('Expenses.xlsx', sheet='Github')
    except Exception as e:
        print('could not open expenses.xlsx:' + str(e))
        sys.exit()

    # join tables
    expenses = petl.outerjoin(exchangeRates, expenses, key='date')

    # fill down missing values
    expenses = petl.filldown(expenses, 'rate')

    # remove dates with no expenses
    expenses = petl.select(expenses, lambda rec: rec.USD != None)

    # add CDN column
    expenses = petl.addfield(expenses, 'CAD',
                             lambda rec: decimal.Decimal(rec.USD) * rec.rate)

    # intialize database connection
    try:
        dbConnection = pymssql.connect(server=destServer,
                                       database=destDatabase)
Exemplo n.º 25
0
# rightjoin()
#############

import petl as etl
table1 = [['id', 'colour'], [1, 'blue'], [2, 'red'], [3, 'purple']]
table2 = [['id', 'shape'], [1, 'circle'], [3, 'square'], [4, 'ellipse']]
table3 = etl.rightjoin(table1, table2, key='id')
table3

# outerjoin()
#############

import petl as etl
table1 = [['id', 'colour'], [1, 'blue'], [2, 'red'], [3, 'purple']]
table2 = [['id', 'shape'], [1, 'circle'], [3, 'square'], [4, 'ellipse']]
table3 = etl.outerjoin(table1, table2, key='id')
table3

# crossjoin()
#############

import petl as etl
table1 = [['id', 'colour'], [1, 'blue'], [2, 'red']]
table2 = [['id', 'shape'], [1, 'circle'], [3, 'square']]
table3 = etl.crossjoin(table1, table2)
table3

# antijoin()
############

import petl as etl
Exemplo n.º 26
0
# outerjoin

table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red'],
          [3, 'purple']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square'],
          [4, 'ellipse']]

from petl import outerjoin, look
look(table1)
look(table2)
table3 = outerjoin(table1, table2, key='id')
look(table3)


# crossjoin

table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square']]

from petl import crossjoin, look
look(table1)
look(table2)
Exemplo n.º 27
0
                'song_title_2017': lambda r: r[0].song_title,
            }).aggregate(
                'song_title_id', {
                    'count_2017': ('artist_count_2017', sum),
                    'song_title_2017': lambda r: r[0].song_title_2017,
                    'artists_2017': ((
                        'artist',
                        'artist_count_2017',
                    ), list)
                }).sort('count_2017',
                        reverse=True).addrownumbers(start=1,
                                                    field='rank_2017'))

totals = (petl.outerjoin(
    song_2018, song_2017, 'song_title_id').select(lambda rec: (rec[
        'count_2018'] or 0) >= 1 or (rec['count_2017'] or 0) > 2).addfield(
            'song_title',
            lambda rec: rec.song_title_2018 or rec.song_title_2017).cutout(
                'song_title_2018', 'song_title_2017', 'song_title_id'))


def rank_change(rec):
    if rec['rank_2017'] is None:
        return 1000
    elif rec['rank_2018'] is None:
        return -1000
    else:
        return rec['rank_2017'] - rec['rank_2018']


winners_losers = totals.addfield('rank_change', rank_change)
winners_losers = winners_losers.sort('rank_change', reverse=True)
Exemplo n.º 28
0
  .aggregate(('artist_id', 'song_title'), {
    'song_count_2017': len,
    'artist_2017': lambda r: r[0].artist,
  })
  .aggregate('artist_id', {
    'count_2017': ('song_count_2017', sum),
    'artist_2017': lambda r: r[0].artist_2017,
    'songs_2017': (('song_title', 'song_count_2017',), list)
  })
  .sort('count_2017', reverse=True)
  .addrownumbers(start=1, field='rank_2017')
)

totals = (
  petl.outerjoin(song_2018, song_2017, 'artist_id')
  .select(lambda rec: (rec['count_2018'] or 0) >= 1 or (rec['count_2017'] or 0) > 2)
  .addfield('artist', lambda rec: rec.artist_2018 or rec.artist_2017)
  .cutout('artist_2018', 'artist_2017', 'artist_id')
)

def rank_change(rec):
  if rec['rank_2017'] is None:
    return 1000
  elif rec['rank_2018'] is None:
    return -1000
  else:
    return rec['rank_2017'] - rec['rank_2018']

winners_losers = totals.addfield('rank_change', rank_change)
winners_losers = winners_losers.sort('rank_change', reverse=True)

for entry in winners_losers.dicts():
Exemplo n.º 29
0
# outerjoin

table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red'],
          [3, 'purple']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square'],
          [4, 'ellipse']]

from petl import outerjoin, look
look(table1)
look(table2)
table3 = outerjoin(table1, table2, key='id')
look(table3)


# crossjoin

table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square']]

from petl import crossjoin, look
look(table1)
look(table2)
Exemplo n.º 30
0
table3


# outerjoin()
#############

import petl as etl
table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red'],
          [3, 'purple']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square'],
          [4, 'ellipse']]
table3 = etl.outerjoin(table1, table2, key='id')
table3


# crossjoin()
#############

import petl as etl
table1 = [['id', 'colour'],
          [1, 'blue'],
          [2, 'red']]
table2 = [['id', 'shape'],
          [1, 'circle'],
          [3, 'square']]
table3 = etl.crossjoin(table1, table2)
table3