CODEWITHSUNDEEP
rdplyrtranspose
Megan Critchley
Megan Critchley

Reputation: 103

How to transpose dataframe by specific column in R

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!

Upvotes: 1

Views: 2963

Answers (2)

moodymudskipper
moodymudskipper

Reputation: 47350

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

Upvotes: 2

GegznaV
GegznaV

Reputation: 5600

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?

Upvotes: 0

Related Questions