Aggregate and fill data for missing dates (days and hours)
Question: Suppose we have a list like this showing the count of each object on a
particular date (mm-dd-yyyy-hour-minute):

    
    
    A = [
     [
        ['07-07-2012-21-04', 'orange', 1],
        ['08-16-2012-08-57', 'orange', 1],
        ['08-18-2012-03-30', 'orange', 1],
        ['08-18-2012-03-30', 'orange', 1],
        ['08-19-2012-03-58', 'orange', 1],
        ['08-19-2012-03-58', 'orange', 1],
        ['08-19-2012-04-09', 'orange', 1],
        ['08-19-2012-04-09', 'orange', 1],
        ['08-19-2012-05-21', 'orange', 1],
        ['08-19-2012-05-21', 'orange', 1],
        ['08-19-2012-06-03', 'orange', 1],
        ['08-19-2012-07-51', 'orange', 1],
        ['08-19-2012-08-17', 'orange', 1],
        ['08-19-2012-08-17', 'orange', 1]
     ],
     [
        ['07-07-2012-21-04', 'banana', 1]
     ],
     [
        ['07-07-2012-21-04', 'mango', 1],
        ['08-16-2012-08-57', 'mango', 1],
        ['08-18-2012-03-30', 'mango', 1],
        ['08-18-2012-03-30', 'mango', 1],
        ['08-19-2012-03-58', 'mango', 1],
        ['08-19-2012-03-58', 'mango', 1],
        ['08-19-2012-04-09', 'mango', 1],
        ['08-19-2012-04-09', 'mango', 1],
        ['08-19-2012-05-21', 'mango', 1],
        ['08-19-2012-05-21', 'mango', 1],
        ['08-19-2012-06-03', 'mango', 1],
        ['08-19-2012-07-51', 'mango', 1],
        ['08-19-2012-08-17', 'mango', 1],
        ['08-19-2012-08-17', 'mango', 1]
     ]
    

]

What I need to do in A is to fill all the missing dates (from minimum date to
maximum date of A) for each object with value as 0. Once the missing dates and
their corresponding values (0) are in, I want to sum up the values for each
date, so that no date repeats - for each sublist.

Now, What I am trying to goes as follows: I am breaking up A's dates and
values separately (in lists named u and v) and converting each sublist into a
pandas Series, and allocating their respective indices to them. So for each
zip(u,v):

    
    
    def generate(values, indices):
    
        indices = flatten(indices)
    
        date_index = DatetimeIndex(indices)
        ts = Series(values, index=date_index)
    
        ts.reindex(date_range(min(date_index), max(date_index)))
    
        return ts
    

But in here, the reindexing is causing raising an exception. What I am looking
for is a purely pythonic way (without pandas) which is totally based on either
list comprehension or maybe even numpy arrays.

There is another issue of aggregation over hours, which means that if all the
dates are same and only the hours are different then I want to fill in all the
missing hours of the day and then repeat the same process of aggregation over
each hour, with missing hours filled in with 0 values.

Thanks in advance.


Answer: What about this:

    
    
    from collections import defaultdict, OrderedDict                              
    from datetime import datetime, timedelta                                      
    from itertools import chain, groupby                                          
    
    flat = sorted((datetime.strptime(d, '%m-%d-%Y-%H-%M').date(), f, c)           
                  for (d, f, c) in chain(*A))                                     
    counts = [(d, f, sum(e[2] for e in l))                                        
              for (d, f), l                                                       
              in groupby(flat, key=lambda t: (t[0], t[1]))]                       
    
    # lets assume that there are some data                                        
    start = counts[0][0]                                                          
    end = counts[-1][0]                                                           
    result = OrderedDict((start+timedelta(days=i), defaultdict(int))             
                         for i in range((end-start).days+1))                      
    for day, data in groupby(counts, key=lambda d: d[0]):                         
        result[day].update((f, c) for d, f, c in data)
    

My question is: do we **really** need to fill non existing dates - I can
easily imagine situation when this will be **a lot** of data, even dangerous
amount of data... I think that it is better to use simple general function and
generator if you want to list them somewhere:

    
    
    from collections import defaultdict                                           
    from datetime import datetime, timedelta                                      
    from itertools import chain, groupby                                          
    
    def aggregate(data, resolution='daily'):                                      
        assert resolution in ['hourly', 'daily']                                  
        if resolution == 'hourly':                                                
            round_dt = lambda dt: dt.replace(minute=0, second=0, microsecond=0)   
        else:                                                                     
            round_dt = lambda dt: dt.date()                                       
    
        flat = sorted((round_dt(datetime.strptime(d, '%m-%d-%Y-%H-%M')), f, c)    
                      for (d, f, c) in chain(*A))                                 
        counts = [(d, f, sum(e[2] for e in l))                                    
                  for (d, f), l                                                   
                  in groupby(flat, key=lambda t: (t[0], t[1]))]
        result = {}                                                              
        for day, data in groupby(counts, key=lambda d: d[0]):                    
            d = result[day] = defaultdict(int)                                   
            d.update((f, c) for d, f, c in data)                                 
        return result                                                            
    
    def xaggregate(data, resolution='daily'):                                      
        aggregated = aggregate(data, resolution)                                 
        curr = min(aggregated.keys())                                            
        end = max(aggregated.keys())                                             
        interval = timedelta(days=1) if resolution == 'daily' else timedelta(seconds=3600)
        while curr <= end:
            # None is sensible value in case of missing data I think                                                       
            yield curr, aggregated.get(curr)                   
            curr += interval                                                                                 
    

In general my suggestion is that you should not use lists as ordered
structures (I mean `['07-07-2012-21-04', 'mango', 1]`). I think that `tuple`
is more appropriate for this purpose and of course `collections.namedtuple` is
even more desired.

