CODEWITHSUNDEEP
rcsv
user6931363
user6931363

Reputation:

R - Converting one table to multiple table

I have a csv file called data.csv that contains 3 tables all merged in just one. I would like to separate them in 3 different data.frame when I import it to R. So far this is what I have got after running this code:

df <- read.csv("data.csv")
View(df)
Student 
Name    Score
Maria   18
Bob     25
Paul    27
Region  
Country Score
Italy   65
India   99
United  88
Sub region  
City    Score
Paris   77
New     55
Rio     78

How can I split them in such a way that I get this result:

First:

View(StudentDataFrame)
Name    Score
Maria   18
Bob     25
Paul    27

Second: 

View(regionDataFrame)
Country Score
Italy   65
India   99
United  88

Third:

View(SubRegionDataFrame)  
City    Score
Paris   77
New     55
Rio     78

Upvotes: 1

Views: 40

Answers (1)

akrun
akrun

Reputation: 887118

One option would be to read the dataset with readLines, create a grouping variable ('grp') based on the location of 'Student', 'Region', 'Sub region' in the 'lines', split it and read it with read.table

i1 <- trimws(lines) %in% c("Student", "Region", "Sub region")
grp <- cumsum(i1)
lst <- lapply(split(lines[!i1], grp[!i1]), function(x)
             read.table(text=x, stringsAsFactors=FALSE, header=TRUE))
lst
#$`1`
#   Name Score
#1 Maria    18
#2   Bob    25
#3  Paul    27

#$`2`
#  Country Score
#1   Italy    65
#2   India    99
#3  United    88

#$`3`
#   City Score
#1 Paris    77
#2   New    55
#3   Rio    78

data

lines <- readLines("data.csv")

Upvotes: 1

Related Questions