CODEWITHSUNDEEP
rextract
David
David

Reputation: 524

How can I split data with strings and spaces into columns?

I am using the following code to read only the line which have the text 3 16 in the text file. 

data = readLines('data/1.txt')

word = paste(c("3", "16"), collapse = " ")
word

library(tidyverse)
data316 = grep(word, data, value = TRUE) %>%
  as.data.frame()

output looks like this.

    .
1   9.023200+4 2.300448+2          0          0          0          09040 3 16    1
2  -6.440430+6-6.440430+6          0          0          1         409040 3 16    2
3           40          2                                            9040 3 16    3
4   6.468430+6 0.000000+0 6.600000+6 1.842410-2 6.800000+6 7.644670-29040 3 16    4
5   7.000000+6 2.498850-1 7.200000+6 4.665000-1 7.400000+6 7.131000-19040 3 16    5
6   7.600000+6 9.584290-1 7.800000+6 1.185110+0 8.000000+6 1.376980+09040 3 16    6
7   8.500000+6 1.727940+0 9.000000+6 1.935280+0 9.500000+6 2.052600+09040 3 16    7
8   1.000000+7 2.117610+0 1.050000+7 2.161300+0 1.100000+7 2.186090+09040 3 16    8
9   1.150000+7 2.206630+0 1.200000+7 2.225060+0 1.250000+7 2.184630+09040 3 16    9
10  1.300000+7 2.057370+0 1.350000+7 1.813340+0 1.400000+7 1.537510+09040 3 16   10
11  1.450000+7 1.249570+0 1.500000+7 9.857581-1 1.600000+7 6.089410-19040 3 16   11
12  1.700000+7 4.387180-1 1.800000+7 3.558570-1 1.900000+7 3.203910-19040 3 16   12
13  2.000000+7 2.946310-1 2.100000+7 2.684380-1 2.200000+7 2.489610-19040 3 16   13
14  2.300000+7 2.335810-1 2.400000+7 2.203580-1 2.500000+7 2.085120-19040 3 16   14
15  2.600000+7 1.987450-1 2.700000+7 1.895320-1 2.800000+7 1.813270-19040 3 16   15
16  2.900000+7 1.747920-1 3.000000+7 1.689870-1 3.000000+7 0.000000+09040 3 16   16
17  2.000000+8 0.000000+0                                            9040 3 16   17

Now I need to split this into 10 columns. How to do this using regrex? 

data316 = grep(word, data, value = TRUE) %>%
  as.data.frame()%>%
  extract(.,., into = paste("Col",1:10,sep="_"),
           regex = "this need to be filled..")

I have tried to extract using regular expressions but failed.

Those 10 columns are explicit to decide. Any help???

Upvotes: 2

Views: 216

Answers (1)

jay.sf
jay.sf

Reputation: 72758

As in comments suggested we can use read.fwf() (i.e. apply the in section "Examples" of ?read.fwf shown procedure by linking and unlinking a tempfile) row-wise in a function with lapply() and bind them together. read.fwf() reads a table with fixed widths format, so we must tell it in a vector which widhts we want. Since you want 10 columns we nest this into a second function that makes rownames (numbers) as a first column.

fun1 <- function(y) {
  d <- do.call(rbind, lapply(y, function(x) {
    ff <- tempfile()
    cat(file=ff, as.character(x))
    return(read.fwf(ff, widths=c(rep(11, 5), 15, 2, 3, 5),  # vector of widths
                    colClasses="character"))  # character to achieve desired output
    unlink(ff)
  }))
  return(setNames(cbind(rownames(d), d), paste0("V", 1:10)))
}

Result

> fun1(data316)
   V1          V2          V3          V4          V5          V6              V7 V8  V9   V10
1   1  9.023200+4  2.300448+2           0           0           0           09040  3  16     1
2   2 -6.440430+6 -6.440430+6           0           0           1          409040  3  16     2
3   3          40           2                                                9040  3  16     3
4   4  6.468430+6  0.000000+0  6.600000+6  1.842410-2  6.800000+6  7.644670-29040  3  16     4
5   5  7.000000+6  2.498850-1  7.200000+6  4.665000-1  7.400000+6  7.131000-19040  3  16     5
6   6  7.600000+6  9.584290-1  7.800000+6  1.185110+0  8.000000+6  1.376980+09040  3  16     6
7   7  8.500000+6  1.727940+0  9.000000+6  1.935280+0  9.500000+6  2.052600+09040  3  16     7
8   8  1.000000+7  2.117610+0  1.050000+7  2.161300+0  1.100000+7  2.186090+09040  3  16     8
9   9  1.150000+7  2.206630+0  1.200000+7  2.225060+0  1.250000+7  2.184630+09040  3  16     9
10 10  1.300000+7  2.057370+0  1.350000+7  1.813340+0  1.400000+7  1.537510+09040  3  16    10
11 11  1.450000+7  1.249570+0  1.500000+7  9.857581-1  1.600000+7  6.089410-19040  3  16    11
12 12  1.700000+7  4.387180-1  1.800000+7  3.558570-1  1.900000+7  3.203910-19040  3  16    12
13 13  2.000000+7  2.946310-1  2.100000+7  2.684380-1  2.200000+7  2.489610-19040  3  16    13
14 14  2.300000+7  2.335810-1  2.400000+7  2.203580-1  2.500000+7  2.085120-19040  3  16    14
15 15  2.600000+7  1.987450-1  2.700000+7  1.895320-1  2.800000+7  1.813270-19040  3  16    15
16 16  2.900000+7  1.747920-1  3.000000+7  1.689870-1  3.000000+7  0.000000+09040  3  16    16
17 17  2.000000+8  0.000000+0                                                9040  3  16    17

However, the data are not yet numerical. (There also seemed to be a misinterpretation of the column with the "9040 "s.) Maybe you'll like it better this way. To achieve this, we could do the following. 

fun2 <- function(x) {
  d <- do.call(rbind, lapply(x, function(y) {
    ff <- tempfile()
    cat(file=ff, as.character(y), sep="\n")
    return(read.fwf(ff, widths=c(rep(11, 6), 4, 2, 3, 5), 
                    colClasses="character"))
    unlink(ff)
  }))
  # coerce subset of dataframe to numerics
  s <- d[, 1:6]
  s <- do.call(cbind, lapply(s, function(z) {
    # precede + and - with an e so that R can recognize number
    z <- gsub("\\+", "e\\+", z)
    z <- gsub("\\-", "e\\-", z)
    s <- as.numeric(z)
  }))
  return(cbind(s, d[, 7:10]))
}

Yielding

> fun2(data316)
          V1        V2       V3        V4       V5        V6   V7 V8  V9   V10
1  9.023e+04 2.300e+02 0.00e+00 0.000e+00 0.00e+00 0.000e+00 9040  3  16     1
2         NA        NA 0.00e+00 0.000e+00 1.00e+00 4.000e+01 9040  3  16     2
3  4.000e+01 2.000e+00       NA        NA       NA        NA 9040  3  16     3
4  6.468e+06 0.000e+00 6.60e+06 1.842e-02 6.80e+06 7.645e-02 9040  3  16     4
5  7.000e+06 2.499e-01 7.20e+06 4.665e-01 7.40e+06 7.131e-01 9040  3  16     5
6  7.600e+06 9.584e-01 7.80e+06 1.185e+00 8.00e+06 1.377e+00 9040  3  16     6
7  8.500e+06 1.728e+00 9.00e+06 1.935e+00 9.50e+06 2.053e+00 9040  3  16     7
8  1.000e+07 2.118e+00 1.05e+07 2.161e+00 1.10e+07 2.186e+00 9040  3  16     8
9  1.150e+07 2.207e+00 1.20e+07 2.225e+00 1.25e+07 2.185e+00 9040  3  16     9
10 1.300e+07 2.057e+00 1.35e+07 1.813e+00 1.40e+07 1.538e+00 9040  3  16    10
11 1.450e+07 1.250e+00 1.50e+07 9.858e-01 1.60e+07 6.089e-01 9040  3  16    11
12 1.700e+07 4.387e-01 1.80e+07 3.559e-01 1.90e+07 3.204e-01 9040  3  16    12
13 2.000e+07 2.946e-01 2.10e+07 2.684e-01 2.20e+07 2.490e-01 9040  3  16    13
14 2.300e+07 2.336e-01 2.40e+07 2.204e-01 2.50e+07 2.085e-01 9040  3  16    14
15 2.600e+07 1.987e-01 2.70e+07 1.895e-01 2.80e+07 1.813e-01 9040  3  16    15
16 2.900e+07 1.748e-01 3.00e+07 1.690e-01 3.00e+07 0.000e+00 9040  3  16    16
17 2.000e+08 0.000e+00       NA        NA       NA        NA 9040  3  16    17

Note: I ran options(scipen=-999, digits=4) before to achieve this scientific output (reset to defaults with options(scipen=0, digits=7)).

Data

data316 <- grep(paste(c("3", "16"), collapse=" "),
                readLines("1.txt"), value = TRUE)

Note: "1.txt" is linked in question.

Upvotes: 2

Related Questions