import csv
f = open('akureyrivedur.csv')
dread = csv.DictReader(f, delimiter = ',', skipinitialspace=True)

ak_data = []
for x in dread:
    ak_data.append(x)
f.close()

f1 = open('grimsstadirvedur.csv')
dread1 = csv.DictReader(f1, delimiter = ',', skipinitialspace=True)

grims_data = []
for x in dread1:
    grims_data.append(x)
f.close()

f2 = open('manarbakkivedur.csv')
dread2 = csv.DictReader(f2, delimiter = ',', skipinitialspace=True)

manar_data = []
for x in dread2:
    manar_data.append(x)
f.close()

f3 = open('manarbakkivedur.csv')
dread3 = csv.DictReader(f3, delimiter = ',', skipinitialspace=True)
sauda_data = []
for x in dread3:
    sauda_data.append(x)
f.close()



dictionary = {}

for i in range(2000,2017):
    for j in range(1,13):
        dictionary[str(i) + ";" + str(j)] = {'temp': [], 'avg_max_temp': [], 'avg_min_temp':[], 'air_pressure':[], 'avg_windspeed':[]}

temp_temp = 0
temp_avg_max_temp = 0
temp_avg_min_temp = 0
temp_air_pressure = 0
temp_avg_windspeed = 0

for i in ak_data:
    if(i['t'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['temp'].append(float(i['t']))
    if(i['tx'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_max_temp'].append(float(i['tx']))
    if(i['tn'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_min_temp'].append(float(i['tn']))
    if(i['p'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['air_pressure'].append(float(i['p']))
    if(i['f'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_windspeed'].append(float(i['f']))

for i in grims_data:
    if(i['t'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['temp'].append(float(i['t']))
    if(i['tx'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_max_temp'].append(float(i['tx']))
    if(i['tn'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_min_temp'].append(float(i['tn']))
    if(i['p'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['air_pressure'].append(float(i['p']))
    if(i['f'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_windspeed'].append(float(i['f']))

for i in manar_data:
    if(i['t'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['temp'].append(float(i['t']))
    if(i['tx'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_max_temp'].append(float(i['tx']))
    if(i['tn'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_min_temp'].append(float(i['tn']))
    if(i['p'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['air_pressure'].append(float(i['p']))
    if(i['f'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_windspeed'].append(float(i['f']))


for i in sauda_data:
    if(i['t'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['temp'].append(float(i['t']))
    if(i['tx'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_max_temp'].append(float(i['tx']))
    if(i['tn'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_min_temp'].append(float(i['tn']))
    if(i['p'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['air_pressure'].append(float(i['p']))
    if(i['f'] != 'NA'):
        dictionary[i['ar'] + ";" + i['man']]['avg_windspeed'].append(float(i['f']))


print(dictionary)

f = open('insertNorthEast.sql', 'w')
#f.write("insert into regions (region) values ('{}');\n".format('capital'))
for i in dictionary:
    temp_year = i.split(";")[0]
    temp_month = i.split(";")[1]
    f.write("insert into weathers (region, year, month, temperature, avg_max_temp, avg_min_temp, air_pressure, avg_windspeed) values ('{}',{},{},{:.2f},{:.2f},{:.2f},{:.2f},{:.2f})\n".format('northeast',temp_year, temp_month, sum(dictionary[i]['temp'])/len(dictionary[i]['temp']), sum(dictionary[i]['avg_max_temp'])/len(dictionary[i]['avg_max_temp']), sum(dictionary[i]['avg_min_temp'])/len(dictionary[i]['avg_min_temp']), sum(dictionary[i]['air_pressure'])/len(dictionary[i]['air_pressure']), sum(dictionary[i]['avg_windspeed'])/len(dictionary[i]['avg_windspeed'])))

f.close()