pandas: split values and join across columns

Question

I have a csv of some user data, that for whatever reason or other has separated the email name and the email domain into two separate columns. Some users also have multiple emails. I would like to join these into a single email or single list, as the case permits.

example:

emailname                          | emaildomain
john.smith; smithj                 | gmail.com, biz.net
sample.name                        | aol.com

I would like to change that to:

email
[[email protected], [email protected]]
[[email protected]] 

from there, it will be pushed to a dictionary, where I will have to iterate over each value in the cell and make an entry from those, which I have a rough idea how to do just using basic python, or by following similar logic.

I was able to split each field to a list using df['email name'] = df['email name'].str.split(';') which gave me a list for each value in the field. However, I am stuck at how I would join them into a single field.

In pure python I would do something like:

emaillist = []
for i in emailname: #where the assumption is there is a 1:1 relationship between each name and domain
    e = '@'.join(emailname[i],emaildomain[i])
    emaillist.append(e)

but in pandas, i am unsure of how to take an index of a list inside a cell of a dataframe. Ideally, I would also like to skip any blank rows, but if just creates an "empty" list like: [@] then it's fine, I can fix that later.

Answer 1

Use nested list comprehension with * for unpack lists:

L = [['@'.join(z) for z in zip(*[y.split(',') for y in x])] 
                  for x in zip(df['emailname'],df['emaildomain'])]
print (L)
[['[email protected]', '[email protected]'], ['[email protected]']]