How to create new columns from a function to print the dates used to calculate duration?

Question

Here is a sample of my data (original contains 1 million rows):

     User   State      change_datetime  endstate
0  100234     XIM  2016-01-19 17:03:12  Inactive
1  100234  Active  2016-01-28 17:17:15       XIM
2  100234  Active  2016-02-16 17:57:50       NaN
3  100234    Live  2018-03-04 06:30:01    Active
4  213421     XIM  2016-02-16 17:57:53       NaN
5  213421  Active  2018-02-01 10:01:51       XIM
6  213421  Active  2018-02-01 20:49:41       NaN
7  213421  Active  2018-02-13 20:40:11       NaN
8  213421       R  2018-03-04 05:38:51    Active
9  612312    B-98  2018-11-01 17:12:11       XIM

I have created a function to print out the duration of each User in an Active state:

df1_S["change_datetime"] = pd.to_datetime(df1_S["change_datetime"])
m1 = df1_S['State'].eq('Active')
m2 = ~m1 & df1_S['endstate'].eq('Active')

s1 = df1_S[m1].groupby('User')['change_datetime'].first()
s2 = df1_S[m2].groupby('User')['change_datetime'].first()

s = s2.sub(s1)
State_duration = s.to_frame()
State_duration = State_duration.reset_index()
State_duration

Here are the results which are printed after running the code:

State_duration:

     User                    change_datetime
    0    100234              19 days 00:40:00
    1    213421              42 days 10:39:00
    2    612312             114 days 16:21:14
    3    100204249020        12 days 15:51:01
    4    100204249030       107 days 15:51:01
    5    100204249040        97 days 15:51:01
    6    100204249050        99 days 15:51:04
    7    100204249060       107 days 15:51:01
    8    100204249070       997 days 15:51:01
    9    100204249340       497 days 15:51:01
    10   100222075820       365 days 05:39:33
    11   100222075830       365 days 05:33:41
    12   100222075900         3 days 05:37:26

Is there a way I can create two new col

umns in my code which are State_duration['Start Date/time'] and State_duration['End Date/time'] so that it prints in each row which start and end date/time stamps were used to calculate the duration? For example, in this way:

     User         Start_dt_time           End_dt_time                 change_datetime
0    100234      2016-01-28 17:17:15    2018-03-04 06:30:01           95 days 10:59:39 
1    213421      2018-02-01 10:01:51    2018-03-04 05:38:51           42 days 10:39:00                                                  97 days 15:51:04
2    612312                                                          114 days 16:21:14
3    100204249020                                                     12 days 15:51:01
4    100204249030                                                    107 days 15:51:01
5    100204249040                                                     97 days 15:51:01
6    100204249050                                                     99 days 15:51:04
7    100204249060                                                    107 days 15:51:01
8    100204249070                                                    997 days 15:51:01
9    100204249340                                                    497 days 15:51:01
10   100222075820                                                    365 days 05:39:33
11   100222075830                                                    365 days 05:33:41
12   100222075900                                                      3 days 05:37:26

Answer 1

We can use pd.concat along axis=1 to concat s1 and s2 after renaming them using Series.rename:

s_duration = pd.concat([s2.sub(s1), s1.rename('Start_dt_time'), 
                        s2.rename('End_dt_time')], axis=1).reset_index()

Result:

    User   change_datetime       Start_dt_time         End_dt_time
0  100234 765 days 13:12:46 2016-01-28 17:17:15 2018-03-04 06:30:01
1  213421  30 days 19:37:00 2018-02-01 10:01:51 2018-03-04 05:38:51