R: row_number not working correctly with pivot_wider?

Question

I’m getting stumped on this one. I have a data frame that I’m trying to reshape into wide form using pivot_wider, but the results aren’t as expected.

My data is currently structured like this:

• One column for location (with multiple rows per location, corresponding to repeat surveys),
• One column for each of four species detection variables,
• One column for each of ten environmental covariates.

In total, it has 1,239 rows x 15 columns. What I want is to "count" the number of surveys at each location using row_number to ultimately produce a data frame that instead has:

• One row per location,
• One column for each species detection per survey (4 species x 20 surveys = 80 columns),
• One column for each covariate.

This should result in a data frame with 76 rows x 91 columns. However, when I attempt to do this, I end up with a data frame that has 1,239 rows x 160 columns. I suspect the issue might be with 'row_number' as it seems to struggle distinguishing between locations with similar names, causing extra rows to be counted for some sites. However, I haven't been able to consistently reproduce this. Any ideas as to what I am doing wrong?

Here is a (simpler) reproducible example:

library(tidyr)
library(dplyr)

#Sample data frame
df <- data.frame(
  Location = c("Site A", "Site A", "Site A", "Site A 1", "Site B", "Site B"),
  Species_1 = c(1,0,1,1,0,1),
  Species_2 = c(0,0,0,1,0,1),
  Species_3 = c(1,1,0,0,1,0),
  Cov_1 = c(10,10,10,20,15,15),
  Cov_2 = c(0.1,0.1,0.1,0.2,0.4,0.4)
)

#Group by location and convert data frame to long form
df_wide <- df %>%
  group_by(Location) %>%
  mutate(row = row_number()) %>%
  ungroup() %>%
  pivot_wider(
     names_from = row,
     values_from = c(Species_1, Species_2, Species_3),
     names_glue = "{.value}_{row}"
)

This creates a data frame with 6 rows x 12 columns instead of the expected 3 rows x 12 columns. Here is an abbreviated version of what I was hoping to get (without species 2 and 3):

Location	Species_1_1	Species_1_2	Species_1_3	Cov_1	Cov_2
Site A	1	0	1	10	0.1
Site A 1	1	N/A	N/A	20	0.2
Site B	0	1	N/A	15	0.4

Edit: I've already reviewed other entries such as this (How can I pivot a dataframe?) and they do not address the issue of row_number not working correctly with pivot_wider.