Solved

Listpopulator with 2 prefixes

P
Badge

Hi,

I don't really know how to solve my current issue.

Context

I have data as follow :

IDCIS1CIS2CIS3TPS1TPS2TPS30001AMACHLLOR780102414200002LIVAMARBN681702895..................

 

 

The TPS columns are times in seconds.

 

I need to add a delay to these times regarding the corresponding CISX value (e.g. add 1000 when CIS is 'AMA', add 1500 when CIS is LOR...).

Then I need to sort the values to get the quicker CIS regarding to the TPS time with the delay added.

Ideas

To do that I think that putting all my CIS and TPS in a list with two attributes can help me, but I can't find how to do it.

What I think I need to have as a first result (to start manipulating my data) :

ID0001List{0}.CISAMAList{0}.TPS780List{1}.CISCHLList{1}.TPS1024List{2}.CISLORList{2}.TPS1420

 

I tried with the ListPopulator, it works fine if I give it CIS as prefix I get my list but of the CIS only.

I can put two ListPopulator, one with CIS as prefix then one with TPS as prefix, but I get two different lists.

How can I create a single list with 2 attributes in it (CIS and TPS) ?

Any other ideas ?

If you think about other ideas to handle my need (add delay, sort and get quicker CIS), please tell me !

icon

Best answer by takashi 1 August 2019, 17:39

View original

16 replies

erik_jan
Rank
Userlevel 2
Badge +16

Have you tried using the ListExpressionPopulator

You could use a regular expression ^[CIS|TPS] for the right attributes.

Hope this helps.

P
Badge

Have you tried using the ListExpressionPopulator

You could use a regular expression ^[CIS|TPS] for the right attributes.

Hope this helps.

I tried with the regexp ^CIS|^TPS (which doesn't produce the same result as your regexp strangely...).

But the result is not as expected, I have a list with the 6 values like this :

ID0001List{0}AMAList{1}CHLList{2}LOR

 

List{3}780List{4}1024

 

List{5}1420
erik_jan
Rank
Userlevel 2
Badge +16

Have you tried using the ListExpressionPopulator

You could use a regular expression ^[CIS|TPS] for the right attributes.

Hope this helps.

And how about using the AttributeManager, renaming CIS1 to List{0}.CIS , TPS1 to List{0}.TPS and so on.

This only works if the source attributes are always the same.

takashi
Rank
Userlevel 2
Badge +17

Hi @p.jeremie, 

I have two ideas:

BulkAttributeRenamer with Regular Expression Replace mode

0684Q00000ArJwKQAV.png

 

JSONTemplater and JSONFlattener

Template Expression

{
    "List" : [
        let $c := fme:get-list-attribute('CIS{}')
        let $t := fme:get-list-attribute('TPS{}')
        for $i in (1 to count($c))
        return
        {
            "CIS" : $c[$i],
            "TPS" : $t[$i]
        }
    ]
}

0684Q00000ArK05QAF.png

ebygomm
Rank
Userlevel 1
Badge +21

How is the information on what figures are needed to be added for each letter combination stored, and how many are there?

P
Badge

Hi @p.jeremie, 

I have two ideas:

BulkAttributeRenamer with Regular Expression Replace mode

0684Q00000ArJwKQAV.png

 

JSONTemplater and JSONFlattener

Template Expression

{
    "List" : [
        let $c := fme:get-list-attribute('CIS{}')
        let $t := fme:get-list-attribute('TPS{}')
        for $i in (1 to count($c))
        return
        {
            "CIS" : $c[$i],
            "TPS" : $t[$i]
        }
    ]
}

0684Q00000ArK05QAF.png

@takashi I like your BulkAttributeRenamer solution ! In the meantime I used the ListZipper transformer to achieve my goal but I prefer this solution. Thanks a lot.

P
Badge

How is the information on what figures are needed to be added for each letter combination stored, and how many are there?

@egomm The delay to be add are in a xlsx file with the key CIS and value in a Delay column.

 

 

CIS

 

Delay

 

AMA

 

1000

 

CHL

 

700

 

LOR

 

1500

 

LIV

 

1000

 

RBN

 

300

 

...

 

...

 

 

For the moment my solution is to explode the list I created and perform a FeatureJoiner on the CIS key. This way I can create a new attribute TPSnew = @Evaluate(@Value(TPS)+@Value(DELAY))

I still have to sort and re-create my CIS1, CIS2... columns.

ebygomm
Rank
Userlevel 1
Badge +21

@egomm The delay to be add are in a xlsx file with the key CIS and value in a Delay column.

 

 

CIS

 

Delay

 

AMA

 

1000

 

CHL

 

700

 

LOR

 

1500

 

LIV

 

1000

 

RBN

 

300

 

...

 

...

 

 

For the moment my solution is to explode the list I created and perform a FeatureJoiner on the CIS key. This way I can create a new attribute TPSnew = @Evaluate(@Value(TPS)+@Value(DELAY))

I still have to sort and re-create my CIS1, CIS2... columns.

I'd probably use a python caller to create a list.

0684Q00000ArMLtQAN.png

But if you're using python anyway, I'd try and do the addition as well, to avoid having to explode and recombine

 

import fme
import fmeobjects

def processFeature(feature):
    lookup = {'AMA':1000,'CHL':700,'LOR':1500,'LIV':1000,'RBN':300}
    tpslist = []
    for i in range(1,4):
        cis = feature.getAttribute('CIS'+str(i))
        tps = feature.getAttribute('TPS'+str(i))
        newtps = tps + float(lookup.get(cis))
        tpslist.append((i,newtps))
        
        feature.setAttribute('NewTPS'+str(i),newtps)
    
    lowest =  (min(tpslist, key = lambda t: t[1]))
    feature.setAttribute('lowest','TPS'+str(lowest[0]))

 

Which would give you this sort of output

0684Q00000ArN22QAF.png

P
Badge

I'd probably use a python caller to create a list.

0684Q00000ArMLtQAN.png

But if you're using python anyway, I'd try and do the addition as well, to avoid having to explode and recombine

 

import fme
import fmeobjects

def processFeature(feature):
    lookup = {'AMA':1000,'CHL':700,'LOR':1500,'LIV':1000,'RBN':300}
    tpslist = []
    for i in range(1,4):
        cis = feature.getAttribute('CIS'+str(i))
        tps = feature.getAttribute('TPS'+str(i))
        newtps = tps + float(lookup.get(cis))
        tpslist.append((i,newtps))
        
        feature.setAttribute('NewTPS'+str(i),newtps)
    
    lowest =  (min(tpslist, key = lambda t: t[1]))
    feature.setAttribute('lowest','TPS'+str(lowest[0]))

 

Which would give you this sort of output

0684Q00000ArN22QAF.png

@egomm Thank you for your proposal.

Can you explain me the part below ?

lowest =  (min(tpslist, key = lambda t: t[1]))    feature.setAttribute('lowest','TPS'+str(lowest[0]))

And how can I use my Excel data of the "Delay table" in the PythonCaller ? I saw you put them in a lookup dictionary but I don't know how to recreate it...

 

In fact, to simplify I said I need to get the quicker TPS, but in reality I need to sort all my CISx with their new time.

So I need to have this result :

Start Table

IDCIS1CIS2CIS3TPS1TPS2TPS30001AMACHLLOR780102414200002LIVAMARBN681702895.....................

 

Delay table

CIS

 

Delay

 

AMA

 

1000

 

CHL

 

700

 

LOR

 

1500

 

LIV

 

1000

 

RBN

 

300

 

...

 

...

New Table (underlined CIS have change place with the time sort)

 

IDCIS1CIS2CIS3newTPS1newTPS2newTPS30001CHLAMA

 

LOR1724178029200002RBNLIVAMA

 

119516811702.....................

 

I assume it can be quicker/more efficient to to all this in python but I don't know how to do it as I'm not used to code python.

And I'm wondering if this kind of processing is stable on a long term (new versions of python). Is it not better to used fme transformer to ensure that my workspace will continue to work well ? i would appreciate any advice on this subject.

P
Badge

And how about using the AttributeManager, renaming CIS1 to List{0}.CIS , TPS1 to List{0}.TPS and so on.

This only works if the source attributes are always the same.

Good idea, I didn't know it was possible to do so.

But I prefer the takashi solution. Thanks anyway !

ebygomm
Rank
Userlevel 1
Badge +21

@egomm Thank you for your proposal.

Can you explain me the part below ?

lowest =  (min(tpslist, key = lambda t: t[1]))    feature.setAttribute('lowest','TPS'+str(lowest[0]))

And how can I use my Excel data of the "Delay table" in the PythonCaller ? I saw you put them in a lookup dictionary but I don't know how to recreate it...

 

In fact, to simplify I said I need to get the quicker TPS, but in reality I need to sort all my CISx with their new time.

So I need to have this result :

Start Table

IDCIS1CIS2CIS3TPS1TPS2TPS30001AMACHLLOR780102414200002LIVAMARBN681702895.....................

 

Delay table

CIS

 

Delay

 

AMA

 

1000

 

CHL

 

700

 

LOR

 

1500

 

LIV

 

1000

 

RBN

 

300

 

...

 

...

New Table (underlined CIS have change place with the time sort)

 

IDCIS1CIS2CIS3newTPS1newTPS2newTPS30001CHLAMA

 

LOR1724178029200002RBNLIVAMA

 

119516811702.....................

 

I assume it can be quicker/more efficient to to all this in python but I don't know how to do it as I'm not used to code python.

And I'm wondering if this kind of processing is stable on a long term (new versions of python). Is it not better to used fme transformer to ensure that my workspace will continue to work well ? i would appreciate any advice on this subject.

I generally work on the idea that if something can be done in fme in less than 5 transformers, I'd choose that over a python solution (with some exceptions for certain things where python is just much faster). 

 

What you are trying to achieve is to me much more straightforward to do in python than in FME (and i only dabble in python in FME, i'm not an expert)

 

The last bit of the python is finding the minimum value of your new TPS values, and writing that to a new attribute. If you actually want the CIS2 to become CIS1 and TPS2 to become TPS1 based on values you need a slightly different process

ebygomm
Rank
Userlevel 1
Badge +21

I generally work on the idea that if something can be done in fme in less than 5 transformers, I'd choose that over a python solution (with some exceptions for certain things where python is just much faster). 

 

What you are trying to achieve is to me much more straightforward to do in python than in FME (and i only dabble in python in FME, i'm not an expert)

 

The last bit of the python is finding the minimum value of your new TPS values, and writing that to a new attribute. If you actually want the CIS2 to become CIS1 and TPS2 to become TPS1 based on values you need a slightly different process

I've attached a template file that will create your NewTable

import fme
import fmeobjects

def processFeature(feature):
    #dictionary of lookup values
    lookup = {'AMA':1000,'CHL':700,'LOR':1500,'LIV':1000,'RBN':300}
    
    #create list
    tpslist = []
    for i in range(1,4):
        cis = feature.getAttribute('CIS'+str(i))
        tps = feature.getAttribute('TPS'+str(i))
        #add delay to tps based on cis value
        newtps = tps + float(lookup.get(cis))
        #add values to list
        tpslist.append((cis,newtps))
    #sort list in ascending order by second item in tuple
    tpslist.sort(key=lambda tup: tup[1])
    #step through ordered list and create new attributes named in same order as list
    for i,(cis,newtps) in enumerate(tpslist):
        feature.setAttribute('CIS'+str(i+1),cis)
        feature.setAttribute('NewTPS'+str(i+1),newtps)

list_populator_community.fmwt

@p.jeremie updated template with lookup from excel

P
Badge

I've attached a template file that will create your NewTable

import fme
import fmeobjects

def processFeature(feature):
    #dictionary of lookup values
    lookup = {'AMA':1000,'CHL':700,'LOR':1500,'LIV':1000,'RBN':300}
    
    #create list
    tpslist = []
    for i in range(1,4):
        cis = feature.getAttribute('CIS'+str(i))
        tps = feature.getAttribute('TPS'+str(i))
        #add delay to tps based on cis value
        newtps = tps + float(lookup.get(cis))
        #add values to list
        tpslist.append((cis,newtps))
    #sort list in ascending order by second item in tuple
    tpslist.sort(key=lambda tup: tup[1])
    #step through ordered list and create new attributes named in same order as list
    for i,(cis,newtps) in enumerate(tpslist):
        feature.setAttribute('CIS'+str(i+1),cis)
        feature.setAttribute('NewTPS'+str(i+1),newtps)

list_populator_community.fmwt

@p.jeremie updated template with lookup from excel

Great !

Now I need to find how can I use my Excel data of the "Delay table" in the PythonCaller. I saw you put them in a lookup dictionary but I don't know how to recreate it... 

ebygomm
Rank
Userlevel 1
Badge +21

Great !

Now I need to find how can I use my Excel data of the "Delay table" in the PythonCaller. I saw you put them in a lookup dictionary but I don't know how to recreate it...

Does the lookup table change?

P
Badge

Does the lookup table change?

Yes it can change (keys and delays)... That's why I can't write it raw in the PythonCaller.

ebygomm
Rank
Userlevel 1
Badge +21

Yes it can change (keys and delays)... That's why I can't write it raw in the PythonCaller.

I've updated the template with an example of how you could do this - it's probably not the most efficient but it works

Reply


Community Stats

28,709
Posts
109,481
Replies
36,554
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.