Fix locate command on Mountain Lion

So apparently upgrading to the Macintosh system Mountain Lion (10.8) breaks the unix "locate" command because it deletes the "nobody" user that was used to own the database. I found this gist that gives instructions how to get it working again. However, in my case I also had to enable the root user and then log in as root in Terminal and run the command /usr/libexec/locate.updatedb as root. Then it finally worked. Sheesh.

Understanding R plot margins

Came across this great VISUAL explanation of the differences between margins (mar) and outer margin (oma) regions. This is very helpful.

How to subset RPy2 dataframe

This example demonstrates how to create subsets of RPy2 data frames using single or multiple criteria. I also wanted to test if using Github Gist is a better way the display code snippets on this blog than using Bloggers formatting tools.

And linked gist is here

How to remap Home/End key bindings in Mac OSX Mountain Lion

This is taken straight from here. Content is copied in case the link dies. I needed to redo the key bindings for Sublime after I upgraded the OS to 10.8. So here it is:

To get your Home and End keys working properly on Mac OS X (in my case, Mountain Lion, although this should work in prior versions back to at least Tiger), simply open the Terminal and do this:

$ cd ~/Library
$ mkdir KeyBindings
$ cd KeyBindings
$ nano DefaultKeyBinding.dict

Put these lines in that file, including the curly braces:

{
/* Remap Home / End keys to be correct */
"\UF729" = "moveToBeginningOfLine:"; /* Home */
"\UF72B" = "moveToEndOfLine:"; /* End */
"$\UF729" = "moveToBeginningOfLineAndModifySelection:"; /* Shift + Home */
"$\UF72B" = "moveToEndOfLineAndModifySelection:"; /* Shift + End */
"^\UF729" = "moveToBeginningOfDocument:"; /* Ctrl + Home */
"^\UF72B" = "moveToEndOfDocument:"; /* Ctrl + End */
"$^\UF729" = "moveToBeginningOfDocumentAndModifySelection:"; /* Shift + Ctrl + Home */
"$^\UF72B" = "moveToEndOfDocumentAndModifySelection:"; /* Shift + Ctrl + End */
}

Press Ctrl+O and then Enter to save the file, and Ctrl+X to exit. Restart your computer to have it take full effect.

Finding values in one table that are not in another

This site has a very good explanation of NOT IN, LEFT JOIN,where NULL, and NOT EXISTS statements for Finding values in one table that are not in another

Matching limited elements using Postgres IN clause

So you have two tables, one a sequence table holds unique sequence ids.  The other table hold sequence mutations and has a 1 to many relationship with the sequence table.  So for a given sequence id in the mutation table, there are many records, one for each mutation.

Now you have two sets of mutations you are interested in, a primary set A and a secondary set B.  You want to know how many sequences contain 1 and only 1 mutation from set A and 1 and only 1 from set B.


select count(*) from (
SELECT distinct a.seq_id
FROM sequence a
JOIN seq_mutations b ON b.seq_id = a.seq_id
JOIN seq_mutations c ON c.seq_id = a.seq_id
WHERE b.mutation IN ('mutA','mutB','mutC')
AND c.mutation IN ('mut1', 'mut2', 'mut3', 'mut4', 'mut5')
GROUP BY a.seq_id
HAVING 
  count(distinct b.mutation) = 1 
  AND count(distinct c.mutation) = 1) a

This query gives you exact, fine control over the conditions you want to test for.  If you then want to test for 1 and only 1 mutation in set A and 2 and only 2 mutations in set B, just change

count(distinct c.mutation) = 2

in the HAVING clause.  This also lets you use > and <, so you can ask for 3 or more mutations by using

count(distinct c.mutation) >= 3

You can also vary the count for b.mutation to adjust the number of mutations allowed from set A, so any combination can now be extracted.

Thanks to Jake for the code help.

Convert Postgres column from text to numeric type

ALTER TABLE foo ALTER COLUMN col TYPE NUMERIC USING col::numeric

You can use this with data in the table.  All data must be convertable (no alpha chars). You can also specify numeric formatting here if you like.