Python: Splitting a column into two Columns based off its value

user_12229134 0
Viewed 174

I am trying to get from My Starting DataFrame

My Starting DataFrame

to My Desired Results

My Desired Results.

I am trying to do a groupby on two columns (Name, Month) and I have a column (Category) that has either the value 'Score1' or 'Score2'. I want to create two columns with the name of values from the Category column and set their values to a value determined from another column.

pd.crosstab([df.Name, df.Month], df.Category)

is the closest I've got to create the desire data frame however I can't figure out how to get the values from my "Value" column to populate the dataframe.

Results from crosstab

Results from crosstab

The Dataframe in code form

df = pd.DataFrame(columns=['Name', 'Month', 'Category', 'Value'])
df['Name'] = ['Jack','Jack','Sarah','Sarah','Zack']
df['Month'] = ['Jan.','Jan.','Feb.','Feb.','Feb.']
df['Category'] = ['Score1','Score2','Score1','Score2','Score1']
df['Value'] = [1,2,3,4,5]

Thanks!

pandas python
2 Answers

You can use Pivot Table

df.pivot_table(index=['Name', 'Month'],values='Value', columns='Category').rename_axis(None, axis=1).reset_index()

Out[1]: 
    Name Month  Score1  Score2
0   Jack  Jan.     1.0     2.0
1  Sarah  Feb.     3.0     4.0
2   Zack  Feb.     5.0     NaN
user_38666780

one way is with groupby and unstack

new_df = (df.groupby(['Name','Month','Category'])
 ['Value'].first().unstack().reset_index())
print(new_df)
Category   Name Month  Score1  Score2
0          Jack  Jan.     1.0     2.0
1         Sarah  Feb.     3.0     4.0
2          Zack  Feb.     5.0     NaN
user_93751020
Related