Customer flow analysis

[stoic_presi]

I  have a monthly data of the following structure:

Month	Name	Drink	Age
jan	A	Coffee	22
jan	B	Tea	24
jan	C	Coffee	21
jan	D	Coffee	25
jan	E	Coke	20
feb	A	Coffee	22
feb	B	Tea	24
feb	C	Tea	21
feb	D	Tea	25
feb	E	Tea	20

I need to create the below matrix where i can see the flow of customers from one drink to another over Month. Eg. 2 customers drinking coffee in Jan prefer Tea in Feb.

The actual problem will need to accommodate comparison of different months and items( like drinks here). 

Jan\Feb	Coffee	Tea	Coke
Coffee	1	2	0
Tea	0	1	0
Coke	0	1	0

[Kushal_Chawda]

@stoic_presi  how much years worth of data do you have?  

[stoic_presi]

3 years of data.  But from analysis perspective  change in 1 month, 3 month, 6 month and 1 year is desirable. and volume wise > monthly snapshot is ~ 1000000 rows.

[Kushal_Chawda]

@stoic_presi  How the comparison is needed? Is it always selected month month with previous month or any two month selections?

[stoic_presi]

Hi Kush, Like mentioned in the reply change in 1 month, 3 month, 6 month and 1 year is desirable if not fully dynamic. Like curr. month vs last month or curr. month vs 3 months ago. etc.           The main script cannot be changed as its a 100+ column data and the requested problem is one of many use cases.