Total sum amount per month using date time in Python

Question

Now I have a list of dates and values, but I don't know how to do calculations with the date structure.

It looks like

[[datetime.date(2018, 8, 10) 1076.2392505636847]
 [datetime.date(2018, 8, 11) 3537.9781979862732]
 [datetime.date(2018, 8, 12) 8637.536518161462]
 [datetime.date(2018, 8, 13) 15660.768121458246]
 [datetime.date(2018, 8, 14) 21087.477911830327]
 [datetime.date(2018, 8, 15) 21087.477911830327]
 [datetime.date(2018, 8, 16) 15660.768121458246]
 [datetime.date(2018, 8, 17) 8637.536518161465]
 [datetime.date(2018, 8, 18) 3537.9781979862732]
 [datetime.date(2018, 8, 19) 1076.2392505636856]]

also, I know that

startdate = datetime.date(2018, 8, 10)
enddate = datetime.date(2018,8, 19)

I want to create another list that consists of ['Year-Month' data, total sum of the month]. In this case, it will be just ['2018-8' total sum]. if enddate is like 2020,8,19, then the length would be 25 (two years and a month).

Could you share some useful functions/approaches that I may use?

Answer 1

collections.defaultdict

You can use collections.defaultdict for an O(n) solution which does not require sorting.

import datetime

L = [[datetime.date(2018, 8, 10), 1076.23], [datetime.date(2018, 8, 11), 3537.97],
     [datetime.date(2018, 8, 19), 1076.23], [datetime.date(2018, 9, 10), 5.23],
     [datetime.date(2018, 9, 11), 10.97], [datetime.date(2018, 10, 19), 15.23]]

from collections import defaultdict

d = defaultdict(int)

for date, val in L:
    d[date.strftime('%Y-%m')] += val

# defaultdict(int,
#             {'2018-08': 5690.43,
#              '2018-09': 16.20,
#              '2018-10': 15.23})

res = list(map(list, d.items()))

print(res)

[['2018-08', 5690.43],
 ['2018-09', 16.20],
 ['2018-10', 15.23]]

pandas

If you are happy to use a 3rd party library, you can use Pandas:

# construct dataframe from list of lists
df = pd.DataFrame(L, columns=['date', 'val'])

# convert to datetime
df['date'] = pd.to_datetime(df['date'])

# perform GroupBy operation over monthly frequency
res = df.set_index('date').groupby(pd.Grouper(freq='M'))['val'].sum().reset_index()

print(res)

        date      val
0 2018-08-31 5690.430
1 2018-09-30   16.200
2 2018-10-31   15.230

Answer 2

You can use min and max to find starttime and endtime. Then use itertools.groupby to group the entries for each month and find sum for each group

lst = [[datetime.date(2018, 8, 10), 1076.2392505636847],
 [datetime.date(2018, 8, 11), 3537.9781979862732],
 [datetime.date(2018, 8, 12), 8637.536518161462],
 [datetime.date(2018, 8, 13), 15660.768121458246],
 [datetime.date(2018, 8, 14), 21087.477911830327],
 [datetime.date(2018, 8, 15), 21087.477911830327],
 [datetime.date(2018, 8, 16), 15660.768121458246],
 [datetime.date(2018, 8, 17), 8637.536518161465],
 [datetime.date(2018, 8, 18), 3537.9781979862732],
 [datetime.date(2018, 8, 19), 1076.2392505636856]]

starttime = min(lst)
endtime   = max(lst)

from itertools import groupby
from operator import itemgetter
res = [[k.strftime('%Y-%m'), sum(map(itemgetter(1), group))] for k,group in groupby(lst, lambda sl: sl[0].replace(day=1))]
print (starttime, endtime)
print (res)

Output

[datetime.date(2018, 8, 10), 1076.2392505636847] [datetime.date(2018, 8, 19), 1076.2392505636856]
[['2018-08', 99999.99999999999]]