here's the head of my df:
> head(waste3)
# A tibble: 6 x 76
Period MaterialGroup council_name year `Mixed glass` `Mixed paper & … `Co mingled mat… `Green waste on…
<chr> <chr> <fct> <dbl> <chr> <chr> <chr> <chr>
1 Apr 0… Glass Adur 2006 243.39 NA NA NA
2 Apr 0… Paper & Card Adur 2006 NA 632.80999999999… NA NA
3 Apr 0… Co-mingled Adur 2006 NA NA 97.36 NA
4 Apr 0… NA Adur 2006 NA NA NA 24.68
5 Apr 0… Bulky Adur 2006 NA NA NA NA
6 Jul 0… Glass Adur 2006 251.51 NA NA NA
and here's the dput
> dput(head(waste3))
structure(list(Period = c("Apr 06 - Jun 06", "Apr 06 - Jun 06",
"Apr 06 - Jun 06", "Apr 06 - Jun 06", "Apr 06 - Jun 06", "Jul 06 - Sep 06"
), MaterialGroup = c("Glass", "Paper & Card", "Co-mingled", NA,
"Bulky", "Glass"), council_name = structure(c(1L, 1L, 1L, 1L,
1L, 1L), .Label = c("Adur", "Allerdale", "Alnwick", "Amber Valley",
"Arun", "Ashfield", "Ashford", "Aylesbury Vale", "Babergh", "Barking and Dagenham",
"Barnet", "Barnsley", "Barrow-in-Furness", "Basildon", "Basingstoke and Deane",
"Bassetlaw", "Bath and North East Somerset", "Bedford", "Berwick-upon-Tweed",
"Bexley", "Birmingham", "Blaby", "Blackburn with Darwen", "Blackpool",
"Blyth Valley", "Bolsover", "Bolton", "Boston", "Bournemouth",
"Bracknell Forest", "Bradford", "Braintree", "Breckland", "Brent",
"Brentwood", "Bridgnorth", "Brighton and Hove", "Bristol", "Broadland",
"Bromley", "Bromsgrove", "Broxbourne", "Broxtowe", "Burnley",
"Bury", "Calderdale", "Cambridge", "Camden", "Cannock Chase",
"Canterbury", "Caradon", "Carlisle", "Carrick", "Castle Morpeth",
"Castle Point", "Central Bedfordshire", "Charnwood", "Chelmsford",
"Cheltenham", "Cherwell", "Cheshire East", "Cheshire West and Chester",
"Chester", "Chester-Le-Street", "Chesterfield", "Chichester",
"Chiltern", "Chorley", "Christchurch", "Colchester", "Congleton",
"Conventry", "Copeland", "Corby", "Cornwall", "Cotswold", "Craven",
"Crawley", "Crewe and Nantwich", "Croydon", "Dacorum", "Darlington",
"Dartford", "Daventry", "Derby", "Derbyshire Dales", "Derwentside",
"Doncaster", "Dorset Waste Partnership", "Dover", "Dudley", "Durham",
"Ealing", "Easington", "East Cambridgeshire", "East Devon", "East Dorset",
"East Hampshire", "East Hertfordshire", "East Lindsey", "East Northamptonshire",
"East Riding of Yorkshire", "East Staffordshire", "Eastbourne",
"Eastleigh", "Eden", "Ellesmere Port and Neston", "Elmbridge",
"Enfield", "Epping Forest", "Epsom and Ewell", "Erewash", "Exeter",
"Fareham", "Fenland", "Folkestone and Hythe", "Forest Heath",
"Forest of Dean", "Fylde", "Gateshead", "Gedling", "Gloucester",
"Gosport", "Gravesham", "Great Yarmouth", "Greenwich", "Guildford",
"Hackney", "Halton", "Hambleton", "Hammersmith and Fulham", "Harborough",
"Haringey", "Harlow", "Harrogate", "Harrow", "Hart", "Hartlepool",
"Hastings", "Havant", "Havering", "Herefordshire", "Hertsmere",
"High Peak", "Hillingdon", "Hinckley and Bosworth", "Horsham",
"Hounslow", "Huntingdonshire", "Hyndburn", "Ipswich", "Isle of Wight",
"Isles of Scilly", "Islington", "Kennet", "Kerrier", "Kettering",
"Kings Lynn and West Norfolk", "Kingston-upon-Hull", "Kirklees",
"Knowsley", "Lambeth", "Lancaster", "Leeds", "Leicester", "Lewes",
"Lewisham", "Lichfield", "Lincoln", "Liverpool", "London", "Luton",
"Macclesfield", "Maidstone", "Maldon", "Malvern Hills", "Manchester",
"Mansfield", "Medway", "Melton", "Mendip", "Merton", "Mid Bedfordshire",
"Mid Devon", "Mid Suffolk", "Mid Sussex", "Middlesbrough", "Milton Keynes",
"Mole Valley", "New Forest", "Newark and Sherwood", "Newcastle-under-Lyme",
"Newcastle-upon-Tyne", "Newham", "North Cornwall", "North Devon",
"North Dorset", "North East Derbyshire", "North East Lincolnshire",
"North Hertfordshire", "North Kesteven", "North Lincolnshire",
"North Norfolk", "North Shropshire", "North Somerset", "North Tyneside",
"North Warwickshire", "North West Leicestershire", "North Wiltshire",
"Northampton", "Northumberland", "Norwich", "Nottingham", "Nuneaton and Bedworth",
"Oadby and Wigston", "Oldham", "Oswestry", "Oxford", "Pendle",
"Penwith", "Peterborough", "Plymouth", "Poole", "Portsmouth",
"Preston", "Purbeck", "Reading", "Redbridge", "Redcar and Cleveland",
"Redditch", "Reigate and Banstead", "Restormel", "Ribble Valley",
"Richmond upon Thames", "Richmondshire", "Rochdale", "Rochford",
"Rossendale", "Rother", "Rotherham", "Royal Borough of Kensington and Chelsea",
"Royal Borough of Kingston upon Thames", "Rugby", "Runnymede",
"Rushcliffe", "Rushmoor", "Rutland County", "Ryedale", "Salford",
"Salisbury", "Sandwell", "Scarborough", "Sedgefield", "Sedgemoor",
"Sefton", "Selby", "Sevenoaks", "Sheffield", "Shrewsbury and Atcham",
"Shropshire", "Slough", "Solihull", "South Bedfordshire", "South Bucks",
"South Cambridgeshire", "South Derbyshire", "South Gloucestershire",
"South Hams", "South Holland", "South Kesteven", "South Lakeland",
"South Norfolk", "South Northamptonshire", "South Oxfordshire",
"South Ribble", "South Shropshire", "South Somerset", "South Staffordshire",
"South Tyneside", "Southampton", "Southend-on-Sea", "Southwark",
"Spelthorne", "St Albans", "St Edmundsbury", "St Helens", "Stafford",
"Staffordshire Moorlands", "Stevenage", "Stockport", "Stockton-on-Tees",
"Stoke-on-Trent", "Stratford-on-Avon", "Stroud", "Suffolk Coastal",
"Sunderland", "Surrey Heath", "Sutton", "Swale", "Swindon", "Tameside",
"Tamworth", "Tandridge", "Taunton Deane", "Teesdale", "Teignbridge",
"Telford and Wrekin", "Tendring", "Test Valley", "Tewkesbury",
"Thanet", "Three Rivers", "Thurrock", "Tonbridge and Malling",
"Torbay", "Torridge", "Tower Hamlets", "Trafford", "Tunbridge Wells",
"Tynedale", "Uttlesford", "Vale of White Horse", "Vale Royal",
"Wakefield", "Walsall", "Waltham Forest", "Wandsworth", "Wansbeck",
"Warrington", "Warwick", "Watford", "Waveney", "Waverley", "Wealden",
"Wear Valley", "Wellingborough", "Welwyn Hatfield", "West Berkshire",
"West Devon", "West Dorset", "West Lancashire", "West Lindsey",
"West Oxfordshire", "West Somerset", "West Wiltshire", "Westminster",
"Weymouth and Portland", "Wigan", "Wiltshire", "Winchester",
"Windsor and Maidenhead", "Wirral", "Woking", "Wokingham", "Wolverhampton",
"Worcester", "Worthing", "Wychavon", "Wycombe", "Wyre", "Wyre Forest",
"York"), class = "factor"), year = c(2006, 2006, 2006, 2006,
2006, 2006), `Mixed glass` = c("243.39", NA, NA, NA, NA, "251.51"
), `Mixed paper & card` = c(NA, "632.80999999999995", NA, NA,
NA, NA), `Co mingled materials` = c(NA, NA, "97.36", NA, NA,
NA), `Green waste only` = c(NA, NA, NA, "24.68", NA, NA), `Fridges & Freezers` = c(NA,
NA, NA, NA, "2.76", NA), no.donors = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_),
`Green garden waste only` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `WEEE - Fridges & Freezers` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), Paper = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Other electrical goods` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Other White Goods` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Mixed cans` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Plastics = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `WEEE - Large Domestic App` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `WEEE - Small Domestic App` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Other Scrap metal` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Other materials` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Card = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Green glass` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `WEEE - TVs & Monitors` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Car tyres` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Other compostable waste` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Textiles & footwear` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Furniture = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Brown glass` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Clear glass` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Waste food only` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Textiles only` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Video tapes, DVDs and CDs` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Wood = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Rubble = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Fluorescent tubes` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Automotive batteries` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Mineral Oil` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `WEEE - Flourescent tubes and other light bulbs` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Mixed tyres` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Post consumer, non automotive batteries` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Vegetable Oil` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Wood for composting` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Mixed Plastic Bottles` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), Books = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Steel cans` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Aluminium cans` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Aluminium foil` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Paint = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `PET [1]` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `HDPE [2]` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `PVC [3]` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `LDPE [4]` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `PP [5]` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `PS [6]` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `OTHER PLASTICS [7]` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Mixed garden and food waste` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Chipboard and mdf` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Composite wood materials` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Van tyres` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Large vehicle tyres` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Soil = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Plasterboard = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Aerosols = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Bric-a-brac` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Composite food and beverage cartons` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Fire extinguishers` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Gas bottles` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Ink & toner cartridges` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Mattresses = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), `Yellow Pages` = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), Bicycles = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), `Footwear only` = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
), Carpets = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), `Absorbent Hygiene Products (AHP)` = c(NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_), Aggregates = c(NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_
)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
))
I want one row to be 1 pair of council_name/Period value. As can be seen each pair of council_name/Period is presented 5 times. What I want to do I can't quite get is to collapse each pair council_name/Period in only one row with the values of each variable (Mixed glass, Mixed paper, etc.) all in the same row as well, and not only one per row. SO basically to group it by council_name & period and have only one row with that and keep the values which are now in different rows for each column.
Basically, something like this:
> head(df.desired)
council_name period mixed.glass mixed.paper green.waste comingled.materials
1 Adur April 2006 243.39 632.81 24.68 97.36
2 Adur July 2006 251.51 540.00 33.00 99.10
3 Barnet April 2006 560.00 599.00 88.00 83.50
I tried the solutions here: R collapse multiple rows into 1 row - same columns and couldn't get it done.
Thanks!
