CODEWITHSUNDEEP
pythonpandas
Thanh Nguyen
Thanh Nguyen

Reputation: 912

Pandas: Split columns into multiple columns by two delimiters

I have data like this

ID   INFO
1    A=2;B=2;C=5
2    A=3;B=4;C=1
3    A=1;B=3;C=2

I want to split the Info columns into

ID   A    B    C
1    2    2    5
2    3    4    1
3    1    3    2

I can split columns with one delimiter by using

df['A'], df['B'], df['C'] = df['INFO'].str.split(';').str

then split again by = but this seems to not so efficient in case I have many rows and especially when there are so many field that cannot be hard-coded beforehand.

Any suggestion would be greatly welcome.

Upvotes: 1

Views: 3214

Answers (5)

sammywemmy
sammywemmy

Reputation: 28644

Another solution :

  #split on ';'
  #explode
  #then split on '='
  #and pivot
  df_INFO = (df.INFO
             .str.split(';')
             .explode()
             .str.split('=',expand=True)
             .pivot(columns=0,values=1)
             )

   pd.concat([df.ID,df_INFO],axis=1)

    ID  A   B   C
0   1   2   2   5
1   2   3   4   1
2   3   1   3   2

Upvotes: 2

Alexandre B.
Alexandre B.

Reputation: 5502

Another solution is Series.str.findAll to extract values and then apply(pd.Series):

df[["A", "B", "C"]] = df.INFO.str.findall(r'=(\d+)').apply(pd.Series)
df = df.drop("INFO", 1)

Details:

df = pd.DataFrame([[1, "A=2;B=2;C=5"],
                [2, "A=3;B=4;C=1"],
                [3, "A=1;B=3;C=2"]],
                 columns=["ID", "INFO"])

print(df.INFO.str.findall(r'=(\d+)'))
# 0    [2, 2, 5]
# 1    [3, 4, 1]
# 2    [1, 3, 2]

df[["A", "B", "C"]] = df.INFO.str.findall(r'=(\d+)').apply(pd.Series)
print(df)
#    ID         INFO  A  B  C
# 0   1  A=2;B=2;C=5  2  2  5
# 1   2  A=3;B=4;C=1  3  4  1
# 2   3  A=1;B=3;C=2  1  3  2

# Remove INFO column
df = df.drop("INFO", 1)
print(df)
#    ID  A  B  C
# 0   1  2  2  5
# 1   2  3  4  1
# 2   3  1  3  2

Upvotes: 2

etrnote
etrnote

Reputation: 136

values = [dict(item.split("=") for item in value.split(";")) for value in df.INFO]
df[['a', 'b', 'c']] = pd.DataFrame(values)

This will give you the desired output: 

    ID INFO         a   b   c
    1  a=1;b=2;c=3  1   2   3
    2  a=4;b=5;c=6  4   5   6
    3  a=7;b=8;c=9  7   8   9

Explanation: The first line converts every value to a dictionary. e.g. 

x = 'a=1;b=2;c=3' 
dict(item.split("=") for item in x.split(";"))

results in : {'a': '1', 'b': '2', 'c': '3'}

DataFrame can take a list of dicts as an input and turn it into a dataframe.

Then you only need to assign the dataframe to the columns you want:
df[['a', 'b', 'c']] = pd.DataFrame(values)

Upvotes: 3

Serge Ballesta
Serge Ballesta

Reputation: 148910

Browsing a Series is much faster that iterating across the rows of a dataframe.

So I would do:

pd.DataFrame([dict([x.split('=') for x in t.split(';')]) for t in df['INFO']], index=df['ID']).reset_index()

It gives as expected:

   ID  A  B  C
0   1  2  2  5
1   2  3  4  1
2   3  1  3  2

It should be faster than splitting twice dataframe columns.

Upvotes: 3

ALollz
ALollz

Reputation: 59549

You could use named groups together with Series.str.extract. In the end concat back the 'ID'. This assumes you always have A=;B=;and C= in a line. 

pd.concat([df['ID'], 
           df['INFO'].str.extract('A=(?P<A>\d);B=(?P<B>\d);C=(?P<C>\d)')], axis=1)

#   ID  A  B  C
#0   1  2  2  5
#1   2  3  4  1
#2   3  1  3  2

If you want a more flexible solution that can deal with cases where a single line might be 'A=1;C=2' then we can split on ';' and partition on '='. pivot in the end to get to your desired output. 

### Starting Data
#ID   INFO
#1    A=2;B=2;C=5
#2    A=3;B=4;C=1
#3    A=1;B=3;C=2
#4    A=1;C=2

(df.set_index('ID')['INFO']
   .str.split(';', expand=True)
   .stack()
   .str.partition('=')
   .reset_index(-1, drop=True)
   .pivot(columns=0, values=2)
)

#    A    B  C
#ID           
#1   2    2  5
#2   3    4  1
#3   1    3  2
#4   1  NaN  2

Upvotes: 3

Related Questions