Average data based on specific columns - python

you can use pandas to do this quickly:

import pandas as pd
from StringIO import StringIO
data = StringIO("""564645  7371810 0   21642   1530    1   2   30.8007
564645  7371810 0   21642   8250    1   2   0.0103
564645  7371810 0   21643   1530    1   2   19.3619
""")
df = pd.read_csv(data, sep="\\s+", header=None)
df.groupby(["X.1","X.2","X.5"])["X.8"].mean()

the output is:

X.1     X.2      X.5 
564645  7371810  1530    25.0813
                 8250     0.0103
Name: X.8

if you don't need index, you can call:

df.groupby(["X.1","X.2","X.5"])["X.8"].mean().reset_index()

this will give the result as:

      X.1      X.2   X.5      X.8
0  564645  7371810  1530  25.0813
1  564645  7371810  8250   0.0103

It's not the most elegant of answers, and I have no idea how fast/efficient it is, but I believe it gets the job done based on the information you provided:

import numpy

data_file = "full_location_of_data_file"
data_dict = {}
for line in open(data_file):
    line = line.rstrip()
    columns = line.split()
    entry = [columns[0], columns[1], columns[4]]
    entry = "-".join(entry)
    try: #valid if have already seen combination of 1,2,5
        x = data_dict[entry].append(float(columns[7]))
    except (KeyError): #KeyError the first time you see a combination of columns 1,2,5
        data_dict[entry] = [float(columns[7])]

for entry in data_dict:
    value = numpy.mean(data_dict[entry])   
    output = entry.split("-")
    output.append(str(value))
    output = "\t".join(output)
    print output

I'm unclear if you want/need columns 3, 6, or 7 so I omited them. Particularly, you do not make clear how you want to deal with different values which may exist within them. If you can elaborate on what behavior you want (ie default to a certain value, or to the first occurrence) I'd suggest either filling in with default values or store the first instance in a dictionary of dictionaries rather than a dictionary of lists.

import os #needed system utils
import numpy as np# for array data processing


datadirectory = '/media/DATA/arxeia/Dimitris/Testing/12_11'
working = os.environ.get("WORKING_DIRECTORY", datadirectory)
os.chdir( working)

##HERE I WAS TRYING TO READ THE FILE, AND THEN USE THE NAME OF THE STRING IN THE FOLLOWING LINE - THAT RESULTED IN THE SAME ERROR DESCRIBED BELOW (ERROR # 42 (I think) - too large name)

data_dict = {} #Create empty dictionary
for line in open('/media/DATA/arxeia/Dimitris/Testing/12_11/1a.dat'): ##above error resolved when used this
    line = line.rstrip()
    columns = line.split()
    entry = [columns[0], columns[1], columns[4]]
    entry = "-".join(entry)
    try: #valid if have already seen combination of 1,2,5
        x = data_dict[entry].append(float(columns[7])) 
    except (KeyError): #KeyError the first time you see a combination of columns 1,2,5
        data_dict[entry] = [float(columns[7])]

for entry in data_dict:
    value = np.mean(data_dict[entry])   
    output = entry.split("-")
    output.append(str(value))
    output = "\t".join(output)
   print output

MY OTHER PROBLEM NOW IS GETTING OUTPUT IN STRING FORMAT (OR ANY FORMAT) - THEN I BELIEVE I KNOW I CAN GET TO THE SAVE PART AND MANIPULATE THE FINAL FORMAT

np.savetxt('sorted_data.dat', sorted, fmt='%s', delimiter='\t') #Save the data

I STILL HAVE TO FIGURE HOW TO ADD THE OTHER COLUMNS - I AM WORKING ON THAT TOO