# How to calculate mean of particular column month wise with years?

I have text file having temperature data of April and May months for six years. I want to calculate mean of every month with each year. I am using awk command but it calculate overall temperature mean. I don't know how to use awk command for this problem.

``````awk '{sum+=\$6; n++} END {print sum/n;}' vk4.txt
``````

The sample file i am showing,

``````STATION_ID,LATITUDE,LONGITUDE,TIME(GMT),DATE(GMT),AIR_TEMP(°C)
IMDE1611_14164B(PITAMPURA)  28.7    77.15   1   04/05/2012  31.4
IMDE1611_14164B(PITAMPURA)  28.7    77.15   2   04/05/2012  31.9
IMDE1611_14164B(PITAMPURA)  28.7    77.15   3   04/05/2012  32.6
IMDE1611_14164B(PITAMPURA)  28.7    77.15   2   05/01/2012  32.1
IMDE1611_14164B(PITAMPURA)  28.7    77.15   3   05/01/2012  32.3
IMDE1611_14164B(PITAMPURA)  28.7    77.15   4   05/01/2012  33
IMDE1611_14164B(PITAMPURA)  28.7    77.15   5   04/01/2013  33.9
IMDE1611_14164B(PITAMPURA)  28.7    77.15   6   04/01/2013  34.2
IMDE1611_14164B(PITAMPURA)  28.7    77.15   7   04/01/2013  34.8
``````
• As you’ve never accepted an answer before: if an answer post actually answers your question, don’t forget to click the grey ☑ under the number at the left of its text to accept it, which means “yes, this answer is valid”! Jul 25, 2018 at 23:36

Another – very flexible – Python solution based on `itertools.groupby`: https://github.com/davidfoerster/group-aggregate

## Installation

``````wget https://github.com/davidfoerster/group-aggregate/raw/master/group-aggregate.py
chmod +x group-aggregate.py
``````

## Usage

``````./group-aggregate.py [--skip N] [options...] groups aggregators...
``````
• `groups` – A list of field indexes or column ranges used to group records (zero-based, comma-separated).

• `aggregators` – A field index (zero-based) or column range, the name of an aggregation function, and optionally a format string, all colon-separated.

• `--skip N` - Skip N lines at the beginning of the input (e. g. header lines).

See the output of `python3 -O group-aggregate.py --help` for more.

## Examples

### Example 1

The grouping and aggregation program can't handle partial fields; let’s reformat your data set with other tools to work around it:

``````awk '{ gsub(/\//, OFS, \$5); print; }'  | ...
``````

Now the grouping field, the year, has the index 6 and the aggregated field, the temperatures, has the index 7 of which you'd like to take the average:

``````... | ./group-aggregate.py --skip 1 6 7:favg < data.csv
``````

You can also format the temperature averages, in this example to show exactly one decimal place:

``````... | ./group-aggregate.py --skip 1 6 7:favg:.1f
``````

### Example 2

Instead of fields separators you can also specify column ranges which works well with your data format:

``````./group-aggregate.py --skip 1 54-58 60-:favg:.1f < data.csv
``````

Now you don't even need to pre-format the data like in example 1.

### Output

The output of both example commands is the same:

``````2012    32.2
2013    34.3
``````

You could do this with a little Python script:

``````#!/usr/bin/env python3

import sys
if len(sys.argv) != 2:
print("You must provide exactly one filename to read as argument.")
exit(-1)

file = open(sys.argv[1])

dict = {}
for line in file:
datestr, tempstr = line.split()[4:]
year, temp = int(datestr.split("/")[-1]), float(tempstr)
dict.setdefault(year, []).append(temp)

for year in dict:
print("{0}:\t{1:.2f}".format(year, sum(dict[year]) / len(dict[year])))
``````

It reads the file specified as argument when executing the script line by line and creates a dictionary that maps years to lists of temperature values. After the whole file got processed, it will calculate and print the average temperatures per year.

Here is an example run with the data file `vk4.txt` you provided. I saved the script above as `avgtemp.py` in the current directory and made it executable using `chmod +x avgtemp.py`:

``````\$ ./avgtemp.py vk4.txt
2012:   32.22
2013:   34.30
``````

If you want, the exact output format could be easily modified by simply editing the `"{0}:\t{1:.2f}"` format string in the last line of the script. You can enter any pattern here, as long as it contains a `{0}` to get replaced with the year and `{1:.2f}` or similar to get replaced with the average temperature, displayed with two decimal digits. The `\t` is a tab.

• Traceback (most recent call last): File "./av_temp.py", line 13, in <module> datestr, tempstr = line.split()[4:] ValueError: not enough values to unpack (expected 2, got 0) when I run this script then it show the above error code. Jun 5, 2017 at 6:52
• @VaibhavKumar That means you are reading a file which doesn't have exactly 6 columns in every row except the first, which is ignored. Please verify the format of your input file. Jun 5, 2017 at 11:56
• Works nicely. With 1 million lines of input file it took `0m09.07s real` on my system to perform calculations Jun 27, 2017 at 21:13

The basic idea will be to create a year-month key from the date field, and then sum and count the entries based on that key using associative arrays e.g.

``````awk '
NR>1 {
split(\$5,d,"/"); s[d[3]"/"d[1]]+=\$6; c[d[3]"/"d[1]]++;
}
END {
for (i in s) print i, s[i]/c[i]
}' vk4.txt
``````

``````\$ mawk '
NR>1 {
split(\$5,d,"/"); s[d[3]"/"d[1]]+=\$6; c[d[3]"/"d[1]]++;
}
END {
for (i in s) print i, s[i]/c[i];
}' vk4.txt
2012/04 31.9667
2012/05 32.4667
2013/04 34.3
``````

If you have GNU awk (`gawk`) v4+ you can add explicit sorting.

• IMDE1611_14164B(PITAMPURA),28.7,77.15,1,04/05/2012,31.4 IMDE1611_14164B(PITAMPURA),28.7,77.15,2,04/05/2012,31.9 IMDE1611_14164B(PITAMPURA),28.7,77.15,7,04/03/2013,34.1 IMDE1611_14164B(PITAMPURA),28.7,77.15,8,04/03/2013,34.6 my file is in csv format and when I used the above script to calculate average of temp (sixth column) every year month wise then it show 0 output. Jun 7, 2017 at 5:00
• @VaibhavKumar this is different from what is said in your original post. Please be consistent. If the file is in csv format, say so in the question. We're not supposed to pull out this information from comments Jun 27, 2017 at 21:14

### Perl solution

Here's a one-line command, which operates on the premise of building two hashes - `\$h1` for summing the temperature values and `\$h2` for storing total number of processed records. Each corresponding has will contain same key in format `MMYYYY` that is extracted from your column #5 ( which is for `perl` array index #4, i.e. `\$F[4]` ):

``````perl -lane 'do{ @a=split "/",\$F[4]; \$k= \$a[0] . \$a[2]; \$h1{\$k}+=\$F[5] and \$h2{\$k}+=1 } if \$. != 1 and \$F[4]; END{ do {print \$_," ",\$h1{\$_}/\$h2{\$_}  } for keys %h1;  }'
``````

Key point to note here:

• we use `do {} if condition1 and condition2` structure. The `{}` action is performed only when line number is not 1 ( i.e. we skip header ) and there is `\$F[4]` ( i.e. we avoid blank or incomplete lines).

• `@a=split "/",\$F[4]` allows us to break down `MM/DD/YYYY` date stamp into parts and with `\$k= \$a[0] . \$a[2]` we create key variable which will allow us to store the data into two hashes.

• `END{}` structure will perform action when the whole file has been read.

The solution performs reasonably well. Here's a test with 1,100 000 lines of input:

``````bash-4.3\$ time perl -lane 'do{ @a=split "/",\$F[4]; \$k= \$a[0] . \$a[2]; \$h1{\$k}+=\$F[5] and \$h2{\$k}+=1 } if \$. != 1 and \$F[4]; END{ do {print \$_," ",\$h1{\$_}/\$h2{\$_}  } for keys %h1;  }' big_input.txt
052012 32.4666666666021
042012 31.8250000001141
042013 34.3000000000646

real    0m8.600s
user    0m8.480s
sys 0m0.032s
bash-4.3\$ wc -l big_input.txt
1100000 big_input.txt
``````

NOTE: for csv format use `perl -a -F',' -lne` instead

This might be more suited to Stack Overflow; however, here's a solution using Python, in which you should replace `temperature_data.txt` in the first line with your file.

``````f=open("temperature_data.txt","r") ### REPLACE temperature_data.txt WITH THE FILE CONTAINING YOUR DATA
f.close()

flines_split=[line.split() for line in flines] #split each line up
data_split=[line for line in flines_split if len(line)>=5 and line[4].count("/")==2] #get only lines with the date in
gathered_data={}
for line in data_split: #this block sanitises the data
month=int(line[4][:2]) ### NOTE THAT THIS ASSUMES YOU ARE USING AMERICAN DATE FORMAT
### IF YOU ARE NOT, REPLACE "month=int(line[4][:2])" WITH "month=int(line[4][3:5])"
year=int(line[4][6:])
if (month,year) in gathered_data:
gathered_data[(month,year)].append(float(line[5]))
else:
gathered_data[(month,year)]=[float(line[5])]

def mean(l): #function to calculate means
return sum(l)/float(len(l))

means={k:mean(gathered_data[k]) for k in gathered_data} #calculate means

print("Month Year Temperature")
for k in sorted(list(means)): #print output
print("{date[0]:^5} {date[1]} {temp:.4}".format(date=k,temp=means[k])) ### the 4 in {temp:.4} specifies precision and can be modified.
``````
• When I have used the script to get output then it show python3 script.py Month Year Temperature Jun 7, 2017 at 7:28