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
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!
CodePudding user response：
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