Reshaping unusual data set [duplicate]

Question

I have run into an unusual data set I need to reshape but the normal reshape/tidyr packages don’t appear to have a way to solve it. While reshaping the dataset with subsetting and rbind is possible, there has to be a more straightforward way to solve this issue.

The data set appears like this:

ID  Item.1  Item.1.Value    Item.2  Item.2.Value    Item.3  Item.3.Value
001     A         3             C         7     
002     B         4             
003     A         2             B         1             F         5
004     C        10             L         3     

Each observation contains 1-3 measurements out of a collection of 20 measurements. Also, the same measurement type can appear in multiple columns across different observations.

I need to change it into this:

ID  Item    Item.Value
001  A      3
001  C      7
002  B      4
003  A      2
003  B      1
003  F      5
004  C      10
004  L      3

Part of my issue is I don’t know the conventional terminology for the configuration of the initial table.

Thanks!

Answer 1

I wouldn't call it an "unusual" data set, but the thing that adds an extra level of complexity is the fact that after the ID column, the remaining columns are all Item-Value pairs. Below are methods to reshape your data from "wide" to "long" format using base reshape and tidyverse functions.

For reproducibility, here's the data frame I started with:

df = structure(list(ID = c("001", "002", "003", "004"), Item.1 = structure(c(1L, 
2L, 1L, 3L), .Label = c("A", "B", "C"), class = "factor"), Item.1.Value = c(3L, 
4L, 2L, 10L), Item.2 = structure(c(3L, 1L, 2L, 4L), .Label = c("", 
"B", "C", "L"), class = "factor"), Item.2.Value = c(7L, NA, 1L, 
3L), Item.3 = c(NA, NA, "F", NA), Item.3.Value = c(NA, NA, 5L, 
NA)), .Names = c("ID", "Item.1", "Item.1.Value", "Item.2", "Item.2.Value", 
"Item.3", "Item.3.Value"), row.names = c(NA, -4L), class = "data.frame")

Base reshape method

dfr = reshape(df, varying=list(seq(2,ncol(df),2),seq(3,ncol(df),2)), direction="long", 
              idvar="ID", timevar=NULL, v.names=c("Item","Value"))
dfr = dfr[!is.na(dfr$Value),]
dfr = dfr[order(dfr$ID),]

dfr

       ID Item Value
001.1 001    A     3
001.2 001    C     7
002.1 002    B     4
003.1 003    A     2
003.2 003    B     1
003.3 003    F     5
004.1 004    C    10
004.2 004    L     3

tidyverse method

I'm not sure if this is the most succinct or elegant way to do it, so please let me know if you have a better way.

library(tidyverse)

dfr = map2_df(seq(2,ncol(df),2), seq(3,ncol(df),2), 
     ~ setNames(df[, c(1,.x,.y)], c("ID","Item","Value"))) %>%
  filter(!is.na(Value)) %>%
  arrange(ID)

   ID Item Value
1 001    A     3
2 001    C     7
3 002    B     4
4 003    A     2
5 003    B     1
6 003    F     5
7 004    C    10
8 004    L     3