How to split a dataframe string column into multiple columns?

Question

I have a pandas dataframe. This dataframe consists of a single column. I want to parse this column according to the '&' sign and add the data to the right of the "=" sign as a new column. Examples are below.

The dataframe I have;

                               tags
0                 letter1=A&letter2=B&letter3=C
1                 letter1=D&letter2=E&letter3=F
2                 letter1=G&letter2=H&letter3=I
3                 letter1=J&letter2=K&letter3=L
4                 letter1=M&letter2=N&letter3=O
5                 letter1=P&letter2=R&letter3=S
.                  .
.                  .

dataframe that I want to convert;

     letter1    letter2   letter3
0      A           B         C
1      D           E         F
2      G           H         I
3      J           K         L
4      M           N         O
.
.

I tried to do something with this code snippet.

columnname= df["tags"][0].split("&")[i].split("=")[0]
value =df["tags"][0].split("&")[i].split("=")[1]

But I'm not sure how I can do it for the whole dataframe. I am looking for a faster and stable way.

Thanks in advance,

See here: stackoverflow.com/questions/14745022/…

Raspberry PyCharm
– Raspberry PyCharm

2021-06-23 13:50:54 +00:00
Commented Jun 23, 2021 at 13:50 — Raspberry PyCharm
– Raspberry PyCharm, Commented Jun 23, 2021 at 13:50

Joseph Konka · Accepted Answer · 2021-06-23 14:36:35Z

3

do this..

import pandas as pd

tags = [
    "letter1=A&letter2=B&letter3=C",
    "letter1=D&letter2=E&letter3=F",
    "letter1=G&letter2=H&letter3=I",
    "letter1=J&letter2=K&letter3=L",
    "letter1=M&letter2=N&letter3=O",
    "letter1=P&letter2=R&letter3=S"
]
df = pd.DataFrame({"tags": tags})

df["letter1"] = df["tags"].apply(lambda x: x.split("&")[0].split("=")[-1])
df["letter2"] = df["tags"].apply(lambda x: x.split("&")[1].split("=")[-1])
df["letter3"] = df["tags"].apply(lambda x: x.split("&")[2].split("=")[-1])
df = df[["letter1", "letter2", "letter3"]]
df

answered Jun 23, 2021 at 14:36

Joseph Konka

1115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sammywemmy · Accepted Answer · 2021-06-23 20:43:32Z

1

Split into separate columns, via str.split, using & :

step1 = df.tags.str.split("&", expand=True)

Get the new columns from the first row of step1:

new_columns = step1.loc[0, :].str[:-2].array

Get rid of the letter1= prefix in each column, set the new_columns as the header:

step1.set_axis(new_columns, axis='columns').transform(lambda col: col.str[-1])

  letter1 letter2 letter3
0       A       B       C
1       D       E       F
2       G       H       I
3       J       K       L
4       M       N       O
5       P       R       S

answered Jun 23, 2021 at 20:43

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Comments

Tanmay Shrivastava · Accepted Answer · 2021-06-23 14:23:36Z

0

d=list(df["tags"])
r={}
for i in d:                                            
    for ele in i.split("&"):                           
        if ele.split("=")[0] in r.keys():              
            r[ele.split("=")[0]].append(ele.split("=")[1])
        else:
            r[ele.split("=")[0]]=[]
            r[ele.split("=")[0]].append(ele.split("=")[1])

df = pd.DataFrame({i:pd.Series(r[i]) for i in r})
print (df)

edited Jun 23, 2021 at 14:23

answered Jun 23, 2021 at 14:18

Tanmay Shrivastava

5794 silver badges9 bronze badges

2 Comments

user14977424 Over a year ago

I don't think that is what the PO wants... He is probably searching for the 'pandas' way to do it (which is most likely not looping through every single value of the dataframe)

buhtz Over a year ago

Please add a description to your answer/code and use consistent coding style.

rpb · Accepted Answer · 2021-06-23 16:22:47Z

Using regex

import pandas as pd
import re
tags = [
    "letter1=A&letter2=B&letter3=C",
    "letter1=D&letter2=E&letter3=F",
    "letter1=G&letter2=H&letter3=I",
    "letter1=J&letter2=K&letter3=L",
    "letter1=M&letter2=N&letter3=O",
    "letter1=P&letter2=R&letter3=S"
]

df = pd.DataFrame({"tags": tags})
pattern=re.compile("\=(\w+)") # Look for pattern
df['letter1'], df['letter3'],df["letter2"] = zip(*df["tags"].apply(lambda x: pattern.findall(x)))

Output

                            tags letter1 letter2 letter3
0  letter1=A&letter2=B&letter3=C       A       B       C
1  letter1=D&letter2=E&letter3=F       D       E       F
2  letter1=G&letter2=H&letter3=I       G       H       I
3  letter1=J&letter2=K&letter3=L       J       K       L
4  letter1=M&letter2=N&letter3=O       M       N       O
5  letter1=P&letter2=R&letter3=S       P       R       S

Collectives™ on Stack Overflow

How to split a dataframe string column into multiple columns?

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related