0

I am new in R and i have searched for similar problem and coulnd't find an answer.sorry if cross posting please refer to correct link please.

I have one 2 csv files. 1-Bm with 86 rows and 2 columns. 

BM <- read.csv('Z:/R_CGV/alB.csv', header = FALSE)

2nd - BG with 80 rows and 14 columns. 

BG <- read.csv('Z:/R_CGV/BG111.csv', header = FALSE)

I need to go through BM file and where (BM's 1st column content equlas to 12th col in BG file)BM[,1]=BG[i,1] want to extract 2nd colums contents. 

library(sqldf)
 BM <- read.csv('Z:/R_CGV/alB.csv', header = FALSE)    
 BG <- read.csv('Z:/R_CGV/BG111.csv', header = FALSE) 

len_BM <- length(BM)
 csNu <- BG[,12]
for (i in len_BM)


BnNam <- BM[i:len_BM,1]
{

    RetC_BN <- read.csv.sql("alB.csv", sql="select * from BM where csNu=BnNam")

}

It throughs me error:

Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such column: csNu)

I checked with keywords and tried to change names but nothing helps. What am I missing here?

example of BM (../alB.csv) file:

10  3    
23  4    
37  3    
# more lines
86  5

Example of BG (.../BG111.csv') file:

10  41.16   1   0.36   47   0.94    49  26.83   26.83   1 0.3249 0.63   49  26.83
100 40.62    0  0.11    55  0.95    107 33.62   42.25   0   0.117   109 0.64    107 33.62
# more lines
101 29.75   0   0.082   111 0.91    107 12.62   29.75   0   0.08    111 0.88    107 12.62

Many thanks, Mil'

mil
  • 301
  • 1
  • 2
  • 15
  • For one thing, I think you have some stuff outside of your `for` loop that you meant to put in it. – Señor O Jul 03 '13 at 14:59
  • This is not a MWE (minimum working example). We don't know how `alB.csv` looks like. – vaettchen Jul 03 '13 at 15:01
  • alB.csv is BM. File which has 86 rows and 2 columns. both columns contain numeric value. from 1 to 100. – mil Jul 03 '13 at 15:03
  • @SeñorO. Thanks I put csNu <- BG[i,12] BnNam <- BM[i,1] inside for loop if you meant that. but still have the error. – mil Jul 03 '13 at 15:06
  • But apparently none of the two columns in `alB.csv` is named `csNu` – vaettchen Jul 03 '13 at 15:08
  • My hunch is that your file name is not in your working directory, meaning you would want "Z:/R_CGV/alB.csv" instead of just "alB.csv". – Señor O Jul 03 '13 at 15:10
  • @vaettchen. Columns in files do not have names. I named it csNu in this script. i tried to assign all rows of 12th colums of file BG to csNu.. – mil Jul 03 '13 at 15:11
  • When you say `"select * from BM..."` are you referring to the object `BM` that you created in R? – Señor O Jul 03 '13 at 15:15
  • @SeñorO. Bm is csv file i'm reading into R. BM <- read.csv('Z:/R_CGV/alB.csv', header = FALSE) – mil Jul 03 '13 at 15:18
  • My question is referring to when you say `sql = "select * from BM where csNU=BnNam"` – Señor O Jul 03 '13 at 15:20
  • when I refer to BM here I refer to file..as i in :'select * from file where. – mil Jul 03 '13 at 15:21
  • Why don't you post a MWE, including workable mock data? With these fragments it will remain guesswork. – vaettchen Jul 03 '13 at 15:22
  • Ok, but `BM` is not a file, it's an R object. – Señor O Jul 03 '13 at 15:30
  • @vaettchen I have edited my question and posted whole piece of code i use and file data. – mil Jul 03 '13 at 15:33
  • At the end of this loop `for (i in len_BM) csNu <- BG[i,12]` csNu will have length == 1 and value == BG[80,12] – vaettchen Jul 03 '13 at 15:44
  • @vaettchen. Thank you. I'm bad with loops . Would this make more sense? len_BN <- length(BN) csNu <- BG[,12] for (i in len_BN){ BnNam <- BN[i:len_BN,1] RetC_BN <- read.csv.sql(...)} – mil Jul 04 '13 at 10:19

1 Answers1

1

I think what you want is 

BM[,2][BM[,1]==BG[,12]]
Señor O
  • 17,049
  • 2
  • 45
  • 47
  • This is what I want to get yes. I was trying to put this in my code:RetC_BN <- read.csv.sql("Z:/alB.csv", sql="select BN[,2] from BN where [BN[,1]==BG[,12]]")AndI get this: Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: near "[,12]": syntax error) In addition: Warning message: closing unused connection 3 (Z:/alB.csv). I keep getting either this error message or the previous one – mil Jul 04 '13 at 10:24
  • Why are you using sqlite at all? Don't you have the data you need in R already? – Señor O Jul 04 '13 at 18:28
  • hi, yes you are right. i understood that i can make it easier and not use sqldf. so working on it now. Thanks for all the help – mil Jul 05 '13 at 13:06