ETL table selection by Variable

Question

I'm trying to select rows within a table and create a new table with the information from the original table using PETL.

My code right now is:

import petl as etl


table_all = (
    etl.fromcsv("practice_locations.csv")
        .convert('Practice_Name', 'upper')
        .convert('Suburb', str)
        .convert('State', str)
        .convert('Postcode', int)
        .convert('Lat', str)
        .convert('Long', str)
)


def selection(post_code):
    table_selected = etl.select(table_all, "{Postcode} == 'post_code'")
    print(post_code)
    etl.tojson(table_selected, 'location.json', sort_keys=True)

But I cannot seem to populate table_selected by using the selection function as it is. The etl.select call will work if I replace post_code to look like

table_selected = etl.select(table_all, "{Postcode} == 4510")

Which outputs the correct table shown as:

    +--------------------------------+--------------+-------+----------+--------------+--------------+
    | Practice_Name                  | Suburb       | State | Postcode | Lat          | Long         |
    +================================+==============+=======+==========+==============+==============+
    | 'CABOOLTURE COMBINED PRACTICE' | 'Caboolture' | 'QLD' |     4510 | '-27.085007' | '152.951707' |
    +--------------------------------+--------------+-------+----------+--------------+--------------+

I'm sure I am just trying to call post_code in a way that is wrong but have tried everything from the PETL documentation and can't seem to figure it out.

Any help is much appreciated.

Dušan Maďar · Accepted Answer · 2018-09-20T13:34:56.920

2

"{Postcode} == 'post_code'" will not replace post_code with the value passed to your selection function.

You need to format your select string (and escape {Postcode} when using format)

table_selected = etl.select(table_all, "{{Postcode}} == {post_code}".format(post_code=post_code))

Testing this in console

>>> "{{Postcode}} == {post_code}".format(post_code=1234)
'{Postcode} == 1234'

edited Sep 20 '18 at 13:34

answered Sep 20 '18 at 13:23

Dušan Maďar

9,269
5
49
64

1

So close! I had very similar before but forgot to add the second set of braces around Postcode, thank you very much, your help is much appreciated! – JackDG Sep 20 '18 at 13:34

ETL table selection by Variable

1 Answers1