data management with python from perl

[Peter Otten]

ben moretti wrote:

> hi
> 
> i'm learning python, and one area i'd use it for is data management in
> scientific computing. in the case i've tried i want to reformat a data
> file from a normalised list to a matrix with some sorted columns. to
> do this at the moment i am using perl, which is very easy to do, and i
> want to see if python is as easy.
> 
> so, the data i am using is some epiphyte population abundance data for
> particular sites, and it looks like this:
> 
> 1.00  1.00    1.00    "MO"    906.00  "genus species 1"       1.00
> 1.00  1.00    1.00    "MO"    906.00  "genus species 2"       1.00
> 1.00  1.00    1.00    "MO"    906.00  "genus species 3"       1.00
> 1.00  1.00    1.00    "MO"    906.00  "genus species 4"       1.00
> 
> (i have changed the data to protect the innocent) the first four
> columns relate to the location, the fifth to the substrate, the sixth
> is the epiphyte species and the seventh the abundance. i need to turn
> this into a substrate x species matrix with columns 1 to 4 retained as
> sorting columns and the intersection of speces and substrate being the
> abundance. the species name needs to be the column headers. this is
> going to go into a multivariate analysis of variance programme that
> only takes its data in that format. here is an example of the output
> 
> region        location        site    stand   substrate       genus species 1 genus species
> 2     genus species 3 genus species 4 genus species 5 genus species
> 6     genus species 7
> 
> <..etc..>
> 
> 1     1       1       MO      906     0       0       0       0       0       0       0       0       0       0       0       0       0       0
> 
> <..etc...>
> 
> so, to do this in perl - and i won't bore you with the whole script -
> i read the file, split it into tokens and then populate a hash of
> hashes, the syntax of which is
> 
>
$HoH{$tokens[0]}{$tokens[1]}{$tokens[2]}{$tokens[3]}{$tokens[4]}{$tokens[5]}
> = $tokens[6]
> 
> with the various location and species values are the keys of the hash,
> and the abundance is the $tokens[6] value. this now gives me a
> multidimensional data structure that i can use to loop over the keys
> and sort them by each as i go, then to write out the data into a
> matrix as above. the syntax for this is generally like
> 
> # level 1 - region
> foreach $region (sort {$a <=> $b} keys %HoH) {
> 
> # level 2 - location
> foreach $location (sort {$a <=> $b} keys %{ $HoH{$region} }) {
> 
> # level 3 - site
> foreach $site (sort {$a <=> $b} keys %{ $HoH{$region}{$location} })
> 
> <... etc ...>
> 
> there is a bit more perl obviously, but that is the general gist of
> it. multidimensional hash and then looping and sorting to get the data
> out.
> 
> ok. so how do i do this in python? i've tried the "perlish" way but
> didn't get very far, however i know it must be able to be done!

The best solution would probably to be to rely on a database that supports
pivot tables. 
However, I've put together a simple class to generate a pivot table to get
you started. It's only 2D, i. e. f(row,col) -> value, but if I have
understood you correctly that should be sufficient (I am not good at
reading perl).
To read your data from a (text) file, have a look at Python's csv module.

Peter

<code>
import sets

class Adder(object):
    """ Adds all values entered via set()
    """
    def __init__(self, value=0):
        self.value = value
    def set(self, value):
        self.value += value
    def get(self):
        return self.value

_none = object()
class First(object):
    """ Accepts any value the first time set() is called,
        requires the same value on subsequent calls of set().
    """
    def __init__(self):
        self.value = _none
    def set(self, value):
        if self.value is _none:
            self.value = value
        else:
            if value != self.value:
                raise ValueError, "%s expected but got %s" % (self.value,
value)
    def get(self):
        return self.value

class Pivot(object):
    """ A simple Pivot table generator class
    """
    def __init__(self, valueAccumulator, rowHeaders):
        self.rows = sets.Set()
        self.columns = sets.Set()
        self.values = {}
        self.valueAccumulator = valueAccumulator
        self.rowHeaders = rowHeaders
    def extend(self, table, extractRow, extractColumn, extractValue):
        for record in table:
            r = extractRow(record)
            c = extractColumn(record)
            self.rows.add(r)
            self.columns.add(c)
            try:
                fxy = self.values[r, c]
            except KeyError:
                fxy = self.valueAccumulator()
                self.values[r, c] = fxy
            fxy.set(extractValue(record))

    def toTable(self, defaultValue=None, columnCompare=None,
rowCompare=None):
        """ returns a list of lists.
        """
        table = []
        rows = list(self.rows)
        rows.sort(rowCompare)
        columns = list(self.columns)
        columns.sort(columnCompare)
        headers = self.rowHeaders + [c for c in columns]
        table.append(headers)
        for row in rows:
            record = list(row)
            for column in columns:
                v = self.values.get((row, column), None)
                if v is not None:
                    v = v.get()
                record.append(v)
            table.append(record)
        return table


def printTable(p):
    for row in p.toTable():
        print row

if __name__ == "__main__":
    table = [
        "Jack Welsh Beer 1",
        "Richard Maier Beer 1",
        "Bill Bush Wine 2",
        "Bill Bush Wine 2",
    ]
    table = [row.split() for row in table]
    print table
    print "-" * 10
    p = Pivot(Adder, ["Christian", "Surname"])
    def extractRow(record):
        return record[0], record[1]
    def extractValue(record):
        return int(record[3])
    def extractColumn(record):
        return record[2]
    p.extend(table, extractRow, extractColumn, extractValue)

    printTable(p)

    columns = "region location site stand substrate species
abundance".split()

    table = [
        [1.0, 1.0, 1.0, "MO", 906, "species 1", 1],
        [1.0, 1.0, 1.0, "MO", 906, "species 2", 1],
        [1.0, 1.0, 1.0, "MO", 906, "species 3", 1],
        [1.0, 1.0, 1.0, "MO", 906, "species 1", 1],
        [1.0, 1.0, 1.0, "GO", 706, "species 4", 1],
#        [1.0, 1.0, 1.0, "GO", 706, "species 4", 2],# uncomment me
        [1.0, 1.0, 1.0, "GO", 806, "species 1", 1],
        [1.0, 1.0, 1.0, "GO", 906, "species 1", 1],
        [1.0, 1.0, 1.0, "GO", 106, "species 1", 1],
    ]
    p = Pivot(First, columns[:5])
    p.extend(table, lambda r: tuple(r[:5]),
                    lambda r: r[5],
                    lambda r: r[6])
    printTable(p)
</code>