Transposing/Reshaping Data Frame

General
1

Hi,

I have the following data.frame:

Date TOTAL Type AMT
Sept 18 3 A 0.5
Sept 18 3 B 0.25
Sept 18 3 C 0.75
Sept 18 3 D 1.5

I want to transpose it as such:
Date Total A B C D
Sept 18 3 0.5 0.25 0.75 1.5

Is there such a way to do this in R-Studio?

Thank you

2

Hi and welcome.

You can do it like this using the Tidyverse and the tidyr function spread:

# Here's your example data
example_data <- data.frame(Date = "Sept",
                           TOTAL = "18 3",
                           Type = LETTERS[1:4],
                           AMT = c(0.5, 0.25, 0.75, 1.5),
                           stringsAsFactors = FALSE)

# Load Tidyverse and "transpose" using tidyr:: spread
suppressPackageStartupMessages(library(tidyverse))
transposed_data <- example_data %>% 
  spread(key = "Type", value = "AMT")

#Print
print(transposed_data)
#>   Date TOTAL   A    B    C   D
#> 1 Sept  18 3 0.5 0.25 0.75 1.5
2 Likes
3

Hi agranholm,

thank you for your help, its actually Sept 18 (date) and total is 3. Apologies if it wasn't clear. I don't think it should make a difference.

But, when I try to apply it to my data (it is an excel import). It says Error in example_data %>% spread(key = "Type", value = "AMT"): could not find function "%>%"

4

This is one reason it's helpful to use the reprex package (short for reproducible example). It helps ensure we're working with/looking at the same stuff.

Right now the best way to install reprex is:

# install.packages("devtools")
devtools::install_github("tidyverse/reprex")

If you've never heard of a reprex before, you might want to start by reading the tidyverse.org help page. The reprex dos and don'ts are also useful.

What to do if you run into clipboard problems

If you run into problems with access to your clipboard, you can specify an outfile for the reprex, and then copy and paste the contents into the forum.

reprex::reprex(input = "fruits_stringdist.R", outfile = "fruits_stringdist.md")

For pointers specific to the community site, check out the reprex FAQ, linked to below.

5

Ahh, it actually worked, I had the wrong package installed. However, it seems to be that it didnt transpose the data correctly.

It seems to have duplicated the A/B/C/D and its corresponding numbers. Rather than consolidating it into 1 date.

@mara - any steps on how to accomplish this?

6

I'm not exactly sure what you're asking. Could you please include your code and output (i.e. a reprex)?

7

To clarify here is another example of my data set:

Date / Type / AMT / Total
Sept 1 / A / 1 / 11
Sept 1 / B / 2 / 11
Sept 1 / C / 3 / 11
Sept 1 / D / 5 / 11
Sept 15 / A / 3 / 10
Sept 15 / B / 3 / 10
Sept 15 / C / 4 / 10
Sept 20 / A / 3 / 12
Sept 20 / B / 2 / 12
Sept 20 / C / 7 / 12

I would like to transpose as follows:

Date / A / B / C / D / Total
Sept 1 / 1 / 2 / 3 / 5 / 11
Sept 15 / 3 / 3 / 4 / 0 / 10
Sept 20 / 3 / 2 / 7 / 0 / 12

Does that make sense?

The above code did not accomplish this task.

Thank you,

8

Update: argranholm's code worked. I had the data set-up wrong and figured it out. Many thanks!

1 Like