Why am I getting "repeat" rows with NAs using tidyr's spread function?

Question

I am trying to go from a dataframe with the following structure:

Date    State    Price.Name    Cost.Name   Price    Cost
Jan       AZ    firm1.price   firm1.cost    100       50
Jan       AZ    firm2.price   firm2.cost    200      100

to a dataframe that looks like:

Date    State    firm1.price    firm2.price    firm1.cost    firm2.cost
Jan        AZ            100            200            50          100

I have tried using spread from tidyr:

data=spread(data,Price.Name, Price)
data=spread(data,Cost.Name, Cost)

But this gives me the following dataframe:

Date    State    firm1.price    firm2.price    firm1.cost    firm2.cost
Jan        AZ            100            NA            50            NA
Jan        AZ             NA           200            NA           100

I have found another thread addressing the same issue here: tidyr spread function generates sparse matrix when compact vector expected, but I couldn't determine if a solution was reached. Someone suggested grouping by a dummy variable before using spread, but I have tried this to no avail.

I'm in the exact same situation, 4 years later. How has a satisfactory solution not been reached? — Sheridan Grant, Jul 03 '20 at 07:59

score 1 · Accepted Answer · answered Jan 25 '16 at 20:18

You could do price and cost separately and then merge (join) them (or cbind them, depending on the specifics of your data):

x <- read.table(text = "Date    State    Price.Name    Cost.Name   Price    Cost
+ Jan       AZ    firm1.price   firm1.cost    100       50
+                 Jan       AZ    firm2.price   firm2.cost    200      100",header = TRUE,sep = "")
> x %>% select(-Cost,-Cost.Name) %>% spread(Price.Name,Price)
  Date State firm1.price firm2.price
1  Jan    AZ         100         200
> x %>% select(-Price,-Price.Name) %>% spread(Cost.Name,Cost)
  Date State firm1.cost firm2.cost
1  Jan    AZ         50        100

this is what I ended up doing. I would have liked to do it all at once because my actual data has three "value" columns (price, cost, and margin), so the "spread then merge" method was a little messier. But this worked, so thank you! — Matt, Jan 25 '16 at 23:32

Why am I getting "repeat" rows with NAs using tidyr's spread function?

1 Answers1