transpose_time_series.py

import csv, statistics

with open('nc_county_time_series0611.csv', 'rU') as time_series_file:
	filereader = csv.reader(time_series_file)
	with open('sts_county_transposed.csv','w') as transposed_file:
		fieldnames = ['nc_county','date', 'date_count', 'cases','new_cases', 'rolling_avg']
		filewriter = csv.DictWriter(transposed_file, fieldnames = fieldnames)
		filewriter.writeheader()
		rowcounter = 0
		for row in filereader:
			case_counts = []
			if rowcounter == 0:
				dates = row
				dates.pop(0)
			else:
				state = row[0]
				fieldcounter = 0
				for field in row:
					if fieldcounter > 1:
						new_cases = int(row[fieldcounter]) - int(row[fieldcounter - 1])
						if new_cases < 0:
							new_cases = 0
						if len(case_counts) > 6:
							case_counts.pop(0)
							case_counts.append(new_cases)
						else:
							case_counts.append(new_cases)
					if fieldcounter > 6:
						rolling_avg = statistics.mean(case_counts)
						filewriter.writerow({'nc_county': state,'date': dates[fieldcounter - 1], 'date_count': fieldcounter - 1, "cases": field, "new_cases": new_cases, "rolling_avg": rolling_avg})
						#filewriter.writerow({'state': state,'date': dates[fieldcounter-1], "cases": field})
					fieldcounter += 1
			rowcounter += 1