Vfrdtyky

I have a column of numbers in a csv file and I want to break the column at regular intervals and transpose them into multiple rows. For example:

Dummy input file:

10

25  

09

04

14

100

01

10

100

04

04

01

04

Expected output (Breaking at regular intervals of 3):

10 25 09 

04 14 100

01 10 100

04 04 01

04

I am trying to do this in R by using for loop but haven't succeeded. I am not getting the desired output but also there are more than 10 million points like these in a single column. So I am not sure if using loop is an efficient way. I have googled and seen other such queries on stackexchange like split string at regular intervals and How to split a string into substrings of a given length?. But it hasn't solved my problem.

Nevertheless, any help with this is appreciated.

Here's a dynamic tidyverse way. Should work for any breaks value.

set.seed(1)

df <- data_frame(x = sample(20, 10))



breaks <- 3



df %>% 

  mutate(

    id = rep(paste0("col", 1:breaks), length.out = nrow(.)),

    rn = ave(x, id, FUN = seq_along)

  ) %>% 

  spread(id, x) %>% 

  select(-rn)



# A tibble: 4 x 3

   col1  col2  col3

  <int> <int> <int>

1     6     8    11

2    16     4    14

3    15     9    19

4     1    NA    NA



# another example with breaks at 6

breaks <- 6



df %>% 

  mutate(

    id = rep(paste0("col", 1:breaks), length.out = nrow(.)),

    rn = ave(x, id, FUN = seq_along)

  ) %>% 

  spread(id, x) %>% 

  select(-rn)



# A tibble: 2 x 6

   col1  col2  col3  col4  col5  col6

  <int> <int> <int> <int> <int> <int>

1     6     8    11    16     4    14

2    15     9    19     1    NA    NA

Here is one base R option. We can pad your input vector/column with NA so that its length becomes a multiple of three. Then, generate index series for each of three columns, and create the desired data frame.

rem <- length(input) %% 3

input <- c(input, rep(NA, ifelse(rem == 0, 0, 3 - rem)))

idx1 <- seq(1, length(input), 3)

idx2 <- seq(2, length(input), 3)

idx3 <- seq(3, length(input), 3)



df <- data.frame(v1=input[idx1], v2=input[idx2], v3=input[idx3])

You can use cut function in dplyr package.

dataframe %>% group_by(column) %>% 

mutate(new_variable = cut(column, breaks=quantile(column, c(0,0.25,0.5,0.75,1), labels=F))

or

#breaks into the intervals you require 

new_variable <- cut(as.numeric(dataset$column),breaks = 3) 

And then use melt function in reshape package to transpose column to rows

If your data is in the form of a vector you can do the following:

data <- c('10', '25', '09', '04', '14', '100', '01',

          '10', '100', '04', '04', '01', '04')

split(data, ceiling(seq_along(data) / 3))

If it is in a data frame this should do it:

library(dplyr)

library(tidyr)

data <- data.frame(

  value = c('10', '25', '09', '04', '14', '100', '01',

        '10', '100', '04', '04', '01', '04'))

data %>%

  mutate(key = rep_len(c('a', 'b', 'c'), length.out = nrow(.))) %>%

  group_by(idx = as.integer((row_number() - 1) / 3)) %>% 

  spread(key, value) %>%

  select(-idx) %>%

  ungroup()