Raw data has column labels spread across two rows

user_5074751 0
Viewed 30

I have a problem with the humans here; they're giving me Citizen Science data in spreadsheets formatted to be attractive and legible. I figured out the right sequence of pivots _longer and _wider to get it into an analyzable format but first I had to do a whole bunch of hand edits to make the column labels usable. I've just been given a corrected spreadsheet so now I have to do the same hand edits all over. Can I avoid this?

reprex <- read_csv("reprex.csv", col_names = FALSE)

gives:

  X1    X2    X3    X4    X5    X6    X7    X8    X9    X10  
1 NA    NA    2014  NA    NA    2015  NA    NA    2016  NA   
2 NA    Total F     M     Total F     M     Total F     M    
3 SiteA 180   92    88    134   40    94    34    20    14   
4 SiteB NA    NA    NA    247   143   104   8     8     0    
5 SiteC 237   194   43    220   95    125   62    45    17

I want column labels like "2014 Total", "2014 F", ... like so:

  Location `2014 Total` `2014 F` `2014 M` `2015 Total` `2015 F` `2015 M` `2016 Total` `2016 F` `2016 M`
1 SiteA             180       92       88          134       40       94           34       20       14
2 SiteB              NA       NA       NA          247      143      104            8        8        0
3 SiteC             237      194       43          220       95      125           62       45       17

...which would allow me to twist it up until I get to something like:

  Location date  Total     F     M
1 SiteA    2014    180    92    88
2 SiteB    2014     NA    NA    NA
3 SiteC    2014    237   194    43
4 SiteA    2015    134    40    94
5 SiteB    2015    247   143   104
6 SiteC    2015    220    95   125
7 SiteA    2016     34    20    14
8 SiteB    2016      8     8     0
9 SiteC    2016     62    45    17

The part from the second table to the third I've got; the problem is in how to get from the first table to the second. It would seem like you could pivot the first and then fill in the missing dates with fill(.direction="updown") except that the dates are the grouping value you need to be following.

tidyverse r format
1 Answers

For this example we could do like this:

library(tidyverse)

df_helper <- df %>% 
  slice(1:2) %>% 
  pivot_longer(cols= everything()) %>% 
  fill(value, .direction = "up") %>% 
  mutate(x = lead(value, 11)) %>% 
  drop_na() %>% 
  unite("name", c(value, x), sep = " ", remove = FALSE) %>% 
  pivot_wider(names_from = name) 
         

df %>% 
  setNames(names(df_helper)) %>% 
  rename(Location = x) %>% 
  slice(-c(1:2))


 Location 2014 Total 2014 F 2014 M 2015 Total 2015 F 2015 M 2016 Total 2016 F 2016 M
3    SiteA        180     92     88        134     40     94         34     20     14
4    SiteB       <NA>   <NA>   <NA>        247    143    104          8      8      0
5    SiteC        237    194     43        220     95    125         62     45     17
user_133216470
Related