Creating separate columns based on string value in python

Question

I am quite new but I am working with a dataframe that looks like the below:

ID       Tag  
123      Asset Class > Equity  
123      Inquiry Type > Demonstration
123      Inquiry Topic > Holdings  
456      Asset Class > Fixed Income  
456      Inquiry Type > BF   
456      Inquiry Topic > VaR

What I am trying to do is change this into a dataframe which looks like the below:

ID       Asset Type      Inquiry Type      Inquiry Topic  
123      Equity          Demonstration     Holdings
456      Fixed Income    Bf                VaR

Sorry if this is quite simple; however, I am having an issue with this manipulation. I have tried .melt but this does not seem to complete what I am trying to complete.

jezrael · Accepted Answer · 2018-12-06 10:19:44Z

1

Use split with pivot and selecting splitted lists by indexing:

s = df['Tag'].str.split(' > ')
df = (pd.pivot(index=df['ID'], columns=s.str[0], values=s.str[1])
       .reset_index()
       .rename_axis(None, 1))
print (df)
    ID   Asset Class Inquiry Topic   Inquiry Type
0  123        Equity      Holdings  Demonstration
1  456  Fixed Income           VaR             BF

answered Dec 6, 2018 at 10:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Garon Smith Over a year ago

Thank you very much for the help!

Collectives™ on Stack Overflow

Creating separate columns based on string value in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related