I have a df with id as the flow id, dttm as the step modification time, and step as the steps in the flow. It is currently ordered by dttm. There could be any number of steps for a particular id.
Current df:
id dttm step
0 81 2015-05-26 07:56:03 A
1 81 2015-05-26 08:19:07 B
2 81 2015-05-26 08:32:05 C
3 91 2015-05-26 08:07:12 B
4 91 2015-05-26 08:07:12 C
I want to create link data to feed into a Sankey. Therefore I ultimately want a df with three columns: source, target, and value. value is the count of ids that have such a step pair.
Desired df:
source target value
0 A B 1
1 B C 2
I know I can stuff step into one row with either groupby or possibly cat. However, I think that would just create a different starting point without actually advancing a solution. Part of what makes it tough is that the steps depend on dttm to stay ordered so that the steps are appropriately paired. Also, the fact that it has to be dynamic because there could be any number of steps adds to the difficulty.
How should I dynamically "stuff" the step column to arrive at the link data?
Is there a way to join the df to itself to get all of the pairs, and then remove rows that are created during the join but are nonsense?
Thank you for any and all insight!