How to transpose dataframe by specific column in R

Question

I am trying to wrangle some data for my masters thesis, and am struggling to restructure a data frame.

I have a data frame that looks like this table, with three variables

Basically I want to flip it on its side so that Sample goes along the top with two rows underneath for farm and year. I have tried these approaches so far (dataframe is called labnums):

labnums <- labnums %>%
  spread(sample, farm + year)

labnums <- xtabs(year + farm~sample, labnums)

however neither work. Does anyone have any idea how I can make it work? (Also sorry for having to use an image, I've never posted on here before)

Thanks!

Welcome to SO! Could you provide dput output of few rows (3-4) of your data ? And, how would the output looks like — YOLO, Jul 17 '18 at 20:01
For posting on SO, formatting can make a huge difference. You can read [editing help](https://stackoverflow.com/editing-help) and see that indenting your code is as straight-forward as highlighting the chunk and pressing Ctrl-K. Furthermore, realize that by posting an image and asking us to run code against your data, you are asking us to transcribe your image into usable data. There are a few good refs for how to improve question quality, such as: [reproducible questions](https://stackoverflow.com/questions/5963269/) and [SO mcve](https://stackoverflow.com/help/mcve). — r2evans, Jul 17 '18 at 20:10
maybe you're only looking for the `t` function ? `df <- as.data.frame(t(df)); df <- setNames(df,df[3,]); df <- df[-3,]` ? — moodymudskipper, Jul 17 '18 at 20:12

moodymudskipper · Answer 1 · 2018-11-14T10:47:53.867

df <- data.frame(farm=c("BADU003","BADU005"),
                 year= 2017:2018,
                 sample= c("Ralcyone.116","Ralcyone.24"),stringsAsFactors = F)

# transpose matrix
df <- as.data.frame(t(df),stringsAsFactors = FALSE)

# set the names as the values of the 3rd row
df <- setNames(df,df[3,])

# remove 3rd row
df <- df[-3,]

#      Ralcyone.116 Ralcyone.24
# farm      BADU003     BADU005
# year         2017        2018

with tidyverse:

library(tidyverse)
gather(df,key,value,-sample) %>% spread(sample,value) %>% column_to_rownames("key")
#      Ralcyone.116 Ralcyone.24
# farm      BADU003     BADU005
# year         2017        2018

I added a `tidyverse` version, it's cleaner code, but you need an additional package — moodymudskipper, Jul 17 '18 at 21:58

score 0 · Accepted Answer · answered Jul 17 '18 at 20:57

0

It would be nice if you showed an example, how the result should look like. My best guess now is this:

library(dplyr)

df <- data.frame(farm = c(3, 5, 5, 5), 
                 year = c(2017, 2018, 2017, 2017),
                 sample = c(116, 24, 88, 105))

t(df) 
#>        [,1] [,2] [,3] [,4]
#> farm      3    5    5    5
#> year   2017 2018 2017 2017
#> sample  116   24   88  105

df %>% t() %>% as.data.frame()
#>          V1   V2   V3   V4
#> farm      3    5    5    5
#> year   2017 2018 2017 2017
#> sample  116   24   88  105

Is it what you wanted?

answered Jul 17 '18 at 20:57

GegznaV

4,938
4
23
43

Thanks for the help, I have managed to produce that dataframe, but I was hoping to have that but with the samples as the names of the columns, Ralcyone.116, Ralcyone.24, Ralcyone.88 etc. – Megan Critchley Jul 17 '18 at 21:31
Then you should read the comments to your question because i answered that 1 hour ago ;) – moodymudskipper Jul 17 '18 at 21:34
Hi, I actually tried your code and it didnt work, it just made the table as above – Megan Critchley Jul 17 '18 at 21:41
see my answer below (I had to add a `stringsAsFactors=FALSE` for the names to be right) – moodymudskipper Jul 17 '18 at 21:43

How to transpose dataframe by specific column in R

2 Answers2