melt/cast two datasets for faceted scatterplot with R

user_5375295 0
Viewed 38

I am having a hard time for what should be a trivial task.

I'd like to make a faceted scatterplots with two datasets at 2 sites. I have a "standard" wide dataframe, proceeding from a merge and then I'd like to melt it in a smart way for a faceted scatterplot. But I am struggling.

## synthetic dataset
bb <- cbind.data.frame("date.hh" = seq.Date(as.Date("1900-01-01"), length.out = 10, by = "day"), 
"var_110.siteA" = rnorm(10), "var_110.siteB" = rnorm(10), "var_200.siteA" = rnorm(10), "var_200.siteB" = rnorm(10))

## one of my attempts:
bb %>%
    tidyr::pivot_longer(., cols = -c(date.hh)) %>% ## make it all long, but keep festa and five as separated columns
    tidyr::separate(., col = name, sep = "_", into = c("var", "prop")) %>%
    tidyr::separate(., col = prop, sep = "\\.", into = c("prop", "site"))

## this results:
# A tibble: 40 x 5
   date.hh    var   prop  site   value
   <date>     <chr> <chr> <chr>  <dbl>
 1 1900-01-01 var   110   siteA  0.353
 2 1900-01-01 var   110   siteB  0.521
 3 1900-01-01 var   200   siteA  0.258
 4 1900-01-01 var   200   siteB -0.261
 5 1900-01-02 var   110   siteA -0.802
 6 1900-01-02 var   110   siteB  0.631
 7 1900-01-02 var   200   siteA  0.620
 8 1900-01-02 var   200   siteB  0.875
 9 1900-01-03 var   110   siteA  0.150
10 1900-01-03 var   110   siteB  0.107
# … with 30 more rows

then I am stuck. I have fiddled with several pivot_wider, group_by etc with no luck.

I'd like a dataset as the following one, and it should not be so distant.

 siteA   siteB  prop    date.hh
 0.353   0.521  110    1900-01-01
 0.258  -0.261  200    1900-01-01
-0.802   0.631  110    1900-01-02
## etc...

Afterwards I can ggplot it using x = siteA, y = siteB and facet = prop.

I hope I made myself clear. Any help is appreciated.

Thx, AB

dplyr r
1 Answers

We may use pivot_longer with names_pattern to capture the characters in the column names - (\\d+) one or more digits after the var_ will be going into 'prop' column and the .value column takes the word (\\w+) at the end ($) of the column name strings

library(dplyr)
library(tidyr)
bb %>% 
  pivot_longer(cols = -date.hh, names_to = c("prop", ".value"), 
        names_pattern = "var_(\\d+)\\.(\\w+)$")

-output

# A tibble: 20 × 4
   date.hh    prop   siteA    siteB
   <date>     <chr>  <dbl>    <dbl>
 1 1900-01-01 110   -0.163  1.57   
 2 1900-01-01 200    0.254 -1.83   
 3 1900-01-02 110   -0.293  0.0374 
 4 1900-01-02 200    0.256 -0.173  
 5 1900-01-03 110   -1.33   1.35   
 6 1900-01-03 200   -0.930 -1.12   
 7 1900-01-04 110   -0.186  1.51   
 8 1900-01-04 200   -0.619 -2.01   
 9 1900-01-05 110    1.28  -0.202  
10 1900-01-05 200   -1.91   1.08   
11 1900-01-06 110    0.849  0.751  
12 1900-01-06 200    0.269  0.536  
13 1900-01-07 110   -1.49  -1.39   
14 1900-01-07 200    0.286 -1.42   
15 1900-01-08 110    0.363  0.869  
16 1900-01-08 200    0.146  0.00417
17 1900-01-09 110   -0.573 -0.0568 
18 1900-01-09 200   -0.548  0.990  
19 1900-01-10 110    0.673  0.0625 
20 1900-01-10 200   -0.497 -0.296
user_37322710
Related