Break a column at regular intervals into multiple rows

user_10660635 0
Viewed 691

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.

rows r split
4 Answers

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])
user_18632290

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
user_96599020

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

user_106608360

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()
user_23643320
Related