Category Overlap Analysis

Question

I am trying to perform some category overlap analysis and need help.

I have data made up of customer service tickets. The tickets are labeled with category data. Tickets can contain multiple category labels.

I have a query that pulls ticket ids and categories. I get multiple rows for IDs with more than one category. I am looking for a way to show the category overlap, for example: how many tickets have category A, have A and B, B and C, etc..

I would like to be able to perform this in Excel or R so that it can easily be incorporated into reports for my management.

An example of my query output is as follows:

category  ticket_id

A   3975472 
D   3975472 
B   3975472 
P   3969484 
B   3969484 
S   3969484 
P   3968360 
C   3968360 
D   3964048 
A   3964048 
C   3963748 
E   3963748

Thank you!

I was hoping to achieve an output such as:

Answer 1

In Excel you could do this with a Pivot table:

In R, assuming the data is in a data frame named df, you could do something like this:

table(df$ticket_id, df$category)
#         A B C D E P S
# 3963748 0 0 1 0 1 0 0
# 3964048 1 0 0 1 0 0 0
# 3968360 0 0 1 0 0 1 0
# 3969484 0 1 0 0 0 1 1
# 3975472 1 1 0 1 0 0 0