Filling in gaps in a series in a list

[mojo2go]

I have a list of sparse values..i.e. some are missing. And for those missing values I want to populate them with the last known value. For example, from the first list below, I want to produce the second list.

 ['25','27','','26','','','','28']
...
['25','27','27','26','26','26','26','28']

I think it requires an accumulator, like in reduce().

[Cobra · Accepted solution]

Hello @mojo2go

It took me a while but I found a way:

WITH ['25','27','','26','','','','28'] AS l
RETURN [x in range (0, size(l)-1) |
    CASE WHEN l[x] = '' THEN
        [t in [y in range(0, x-1) |
            CASE WHEN l[y] <> '' THEN l[y] END] WHERE t IS NOT NULL][-1]
    ELSE l[x] END]

Regards,
Cobra

[mojo2go]

Cobra:

WITH ['25','27','','26','','','','28'] AS l
RETURN [x in range (0, size(l)-1) |
    CASE WHEN l[x] = '' THEN
        [t in [y in range(0, x-1) |
            CASE WHEN l[y] <> '' THEN l[y] END] WHERE t IS NOT NULL][-1]
    ELSE l[x] END]

Nice work Cobra!
It works perfectly but can you help me with how it works?

I can see that the top-level list comprehnsion walks methodically through the original list, and for each element it just writes it back out to a new list if the element is not an empty-string. So far so good!
But if that top-level CASE does detect an empty string, then you run a new list comprehension within the first, just for determining what should be the value for that element.
There you create a new range of all indexes of elements already covered (from zero up-to-but-not-including the current index). Then you walk that list starting from zero, skipping over empty-strings, continually overwriting the value of t. So t keeps changing with every valid value in the list until it has checked all previous values. The last valid value that t holds will be the latest valid value available before our empty-string.

If that is correct, what is the construct you are using when you select that last good value?
I'm confused at "[t in [y i". Are there 3 list comprehensions here?...or two?

[Cobra]

Yes, you are right

There are three comprehension lists, to get the good value, I just select the last value ([-1]) of this result list:

• This part returns the list of values before l[x] but it contains some null values

[y in range(0, x-1) | CASE WHEN l[y] <> '' THEN l[y] END]

• That's why I clean the result list to remove null values from it and select the last value in the list:

[t in $previous_list WHERE t IS NOT NULL ][-1]

• So in the end:

RETURN [x in range (0, size(l)-1) | CASE WHEN l[x] = '' THEN $last_value_of_previous_list ELSE l[x] END]

Hope it helps you to understand, it was very tricky to code

[mojo2go]

Hi Cobra,
This is just for your interest (and part of my learning process). I made a variation on your solution that keeps your top level list comprehension but replaces the inner two list comprehensions with a reduce function. This reduce doesn't accumulate anything it just keeps passing forward the last good value.

WITH ['25','27','','26','','','','28'] AS l
RETURN [x IN range (0, size(l)-1) |
         CASE WHEN l[x] = '' THEN
             reduce(lastgoodx='', y IN range(0,x-1) | CASE WHEN l[y] <> '' THEN l[y] ELSE lastgoodx END)
         ELSE l[x] END]

Joe

[Cobra]

Like this, we are both learning Nice idea!

[mojo2go]

Now I understand the [-1], and the NULL.

Also I didn't realize that you don't need to include a pipe (|) if you're not going to transform the elements.

Thanks so much for the explanation. This was a great learning session.