how to transform/flatten R data frame

Question

I have a data frame with names for row identity and dates as column identity.

df <- data.frame(`20171007` = c(8, 4),
                 `20171014` = c(9, 7),
                 row.names = c("Kohl", "Travis"))

What I want is a data structure with three columns, where the identity is actually data.

Date (get from column identity)
Name (get from row identity)
Value (get from df[r,c])

In this particular case I would end up with 4 rows, one for each value. How do I go about flattening my df into that structure?

How to write a reproducible example. https://stackoverflow.com/a/5965451/3651529 — kangaroo_cliff, Oct 16 '17 at 21:25

acylam · Accepted Answer · 2017-10-16T22:39:31.263

You can do something like this with tidyverse:

library(tidyverse)

df %>%
  rownames_to_column(var = "Name") %>%      # tibble
  gather(Date, Value, -Name) %>%            # tidyr
  mutate(Date = as.Date(Date, "X%Y%m%d"))   # dplyr

Result:

    Name       Date Value
1   Kohl 2017-10-07     8
2 Travis 2017-10-07     4
3   Kohl 2017-10-14     9
4 Travis 2017-10-14     7

Data:

df = data.frame(`20171007` = c(8, 4),
                `20171014` = c(9, 7),
                row.names = c("Kohl", "Travis"))

andyczerwonka · Answer 2 · 2017-10-16T22:33:45.037

1

The simple one-liner is to transpose the df and then melt (from reshape2)

melt(t(df), value.name = "Value", varnames = c("Date", "Name"))

edited Oct 16 '17 at 22:33

answered Oct 16 '17 at 22:24

andyczerwonka

4,230
5
34
57

jacobsg · Answer 3 · 2017-10-17T14:54:20.447

0

The simplest thing to do is use something similar to below. One note of caution, it is not generally good practice to have a numeric as a column name. This will lead to compatibility issues with other functions.

df = data.frame(`20171007` = c(8,4), 
            `20171014` = c(9, 7))
row.names(df) = c('Kohl', 'Travis')

Create column with row names

df$names <- row.names(df)

Use the package tidyr to reshape data

EDIT - @useR

df %>% 
  gather(date, value, -names)

edited Oct 17 '17 at 14:54

answered Oct 16 '17 at 21:31

jacobsg

121
6

This doesn't give the desired result, since your `names` is also gathered. You need to exclude `name` from `gather` like `df %>% gather(date, value, -name)` from my answer – acylam Oct 16 '17 at 21:34
Right you are. Thanks for the information. I like your much better anyways (without the gaff). – jacobsg Oct 17 '17 at 14:53

how to transform/flatten R data frame

3 Answers3