How to set value to pandas crosstab?-CodePudding

I am just learning pandas, I do not know even possible to set values to a crosstab. For example: My data frame is:

data = [['Alice', 1, 2], ['Bob', 2, 5], ['Cameron', 3, 1],['Alice', 2, 5],['Bob', 3, 1]]
df = pd.DataFrame(data, columns = ['Name', 'Music_id', 'Music_type'])

The output of the data frame

Now apply crosstab on df:

ct = pd.crosstab([df.Name,df.Music_id],df.Music_type)

I got: The output of crosstab

However, that is not what I expected.

I want it shows somehow like this:

Music_id	1	2	3
Name
Alice	2	5	0
Bob	0	5	0
Cameron	0	0	1

Basically, I want it instead of counting how many times the Name matches Music_id, it gives the relative value from the third column (Music_type). Did I use the right function to do that? Any advice is appreciated!

CodePudding user response：

You can try this solutions to resolve :

pd.crosstab

pd.crosstab(df['Name'], columns = df['Music_id'], values = df['Music_type'], aggfunc = 'first').fillna(0)

# Output :
# Music_id    1    2    3
# Name
# Alice     2.0  5.0  0.0
# Bob       0.0  5.0  1.0
# Cameron   0.0  0.0  1.0

df.pivot

df.pivot(index = 'Name', columns = 'Music_id')['Music_type'].fillna(0)
# Output :
# Music_id    1    2    3
# Name
# Alice     2.0  5.0  0.0
# Bob       0.0  5.0  1.0
# Cameron   0.0  0.0  1.0