Most efficient way to concatenate strings in millions of records ignoring nulls and spaces

Question

Hi everyone,

I am trying to concatenate address strings across a dataset that has 3.5 million records. I want to concatenate the values of eight fields into a single string, but many of the fields have null values and many have spaces instead of nulls or extra spaces, etc.

What is the most efficient way in Python or Arcade to join strings ignoring nulls or spaces and just having a single space between the joined values? I usually use a function like " ".join((i for i in (str1,str2,str3) if i)) to do this, but across millions of records I'm hoping to avoid the bottleneck of using an if statement. Also, I'm hoping to avoid having to do trim calculations on each field first. Is there a more efficient way to do this?

Thanks!

DanPatterson · Answer

v = ['a', 'c', 'b', 'd', None]

v*2  # --- just to show you can expand lists
['a', 'c', 'b', 'd', None, 'a', 'c', 'b', 'd', None]

v0 = v*1000000  # lets make one with 5 million values

# ---- As Joshua says, it isn't slow, and there is an `if` statement in the # 
       list comprehension

%timeit ' '.join([x for x in v0 if x is not None ])
217 ms ± 31.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

# ---- What about filter using the first 4 values agains `None`

' '.join(filter(None, v[:4]))
'a c b d'

# ---- no slouch either, and largely insignificant

%timeit ' '.join(filter(None, v0))
170 ms ± 8.33 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

slow coding night 😉

JoshuaBixby · Answer

A Python if statement will be far from the slowest part of this process.

RhettZufelt · Answer

I use this one quite often:

strval = ' '.join(filter(None, [val1, val2, val3,val4]))

The space in the single quotes is the delimiter (single space).  If any of the val variables have a value, it will be concatenated, but, if Null, will just be ignored without error.

R_