I have a question regarding pivoting. If I have a dataset which I scrape from the web and only gave me all the data in column, how can I pivot the data to their specific column: example

The ideal output of the table would be:

CodePudding user response：

You can use matrix to reshape the vector. If needed this could be converted to a data.frame with as.data.frame. If needed the type of the columns could be converted by using type.convert.

. <- matrix(s, ncol=3, byrow=TRUE)
. <- setNames(as.data.frame(.[-1,]), .[1,])
type.convert(., as.is=TRUE)
#        Date  Number Additional Number
#1 2022-08-10 2,4,5,6                 7
#2 2022-08-11 2,4,5,3                11
#3 2022-08-12 2,4,5,8                14

Data

s <- c("Date","Number","Additional Number","2022-08-10","2,4,5,6","7",
       "2022-08-11","2,4,5,3","11","2022-08-12","2,4,5,8","14")

CodePudding user response：

I am putting a more "classic" solution to the problem below. This involves the modulo method which is useful to unpivot data like this.

library(tidyverse)
df <- tibble(x=letters)
df %>% 
  mutate(modulo=row_number()%%4, #pattern repeats every 4 entries so i put a 4
         id=row_number()) %>% # row id
  pivot_wider(names_from=modulo,values_from=x) %>% 
  fill(3:5,.direction="up") %>% #leave the first column and fill direction up
  rename(col=2,col1=3,col2=4,col3=5) %>% #renamed the cols
  filter(!is.na(col)) #remove empty from first column

CodePudding user response：

Here is another try in base R if your original data is a data frame with one column:

df <- as.data.frame(t(do.call(cbind, split(df, rep(1:(nrow(df) %/% 3), each = 3)))))
names(df) <- as.character(unlist(df[1,]))
df <- df[-1, ]
row.names(df) <- NULL
df

        Date  Number Additional Number
1 2022-08-10 2,4,5,6                 7
2 2022-08-11 2,4,5,3                11
3 2022-08-12 2,4,5,8                14