Expand a string from a column into different separate columns in Pandas

user_15128170 0
Viewed 110

I have a df in the following form:

id   sid      steps
A     1       step1
A     1    step1-step2
A     1  step1-step2-step3

This contains data of how a user A went through a certain series of pages (steps) in a given session (sid). I want to take these dash delimitated steps and create individual columns for every step.

Result:

id     sid      steps       page_step1 page_step2  page_step3
  A     1       step1         step1        NA           NA
  A     1    step1-step2      step1      step2          NA
  A     1  step1-step2-step3  step1      step2        step3

I don't know exactly how many steps so I was hoping that they're created dynamically. Been stuck on this all week, thanks!

pandas data-analysis dataframe python
2 Answers

Use str.split:

>>> df.join(df["steps"].str.split("-",expand=True).add_prefix("page_step"))

 id  sid              steps page_step0 page_step1 page_step2
0  A    1              step1      step1       None       None
1  A    1        step1-step2      step1      step2       None
2  A    1  step1-step2-step3      step1      step2      step3

If the numbering must start from 1 instead of 0:

steps = df["steps"].str.split("-",expand=True)
output = df.join(steps.rename(columns={i: f"page_setup{i+1}" for i in steps.columns}))

>>> output
  id  sid              steps page_setup1 page_setup2 page_setup3
0  A    1              step1       step1        None        None
1  A    1        step1-step2       step1       step2        None
2  A    1  step1-step2-step3       step1       step2       step3
user_98576310

I used series.map. I was not able to apply a filter to the map so I used an if in the function and created slice2 and slice3 to extract values from the dataframe list

txt="""id\tsid\tsteps
A\t1\tstep1
A\t1\tstep1-step2
A\t1\tstep1-step2-step3"""

df=pd.read_csv(io.StringIO(txt),sep='\t')
#print(df)
results=df['steps'].apply(lambda x: x.split('-'))
print(results.tolist())

def Slice2(x):
    print(x)
    if len(x)>=2:
        return x[1]
    return None

def Slice3(x):
    print(x)
    if len(x)>=3:
        return x[2]
    return None


df['page_step1']=results.map(lambda x: x[0])
df['page_step2']=results.map(Slice2)
df['page_step3']=results.map(Slice3)

print(df)

output

 id  sid              steps page_step1 page_step2 page_step3
0  A    1              step1      step1       None       None
1  A    1        step1-step2      step1      step2       None
2  A    1  step1-step2-step3      step1      step2      step3
user_40011770
Related