Is there a way where i can rearrange a dataframe and turn it into a 3 column dataframe

Question

I am having trouble figuring out how to move around my variables/data in order to achieve a 3 column data frame. the original data frame contains 200+ columns and I want move the data around so that it looks like the example i have shown below.

Do not have very much background as I am Not sure how to start to transform my dataframe.

Candy_Hierarchy <- tribble(~COUNTRY, ~candy1, ~candy2, ~candy3,
              'US, Canada, and UK', 1.6, 1, 0.8, 
              'United States',1.67, 1, 1,
              'Canada', 2, 0, 1,
              'United Kingdom', 1, 2, 0)

into

Candy_Hierarchy <- tribble(~Country, ~Candy, ~Average,
                 'US, Canada, and UK', 'candy1', 1.6,
                 'US, Canada, and UK', 'candy2', 1,
                 'US, Canada, and UK', 'candy3', 0.8,
                 'United States', 'candy1', 1.67,
                 'United States', 'candy2', 1,
                 'United States', 'candy3', 1,
                 'Canada', 'candy1', 2,
                 'Canada', 'candy2', 0,
                 'Canada', 'candy3', 1,
                 'United Kingdom', 'candy1', 1,
                 'United Kingdom', 'candy2', 2,
                 'United Kingdom', 'candy3', 0)

have not receive any error messages as i am not entirely sure how to achieve what i want to do.

score 1 · Answer 1 · answered Jun 18 '19 at 01:50

1

We can use gather.

library(tidyverse)

Candy_Hierarchy2 <- Candy_Hierarchy %>%
  gather(Candy, Average, -COUNTRY) %>%
  arrange(COUNTRY, Candy)
Candy_Hierarchy2
# # A tibble: 12 x 3
#   COUNTRY            Candy  Average
#   <chr>              <chr>    <dbl>
#  1 Canada             candy1    2   
#  2 Canada             candy2    0   
#  3 Canada             candy3    1   
#  4 United Kingdom     candy1    1   
#  5 United Kingdom     candy2    2   
#  6 United Kingdom     candy3    0   
#  7 United States      candy1    1.67
#  8 United States      candy2    1   
#  9 United States      candy3    1   
# 10 US, Canada, and UK candy1    1.6 
# 11 US, Canada, and UK candy2    1   
# 12 US, Canada, and UK candy3    0.8

answered Jun 18 '19 at 01:50

www

38,575
12
48
84

Thank you so much this makes a lot of sense to me now – Felix Chan Jun 18 '19 at 02:06
Thank you so much this makes a lot of sense to me now – Felix Chan Jun 18 '19 at 02:09
@FelixChan If this solves your question. Please accept it as the answer. Same as your last questions. – www Jun 18 '19 at 02:18
This does solve this question. however, I am still trying to create an a 'US', 'Canada', and 'UK' row that can be included in the data frame above. – Felix Chan Jun 18 '19 at 02:29
@FelixChan You said yourself. "This does solve this question." – www Jun 18 '19 at 05:10

Is there a way where i can rearrange a dataframe and turn it into a 3 column dataframe

1 Answers1