pivot_wider introduces NA values when reshaping from long to wide format [duplicate]

Question

I am encountering a problem when I reshape my data using pivot_wider().

My data looks like this:

df <- data.frame(area = c( "Area1","Area1","Area1","Area2","Area2","Area2","Area3","Area3","Area3"),
                   species = c("species1","species2","species3","species1","species2","species3","species1","species2","species3"),
                   season= c("Season1","Season1","Season1","Season2","Season2","Season2","Season3","Season3","Season3"),
                   value= c(2,3,5,7,9,2,6,9,3))

I am able to change the data frame to the wide format as below.

df_wide <- df %>%
  mutate(row = row_number()) %>%
  pivot_wider(id_cols= c(row,species),
              ,names_from = "season",
              values_from = "value") %>%
  select(-row)

this is the figure of output.

My problem is that it introduces NAs because pivot_wider() makes a new column for each value.

If you help me, I would be great...

Answer 1

library(tidyr)

pivot_wider(df, id_cols = species, names_from = season, values_from = value)

#   species  Season1 Season2 Season3
#   <chr>      <dbl>   <dbl>   <dbl>
# 1 species1       2       7       6
# 2 species2       3       9       9
# 3 species3       5       2       3

Or base R:

cbind(species = unique(df$species), unstack(df, value ~ season))

#    species Season1 Season2 Season3
# 1 species1       2       7       6
# 2 species2       3       9       9
# 3 species3       5       2       3

Answer 2

Try this. You want a wide dataframe so the issue is that row numbers and area are creating additional rows that can mess the expected output. One way to solve the issue can be:

library(dplyr)
library(tidyr)
#Code
newdf <- df %>% select(-area) %>% pivot_wider(names_from = season,values_from=value)

Output:

# A tibble: 3 x 4
  species  Season1 Season2 Season3
  <fct>      <dbl>   <dbl>   <dbl>
1 species1       2       7       6
2 species2       3       9       9
3 species3       5       2       3