Search code examples
rtidyversefixed-widthreadr

Reading fixed width data: positioning row using a marker

I (should) have a fixed width data like this,

134265311
125255388
199265335

I can read the data like this,

first_ex <- readr::read_fwf("~/example_1.txt", fwf_widths(c(1, 2, 1, 2, 1, 2)))
first_ex
> first_ex
# A tibble: 3 x 6
     X1    X2    X3    X4    X5    X6
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1    34     2    65     3    11
2     1    25     2    55     3    88
3     1    99     2    65     3    35

The X1, X3, X5, i.e. the first digits (1, 2 and 3) of the original data are the markers for the next column.

Now, I have data like this, 

265311
125388
335

In the first row the datapoint with marker 1, second row datapoint with marker 2 and third row datapoint with market 1 and 2 are missing. I want to find a way to convert the data like the following one,

> first_ex1
# A tibble: 3 x 6
     X1 X2       X3 X4       X5    X6
  <dbl> <chr> <dbl> <chr> <dbl> <dbl>
1     1 00        2 65        3    11
2     1 25        2 00        3    88
3     1 00        2 00        3    35

Any help, suggestions would be greatly appreciated.

Addition

I am trying to implement @ suggestion in the following dataset, where l.group <- 16 and max.index <- 99. The index starts from 10 (2 digits instead of 1). 

values <- c("1300000190000148200000005000003099000002400001789800000050000030", 
          "1300000190000198290000003000001299000002200002109800000030000012",
          "130000064000011499000006400001149800000000000000",
          "1300000180000129330000003000002199000002100001509800000030000021", 
          "130000025000018099000002500001809800000000000000", 
          "13000001900000633100000020000002480000001000001699000002200000819800000030000018")

I am not having the output I want. E.g. if the column marker is 13, then the corresponding column number should be V25 and V26. But I see different in the output;

enter image description here

How to fit the code into my data perfectly?

Solution

  • How about this?

    library(stringi)
library(data.table)
library(magrittr)

values <- c(265311,
            125388,
            335)

# length of each group, for splitting up
l.group <- 3
# what is the maximum we go up to e.g. 1,2,3 at the moment
max.index <- 3
# NEW: number of digits, has to be same for all
digits <- 1

# make a template
grid <- as.data.table(matrix(sapply(1:max.index, function(x){c(x,0)}), nrow=1))

# split them up from one string
values.split <- trimws(gsub(sprintf("(.{%s})", l.group), "\\1 ", values)) %>%
  stringi::stri_split_regex(., "\\s")

# loop through, append to grid and combine
output <- lapply(values.split, function(x){
  # NEW: made it depend on the digits of index
  index <- as.integer(as.numeric(stringi::stri_sub(x, 1, digits))*2)  
  values <- as.numeric(stringi::stri_sub(x, (digits+1), nchar(x)))
  out <- copy(grid)
  for(i in seq_along(index)) set(out, j=index[i], value=values[i])
  out
}) %>% rbindlist(.)
output[]

    with the new e.g. try with digits <- 2