Pivot table for non-numerical values using Pandas

Hello friends! Recently I got the requirement to create pivot table for text values to be aggregated and concatenated with ‘,’ for same row label in pandas. It can be easily done using crosstab tool in Alteryx. We’ll be doing that using pandas for the quick tutorial.

Data

The data is the feedback survey form and exported as something like below table. It can be seen as for same ID and Que there are multiple answer_text and we want to pivot and concatenate with ‘,’ for same ID-Que combination.

Id	Que	answer_text
Id1	Q1	High
Id2	Q2	Low
Id3	Q3	Medium
Id4	Q1	Average
Id2	Q1	Low
Id2	Q1	High
Id1	Q1	Medium
Id2	Q2	Low

Code

Importing libraries

import pandas as pd

Reading data

feedbck = pd.read_csv('feedback.csv')

pivot_table = feedbck.pivot_table(index='Id',
columns = 'Que',
values = 'answer_text',
aggfunc = lambda x: ','.join(str(v) for v in x))

Output

pivot_table

Id	Q1	Q2	Q3
Id1	High,Medium
Id2	Low,High	Low,Low
Id3			Medium
Id4	Average

Output to csv

pivot_table.to_csv('output.csv')

Check my Github repository

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries