Counting the transition in a dataframe overtime

Question

I am stuck at a problem and am thinking how to come out of it. I want to write a code in python with dataframe as below:

data = {'Id':['a', 'a', 'b', 'b', 'c', 'c'], 'value':['Active', 'Notactive', 'Active', 'Superactive', 'Notactive', 'Superactive'], 'date':['8-09-2019','15-09-2019','8-09-2019','15-09-2019','8-09-2019','15-09-2019']} df = pd.DataFrame(data) I want to reach to the result where i count the number of users who did transition in the 7 day difference: Active to notactive: 1 Active to superactive: 1 Not active to superactive: 1 Active to Active: 0

Any help on how to proceed would be appreciated on python 3.7

Thanks

Answer 1

For a small dataset try this:

# change type of date column so that it is possible to find min and max correctly
df['date'] = pd.to_datetime(df['date'])

# put value of the oldest resord and newest record side by side
df_min_max_grouped = pd.merge(
    df.loc[df.groupby('Id')['date'].idxmin()],
    df.loc[df.groupby('Id')['date'].idxmax()],
    on='Id',
    suffixes=('_old', '_new')
)

# group by old value and new value and find counts
results = df_min_max_grouped.groupby(['value_old', 'value_new']).size()

results

Output:

value_old  value_new  
Active     Notactive      1
           Superactive    1
Notactive  Superactive    1
dtype: int64

The approach I chose for this answer:

1. Convert date values to be of type datetime, Correct ordering will not work with string dates.
2. First find the oldest and the newest records for any Id, This happens in : df.loc[df.groupby("Id")['date'].idxmin()] for oldest and df.loc[df.groupby("Id")['date'].idxmax()] and newest records.
3. Join the oldest and newest records on the basis of Id, and then
4. Group by old value and new value combination to get a count in each group.

I found that resetting index of the resulting Series produces nice output:

results.reset_index()

	value_old	value_new	0
0	Active	Notactive	1
1	Active	Superactive	1
2	Notactive	Superactive	1

The combinations not listed here have 0 records against them. I hope you find this helpful!