CODEWITHSUNDEEP
pythonpandasdataframe
Qubix
Qubix

Reputation: 4353

Split pandas column by separator for different string sizes

How can one split a column like df.value 

value
--------
Top/Animals/Cat
Top/Dog
Pig/Guineea/Piglet

into multiple columns 

val1 |   val2   | val3 |
Top    Animals     Cat
NaN      Top       Dog
Pig    Guineea    Piglet

such that I have the number of columns of the longest string and NaN where other strings are not the same length?

Upvotes: 0

Views: 1340

Answers (5)

datapug
datapug

Reputation: 2441

This solution returns NaN and not None (as requested by the OP)

import pandas as pd
import numpy as np

df = pd.DataFrame({"c1": ["Top/Animals/Cat",
                          "Top/Dog", 
                          "Pig/Guineea/Piglet"]})
df["c1"] = df["c1"].str.split("/")
c1_max_len = df["c1"].map(len).max()
df["c1"] = df["c1"].map(lambda x: (c1_max_len - len(x)) * [np.nan] + x)
df_exploded = pd.DataFrame(df.c1.values.tolist(), index= df.index)

Upvotes: 0

Serge Ballesta
Serge Ballesta

Reputation: 148965

You can use a DataFrame constructor to build the columns in reverse order, then reindex them:

resul = pd.DataFrame([reversed(i) for i in df['value'].str.split('/')])
resul = resul.reindex(reversed(resul.columns), axis=1)
resul.columns = ['val' + str(i+1) for i in range(len(resul.columns))]

It gives as expected:

   val1     val2    val3
0   Top  Animals     Cat
1  None      Top     Dog
2   Pig  Guineea  Piglet

Upvotes: 0

BENY
BENY

Reputation: 323306

Let us try something new 

sep='/'
s=df.value.str.count(sep)
s=((s.max()-s).map(lambda x : x*sep)+df.value).str.split(sep,expand=True)
     0        1       2
0  Top  Animals     Cat
1           Top     Dog
2  Pig  Guineea  Piglet

Upvotes: 2

Poojan
Poojan

Reputation: 3519

  • You can first reverse the order of string and then use str.split with expand parameter
df = pd.DataFrame({'value' : ['Top/Animals/Cat', 'Top/Dog', 'Pig/Guineea/Piglet', 'Top']})

# reverse string first. Top/Animals/Cat will become Cat/Animals/
df = df['value'].apply(lambda x : '/'.join(x.split('/')[::-1])).str.split('/', expand=True) 

# column renaming and order
df.columns = ['val' + str(i) for i in range(len(df.columns), 0,-1)]
df = df[df.columns[::-1]]
df

output

    val1    val2    val3
0   Top     Animals Cat
1   None    Top     Dog
2   Pig     Guineea Piglet
3   None    None    Top

Upvotes: 0

gosuto
gosuto

Reputation: 5741

.str.split()'s expand=True is your friend here!

df['value'].str.split('/', expand=True)

Upvotes: 1

Related Questions