I have large number of precipitation daily data in a column (for 53 years).I need to convert it to monthly sum. How can I do that? The data available format and required format are attached in this sheet. Kindly guide me
hey!!
Check the file below. It has been solved. please let me know if there is any problem.
Use pivot table for long data.
Cheers!!
I've missed about 3 days of work for this, but I think it was worth while. :)
The data posted by Arindam contains some incorrect values for the monthly totals for 1961.
For the purpose of repeatable data analysis, I have produced the attached spreadsheet and written 2 Excel subroutines to help analyze the data:
- The main sub generates the requested output as required, and will work even if the data set on the original worksheet changes. I have formatted the results with conditional formatting, so a neat heatmap of all 53 years is visible.
- Another subroutine calculates the yearly totals which is displayed on a separate worksheet and a simple bar chart over time is displayed. It will also work if the data changes.
- Note that I had problems with the original file provided because of inconsistent date formats, possibly stemming from different computer locale settings (by default my computer is set to English (Canada) as well as errors when the data was entered. I wrote another sub to write the data to a standard .csv file with a yyyy-mm-dd date format which works fine when imported into Excel. This is the data that was used in this spreadsheet.
- The data fixing also included setting some missing values and negative values to 0.
Please let me know if you find this file useful!
Muhammad, can you tell me where this data came from so I can source it? May I use it in other analysis? Also, where in the world is the data from? Seems to have lots of rainfall in June - August and not much in December, but not sure if it is just rainfall or also includes snowfall.
Hi, you can copy both (given and required )in one sheet , make a formula in which you take the values alernatively (1,2,3,4,5 and so on in one month) and after that copy the formula as correspond for each year, the only problem is in February but you can solve that manually.It is for Excell
Dear Arindam Roy and Rick Daniel Henderson I highly appreciate your kind response. Dear Rick Daniel Henderson Your suggested solution is very helpful and descriptive. This is flow data of a Sub basin of Indus river in Cumec (cubic meter per second) . You can use it in other analysis and give source as WAPDA, Pakistan.
Dear Muhammad,
I use R statistical program. Although I don't know if you use R, I am posting a script I have developed which gives sums of monthly rainfall from your data. It may be useful for others also who may need a quick way of doing this. It is appended/pasted below..
setwd("D:\\IMTR\\AMTC16_STUDENT PROJECTS\\Victor_Bible") ## set your own directory/path in your computer drive where you saved your data--Data_solved--
yourdata
Richard, I was going to try working on it in R as well. Thanks for your solution. I also started a solution in Python but might not get to that for a while.
- Badges
- Science topic
- Similar topics
- Geoscience
- Atmospheric Sciences