Include row and column titles as variables in a data set in R

Question

I have a set of data that has laboratory test type as row names and date as column name. I would like to transform this table so that every value in the name has a laboratory test and a date. For example:

             2017-01-01       2017-01-15
calcium      80               91
glucose      67               69
Neutrophils  3500             3812
Platlets     391653           370108
Lymphocytes  1839             2008

I would like to transform this table to the following:

calcium        2017-01-01    80
glucose        2017-01-01    67
neutrophils    2017-01-01    3500
platlets       2017-01-01    391653
lymphocytes    2017-01-01    1839
calcium        2017-01-15    91
glucose        2017-01-15    69
neutrophils    2017-01-15    3812 
platlets       2017-01-15    370108
lymphocytes    2017-01-15    2008

There are over 100 dates and over 50 tests, so this would take too long to do by hand. Does anyone know a function or a tool online that can do this?

google, `r reshape wide long format` – Andre Elrico Dec 05 '18 at 14:31 — Andre Elrico, Dec 05 '18 at 14:31

Dan · Answer 1 · 2018-12-05T14:38:32.240

Here's a tidyverse solution. First, I create the data frame.

# Create data frame
df <- read.table(text = "             2017-01-01       2017-01-15
calcium      80               91
                 glucose      67               69
                 Neutrophils  3500             3812
                 Platlets     391653           370108
                 Lymphocytes  1839             2008", header = TRUE, row.names = 1)

This introduces Xs in the column names, which I'll have to deal with later. I use rownames_to_column to turn rownames into a column, I gather the columns headed with dates to go from wide to long format, then fix the date format.

df %>% 
  rownames_to_column() %>% 
  gather(var, value, -rowname) %>% 
  mutate(var = gsub("X", "", var),
         var = gsub("\\.", "-", var))

#        rowname        var  value
# 1      calcium 2017-01-01     80
# 2      glucose 2017-01-01     67
# 3  Neutrophils 2017-01-01   3500
# 4     Platlets 2017-01-01 391653
# 5  Lymphocytes 2017-01-01   1839
# 6      calcium 2017-01-15     91
# 7      glucose 2017-01-15     69
# 8  Neutrophils 2017-01-15   3812
# 9     Platlets 2017-01-15 370108
# 10 Lymphocytes 2017-01-15   2008

Include row and column titles as variables in a data set in R

1 Answers1