Transform Data Frame Elements Into a Summary Table

Question

I have a data frame and I would like to create a table of all cell values, along with their row and column names. For example,

a <- c(1:4)
df <- matrix(a, nrow = 2, ncol = 2, byrow = T)
rownames(df) <- c("Paul", "Matt")
colnames(df) <- c("Beach", "Hike")
df <- as.data.frame(df)
df

I would like the output to be a data frame with the following columns:

Paul | 1 | Beach
Paul | 2 | Hike
Matt | 3 | Beach
Matt | 4 | Hike

I need to sort the numeric value for all combinations of rows and colums for a very large data set so if anyone could help me out that'd awesome :)

Thanks!

Also : `melt(df)` https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format could be helpful. — Ronak Shah, Feb 22 '18 at 05:09

Maurits Evers · Accepted Answer · 2018-02-22T04:55:51.580

1

If you don't care about the rownames of df you can use base R's stack:

stack(df);
#  values   ind
#1      1 Beach
#2      3 Beach
#3      2  Hike
#4      4  Hike

Or the tidyverse approach:

require(tidyverse);
df %>% 
    gather(key, values, 1:2) %>% 
    mutate(id = rep(rownames(df), ncol(df))) %>% 
    arrange(desc(id));
#    key values   id
#1 Beach      1 Paul
#2  Hike      2 Paul
#3 Beach      3 Matt
#4  Hike      4 Matt

edited Feb 22 '18 at 04:55

answered Feb 22 '18 at 04:50

Maurits Evers

49,617
4
47
68

1

Or just put the rownames back - `cbind(name=rownames(df), stack(df))` – thelatemail Feb 22 '18 at 05:01

score 0 · Answer 2 · answered Feb 22 '18 at 05:15

0

 library(tidyverse)
  df %>% 
  rownames_to_column(var="name") %>% 
  gather(key,value,-name) %>% 
  arrange(value)

  name   key value
1 Paul Beach     1
2 Paul  Hike     2
3 Matt Beach     3
4 Matt  Hike     4

answered Feb 22 '18 at 05:15

vsb

428
6
15

score 0 · Answer 3 · answered Feb 22 '18 at 05:48

res_df = data.frame()
for(x in rownames(df)){
   for(y in colnames(df)){
      res_df = rbind(res_df, as.data.frame(t(c(person=x, count=df[x,y], activity=y))))
   }
}


  person count activity
1   Paul     1    Beach
2   Paul     2     Hike
3   Matt     3    Beach
4   Matt     4     Hike

Transform Data Frame Elements Into a Summary Table

3 Answers3