read, subset "select" and write txt file in R

Question

I want to select some portion from file1.txt and save that in another file named file2.txt. File1.txt:

abc 1 6 a
abc 2 7 b
efg 3 8 c
abc 4 9 d
efg 5 10 e

Database query that I want to apply here is (not syntax specific):

file2.txt <- select col2, col3, col4 from file1.txt where col1=abc

File2.txt:

1 6 a
2 7 b
4 9 d

Is there any way to apply database type query in R on a text file? I know that we can search a letter in .txt file by using grep() function. But I'm unable to find any online help for this? Does anyone know how can I resolve my problem? Thank you in advance :) Please don't mark this question duplicate to:

Searching for string within a text file in R

This question is something different. Moreover I cannot use sqldf because this package is not for .txt files.

Why doesn't the answer in your link work? `read.table("file1.txt")[grep("abc", readLines("file1.txt")), -1]`. — Rui Barradas, Jul 23 '18 at 11:23
@RuiBarradas I'm here talking about data base query, that will. I want to select those lines that contain only `abc`. — Knowledge Seeker, Jul 23 '18 at 11:27
please [google](https://www.r-bloggers.com/running-sql-queries-in-r-with-the-sqldf-package/) and come back with very specific questions/problems. — Andre Elrico, Jul 23 '18 at 11:30
Yeah, I know that package, but I want to apply this on a `.txt` file. `sqldf` is not for .txt files. — Knowledge Seeker, Jul 23 '18 at 11:32
@AndreElrico do you really think that I came here without googling? Alas. :/ — Knowledge Seeker, Jul 23 '18 at 11:33
Correct me if I'm wrong. You have a data set in a txt file and you would like to subset this data set to another txt file. Using a syntax similar to select. — A. Suliman, Jul 23 '18 at 11:36
Then I sugest to change the question title to **read, subset "select" and write txt file in R** and remove database from the title. You can mention you would like somthing like _select_ — A. Suliman, Jul 23 '18 at 11:41
Unfortunately, I do not have. But If I were in your situation, I would read the data set as txt into R, do the subset then exported as txt file. — A. Suliman, Jul 23 '18 at 11:51
If you want to work on the file directly, then why do you need R? Just use the terminal. — Rich Scriven, Jul 23 '18 at 11:52
Or use Excel if you are just interested in selecting specific rows. — A. Suliman, Jul 23 '18 at 11:55

score 1 · Answer 1 · answered Jul 23 '18 at 11:50

does below help answer to subset data from csvfile?

library(sqldf);
read.csv.sql(file, sql = "select * from file", header = TRUE, sep = ",")

Description Read a file into R filtering it with an sql statement. Only the filtered portion is processed by R so that files larger than R can otherwise handle can be accommodated.

hannes101 · Answer 2 · 2018-07-23T12:25:04.990

That should be all that's needed, mind that like() from the data.table package uses grepl internally, so I think regex might be an option as well.

library(data.table)
# Depending on the characteristics of the csv file this call has to be adjusted
dt <- data.table(read.csv("File1.txt", header = FALSE, sep = " "))
# or
dt <- fread("test.txt")
# data.table looks like this after import
dt <- structure(list(V1 = structure(c(1L, 1L, 2L, 1L, 2L)
      , .Label = c("abc", "efg")
      , class = "factor")
      , V2 = 1:5
      , V3 = 6:10
      , V4 = structure(1:5, .Label = c("a", "b", "c", "d", "e")
      , class = "factor")), row.names = c(NA, -5L)
      , class = c("data.table", "data.frame"))

write.csv(dt[like(V1, "abc"), .(V2
                      , V3
                      , V4
                      )],file = "File2.txt", row.names = FALSE)

score 1 · Accepted Answer · answered Jul 23 '18 at 23:40

Assuming the file reproducibly created in the Note at the end:

library(sqldf)

read.csv.sql("File1.txt", 
  "select V2, V3, V4 from file where V1 = 'abc'", header = FALSE, sep = " ")

giving:

Note

Lines <- "abc 1 6 a
abc 2 7 b
efg 3 8 c
abc 4 9 d
efg 5 10 e
"
cat(Lines, file = "File1.txt")

read, subset "select" and write txt file in R

3 Answers3

Note