6

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.

Improve this question

2 Answers 2

Reset to default
1

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']])
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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!
0

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.