Reshape seleted columns based in a factor levels

September 21, 2022

In my bands_F data.frame:

# Open band file
bands_F <- read.csv("https://raw.githubusercontent.com/Leprechault/trash/main/bands.csv")
str(bands_F)
# 'data.frame': 2432 obs. of  10 variables:
#  $ STAND  : chr  "MA02ABOB7D" "MA02ABOB7D" "MA02ABOB7D" "MA02ABOB7D" ...
#  $ COMPANY: chr  "KLABIN" "KLABIN" "KLABIN" "KLABIN" ...
#  $ SPACING: chr  "3.3x1.8" "3.3x1.8" "3.3x1.8" "3.3x1.8" ...
#  $ SPECIES: chr  "EUCALYPTUS" "EUCALYPTUS" "EUCALYPTUS" "EUCALYPTUS" ...
#  $ AGE    : int  1 1 1 1 1 1 1 1 1 1 ...
#  $ STATS  : chr  "MEAN" "SD" "MIN" "MAX" ...
#  $ B2     : num  242.6 84.5 102 534 184.3 ...
#  $ B3     : num  359 138 115 867 332 ...
#  $ B4     : num  299 159 87 886 278 ...
#  $ B8     : num  2678 872 901 4721 2695 ...,

I’d like to create new columns for each STATS level ("MEAN","SD","MIN","MAX") and my desirable output is:

#  $ STAND  : chr  "MA02ABOB7D" "MA02ABOB7D" "MA02ABOB7D" "MA02ABOB7D" ...
#  $ COMPANY: chr  "KLABIN" "KLABIN" "KLABIN" "KLABIN" ...
#  $ SPACING: chr  "3.3x1.8" "3.3x1.8" "3.3x1.8" "3.3x1.8" ...
#  $ SPECIES: chr  "EUCALYPTUS" "EUCALYPTUS" "EUCALYPTUS" "EUCALYPTUS" ...
#  $ AGE    : int  1 1 1 1 1 1 1 1 1 1 ...
#  $ B2_MEAN     : num  242.6 ...
#  $ B3_MEAN     : num  359  ...
#  $ B4_MEAN     : num  299  ...
#  $ B8_MEAN     : num  2678  ...
# ...
#  $ B2_MAX     : num   534  ...
#  $ B3_MAX     : num   867  ...
#  $ B4_MAX    : num    886  ...
#  $ B8_MAX     : num   4721  ...

Please, any help it?

>Solution :

We may use pivot_wider after creating a sequence column

library(dplyr)
library(tidyr)
library(data.table)
bands_F %>% 
   mutate(rn = rowid(STATS)) %>%
   pivot_wider(names_from = STATS, values_from = c(B2, B3, B4, B8)) %>%
   select(-rn)

-output

# A tibble: 608 × 21
   STAND  COMPANY SPACING SPECIES   AGE B2_MEAN B2_SD B2_MIN B2_MAX B3_MEAN B3_SD B3_MIN B3_MAX B4_MEAN B4_SD B4_MIN B4_MAX B8_MEAN B8_SD
   <chr>  <chr>   <chr>   <chr>   <int>   <dbl> <dbl>  <dbl>  <dbl>   <dbl> <dbl>  <dbl>  <dbl>   <dbl> <dbl>  <dbl>  <dbl>   <dbl> <dbl>
 1 MA02A… KLABIN  3.3x1.8 EUCALY…     1   243.   84.5    102    534    359. 138.     115    867    299. 159.      87    886   2678.  872.
 2 MA02A… KLABIN  3.3x1.8 EUCALY…     1   184.   85.2     48    462    332. 147.      77    824    278. 167.      56    934   2695.  881.
 3 MA02A… KLABIN  3.3x1.8 EUCALY…     1   256.   80.8    104    538    384. 131.     144    903    324. 153.     118    914   2546.  734.
 4 MA02A… KLABIN  3.85x1… EUCALY…     1   142.   38.4     75    543    267.  58.1    136    765    151.  79.1     75   1006   3636.  764.
 5 MA02A… KLABIN  3.85x1… EUCALY…     1    99.4  38.9     31    452    236.  57.2    111    678    138.  74.9     65    871   3858.  875.
 6 MA02A… KLABIN  3.85x1… EUCALY…     1   111.   35.4     30    382    234.  52.9    106    606    144.  61.4     56    686   3654.  861.
 7 MA02A… KLABIN  3.85x1… EUCALY…     1    78.2  35.8      1    336    196.  48.0     50    519    128.  62.4     33    613   3531.  820.
 8 MA02A… KLABIN  3.85x1… EUCALY…     1   133.   44.6     58    388    246.  58.0    109    585    179.  97.1     74    807   3460.  887.
 9 MA02A… KLABIN  3.85x1… EUCALY…     1   179.   44.1     94    439    286.  55.6    154    656    218.  97.6    106    905   3338.  832.
10 MA02A… KLABIN  3.85x1… EUCALY…     1   163.   51.7     65    453    338.  64.3    190    761    302. 109.     152   1052   3111.  685.
# … with 598 more rows, and 2 more variables: B8_MIN <dbl>, B8_MAX <dbl>