[traits.build wide table] Import as_wide_table and make part of build

[dfalster]

The austraits package has a function as_wide_table.R, which converts the various tables into a single wide table (see https://github.com/traitecoevo/austraits/blob/master/R/as_wide_table.R). Suggest moving this in here and making it a possible output of the build.

Benefits

Wide table is better target for some for downloads, as

• single table is easier for most users
• simplifies austraits R package
• easier to write as a standard
• better for API

But large size

A downside of the large table is that file is substantially larger, e.g. for AusTraits v4.1.0

• current .csv output is 260Mb (with most 230Mb is traits.csv)
• by comparison, as plain csv wide table is 4.2GB

[dfalster]

As noted in #20 , we can drastically reduce file size by using alternative output types with better compression. Using AusTraits 4.2.1, the wide table is

• 4.2GB as .csv
• 72Mb as .csv.gz
• 26Mb as .parquet

austraits <- readRDS("export/as_wide/austraits_wide.rds")

austraits %>%
   select(-c("population_id", "individual_id", "temporal_id", "source_id", "location_id", "entity_context_id", "plot_id", "treatment_id", "method_id")) %>% write_csv("~/Downloads/austraits_wide.csv.gz")

However, if save as parquet format, data comes in at only 24Mb

austraits %>%
   select(-c("population_id", "individual_id", "temporal_id", "source_id", "location_id", "entity_context_id", "plot_id", "treatment_id", "method_id")) %>% arrow::write_parquet("export/as_wide/austraits_wide.parquet")

@cboettig suggested we should not see file size as deterrent to using wide table, if that is preferable for some users. We can solve this with smarter compression methods.

We can also filter before read, It's slower to read but saves RAM by only loading the set you want

x <- arrow::open_dataset("export/as_wide/austraits_wide.parquet")

x %>%
  filter(taxon_name == "Banksia serrata") %>%
  collect() -> y

can do this on csv too

x <- arrow::open_dataset("export/as_wide/austraits_wide.csv.gz", format = "csv")

[ehwenk]

There is a new function here that will build a single table that joins and packs all information from the relational tables into a single combined table.

Information for location properties and the various categories of context properties are packed into single columns.

I'd thought about writing a function that allowed unpacking, but am thinking that is a bad idea:

• even though I have used unique syntax that allows unpacking, it assumes that many symbols (including = and ;) are never used for location properties or context property names, values, or descriptions. This isn't realistic
• the only way to unpack the information is to use R - and the entire point of the combined table is to have a single joined output for non-R users.

Instead 2 alternatives. In addition to the combined table output, add

• function/output that is an unpacked combined table with many, many columns for location properties and context properties
• new function that lets users select which location properties and context properties they want to add, through a list of values.