Combine columns that have the same row values but are spread out

Question

I want to combine these columns based on the value in "Date" so that there are only unique values of date with the corresponding age groups conglomerated. This was a result from using spread() in tidyr. If u look the values for Date are repeated

dput(dataframe) reads ....

structure(list(Date = c("201740", "201740", "201740", "201740", 
"201741", "201741", "201741", "201741", "201742", "201742", "201742", 
"201742", "201743", "201743", "201743", "201743", "201743", "201743", 
"201744", "201744", "201744", "201744", "201744", "201744", "201745", 
"201745", "201745", "201745", "201745", "201745", "201746", "201746", 
"201746", "201746", "201746", "201746", "201747", "201747", "201747", 
"201747", "201747", "201747", "201748", "201748", "201748", "201748", 
"201748", "201748", "201749", "201749", "201749", "201749", "201749", 
"201749", "201750", "201750", "201750", "201750", "201750", "201750", 
"201751", "201751", "201751", "201751", "201751", "201751", "201752", 
"201752", "201752", "201752", "201752", "201752", "201801", "201801", 
"201801", "201801", "201801", "201801", "201802", "201802", "201802", 
"201802", "201802", "201802", "201803", "201803", "201803", "201803", 
"201803", "201803", "201804", "201804", "201804", "201804", "201804", 
"201804", "201805"), `0-4 yr` = c(NA, 0.1, NA, NA, NA, 0.2, NA, 
NA, NA, 0.2, NA, NA, NA, NA, 0.3, NA, NA, NA, NA, NA, 0.6, NA, 
NA, NA, NA, NA, 0.7, NA, NA, NA, NA, NA, 1, NA, NA, NA, NA, NA, 
1.8, NA, NA, NA, NA, NA, 2.7, NA, NA, NA, NA, NA, 3.3, NA, NA, 
NA, NA, NA, 5.2, NA, NA, NA, NA, NA, 7.9, NA, NA, NA, NA, NA, 
13.7, NA, NA, NA, NA, NA, 18.3, NA, NA, NA, NA, NA, 23.3, NA, 
NA, NA, NA, NA, 28.2, NA, NA, NA, NA, NA, 35.6, NA, NA, NA, 41.9
), `18-49 yr` = c(NA, 0.1, NA, NA, 0.1, NA, NA, NA, NA, 0.2, 
NA, NA, NA, 0.2, NA, NA, NA, NA, NA, 0.4, NA, NA, NA, NA, NA, 
0.5, NA, NA, NA, NA, NA, 0.7, NA, NA, NA, NA, NA, 1, NA, NA, 
NA, NA, NA, 1.4, NA, NA, NA, NA, NA, 1.9, NA, NA, NA, NA, NA, 
2.7, NA, NA, NA, NA, NA, 4.2, NA, NA, NA, NA, NA, 6.6, NA, NA, 
NA, NA, NA, 9.3, NA, NA, NA, NA, NA, 12.5, NA, NA, NA, NA, NA, 
15.2, NA, NA, NA, NA, NA, 17.7, NA, NA, NA, NA, NA), `5-17 yr` = c(0, 
NA, NA, NA, 0.1, NA, NA, NA, 0.1, NA, NA, NA, 0.1, NA, NA, NA, 
NA, NA, 0.2, NA, NA, NA, NA, NA, 0.3, NA, NA, NA, NA, NA, 0.5, 
NA, NA, NA, NA, NA, 0.7, NA, NA, NA, NA, NA, 0.9, NA, NA, NA, 
NA, NA, 1.2, NA, NA, NA, NA, NA, 1.7, NA, NA, NA, NA, NA, 2.5, 
NA, NA, NA, NA, NA, 3.5, NA, NA, NA, NA, NA, 4.3, NA, NA, NA, 
NA, NA, 5.9, NA, NA, NA, NA, NA, 7.3, NA, NA, NA, NA, NA, 9, 
NA, NA, NA, NA, NA, NA), `50-64 yr` = c(NA, NA, 0.2, NA, NA, 
NA, 0.3, NA, NA, NA, 0.5, NA, NA, NA, NA, NA, 0.8, NA, NA, NA, 
NA, NA, 1.1, NA, NA, NA, NA, NA, 1.6, NA, NA, NA, NA, NA, 2.2, 
NA, NA, NA, NA, NA, 3.1, NA, NA, NA, NA, 4.1, NA, NA, NA, NA, 
NA, 5.4, NA, NA, NA, NA, NA, NA, 8.1, NA, NA, NA, NA, NA, 13.7, 
NA, NA, NA, NA, 21.7, NA, NA, NA, NA, NA, NA, 32.6, NA, NA, NA, 
NA, NA, 42.9, NA, NA, NA, NA, NA, 52, NA, NA, NA, NA, NA, 60.2, 
NA, NA), `65+ yr` = c(NA, NA, NA, 0.5, NA, NA, NA, 1, NA, NA, 
NA, 2.1, NA, NA, NA, NA, NA, 3, NA, NA, NA, NA, NA, 3.9, NA, 
NA, NA, NA, NA, 5.1, NA, NA, NA, NA, NA, 6.5, NA, NA, NA, NA, 
NA, 9.2, NA, NA, NA, NA, NA, 14.3, NA, NA, NA, NA, NA, 20.5, 
NA, NA, NA, NA, NA, 30.2, NA, NA, NA, NA, NA, 50.2, NA, NA, NA, 
NA, NA, 90.1, NA, NA, NA, NA, NA, 137.9, NA, NA, NA, NA, NA, 
179.5, NA, NA, NA, NA, NA, 217.4, NA, NA, NA, NA, NA, 251.8, 
NA)), .Names = c("Date", "0-4 yr", "18-49 yr", "5-17 yr", "50-64 yr", 
"65+ yr"), class = "data.frame", row.names = c(NA, 97L))

Combine in what way? Why did you spread to begin with? Can you post the original data you started with and then what you want your data to look like. DO NOT post an image, it is not helpful. Post your data as using dput(dataframe) — Amar
Code detailing how to create your data frame would be very helpful in additional to a data frame. I recommend reading this guide for producing a reproducible example for more information: stackoverflow.com/questions/5963269/… — MBorg
I used spread() because originally the age groups were all in one column and I wanted them in their own separate columns. After spreading it ended up with this weird situation — user8841306

zacdav zacdav · Accepted Answer · 2018-02-22T02:06:08

Could try aggregation, this could have been done before your spread. But after works as well

library(tidyverse)
dataframe %>%
    group_by(Date) %>%
    summarise_all(funs(sum(., na.rm = T)))

I've used sum() here because its not clear how you want to summarise.

A more suitable way might be:

dataframe %>%
    gather("age_group", "value", -Date) %>%
    filter(!is.na(value)) %>%
    spread(age_group, value)

Where we gather the data back to may have what been your original input, this needs to be filtered and then just re-spread

Combine columns that have the same row values but are spread out

1 Answers