python - 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.

question from:https://stackoverflow.com/questions/65951438/pandas-split-values-and-join-across-columns

Answer 1

Well you can try this. It will create a new column email of your desired output

final_email = []
for i,k in enumerate(zip(list(df['Emailname'].values), list(df['Emaildomain'].values))):
  name,domain = k
  a = []
  for ij, val in enumerate(name.split(';')):
    val = val+'@'+str(domain.split(',')[ij]).strip()
    a.append(val)
  final_email.append(a)
df['Email'] = final_email
df