CODEWITHSUNDEEP
r
Raghu
Raghu

Reputation: 57

Splitting a dataframe based on rows in R

I have a table in a PDF file. I converted it into CSV and here is the CSV content.

A   23  45  53  34
    62  87  94  75

B   120 61  113 41
    109 48  90  95
    123 113 112 101

I loaded the CSV into R, but the dataframe is not as expected.

A   23  45  53  34  62  87  94  75

B   120 61  113 41  109 48  90  95  123 113 112 101

How can I split the rows in a dataframe. Any help is appreciated.

At the end, I am trying to acheive something like:

A1  23  45  53  34
A2  62  87  94  75

B1  120 61  113 41
B2  109 48  90  95
B3  123 113 112 101

Thanks a lot

Upvotes: 0

Views: 163

Answers (3)

Henrik
Henrik

Reputation: 67828

Another possibility:

# read data
df <- read.table(text = "A   23  45  53  34  62  87  94  75
B   120 61  113 41  109 48  90  95  123 113 112 101", fill = TRUE)

df
#   V1  V2 V3  V4 V5  V6 V7 V8 V9 V10 V11 V12 V13
# 1  A  23 45  53 34  62 87 94 75  NA  NA  NA  NA
# 2  B 120 61 113 41 109 48 90 95 123 113 112 101

# from df, remove first column
# then, make a four-column matrix of the non-NA values in each row
l <- apply(df[ , -1], 1, function(x) matrix(na.omit(x), ncol = 4, byrow = TRUE))
l
# [[1]]
#      [,1] [,2] [,3] [,4]
# [1,]   23   45   53   34
# [2,]   62   87   94   75
# 
# [[2]]
#      [,1] [,2] [,3] [,4]
# [1,]  120   61  113   41
# [2,]  109   48   90   95
# [3,]  123  113  112  101

# add id column
lapply(seq_along(l), function(x){
  cbind.data.frame(id = paste0(df$V1[x], seq(from = 1, to = nrow(l[[x]]))), l[[x]])
})

# [[1]]
#   id  1  2  3  4
# 1 A1 23 45 53 34
# 2 A2 62 87 94 75
# 
# [[2]]
#   id   1   2   3   4
# 1 B1 120  61 113  41
# 2 B2 109  48  90  95
# 3 B3 123 113 112 101

Upvotes: 1

Renald
Renald

Reputation: 114

Would this work?

x <-read.csv("your-file.csv", header=F)

h=vector("character",nrow(x)) 
n=1; p=NA 
for (i in 1:nrow(x)) { 
  if (""==x$V1[i]) { n=n+1; z = p} else { n = 1; z = p = x$V1[i] }
  h[i] = paste0(z,n)  
}

x$V1 <- h

Probably there are better solutions, without a for-loop, but this is the quickest I could devise...

Upvotes: 1

hrbrmstr
hrbrmstr

Reputation: 78842

This might work but you really haven't provided enough information to know for sure.

dat <- readLines("thefile.csv")

prev.x <- ""
ctr <- 1

df <- ldply(strsplit(dat, "\ +"), function(x) { 

  if (nchar(x[1]) == 0) {
    x[1] <- prev.x
    ctr <- ctr + 1
  } else {
    prev.x <<- x[1]
    ctr <- 1
  }  

  x[1] <- sprintf("%s%d", x[1], ctr)

  return(as.data.frame(matrix(x, nrow=1)))

})

colnames(df) <- c("ltr", "v1", "v2", "v3", "V4")

df <- df[complete.cases(df),]
df

Upvotes: 1

Related Questions