bgrundy bgrundy - 18 days ago 4
Python Question

Creating chart data from a list of lists ( with start/stop date)

I'm trying to take a list of dates and numbers in a list of lists, pulled from a spreadsheet, and create a list of dictionaries where each entry is a start date, stop date, and value. The sample data looks like this:

d = [[datetime.datetime(2016, 11, 1, 0, 0), 50L],
[datetime.datetime(2016, 11, 2, 0, 0), 50L],
[datetime.datetime(2016, 11, 2, 0, 0), 50L],
[datetime.datetime(2016, 11, 2, 0, 0), 23L],
[datetime.datetime(2016, 11, 2, 0, 0), 23L],
[datetime.datetime(2016, 11, 2, 0, 0), 23L],
[datetime.datetime(2016, 11, 7, 0, 0), 23L],
[datetime.datetime(2016, 11, 8, 0, 0), 23L],
[datetime.datetime(2016, 11, 9, 0, 0), 23L],
[datetime.datetime(2016, 11, 9, 0, 0), 23L],
[datetime.datetime(2016, 11, 9, 0, 0), 66L],
[datetime.datetime(2016, 11, 9, 0, 0), 66L],
[datetime.datetime(2016, 11, 13, 0, 0), 50L],
[datetime.datetime(2016, 11, 14, 0, 0), 50L],
[datetime.datetime(2016, 11, 15, 0, 0), 100L],
[datetime.datetime(2016, 11, 16, 0, 0), 100L],
[datetime.datetime(2016, 11, 17, 0, 0), 100L],
[datetime.datetime(2016, 11, 17, 0, 0), 100L],
[datetime.datetime(2016, 11, 17, 0, 0), 100L],
[datetime.datetime(2016, 11, 20, 0, 0), 100L],
[datetime.datetime(2016, 11, 21, 0, 0), 100L],
[datetime.datetime(2016, 11, 21, 0, 0), 100L],
[datetime.datetime(2016, 11, 23, 0, 0), 50L]]


What I need (for later conversion to a Gantt Chart) is output like this:

{'start': '11/01/2016', 'end': '11/02/2016', 'IP': '50'}
{'start': '11/02/2016', 'end': '11/09/2016', 'IP': '23'}
{'start': '11/09/2016', 'end': '11/09/2016', 'IP': '66'}
{'start': '11/13/2016', 'end': '11/14/2016', 'IP': '50'}
{'start': '11/15/2016', 'end': '11/21/2016', 'IP': '100'}
{'start': '11/23/2016', 'end': '11/24/2016', 'IP': '50'}


I'm getting close, but I can't figure out how to include the last value (yes, they are IP addresses, shortened in my test set for readability). My current code also messes up the dates on the last to items.

And here is what I'm getting:

{'start': '11/01/2016', 'end': '11/02/2016', 'IP': '50'}
{'start': '11/02/2016', 'end': '11/09/2016', 'IP': '23'}
{'start': '11/09/2016', 'end': '11/09/2016', 'IP': '66'}
{'start': '11/14/2016', 'end': '11/14/2016', 'IP': '50'}
{'start': '11/16/2016', 'end': '11/21/2016', 'IP': '100'}


Here's my code so far:

df = []
fmtD = '%m/%d/%Y'
dates = []
lastIP = 0

for x in range(len(d)):
ipAdd = d[x][1]
if ipAdd == lastIP or x == 0:
dates.append(d[x][0])
elif ipAdd != lastIP:
df.append(dict(IP = str(lastIP),\
start=min(dates).strftime(fmtD),\
end = max(dates).strftime(fmtD)))
dates = []
elif x == len(d):
df.append(dict(IP = str(lastIP),\
start=min(dates).strftime(fmtD),\
end = max(dates).strftime(fmtD)))
lastIP = ipAdd

for item in df:
print item

Answer

Your code will never reach last elif condition, elif x == len(d) - 1: because previously you checked both ipAdd == lastIP and ipAdd != lastIP in if ipAdd == lastIP or x == 0: and elif ipAdd != lastIP: condition. So, i guess you have added the last elif condition to add the last item of the list to df. So, it should be placed outside the for loop. Moreover, after resetting the dates list inside elif ipAdd != lastIP: condition, you didn't store the current value of the date! Adding a statement, dates.append(d[x][0]) solves your problem.

Overall, you can modify your code in the following way to achieve your desired output.

for x in range(len(d)):
    ipAdd = d[x][1]
    if ipAdd == lastIP or x == 0:
        dates.append(d[x][0])
    elif ipAdd != lastIP:
        df.append(dict(IP = str(lastIP),\
                       start=min(dates).strftime(fmtD),\
                       end = max(dates).strftime(fmtD)))
        dates = []
        dates.append(d[x][0]) 
    lastIP = ipAdd

if x == len(d) - 1:
    df.append(dict(IP = str(lastIP),\
                   start=min(dates).strftime(fmtD),\
                   end = max(dates).strftime(fmtD)))
for item in df:
    print item

It will print: (as you expected)

{'start': '11/01/2016', 'end': '11/02/2016', 'IP': '50'}
{'start': '11/02/2016', 'end': '11/09/2016', 'IP': '23'}
{'start': '11/09/2016', 'end': '11/09/2016', 'IP': '66'}
{'start': '11/13/2016', 'end': '11/14/2016', 'IP': '50'}
{'start': '11/15/2016', 'end': '11/21/2016', 'IP': '100'}
{'start': '11/23/2016', 'end': '11/23/2016', 'IP': '50'}