Posts for the month of February 2009

Reshape R - long to wide conversion

--May be incorrect, working on a fix will post when done-- Keeping data in long format just makes sense, but for some reason statistics often requires your data in wide format. The good news is that it's much easier to go from long to wide than the other way around. Although the tool I'm about to describe can go both ways.

Using  R and pulling a dataframe in from an SQLite database the following command will take the dataframe and for every Species listed create a new column based on it. Then all the records are grouped by their Plot and the resulting Percent Cover for a given species in a plot is now a value in one of the columns instead of it being it's own row.

Plant (the data.frame)

Plot Species PrCover
A Poppy 5
A Redwood 20
B Oak 50
B Poppy 10
 WidePlant <- reshape(Plant, v.names = "PrCover", idvar = "Plot", timevar = "Species", direction = "wide")

WidePlant (the results)

Plot PrCover.Poppy PrCover.Redwood PrCover.Oak
A 5 20 NA
B 10 NA 50

The documentation is kinda hard to read, so here's my attempt at plain english

  • v.names = the values you want to show up under your new columns
  • idvar = the id that you want to group your data record by
  • timevar = the values that you want to make up the new columns, however many distinct values are in this column determines the number of new columns
  • direction = wide, the destination or resulting format we want

Installing Sqlite 3.6.x on Ubuntu Intrepid

I needed sqlite 3.6 or newer for an application I'm working on but Ubuntu Intrepid has 3.5.9, specifically for Rtree spatial indexes. (In order to build and use  Spatialite.

After weighing my options and doing a little research I noticed that the Jaunty packages barely have any dependencies and they are already met by Intrepid.

So I downloaded:

Steps to follow:

  1. Uninstall libsqlite3-dev 3.5.9
  2. Install libsqlite3 3.6.10
  3. Install libsqlite3-dev 3.6.10
  4. Install sqlite3 3.6.10

To test with python(happens to be what I'm developing with)

from pysqlite2 import dpapi2 as sqlite3
print sqlite3.sqlite_version