Vfrdtyky

I have a data set which shows the performance of a device per month. Here is how the data looks like

device, date, performance

   1  , 06/16, 50.4

   1  , 07/16, 54

   1  , 08/16, 6

   1  , 09/17, 67

   1  , 10/17, 56

   2  , 01/12, 34

       ....

A device may not be working in some months so there are some gaps in the data. For example, device one has some gaps in the above data. I'd like to create a data frame which shows the time ranges each device was working, here is how the resulting data set should look like

device, start, end

 1    ,  06/16, 08/16

 1    ,  09/17, 10/17

     ....

How can I create that using pandas?

You can create series with the same number when consecutive months or same device. To do this, you can use shift and add a a month with DateOffset to check if the following row is a month apart. Use cumsum to increment the value for each new group. First you need the column 'date' as datetime in a series:

ser_date = pd.to_datetime(df['date'],format='%m/%y')

ser_group = ((((ser_date.shift() + pd.DateOffset(months=1)) != ser_date) | #month apart

              (df.device.diff() != 0)) # different device

              .cumsum()) #increment value for each group

Now you can use groupby on ser_group, join the first and the last of each group such as:

g_df = df.groupby(ser_group) #grouped data

df_new = (g_df['device','date'].first() #first of each group

          .join(g_df['date'].last(),rsuffix='_') #joined with last of each group

          .rename(columns={'date':'start','date_':'end'})) #rename column start/end



print (df_new)

   device   start     end

1       1   06/16   08/16

2       1   09/17   10/17

3       2   01/12   01/12

To be more instructive, I extended a little your source data, so that it
contains full sets of 5 readings for 2 devices.
I also decided to change date column to true dates.

The concept is based on groupping by device and then generating
start / end dates for each group.

The script generating start / end periods can be as follows:

import pandas as pd



# Source data

df = pd.DataFrame(data={ 'device': [ 1, 1, 1, 1, 1, 2, 2, 2, 2, 2 ],

    'date': [ '06/16', '07/16', '08/16', '09/17', '10/17',

        '03/16', '04/16', '11/16', '12/16', '01/17' ],

    'performance': [ 50.4, 54, 6, 67, 56, 50, 45, 30, 72.3, 42 ] })

# Convert date to true dates

df.date = pd.to_datetime(df.date, format='%m/%y')

grp = df.groupby(by='device')

# Starts of periods

st = grp.apply(lambda x: x.date[x.date - pd.DateOffset(months = 1)

    != x.date.shift()]).reset_index(level=1,drop=True)

# Ends of periods

en = grp.apply(lambda x: x.date[x.date + pd.DateOffset(months = 1)

    != x.date.shift(-1)]).reset_index(level=1,drop=True)

# Result - concat start / end dates

res = pd.concat([st, en], axis=1).reset_index()

# Set column names

res.columns = ['device', 'start', 'end']

The result is:

   device      start        end

0       1 2016-06-01 2016-08-01

1       1 2017-09-01 2017-10-01

2       2 2016-03-01 2016-04-01

3       2 2016-11-01 2017-01-01