How to melt a dataframe with tidyverse, and create a new column

I have pet survey data from 6 households. The households are split into levels (a,b).

I would like to melt the dataframe by aminal name (id.var), household (var.name), abundance (value.name), whilst adding a new column ("level") for the levels a&b.

My dataframe looks like this: pet abundance data

I can split it using reshape2:melt, but I don't know how to cut the a, b, from the column names and make a new column of them? Please help.

raw_data = as.dataframe(raw_data)

melt(raw_data,
     id.variable = 'Animal', variable.name = 'Site', value.name = 'Abundance')

Having a go on some simulated data, pivot_longer is your best bet:

library(tidyverse)

df <- tibble(
  Animal = c("dog", "cat", "fish", "horse"),
  `1a` = sample(1:10, 4),
  `1b` = sample(1:10, 4),
  `2a` = sample(1:10, 4),
  `2b` = sample(1:10, 4),
  `3a` = sample(1:10, 4),
  `3b` = sample(1:10, 4)
)


df |>
  pivot_longer(
    -Animal,
    names_to = c("Site", "level"),
    values_to = "Abundance",
    names_pattern = "(.)(.)"
  ) |> 
  arrange(Site, level)

#> # A tibble: 24 × 4
#>    Animal Site  level Abundance
#>    <chr>  <chr> <chr>     <int>
#>  1 dog    1     a             9
#>  2 cat    1     a             5
#>  3 fish   1     a             8
#>  4 horse  1     a             6
#>  5 dog    1     b             4
#>  6 cat    1     b             2
#>  7 fish   1     b             8
#>  8 horse  1     b            10
#>  9 dog    2     a             8
#> 10 cat    2     a             3
#> # … with 14 more rows