Rinaldini registered the tree rings measurements as groups of lines, one line per section, one group for tree. Measurements are progressive, from bark (or underbark if missing), coded by modifying character format (bold for bark, red for the two measurements with pith -not measured- in between) Having used format attributes to code info, direct data extraction (say via CSV) is not viable since formatting would be lost. Google scripts have been developed in order to transfer original information in explicit tables: one listing sections data (section distance from tree base: s_height), the other with one line for each measurement, explicitly connecting it with: TreeId, row (hence section), col (hence year of growth), is-bold and is-red. Such raw tables have to be further processed to:
* directly connect to a meaningful SectionId
* distinguish measurements before and after pith
* explicitly connect each measurements with ‘year of growth’
* set bark apart
* compute ring widths

Connect to GS

(https://cran.r-project.org/web/packages/googlesheets/vignettes/basic-usage.html)

library(googlesheets)
suppressMessages(library(dplyr))
gsn <- "RotelleVer2"
gsurl <- "https://docs.google.com/spreadsheets/d/15ZLnI780oKr4QKuc4k4UAwZ6w6YmEE8tj4c2sU44xLQ/edit#gid=1996889235"
gs_ls(gsn)
## # A tibble: 1 × 10
##   sheet_title author  perm version             updated
##         <chr>  <chr> <chr>   <chr>              <dttm>
## 1 RotelleVer2 scotti    rw     old 2017-08-06 18:19:13
## # ... with 5 more variables: sheet_key <chr>, ws_feed <chr>,
## #   alternate <chr>, self <chr>, alt_key <chr>
Rv2 <- gs_url(gsurl)
Sects <- Rv2 %>%  gs_read(ws = "Sections", progress=F)
Diams <- Rv2 %>%  gs_read(ws = "Diameters", progress=F)

Verify basic characteristics

  1. Are the mesurements, within each section, progressive?
    (Non progressive measurements within each section are evidenced in the Google script pahase, and should already have been corrected before this pahase)
  2. Each section has exactly two consecutive ‘red’ measurements?
  3. All sections (‘row’) in Diams are present in Sects table?
library(sqldf, quietly=F)
cat(paste("A) n. of non-progressive measures:",nrow(sqldf("
  select A.TreeId, A.row, A.col, B.col, A.value, B.Value, B.value - A.Value inc0 from Diams A join Diams B on A.TreeId = B.TreeId and A.row = B.row and A.col +1 = B.col where inc0<=0 "))))
## A) n. of non-progressive measures: 0
cat(paste("B1) n. of sections without a 'red' couple:", nrow(sqldf("
  select TreeId, row, count(*) n_red from (select * from Diams where red) group by TreeId, row having n_red <> 2    "))))
## B1) n. of sections without a 'red' couple: 0
cat(paste("B2) n. of not consecutive 'red' couples:", nrow(sqldf("
  select TreeId, row, min(col) a_max, max(col) b_min, max(col)-min(col) d from (select * from Diams A where red) group by TreeId, row having d <> 1"))))
## B2) n. of not consecutive 'red' couples: 0
cat(paste("C) n. of 'sections Id' in Diam missing in Sects table:", nrow(sqldf("
  select * from (select distinct row from Diams) left natural join Sects where TreeId is NULL or s_height is NULL  "))))
## C) n. of 'sections Id' in Diam missing in Sects table: 0

Complete tables for sections and diameters

# In Sects: add numeric section height attribute
x <- as.numeric(Sects$s_height) 
Sects$s_height_n <- ifelse(is.na(x), 0, x) 

# In Sects: add tree level progressive SectionId
if (!('SectId' %in% names(Sects))) {
  Sects <- sqldf("select A.*, count(*) SectId from Sects A join Sects B on A.TreeId=B.TreeId and A.S_height_n >= B.s_height_n group by A.TreeId, A.row order by A.TreeId, A.S_height_n")
}

# In Diams: add SectId reference
if (!('SectId' %in% names(Diams))) {
  Diams <- sqldf("select A.*, SectId from Diams A left join Sects B using(row)")
}

# Pull out 'comments' from Diams
Sects_comments <- sqldf("select row, col, TreeId, SectId, comment from Diams where value is NULL")
Diams <- sqldf("select * from Diams where value is not NULL")

# In Diams: distinguish measurements before (a) and after (b) the pith
if (!('side' %in% names(Diams))) {
  Diams <- sqldf("select A.*, case when col <= a_max then 'a' else 'b' end side from Diams A natural join (select TreeId, SectId, min(col) a_max from (select * from Diams A where red) group by TreeId, SectId) B ")
}

# In Diams: add 'year of growth', select and reorder attributes
cutting_year <- 2017
if (!('year' %in% names(Diams))) {
  # col expresses the progression of measurements, to get the actual year 
  # consider following cases:
  # A1 - section starts with bold: 1st measuremen is under bark, year=cutting year
  # A2 - section starts without bold: 1st measuremen is under first ring, year=c.year-1
  # B - till pith (first red) year decreases
  # C - for 1st measurements after pith (2nd red) year = year of 'first red'
  # D - for successive measurements year increases
  
  # for side = 'a', year = cutting_year - (col - min_col)
  # if 1st meas. is bark, meas. includes cutting year growth, year = c.year
  # else, 1st meas. excludes cutting year, year = c.year -1
  tmp <- sqldf(" select TreeId, SectId, side, case when side='a' and not A.bold then A.col-1 else a.col end min_col from Diams A natural join (select TreeId, SectId, side, min(col) col from Diams group by TreeId, SectId, side) B ")
  tmp <- sqldf(" select A.*, col-min_col ry from Diams A natural join tmp B ")
  tmp[tmp$side=='a','year'] <- cutting_year - tmp[tmp$side=='a','ry']
  
  # process side = 'b'
  tmp <- sqldf("select A.*, min_year from tmp A natural left join (select TreeId, SectId, min(year) min_year from (select * from tmp where side='a') group by TreeId, SectId) B ")
  tmp[tmp$side=='b','year'] <- tmp[tmp$side=='b','min_year'] + tmp[tmp$side=='b','ry']
  tmp[tmp$side=='b' & tmp$bold,'year'] <- NA
  Diams <- sqldf("select row, col, TreeId, SectId, side, year, bold=1 bark, value from tmp")
}

Verify measurements internal coherence

## A) section above has more rings than the one below 
##    n. of non compatible couples of sections: 3
##   TreeId SectId nam SectId nam diff
## 1     32      2  30      3  31   -1
## 2     32      5  25      6  26   -1
## 3     33      4  31      5  32   -1
## B0) if 'TRUE' successive tests are unreliable: FALSE
## B1)  n. of 'implicitly complete sections (i.e. 'side b' has no bark and no 'comment'): 9
## B2)  n. of 'complete' sections: 157  - out of: 189
## B3)  List of sections signalled as NOT 'complete' (i.e. with 'comment) 
## 
##         that actually seem complete:
##   TreeId SectId abark nam nbm bbark nm comment complete max_b_year
## 1     14      1     1  48  49     0 98   rotta    FALSE       2017
## C1)  n. of 'complete' with matching sides: 17
## C2)  n. of 'complete' with NON matching sides: 140