Thanks you guys, you are awesome.
-S


On Sun, Jul 20, 2014 at 5:36 PM, John McKown <john.archie.mckown@gmail.com>
wrote:

> On Sun, Jul 20, 2014 at 6:33 AM, Ubernerdy <ubernerdy@gmail.com> wrote:
> > Hello guys!
> >
> > I have been messing around with R for a while now, but this situation has
> > me a bit stumped. I was unable to solve it by reading documentation.
> >
> > So I have this table (currently in Excel - could export it as csv) with
> > values in 3 columns. Let's call them value P (for position), value B
> > (beginning) and E (end). P represents the position of a mutation in the
> > genome, B and E are the beginnings and ends of a nearby gene that either
> > contains the mutation or not.
> >
> > I am trying to compute the distance between the mutation and the gene.
> >
> > If the mutation is contained in the gene, that is value P is greater
> than B
> > and lesser than E, the result is 0.
> >
> > If the mutation is "left" of the gene, the distance is negative and is
> > equal to P-B.
> >
> > If the mutation is "right" of the gene, the distance is positive and is
> > equal to P-E.
> >
> > How would i achieve this in R?
> >
> > Regards and thanks, S.
> >
>
> Jim has given you the answer in R. So why am I "shooting off my
> mouth?". Well, good question! <grin/>
>
> But I thought it might be a bit helpful to mention a couple of things.
> First, you can do this same calculation in Excel by using the
> =IF(logical-expression,true-expression,false-expression) in another
> column on the spreadsheet. Just in case you wanted to do it in Excel
> in addition to, or instead of, R. And, like the ifelse() function in
> R, you can use a "nested" IF() within either, or both, of the value
> expressions of the =IF. Something like:
>
> =IF(P<B,P-B,IF(P>E,P-E,0))
>
> where "P", "B", and "E" are replaced by the proper cell names. Of
> course, the plus in R is that you can create the distance values "all
> at once" because R can work with vectors. Whereas in Excel, you'll
> need to put the =IF expression in every cell in the appropriate
> column. I mention it only for completeness, just in case you need the
> data in the spreadsheet also.
>
> Secondly, you mentioned exporting to a csv. I would _guess_ that you
> are using Windows, not a Mac nor Linux. If so, then built into Windows
> is an ODBC driver for reading (and writing) from (to) an Excel
> spreadsheet. Most users don't know much about ODBC, but it is there.
> You might be interested:
> http://www.cyberfella.co.uk/2012/06/11/windows7-odbc/
>
> http://msdn.microsoft.com/en-us/library/windows/desktop/dn259722%28v=vs.85%29.aspx
> I'm not where I can work my way through it because I'm at home on a
> Linux machine. But if you need some step-by-step, I'll be glad to
> _try_ on Monday when I'm back in the office. I just set up an ODBC for
> accessing MS-SQL and the process is similar with Excel. Or you might
> search YouTube, which is where I found out about this, albeit not
> using R. On http://Youtube.com try searching on "set up odbc
> connections excel" But I didn't really see too many which were
> relevant. Most were how to set up ODBC to allow Excel to read from
> some other database.
>
> Anyway, once this is done, you could then read the Excel data using
> the RODBC driver. Something like:
>
> library(RODBC);
> ch <- odbcConnect("Excel"); # The name of the ODBC connection you made
> above
> data <- sqlFetch(ch,"SheetName"); # read all the data in the Excel
> workbook in the tab named "SheetName"
> # please forgive any errors - not where I can test today
>
> Another possibility is to use an "R native" access via either openxlsx
> or XLConnect. I use openxlsx myself, not because it is "better", but
> because XLConnect uses Apache POI which means using Java which means
> it is not as fast. Well, at least in my assumptions XLConnect must be
> slower due to Java. I have not done any performance or reliability
> testing. I have done some functionality testing and both seem to work
> on my test Excel spreadsheet. In addition, these packages can also
> create or replace an Excel spreadsheet by rewriting the ???.xlsx file.
> Note this is not an "update in place" like with a real database.
> Honestly, for me, these latter are easier to understand.
>
> --
> There is nothing more pleasant than traveling and meeting new people!
> Genghis Khan
>
> Maranatha! <><
> John McKown
>

	[[alternative HTML version deleted]]

