Split Pandas Series to Multiple Column by Substring

user_11845588 0
Viewed 216

I am trying to split a DataFrame Series with likely regex into multiple columns.

Replicable code:

pd.DataFrame({"Animals":["(Cat1, Dog1)", "(Cat1, Dog2)", "(Cat1, Dog3)", "(Cat2, Dog1)", "(Cat2, Dog2)", "(Cat2, Dog3)"]})

Input Table:

enter image description here

Desired Table:

enter image description here

Thanks in advance!

pandas python
3 Answers

EDIT:

Per comments, Shubham's solution is the cleanest:

df[['Animals1', 'Animals2']] = df['Animals'].str.extract(r'(\w+), (\w+)')

You can also use replace to get rid of parenthses and spaces and then split(',') with expand=True to create new columns:

df[['Animal1', 'Animal2']] = (df['Animals'].replace(['\(', '\)', '\s+'], '', regex=True)
                              .str.split(',', expand=True))
df
Out[1]: 
        Animals  Animal1  Animal2
0  (Cat1, Dog1)     Cat1     Dog1
1  (Cat1, Dog2)     Cat1     Dog2
2  (Cat1, Dog3)     Cat1     Dog3
3  (Cat2, Dog1)     Cat2     Dog1
4  (Cat2, Dog2)     Cat2     Dog2
5  (Cat2, Dog3)     Cat2     Dog3
user_63667700

Try:

df[['Animal1', 'Animal2']] = df['Animals'].str[1:-1].str.split(', ', expand=True)

    Animals         Animal1 Animal2
0   (Cat1, Dog1)    Cat1    Dog1
1   (Cat1, Dog2)    Cat1    Dog2
2   (Cat1, Dog3)    Cat1    Dog3
3   (Cat2, Dog1)    Cat2    Dog1
4   (Cat2, Dog2)    Cat2    Dog2
5   (Cat2, Dog3)    Cat2    Dog3
user_66603730

One way would be:

df = pd.DataFrame({"Animals":["(Cat1, Dog1)", "(Cat1, Dog2)", "(Cat1, Dog3)", "(Cat2, Dog1)", "(Cat2, Dog2)", "(Cat2, Dog3)"]})
df['Animal1'] = df['Animals'].map(lambda x: x.split(', ')[0][1:])
df['Animal2'] = df['Animals'].map(lambda x: x.split(', ')[1][:-1])
user_56792850
Related