CODEWITHSUNDEEP
pythonpandas
skimchi1993
skimchi1993

Reputation: 229

split pandas df row on delimiter and then rename another row based on index

lets say i have the following df:

 name    job    age&dob
 bob     teacher  35/1-1-85
 kyle    doctor   25/1-1-95

I want to split the rows age&dob based on the '/' delimiter which can be achieved by putting age&dob into a list and then stacking it. However, i do not know how to rename the row based on age&dob index. For example, i want this.

 name    metadata    age&dob   job
 bob     age         35        teacher
 bob     dob         1-1-85    teacher
 kyle    age         25        doctor
 kyle    dob         1-1-95    doctor

i want metadata to be created by the index based on the split. So in this case, since i know that age&dob.spilt('/')[0] is always going to be age, i want 35 to be there and then metadata to be updated to show age. I know how to split the df, its just the renaming of the additional row value.

Upvotes: 0

Views: 102

Answers (2)

BENY
BENY

Reputation: 323316

Let us do 

df['metadata'] = 'age&dob'
df['age&dob'] = df['age&dob'].str.split('/')
s=df.explode('age&dob').assign(metadata=df['metadata'].str.split('&').explode().tolist())
   name      job age&dob metadata
0   bob  teacher      35      age
0   bob  teacher  1-1-85      dob
1  kyle   doctor      25      age
1  kyle   doctor  1-1-95      dob

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

IIUC, lets use str.split, rename, stack and finally concat

s = df['age&dob'].str.split('/',expand=True).rename({0 : 'age', 1 : 'dob'},axis=1)\
            .stack().reset_index(1)\
            .rename({'level_1' : 'metadata', 0 : 'age&dob'},axis=1)

df2 = pd.concat([df.drop(['age&dob'],axis=1),s],axis=1)

   name      job metadata age&dob
0   bob  teacher      age      35
0   bob  teacher      dob  1-1-85
1  kyle   doctor      age      25
1  kyle   doctor      dob  1-1-95

Upvotes: 0

Related Questions