2

I have a pandas series containing zeros and ones:

df1 = pd.Series([ 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0])
df1
Out[3]: 
0         0
1         0
2         0
3         0
4         0
5         1
6         1
7         1
8         0
9         0
10        0

I would like to create a dataframe df2 that contains the start and the end of intervals with the same value, together with the value associated... df2 in this case should be...

df2
Out[5]: 
   Start     End  Value
0      0  4         0
1      5  7         1
2      8  10        0

My attempt was:

from operator import itemgetter
from itertools import groupby

a=[next(group) for key, group in groupby(enumerate(df1), key=itemgetter(1))]   
df2 = pd.DataFrame(a,columns=['Start','Value'])

but I don't know how to get the 'End' indeces

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
gabboshow
  • 5,359
  • 12
  • 48
  • 98

4 Answers4

0

You could use the pd.Series.diff() method so as to identify the starting indexes:

df2 = pd.DataFrame()
df2['Start'] = df1[df1.diff().fillna(1) != 0].index

Then compute end indexes from this:

df2['End'] = [e - 1 for e in df2['Start'][1:]] + [df1.index.max()]

And finally gather the associated values :

df2['Value'] = df1[df2['Start']].values

ouput

   Start  End  Value
0      0    4      0
1      5    7      1
2      8   10      0
Archeo
  • 221
  • 1
  • 6
0

The thing you are looking for is get first and last values in a groupby 

import pandas as pd

def first_last(df):
    return df.ix[[0,-1]]

df = pd.DataFrame([3]*4+[4]*4+[1]*4+[3]*3,columns=['value'])
print df
df['block'] = (df.value.shift(1) != df.value).astype(int).cumsum()
df = df.reset_index().groupby(['block','value'])['index'].agg(['first', 'last']).reset_index()
del df['block']
print df
Hardik Sondagar
  • 4,347
  • 3
  • 28
  • 48
0

You can groupby using shift and cumsum and find first and last valid index

df2 = df1.groupby((df1 != df1.shift()).cumsum()).apply(lambda x: np.ravel([x.index[0], x.index[-1], x.unique()]))
df2 = pd.DataFrame(df2.values.tolist()).rename(columns = {0: 'Start', 1: 'End',2:'Value'})

You get

    Start   End     Value
0   0       4       0
1   5       7       1
2   8       10      0
Vaishali
  • 37,545
  • 5
  • 58
  • 86
0

You can groupby by Series which is create by cumsum of shifted Series df1 by shift.

Then apply custum function and last reshape by unstack.

s = df1.ne(df1.shift()).cumsum()
df2 = df1.groupby(s).apply(lambda x: pd.Series([x.index[0], x.index[-1], x.iat[0]], 
                                                index=['Start','End','Value']))
                   .unstack().reset_index(drop=True)
print (df2)
   Start  End  Value
0      0    4      0
1      5    7      1
2      8   10      0

Another solution with aggregation by agg with first and last, but there is necessary more code for handling output by desired output.

s = df1.ne(df1.shift()).cumsum()
d = {'first':'Start','last':'End'}
df2 = df1.reset_index(name='Value') \
         .groupby([s, 'Value'])['index'] \
         .agg(['first','last'])  \
         .reset_index(level=0, drop=True) \
         .reset_index() \
         .rename(columns=d) \
         .reindex_axis(['Start','End','Value'], axis=1)
print (df2)
   Start  End  Value
0      0    4      0
1      5    7      1
2      8   10      0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks for the answer... I just realized that if the pd.Series contains np.nan the solution does not work... do you know how to include also this case? – gabboshow Jun 29 '17 at 10:00
  • Is possible use hack like `s = s.astype(str)` ? – jezrael Jun 29 '17 at 10:16
  • if the values in df2['Value'] are kept numbers yes... but I tried to add s = s.astype(str) before df2 =.... and it is not working.. – gabboshow Jun 29 '17 at 10:20
  • I created another question here https://stackoverflow.com/questions/44822495/group-values-contaning-np-nan-in-intervals – gabboshow Jun 29 '17 at 10:22