Pivot data frame without Null

Question

I have looked to all similar question of stackoverflow but without success.

Consider the iris data set. I just want to put the species as columns, and get below each species names, only the Sepal Length.

So far I get:

library("reshape2")
data(iris)
iris$ID <- 1:nrow(iris)
acast(iris,ID~Species,value.var="Sepal.Length")

The result is almost what I expected... Excepted all the NA that shouldn't be here (each column values should be shifted up to that we get only 50 rows for the whole data frame).

Answer 1

Here are couple of options -

library(dplyr)
library(tidyr)

iris %>%
  select(Species, Sepal.Length) %>%
  group_by(Species) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = Species, values_from = Sepal.Length) %>%
  select(-row)

#   setosa versicolor virginica
#    <dbl>      <dbl>     <dbl>
# 1    5.1        7         6.3
# 2    4.9        6.4       5.8
# 3    4.7        6.9       7.1
# 4    4.6        5.5       6.3
# 5    5          6.5       6.5
# 6    5.4        5.7       7.6
# 7    4.6        6.3       4.9
# 8    5          4.9       7.3
# 9    4.4        6.6       6.7
#10    4.9        5.2       7.2
# … with 40 more rows

data.table -

library(data.table)

df <- iris

dcast(setDT(df), rowid(Species)~Species, value.var = "Sepal.Length")