Subsetting and transposing table iterating in R

Question

I have this table (inputdf):

Number	Value
1	0.2
1	0.3
1	0.4
2	0.2
2	0.7
3	0.1

and I want to obtain this (outputdf):

Number1	Number2	Number3
0.2	0.2	0.1
0.3	0.7	NA
0.4	NA	NA

I have tried it by iterating with a for loop through the numbers in column 1, then subsetting the dataframe by that number but I have troubles to append the result to an output dataframe:

inputdf <- read.table("input.txt", sep="\t", header = TRUE)
outputdf <- data.frame()

i=1
total=3 ###user has to modify it

for(i in seq(1:total)) {
  cat("Collecting values for number", i, "\n")
  values <- subset(input, Number == i, select=c(Value))
  cbind(outputdf, NewColumn= values, )
  names(outputdf)[names(outputdf) == "NewColumn"] <- paste0("Number", i)
}

Any help or hint will be very wellcomed. Thanks in advance!

Answer 1

In the tidyverse, you can create an id for each element of the groups and then use tidyr::pivot_wider:

library(tidyverse)
dat %>% 
  group_by(Number) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(names_from = Number, names_prefix = "Number", values_from = "Value")

# A tibble: 3 × 4
      n Number1 Number2 Number3
  <int>   <dbl>   <dbl>   <dbl>
1     1     0.2     0.2     0.1
2     2     0.3     0.7    NA  
3     3     0.4    NA      NA 

---

in base R, same idea. Create the id column and then reshape to wide:

transform(dat, id = with(dat, ave(rep(1, nrow(dat)), Number, FUN = seq_along))) |>
  reshape(direction = "wide", timevar = "Number")