Putting several rows into one column in R

Try this:

library(tidyverse)

df <- read_csv("test.csv",col_names = FALSE)
df

# A tibble: 6 x 4
# X1        X2    X3    X4
# <chr>  <dbl> <dbl> <dbl>
# 1 Year     123   124   125
# 2 Number     1     2     3
# 3 Year     126   127   128
# 4 Number     4     5     6
# 5 Year     129   130   131
# 6 Number     7     8     9

# Removing first column and transpose it to get a dataframe of numbers
df_number <- as.data.frame(as.matrix(t(df[,-1])),row.names = FALSE)
df_number
#   V1 V2  V3 V4  V5 V6
# 1 123  1 126  4 129  7
# 2 124  2 127  5 130  8
# 3 125  3 128  6 131  9

# Keep the first two column (V1,V2) and assign column names
df_new <- df_number[1:2]
colnames(df_new) <- c("Year","Number")

# Iterate and rbind with subsequent columns (2 by 2) to df_new
for(i in 1:((ncol(df_number) - 2 )/2)) {
  df_mini <- df_number[(i*2+1):(i*2+2)]
  colnames(df_mini) <- c("Year","Number")
  df_new <- rbind(df_new,df_mini)
}
df_new
#    Year Number
# 1  123      1
# 2  124      2
# 3  125      3
# 4  126      4
# 5  127      5
# 6  128      6
# 7  129      7
# 8  130      8
# 9  131      9

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top