user3719620 user3719620 - 9 months ago 65
Python Question

How to move duplicate rows into columns with python

I'm having a very tough time trying to figure out how to do this with python. I have the following table:

john_1 1
john_2 2
john_3 3
bro_1 4
bro_2 5
bro_3 6
guy_1 7
guy_2 8
guy_3 9

And I would like to go to:

john 1 2 3
bro 4 5 6
guy 7 8 9

I have tried with pandas, so I first split the index (NAMES) and I can create the new columns but I have trouble indexing the values to the right column.

Can someone at least give me a direction where the solution to this problem is? I don't expect a full code (I know that this is not appreciated) but any help is welcome.

Answer Source

After splitting the NAMES column, use .pivot to reshape your DataFrame.

# Split Names and Pivot.
df['NAME_NBR'] = df['NAMES'].str.split('_').str.get(1)
df['NAMES'] = df['NAMES'].str.split('_').str.get(0)
df = df.pivot(index='NAMES', columns='NAME_NBR', values='VALUE')

# Rename columns and reset the index.
df.columns = ['VALUE{}'.format(c) for c in df.columns]

If you want to be slick, you can do the split in a single line:

df['NAMES'], df['NAME_NBR'] = zip(*[s.split('_') for s in df['NAMES']])