How to move duplicate rows into columns with python

Question

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

NAMES    VALUE
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:

NAMES     VALUE1     VALUE2     VALUE3
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.

score 1 · Accepted Answer · answered Mar 10 '16 at 21:39

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]
df.reset_index(inplace=True)

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']])

This works perfectly! I kinda executed the df.pivot wrong in my own script, but then again, I learned some functions now that I didn't know of. Thanks a a alot! — user3719620, Mar 11 '16 at 07:53

score 0 · Answer 2 · edited Aug 27 '23 at 23:00

0

Split/Clean the data as explained by root; then you can also use

df_out=pd.crosstab(index=[df['NAMES']],columns=df['VALUE'])

edited Aug 27 '23 at 23:00

cottontail

10,268
18
50
51

answered Aug 25 '23 at 17:22

ninad taksande

19
2

How to move duplicate rows into columns with python

2 Answers2

Linked