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



1 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(
    suffixes=('_old', '_new')

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



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:

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!

  • 1
    $\begingroup$ @Iytseeker thanks for the response. It is a perfect script to give the necessary output. Thanks, $\endgroup$
    – Khalid M
    Sep 17, 2019 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.