0

So I have a list of dates in one column and a list of values in another. 

2/8/13  474
2/7/13  463.25
2/6/13  456.47
2/5/13  444.05
2/4/13  453.91
2/1/13  459.11
1/31/13 456.98
1/30/13 457
1/29/13 458.5
1/28/13 437.83
1/25/13 451.69
1/24/13 460
1/23/13 508.81
1/22/13 504.56
1/18/13 498.52
1/17/13 510.31

I need to find a way to compile the dates in the first column and output the average value for that month.

The output should look like month:year average_value_for_month.

For example, the first two outputs should look like 

02:2013  458.46

01:2013  500.08

^this says that for the months of feb,jan in 2013, the average values were 458.46,500.08

Right now my code is, 

def averageData(list_of_tuples):
    #print(list_of_tuples) #prints out the list obtained from getDataList
    sep_list = []
    for i in range(0,len(list_of_tuples)): 
        split_list = list_of_tuples[i].split()
        sep_list.append(split_list)
        #print(sep_list[i]) #prints out list with index [i][0] being the date and index [i][1] being the column value
    new_list = []
    for i in range(0,len(sep_list)):
        sep_list[i][0] = sep_list[i][0].split('-') #splits dates in year, month, day
        #print(sep_list[i][0])
        print(sep_list[i][0])
    for i in range(0,len(sep_list)):
        if sep_list[i][0][0] == sep_list[i+1][0][0] and sep_list[i][0][1] == sep_list[i+1][0][1]:
            new_date = sep_list[i][0][1]+':'+sep_list[i][0][0]
        new_list.append(new_date)
        #print(new_list[i])

The original list is formatted like 

['2013-02-08 474.00']

My first for loop makes the list become 

['2013-02-08', '474.00']

then the second for loop turns the list into 

[['2013', '02', '08'], '474.00']

I'm stuck on where to go from here. Please help.

NuktukHotS
  • 11
  • 1
  • 4
  • try using pivot table! http://stackoverflow.com/questions/15570099/pandas-pivot-tables-row-subtotals – dot.Py Jul 13 '16 at 20:23

2 Answers2

0

You can combine list methods like .split and the [:] delimiters to get less loops and lists and keep a better overview. An example for a given tuple called "tuple":

    datelist=tuple.split(" ")[0].split("/")
    month=datelist[0]
    year=datelist[2]
    value=tuple.split(" ")[1]

If you keep your variables organized like this, I think you can figure out the rest by yourself =)

Domme
  • 141
  • 7
0

Here's my solution. Hope this helps:

from datetime import datetime

def averageData(list_of_tuples):
    dic = {}    
    for i in list_of_tuples:
        i = list(map(str,i.strip().split(' ')))
        dt = datetime.strptime(i[0] , '%Y-%m-%d')
        if (dt.month,dt.year) in dic:
            dic[(dt.month,dt.year)].append(float(i[1]))
        else:
            dic[(dt.month,dt.year)] = [float(i[1])]

    for i in dic.items():
        #print(i)
        print (str(i[0][0])+':'+str(i[0][1])+' '+str(round(sum(i[1])/len(i[1]),2)))

tuples = ['2013-02-08 474','2013-02-07 463.25','2013-02-06 456.47',
'2013-02-05 444.05',
'2013-02-04 453.91',
'2013-02-01 459.11',
'2013-01-31 456.98',
'2013-01-30 457',
'2013-01-29 458.5',
'2013-01-28 437.83',
'2013-01-25 451.69',
'2013-01-24 460',
'2013-01-23 508.81',
'2013-01-22 504.56',
'2013-01-18 498.52',
'2013-01-17 510.31']

averageData(tuples)
HIM
  • 1
  • 2