Pandas pivot one column while using same column value as column headers

Question

I want to pivot a column in a data frame where column values become the column header and actual value for those columns become 1 or 0.

Example:

        movie_id  cluster_id      answer_id
0         73        1               4
1         80        1               5
4         81        1               2
7         84        1               1
10        88        1               1
11        83        1               4
13        85        1               1
16        54        1               1
22        79        1               3
23        87        1               1

I want the outcome after pivot to be:

        movie_id  cluster_id     1   2   3   4   5
0         73        1            0   0   0   1   0 
1         80        1            0   0   0   0   1
4         81        1            0   1   0   0   0

One way to do is copy answer_id columns to a different name and then use it in the pivot_table function. But not sure how the fill up can be done or overall is there a better way to carry this out without actually copy a column.

    pivot_df = df.pivot_table(
        values='copy_answer_id',
        index=['movie_id', 'cluster_id'],
        columns='answer_id').reset_index()

Once above is done you get all the NaN and content in the answer_id for respective columns.

        movie_id  cluster_id     1    2   3   4   5
0         73        1           NaN  NaN NaN  4  NaN
1         80        1           NaN  NaN NaN NaN   5
4         81        1           NaN   2  NaN NaN NaN

Then I could do:

cols = [1,2,3,4,5]
pivot_df[cols] = pivot_df[cols].replace({1:1,2:1,3:1,4:1,5:1})

After that to convert NaN to zeros: I could do pivot_df.fillna(0, inplace=True) to convert all the NaN to zeros.

But is there a better way to do this just within the pivot_table function.

Answer 1

Incase you want to rely only on pivot_table. You can do this way :

# Use a temporary column with values one, pivot and fill nan with 0
new = df.assign(val=1).pivot_table(columns='answer_id',index=['cluster_id','movie_id'],values='val',fill_value=0).reset_index()

Or, you can go with get_dummies since it is faster than pivot_table i.e:

new = pd.concat([df[['movie_id','cluster_id']],pd.get_dummies(df['answer_id'])],1)

    movie_id  cluster_id  1  2  3  4  5
0         73           1  0  0  0  1  0
1         80           1  0  0  0  0  1
4         81           1  0  1  0  0  0
7         84           1  1  0  0  0  0
10        88           1  1  0  0  0  0
11        83           1  0  0  0  1  0
13        85           1  1  0  0  0  0
16        54           1  1  0  0  0  0
22        79           1  0  0  1  0  0
23        87           1  1  0  0  0  0