[R] Meaning of error message when exporting to MS Excel
Hi, I’m attempting to export data (split into multiple files from one large dataset) from R to excel using the excel.link package. The code for export is as follows: for(i in practicesNN){ #Create relevant data for input #Separate out all parts of data – PracticeName is removed from example data for privacy reasons detailedH-dataExport2[dataExport1$PracticeName == i dataExport1$RISK_LEVEL == 'High',] detailedM-dataExport2[dataExport1$PracticeName == i dataExport1$RISK_LEVEL == 'Medium',] detailedL-dataExport2[dataExport1$PracticeName == i dataExport1$RISK_LEVEL == 'Low',] print(paste(i,2)) x-paste(i,.xls,sep=) #Open excel template xl.workbook.open(Template.xls) #Create practice specific file xl.workbook.save(x) #Activate detailed High risk sheet xl.sheet.activate(High Risk detailed) #Update detailed High risk spreadsheet xlrc[a1]-detailedH #Activate detailed Medium risk sheet xl.sheet.activate(Medium Risk detailed) #Update detailed Medium risk spreadsheet xlrc[a1]-detailedM #Activate detailed Low risk sheet xl.sheet.activate(Low Risk detailed) #Update detailed Low risk spreadsheet xlrc[a1]-detailedL #Save spreadsheet xl.workbook.save(x) #Close spreadsheet xl.workbook.close(x) } I attached an example file of data of the first 8 rows of the first xls. The columns of the spreadsheet are filled until it gets to column ‘HbA1c_mmol’ which produces the following error: Error in apply(r.obj[, iter], 1, paste, collapse = \t) : dim(X) must have a positive length I removed the offending column and the same occurs when column ‘BMI’ is encountered. Having searched for similar error message I have been unable to deduce the meaning of the error, particularly the ‘apply(r.obj[, iter], 1, paste, collapse = \t)’ part. Can anyone explain what the error message means and how to resolve it? Many thanks, Dove ExampleData.csv http://r.789695.n4.nabble.com/file/n4660378/ExampleData.csv -- View this message in context: http://r.789695.n4.nabble.com/Meaning-of-error-message-when-exporting-to-MS-Excel-tp4660378.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
Re: [R] Install of R-2.15.1 for Windows (64 bit) on application server
Thanks, will go ahead -- View this message in context: http://r.789695.n4.nabble.com/Install-of-R-2-15-1-for-Windows-64-bit-on-application-server-tp4644042p4644829.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
[R] Install of R-2.15.1 for Windows (64 bit) on application server
Hi, I'm looking to install ‘R’ on an application server which we can give plenty of resource to, and actually run the program from a terminal server. Do you know if this is possible, and if so are there any special installation instructions required? Many thanks, D -- View this message in context: http://r.789695.n4.nabble.com/Install-of-R-2-15-1-for-Windows-64-bit-on-application-server-tp4644042.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
[R] What does package 'RDCOMClient' is not installed for 'arch=x64' exactly mean?
Hi, I'm trying to use the excel.link package to write data to excel spreadsheets. I've installed the RDCOMClient package as required but get the error: package 'RDCOMClient' is not installed for 'arch=x64' I'm on Rx64 2.13.0. I assume it means the RDCOMClient package does not work on the x64 version. I've tried to see if there does a x64 RDCOMClient package version exist but to no available. Any suggestions? Cheers D -- View this message in context: http://r.789695.n4.nabble.com/What-does-package-RDCOMClient-is-not-installed-for-arch-x64-exactly-mean-tp4504469p4504469.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
[R] Converting strings into data frame column names
Hi all, I want to loop through a series of data frames and append them into one data frame, however I do not want all columns of the original data frames to be in the new data frame. I have the following code: phList-c('ph2010','ph2009','ph2008','ph2007','ph2006') #Name of original data frames for (i in phList) { x-c(paste(i,$ID,sep=),paste(i,$DATE_DISPENSED,sep=),paste(i,$TG_NAME1,sep=), paste(i,$TG_NAME2,sep=),paste(i,$TG_NAME3,sep=)) # I may be over complicating things here!? phNew-rbind(phNew,(data.frame(ID=get(x[1]), DATE_DISPENSED=get(x[2]), TG_NAME1= get(x[3]), TG_NAME2=get(x[4]), TG_NAME3=get(x[5] } The error i get is that the columns for extration are not recognised as objects which i understand. What i need resolving is how do i get around this so that each column desired is extracted from the original data frames? Is there a way to make the string (where my over complicating note is) of the column name into the column name as get() does not work as I have it. Your help is much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Converting-strings-into-data-frame-column-names-tp4325870p4325870.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
Re: [R] Extracting rows with latest date from a data frame
Thanks Petr, one line of code - awesome. -- View this message in context: http://r.789695.n4.nabble.com/Extracting-rows-with-latest-date-from-a-data-frame-tp4305651p4319303.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
[R] Extracting data from SQL Server based on R parameters
Hi, Can you extract data from a SQL server table based on parameters already in R? For example I have a list of unique patient IDs with latest hospitalisation date which I've processed in R. I need to join this data to other tables in SQL server. The tables in SQL server are too large to import without reducing the number of records. I do not have write access to the SQL server. mMPILatestHosp #this is my data frame of patients with unique IDs (ID) and most recent date (EVENDate) The select query is easy query-select * (plus columns in mMPILatestHosp) from Pharm2011 where#This is where I want to insert mMPILatestHosp$ID in the where clause I'm new to R so would appreciate your help. Cheers D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-data-from-SQL-Server-based-on-R-parameters-tp4319919p4319919.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
[R] Extracting rows with latest date from a data frame
Hi, I have a list of unique patient IDs which I want to run against a dataframe of unique patient IDs with dates. There are multiple date instances against unique IDs. The end result I wish is that I have a list of IDs (which are unique) with the latest date. I have begun with using test data (but I haven't got very far!) #Create test data #Data frame creation df1-data.frame(uID=c(1,2)) df2-data.frame(uID2=c(1,1,1,2,2,2),Date=c(as.Date(20/12/2010, format=%d/%m/%Y), as.Date(12/05/2011, format=%d/%m/%Y),as.Date(12/07/2011, format=%d/%m/%Y), as.Date(12/07/2010,format=%d/%m/%Y),as.Date(12/05/2009,format=%d/%m/%Y), as.Date(20/10/2012,format=%d/%m/%Y))) for (i in uID) { #For all the unique patient IDs df3-data.frame(i) #Create a one record data frame of record m1-merge(df3,df2,by.x=i, by.y=uID2) #Merge with list of IDs and dates of event for (j in m1) { #maybe an if statement in here but I’m not sure how to seperate the dates out and then create a new dataframe with the IDs and latest date?? } } I am completely new to R so R vocabulary is limited at the moment. I'm not sure if i'm on the right tracted here. For example I not sure if I need dataframe of uniqueIDs (df1). Is there a way in a For loop that you can pinpoint all date instances for a given ID and then select the latest and add to new dataframe? Your help is much appreciated. D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-rows-with-latest-date-from-a-data-frame-tp4305651p4305651.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.
Re: [R] Extracting Data from SQL Server
Thanks for you help guys, your suggestion Ajay worked fine, cheers Dyfed Thomas Population Health Analyst DDI: 07 858 5967 Mob: 021 409 800 Midlands Health Network WEL House 711 Victoria Street PO Box 983 Hamilton 3240 Phone: 07 839 2888 Fax: 07 834 9242 From: Ajay Askoolum [via R] [mailto:ml-node+s789695n4281558...@n4.nabble.com] Sent: Tuesday, 10 January 2012 10:40 p.m. To: Dyfed Thomas Subject: Re: Extracting Data from SQL Server try: SELECT a.UNIQUE_ID, a.diag01 from LoadPUS a left join CVD_ICD10 b on a.diag01 = b.[ICD-10 Codes] or a.diag02 = b.[ICD-10 Codes] or a.diag03 = b.[ICD-10 Codes] I am not sure why your table name CVD_ICD10 has a suffix $. From: Jeff Newmiller [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=0 To: dthomas [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=1; [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=2 Sent: Tuesday, 10 January 2012, 8:00 Subject: Re: [R] Extracting Data from SQL Server This is OT here. However, you might want to investigate the UNIQUE keyword in the SQL Server documentation for SELECT. --- Jeff NewmillerThe . . Go Live... DCN:[hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=3 Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/BatteriesO.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --- Sent from my phone. Please excuse my brevity. dthomas [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=4 wrote: Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=5 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. __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=6 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. [[alternative HTML version deleted]] __ [hidden email]/user/SendEmail.jtp?type=nodenode=4281558i=7 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. If you reply to this email, your message will be added to the discussion below: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281558.html To unsubscribe from Extracting Data from SQL Server, click herehttp://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=4281000code=ZHlmZWQudGhvbWFzQG1pZGxhbmRzaG4uaGVhbHRoLm56fDQyODEwMDB8NjI5Mzc3NzM2. NAMLhttp://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespacebreadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml # Please Read the following: This message is for the named person's use only
[R] Extracting Data from SQL Server
Hi, I am new to R (and rusty on SQL!) and I'm trying to extract records from a SQL server database. I have a table of patient records (LoadPUS) which have three code columns which i want to evaluate against a list of particular codes (CVD_ICD$ table). Given the size of the patient table I want to restrict the data I pull into R to the data I only want to analyse so I am using SQL to do this. The code i have is as follows: library(RODBC) channel-odbcConnect(NatCollections) query-SELECT UNIQUE_ID, diag01 from LoadPUS WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) This returns duplicate values, I don't want to hardcode the values because it is quite a long list. Running the IN function just for diag01 returns the correct number of records, however when combining with another IN function it doesn't return the correct number of records. Can you see where my SQL is incorrect or is there another way of doing this? Much appreciated, D -- View this message in context: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org 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.