How to split dataframe columns into row values?

Question

I have a dataframe with more descriptions for products.

data = {'product': ['1234', '5678'],
        'descriptionOLD': ['old1234', 'old5678'],
        'descriptionNEW': ['new1234', 'new5678'],
        'descriptionFUTURE': ['future1234', 'future5678'],
        }

How can i split this dataframe in this kind of format?

product type            description
1234    descriptionOLD  old1234
1234    descriptionOLD  old5678
1234    descriptionNEW  new1234
1234    descriptionNEW  new5678
1234    descriptionFUTURE future1234
1234    descriptionFUTURE future5678
...

So far i have tried:

df=pd.DataFrame(data)
df['type']=''
df['description']=''
for id, row in df.iterrows():
    df['type'][id]='descriptionOLD'
    df['description'][id]=row['descriptionOLD']

df.pop('descriptionOLD')
df.pop('descriptionNEW')
df.pop('descriptionFUTURE')

and it gets me this result:

  product            type description
0    1234  descriptionOLD     old1234
1    5678  descriptionOLD     old5678

but i don't know how to handle the new and future descriptions as well to achieve the desired dataframe.

@AnuragDabas and what if there are more columns and I only want to melt the df for the description part? — viktor_dmitry
– viktor_dmitry, Commented Jun 8, 2021 at 13:34

Anurag Dabas · Accepted Answer · 2021-06-08 13:49:37Z

3

try via filter() method for filtering out columns,join() method for getting back the 'product' column and finally melt() method:

out=(df.filter(like='description')
   .join(df['product'])
    .melt(id_vars='product',var_name='type',value_name='description'))

OR

try via filter() method for filtering out columns,set_index() method for preserving the 'products',reset_index() for making product back to column and finally melt() method:

out=(df.set_index('product')
       .filter(like='description')
       .reset_index()
       .melt(id_vars='product',var_name='type',value_name='description'))

OR

As suggested by @NK03 you can also use a combo of filter() and stack():

out=(df.set_index('product')
       .filter(like='description')
        .stack()
        .reset_index(name ='description')
        .rename(columns = {'level_1' : 'type'}))

edited Jun 8, 2021 at 13:49

answered Jun 8, 2021 at 13:38

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

4 Comments

Nk03 Over a year ago

I guess there's no need for melt -> df.set_index('product').stack().reset_index(name ='description').rename(columns = {'level_1' : 'type'})

Anurag Dabas Over a year ago

yes brother....It will also work but we have to chain 5 methods for its working

Anurag Dabas Over a year ago

@Nk03 bhai pls undelete your answer on this question stackoverflow.com/questions/67915609/… since plot without using seaborn was your answer

Nk03 Over a year ago

Bro, it's okay!!. 😅 I also like seaborn more.. :p

Collectives™ on Stack Overflow

How to split dataframe columns into row values?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related