0

I've the following dataset

Pet Shop Item Price
A dog 300
A fish 20
A turtle 50
A dog 250
A cat 280
A rabbit 180
A cat 270
B dog 350
B fish 70
B cat 220
B turtle 80
B fish 55
B fish 75
C dog 280
C cat 260
C fish 65

The code for the data is as follows

Pet_Shop = c(rep("A",7), rep("B",6), rep("C",3))
Item = c("Dog","Fish","Turtle","Dog","Cat","Rabbit","Cat","Dog","Fish","Cat","Turtle","Fish","Fish","Dog","Cat","Fish")
Price = c(300,20,50,250,280,180,270,350,70,220,80,55,75,280,260,65)
Data = data.frame(Pet_Shop, Item, Price)

I'm trying to change the data into the following table

Output Dataframe

Does anyone know how to do this? I tried doing the following method using spread but it returns an error

Test = spread(Data, Item, Price)

Error: Each row of output must be identified by a unique combination of keys.
Keys are shared for 7 rows:
* 5, 7
* 1, 4
* 9, 12, 13

Thank you!

ekoam
  • 8,744
  • 1
  • 9
  • 22
Luther_Proton
  • 348
  • 1
  • 7

1 Answers1

1

This should get you what you're looking for. Note pivot_wider and pivot_longer are the new dplyr versions of spread and gather, respectively. The issue was that you needed to create a unique identifier row for each name before transforming.

new_data <- Data %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = Item, values_from = Price) %>%
  select(-row)

Output:

   Pet_Shop   Dog  Fish Turtle   Cat Rabbit
   <chr>    <dbl> <dbl>  <dbl> <dbl>  <dbl>
 1 A          300    NA     NA    NA     NA
 2 A           NA    20     NA    NA     NA
 3 A           NA    NA     50    NA     NA
 4 A          250    NA     NA    NA     NA
 5 A           NA    NA     NA   280     NA
 6 A           NA    NA     NA    NA    180
 7 A           NA    NA     NA   270     NA
 8 B          350    NA     NA    NA     NA
 9 B           NA    70     NA    NA     NA
10 B           NA    NA     NA   220     NA
11 B           NA    NA     80    NA     NA
12 B           NA    55     NA    NA     NA
13 B           NA    75     NA    NA     NA
14 C          280    NA     NA    NA     NA
15 C           NA    NA     NA   260     NA
16 C           NA    65     NA    NA     NA
Ian Noriega
  • 121
  • 5