pandas calculate timespan between same nearest values

Question

I am a python user. have a excel like this:

time    size   
2017-08-16 00:00:00 12    
2017-08-16 00:01:00 12    
2017-08-16 00:02:00 24   
2017-08-16 00:03:00 24    
2017-08-16 00:04:00 36   
2017-08-16 00:05:00 24    
2017-08-16 00:06:00 36    
2017-08-16 00:07:00 24    
2017-08-16 00:08:00 24    
2017-08-16 00:09:00 24    

want to figure out time span if the between the nearest same num,like this:

    time    size   timespan
    2017-08-16 00:00:00 12    0
    2017-08-16 00:01:00 12    60
    2017-08-16 00:02:00 24    0
    2017-08-16 00:03:00 24    60
    2017-08-16 00:04:00 36    0
    2017-08-16 00:05:00 24    0
    2017-08-16 00:06:00 36    0
    2017-08-16 00:07:00 24    0
    2017-08-16 00:08:00 24    0
    2017-08-16 00:09:00 24    120

please note the middle num 24 is ignored. can be used in pandas is best.

Answer 1

Here I assume you have exported the excel file to csv first, say time.csv

time,size
2017-08-16 00:00:00, 12    
2017-08-16 00:01:00, 12    
2017-08-16 00:02:00, 24   
2017-08-16 00:03:00, 24    
2017-08-16 00:04:00, 36   
2017-08-16 00:05:00, 24    
2017-08-16 00:06:00, 36    
2017-08-16 00:07:00, 24    
2017-08-16 00:08:00, 24    
2017-08-16 00:09:00, 24

and the solution is as follows. The main idea is that a result value needs to be calculated when the size is same as the previous one but different from the next one.

import pandas as pd                                                                
from datetime import datetime                                                      

a = pd.read_csv('time.csv')                                                        
times = [datetime.strptime(x, '%Y-%m-%d %H:%M:%S') for x in a['time']]             

aa = list(a['size']) + [None]                                                      
res = [0] * len(a)                                                                 

prev = None                                                                        
for i, x in enumerate(a['size']):                                                  
    if x != prev:                                                                  
        begin_time = times[i]                                                      
    elif x != aa[i + 1]:                                                           
        res[i] = (times[i] - begin_time).seconds                                   
    prev = x                                                                       
print res  

The output is [0, 60, 0, 60, 0, 0, 0, 0, 0, 120]