Create a row with unique ID and add new variable with previous variable name in r

Question

I need to create a row with unique ID and add new variable with previous variable name , basically add the row number as an new row. Example data frame looks like this:

ID   V1  V2
1     2  0
2     0  4
3     1  5

How to make it look like this:

Unique_ID  ExVar  ExID
1            V1     1
2            V1     1
3            V2     2
4            V2     2
5            V2     2
6            V2     2
7            V1     3
8            V2     3
9            V2     3
10           V2     3
11           V2     3
12           V2     3

Many thanks.

Answer 1

Here is a tidyverse approach using tidyr::uncount(), which performs the opposite operation to dplyr::count(), duplicating rows according to a weighting variable.

dat |>
    tidyr::pivot_longer(-ID, names_to = "ExVar", values_to = "count") |>
    tidyr::uncount(count) |>
    dplyr::mutate(Unique_ID = dplyr::row_number(), .before = ID)

# # A tibble: 12 × 3
#    Unique_ID    ID ExVar
#        <int> <int> <chr>
#  1         1     1 V1
#  2         2     1 V1
#  3         3     2 V2
#  4         4     2 V2
#  5         5     2 V2
#  6         6     2 V2
#  7         7     3 V1
#  8         8     3 V2
#  9         9     3 V2
# 10        10     3 V2
# 11        11     3 V2
# 12        12     3 V2

Sample data

dat <- structure(list(ID = 1:3, V1 = c(2L, 0L, 1L), V2 = c(0L, 4L, 5L)), class = "data.frame", row.names = c(NA, -3L))