jeudi 16 juin 2016

Transform sequences of values in a column to rows for a timeseries of events in Pandas

I am working with a timeseries with certain events that occur with a given order: A->B->C->D and I want to create a new dataframe having as columns the time of these events, namely from the dataframe old_df:

    ev_type       ev_time
1     W      2012-05-27 02:06:01
2     A      2012-05-28 02:06:01
3     B      2012-05-28 03:06:01
4     C      2012-05-28 04:06:01
5     D      2012-05-28 02:06:03
6     K      2012-05-28 02:06:01
...   ...    ...................
60000 D      2016-01-01 01:01:01

I'd like to get df:

              A_time               B_time               C_time                D_time
1       2012-05-28 02:06:01  2012-05-28 03:06:01  2012-05-28 04:06:01  2012-05-28 04:06:01
...             ....             ....               ....                    ....
5000    2015-05-28 02:06:01  2015-06-28 02:06:01 2015-07-28 02:06:01 2015-08-28 02:06:01

What I did is

A_events = old_df.evtype == 'A'
df = old_df[A_events ].ev_time.to_frame()
df.rename(columns={"ev_time":"A_time"},inplace=True)
df.join(old_df[A_events.shift(1).fillna(False)].ev_time.shift(-1),axis=1)

But this last line doesn't work because it doesn't change the index. The best I could get is

     A_time               B_time 
2  2012-05-28 02:06:01    NaT
3   NaT                  2012-05-28 03:06:01

How can I align the two Series? Or are there better strategies to extract a sequence of event or a pattern from a pandas dataframe?

Aucun commentaire:

Enregistrer un commentaire