0

There's a dataset in a csv file csv_file.csv whose sample structure is like this:

state_code,year,month,value
01,2012,M01, 8.1
01,2012,M02, 8.0
01,2012,M03, 8.0
01,2012,M04, 8.1
01,2012,M05, 8.2
01,2012,M06, 8.2
01,2012,M07, 8.2
01,2012,M08, 8.1
01,2012,M09, 7.9
01,2012,M10, 7.8
01,2012,M11, 7.7
01,2012,M12, 7.6

I also have another csv file which has states code mapping to the states names - states.csv:

state_code,state_name
01,Alabama
02,Alaska
04,Arizona
05,Arkansas
06,California
08,Colorado
09,Connecticut

Now what I want to do is from csv_file.csv I want to convert value data from monthly data into quarterly data for each year and for each state by taking average of monthly values. So for example for state 01 I have data for 12 months(M01 to M12) and I want to convert it into quarterly data by taking average of every 3 months (average of M01,M02,M03 to make Q01 and so on). This I want to do for each year of each state. At the end I want to write the results to a new csv file with replacing the state_code with the state_name from the mapping with data from state.csv. So the desired output should be something like this:

state,year,quarter,value
Alabama,2012,Q01,6.5
Alabama,2012,Q02,6.0
Alabama,2012,Q03,6.1
Alabama,2012,Q04,5.7
Alabama,2013,Q01,6.2
  .
  .
  .

I know how to read/write to csv and have that part as:

fileRD = open('csv_file.csv', 'r') # Input file
fileST = open('states.csv', 'r') # States mapping file
fileWT = open('Output.csv', 'wb') #Output file to write to

reader_RD = csv.reader(fileRD)
reader_ST = csv.reader(fileST)
writer = csv.writer(fileWT)

How can I do the above mentioned averaging of data in python?

Jason Donnald
  • 2,256
  • 9
  • 36
  • 49
  • Have you heard about [pandas](http://pandas.pydata.org/)? – Fabzi Sep 09 '15 at 17:36
  • What have you tried? Which solutions have you considered? Where did you get stuck? There is a [CSV section](https://docs.python.org/2/library/csv.html) in the Python docs which can help you. – Sam van Herwaarden Sep 09 '15 at 17:41
  • @SamvanHerwaarden since I am new to python so I am having difficulty in getting how to take the average so as to covert monthly data to quaterly across a year and state. I know how to read and write to csv. – Jason Donnald Sep 09 '15 at 17:59
  • Then I would suggest you post some of the read/write code and shorten the sample data snippets, so we can help you with solutions that fit in the structure you have in place. In any case [this question](http://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks-in-python) might be helpful if your data is complete (no gaps), then you only need a function to merge each chunk into a quarterly data point. – Sam van Herwaarden Sep 09 '15 at 18:06
  • @SamvanHerwaarden I have shorten the dataset to one year and have also provided the read/write part that I have in place. Also there is no gap in the data so for each state and each year there exactly 12 monthly values – Jason Donnald Sep 09 '15 at 18:15

0 Answers0