Thanks you guys, you are awesome. -S
On Sun, Jul 20, 2014 at 5:36 PM, John McKown <[email protected]> wrote: > On Sun, Jul 20, 2014 at 6:33 AM, Ubernerdy <[email protected]> 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]] ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
