Merging rows with different variables

Question

After using pivot_wider(), I have a data frame like this:

country	year	variable 1	variable 2
A	2000	0.5	NA
A	2000	NA	68
B	2000	NA	55
B	2000	0.9	NA

What is the easiest way to make it like this:

country	year	variable 1	variable 2
A	2000	0.5	68
B	2000	0.9	55

Thank you

Before pivot_wider(), the data frame was like this:

country	year	variables	values
A	2000	variable 1	0.5
A	2000	variable 2	68
B	2000	variable 1	0.9
B	2000	variable 2	55

Code:

data <- data %>% pivot_wider(names_from = variables, values_from = values)

Since that result is post-pivot_longer, that suggests the pivot was either done incorrectly or should not have been done in the first place. Please post the pre-pivot data and the code you used to get to this. — r2evans
– r2evans, Commented Jan 4, 2021 at 16:41
When I run your code on that new batch of data, I get your desired output. — r2evans
– r2evans, Commented Jan 4, 2021 at 16:51
See the answer below. You have to specify id columns in argument id_cols — AnilGoyal
– AnilGoyal, Commented Jan 4, 2021 at 16:57

AnilGoyal · Accepted Answer · 2021-01-04 16:50:58Z

1

Correct way for pivot_wider

data <- data %>% pivot_wider(id_cols = c(country, year), names_from = variables, values_from = values)

answered Jan 4, 2021 at 16:50

AnilGoyal

26.3k4 gold badges34 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1