Adding "Import from ClimMob" for Tricot Data Analysis

[lilyclements]

This is related to a dialog to "Import from ClimMob" for our new set of Tricot Analyses menu:

1, "Server Name" runs the parameter server. This has a drop down list of the four server names that this variable takes: climmob3, 1000FARMS, AVISA, RTB. server = "climmob3" is the default
2. API Key runs the parameter key. This can have a button next to it where you browse .txt files for the stored key in a .txt file. This is similar to how we get the key in the Import from OpenAppBuilder and Import from RapidPro dialogs

key <- read.table("path/to/key.txt", quote="\"", comment.char="")

3. The "Find forms" button runs the R code getProjectsCM(server = <server name>, key = <key>). This R code gives a table, and we just want to return in the drop down list the project_id column in this list. I assume this is similar to how the "Import from ODK" dialog works.
4. There is a checkbox, default unchecked:

a) When you click OK, the following runs:

user_owner <- project_list %>% dplyr::filter(project_id == project) %>% dplyr::pull(user_owner)

ClimMobTools::getDataCM(key = <key>
                        project = < project ID selected in the drop down >,
                        userowner = user_owner,
                        server = <server>,
                        pivot.wider = TRUE)

b) We get one data frame, which is called the name given in the ucrSave after "New Data Frame Name:"

R Code Example:

# get a list of projects
key <- "d39a3c66-5822-4930-a9d4-50e7da041e77"
# or in our case we will store the key in a .txt file (without the quotes) and call by:
# key <- read.table("C:/Users/lclem/Downloads/tricot_api_key.txt", quote="\"", comment.char="")
project_list <- ClimMobTools::getProjectsCM(key = key, server = "climmob3")

# display the project_list$project_id in the list

Second bit that runs gets the data from ClimMob

# Let's say they choose the project titled "breadwheat":
# and that the name in the ucrSave is "breadwheat"
project <- "breadwheat"
user_owner <- project_list %>% dplyr::filter(project_id == project) %>% dplyr::pull(user_owner)
breadwheat <- ClimMobTools::getDataCM(key = key,
                             project = project,
                             userowner = user_owner, # we run this ourselves
                             server = "climmob3",
                             pivot.wider = TRUE)
data_book$import_data(data_tables=list(breadwheat = breadwheat))

Third bit that runs is only run if the checkbox is checked. I'll outline this in the next message

[lilyclements]

If the checkbox is checked, then

1. Our R code above of the "Second bit that runs gets the data from ClimMob" runs at this point, instead of at the OK.

2. There is a sub-dialog to place the different variables:

a) ID Variable is a single receiver which gives the column to read into id_var
b) Option Variables is a multiple receiver which gives the columns to read into Option Variables. Can we instead give this the label "Variety Variables"?
c) Possible Ranks is a single input, where you separate items by a comma. Default A, B, C
d) Good Trait Suffix is a single input, where you give one item. Default _pos
e) Bad Trait Suffix is a single input, where you give one item. Default _neg
f) NA Denoted By is a single input, where you give one item. Default Not observed.
g) The ones highlighted are optional columns. They're not used in this function, but instead for defining, which is outlined in the next message.

instatExtras::pivot_tricot(data = breadwheat,
                           id_var = < ID variable > ,
                           option_cols = c(< Option Columns (or "Variety Variables")>),
                           possible_ranks = c("A", "B", "C"),
                           trait_good = "_pos", 
                           trait_bad = "_neg",
                           na_value = "Not observed")

For example:

breadwheat_by_variety_ID <- pivot_tricot(data = breadwheat,
             id_var = "id",
             option_cols = c("package_item_A", "package_item_B", "package_item_C"),
             possible_ranks = c("A", "B", "C"),
             trait_good = "_pos", 
             trait_bad = "_neg",
             na_value = "Not observed")
data_book$import_data(data_tables=list(breadwheat_by_variety_ID = breadwheat_by_variety_ID))

This gives a data frame with variables "ID", "variety", and then a bunch of other variables

3. We rename variables

data_book$rename_column_in_data(data_name="breadwheat", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("registration_"), pattern="registration_", replacement="")
data_book$rename_column_in_data(data_name="breadwheat", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("qst_"), pattern="qst_", replacement="")

4. We hide some variables from our data set imported on the main page (the breadwheat)

Here we hide any ending in _neg or _pos.
In other words, we hide any columns ending in the value defined in the " Good Trait Suffix" and "Bad Trait Suffix" single inputs.

# Column selection subdialog: Created new column selection
data_book$add_column_selection(data_name="breadwheat", name="remove_traits", column_selection=list(C0=list(operation="tidyselect::ends_with", parameters=list(match="_neg", ignore.case=FALSE), negation=TRUE), C1=list(operation="tidyselect::ends_with", parameters=list(match="_pos", ignore.case=FALSE), negation=TRUE)), and_or="&")

# Dialog: Column Selection
data_book$set_current_column_selection(data_name="breadwheat", name="remove_traits")

5. We then autodefine bits, like in the climatic dialogs. This is outlined in the message below. This also adds a selection option

6. We run a transformation to get data at the variety level

data_book$calculate_summary(data_name="breadwheat_by_ID_variety",
                            factors="variety",
                            store_results=TRUE,
                            summaries=c("summary_count"), silent=TRUE)
data_book$rename_dataframe(data_name="breadwheat_by_ID_variety_by_variety", new_value="breadwheat_by_variety", label="")

7. We rename variables

data_book$rename_column_in_data(data_name="breadwheat_by_ID_variety", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("firstassessment_"), pattern="firstassessment_", replacement="")
data_book$rename_column_in_data(data_name="breadwheat_by_ID_variety", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("lastassessment_"), pattern="lastassessment_", replacement="")

data_book$add_column_selection(data_name="breadwheat_by_ID_variety", name="selection", column_selection=list(C0=list(operation="base::match", parameters=list(x=c("earlygrowth","grainquality","overallperf", "yield")))), and_or="|")

8. We Create a Rankings Object:

traits <- data_book$get_column_selection(data_name = "breadwheat_by_ID_variety", name = "selection") #get_object (all_trial_vars)
traits <- traits$conditions$C0$parameters$x
breadwheat_by_ID_variety <- data_book$get_data_frame("breadwheat_by_ID_variety") %>%
  tidyr::pivot_longer(cols = all_of(traits), names_to = "trait", values_to = "rank") %>%
  dplyr::group_by(id, trait) %>%
  dplyr::mutate(x = ifelse(any(is.na(rank)), 1, 0)) %>%
  filter(x == 0)
rankings_list <- traits %>%
  purrr::map(~ {
    breadwheat_by_ID_variety %>%
      dplyr::filter(trait == .x) %>%
      gosset::rank_numeric(data = ., 
                           items = "variety", 
                           input = "rank", 
                           id = "id", 
                           ascending = TRUE)
  })
names(rankings_list) <- traits

data_book$add_object(data_name="breadwheat_by_ID_variety", object_name="rankings_list", object_type_label="structure", object_format="text", object=rankings_list)

[lilyclements]

Autodefine - this is being refined still.

For our "Breadwheat" data:

a) We autodefine "ID" to be the variable in the "ID Variable" single receiver from the sdg
b) We autodefine "Varieties" to be the set of variables in the "Variety Variables" multiple receiver from the sdg
c) We autodefine "Latitude" to be the variable in the "Latitude" single receiver
d) We autodefine "Longitude" to be the variable in the "Longitude" single receiver
e) We autodefine "Planting Date" to be the variable in the "Planting Date" single receiver

E.g.

data_book$define_as_tricot(data_name="breadwheat_data",
                           key_col_names=c("id"),
                           types=c(id = "id",
                                   lat = "firstassessment_farm_geo_latitude",
                                   lon = "firstassessment_farm_geo_longitude",
                                   planting_date = "firstassessment_plantingdate",
                                   varieties=c("package_item_A", "package_item_B",
                                               "package_item_C")),
                           auto_selection = TRUE)

For our "Breadwheat_by_ID_variety" data, the autodefining happens to our data that is created by the pivot_tricot function in the sdg. We just run the following:

all_traits <- names(breadwheat_by_ID_variety %>% dplyr::select(-c("id", "variety")))
data_book$define_as_tricot(data_name="breadwheat_by_ID_variety",
                           key_col_names=c("id", "variety"),
                           types=c(variety = "variety",
                                   id = "id",
                                   traits = all_traits),
                           auto_selection = TRUE)

# then finally we set a selection for our sub-traits too:
overall_traits <- c("lastassessment_overallperf_neg", "lastassessment_overallperf_pos") # from the overall traits receiver.
overall_traits <- gsub("(_neg|_pos)$", "", overall_traits) # _neg/_pos from the suffix receivers
overall_traits <- unique(overall_traits)
sub_traits <- setdiff(all_traits, overall_traits)
# selection for our non-overall trait:
data_book$add_column_selection(
  data_name = "breadwheat_by_ID_variety",
  name = "sub_traits_selection",
  column_selection = list(C0 = list(operation = "base::match", parameters = list(x = sub_traits))),
  and_or = "|"
)

[lilyclements]

Te complete R script:

#########################################################
###########   IMPORT   FROM   CLIMMOB     ###############
#########################################################

# Press "Get Forms": Gets a list of projects
key <- "d39a3c66-5822-4930-a9d4-50e7da041e77"
# or in our case we will store the key in a .txt file (without the quotes) and call by:
# key <- read.table("C:/Users/lclem/Downloads/tricot_api_key.txt", quote="\"", comment.char="")
project_list <- ClimMobTools::getProjectsCM(key = key, server = "climmob3")

# Press OK (or checkbox): Let's say they choose the project titled "breadwheat":
# and that the name in the ucrSave is "breadwheat"
project <- "breadwheat"
user_owner <- project_list %>% dplyr::filter(project_id == project) %>% dplyr::pull(user_owner)
breadwheat <- ClimMobTools::getDataCM(key = key,
                                      project = project,
                                      userowner = user_owner, # we run this ourselves
                                      server = "climmob3",
                                      pivot.wider = TRUE)
data_book$import_data(data_tables=list(breadwheat = breadwheat))

# If the checkbox is unchecked, then leave it there
# If the checkbox is checked then the following runs:
# 1. Rename variables
data_book$rename_column_in_data(data_name="breadwheat", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("registration_"), pattern="registration_", replacement="")
data_book$rename_column_in_data(data_name="breadwheat", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("qst_"), pattern="qst_", replacement="")

# 2. Define tricot data
data_book$define_as_tricot(data_name="breadwheat",
                           key_col_names=c("id"),
                           types=c(id = "id",
                                   lat = "firstassessment_farm_geo_latitude",
                                   lon = "firstassessment_farm_geo_longitude",
                                   planting_date = "firstassessment_plantingdate",
                                   varieties=c("package_item_A", "package_item_B",
                                               "package_item_C")),
                           auto_selection = TRUE)

# 3. Create by data at ID and Variety level:
breadwheat_by_ID_variety <- instatExtras::pivot_tricot(data = breadwheat,
                                                       id_var = "id",
                                                       option_cols = c("package_item_A", "package_item_B", "package_item_C"),
                                                       possible_ranks = c("A", "B", "C"),
                                                       trait_good = "_pos", 
                                                       trait_bad = "_neg",
                                                       na_value = "Not observed")
data_book$import_data(data_tables=list(breadwheat_by_ID_variety = breadwheat_by_ID_variety))

# 4. Define this data as Tricot data
all_traits <- names(breadwheat_by_ID_variety %>% dplyr::select(-c("id", "variety")))
data_book$define_as_tricot(data_name="breadwheat_by_ID_variety",
                           key_col_names=c("id", "variety"),
                           types=c(variety = "variety",
                                   id = "id",
                                   traits = all_traits),
                           auto_selection = TRUE)

# 5. Add final selection (I can set this as an additional function if we'd like?)
overall_traits <- c("lastassessment_overallperf_neg", "lastassessment_overallperf_pos")
overall_traits <- gsub("(_neg|_pos)$", "", overall_traits)
overall_traits <- unique(overall_traits)
sub_traits <- setdiff(all_traits, overall_traits)
# selection for our non-overall trait:
data_book$add_column_selection(
  data_name = "breadwheat_by_ID_variety",
  name = "sub_traits_selection",
  column_selection = list(C0 = list(operation = "base::match", parameters = list(x = sub_traits))),
  and_or = "|"
)


# 6. Pivot/transformation that gives data at Variety Level too
data_book$calculate_summary(data_name="breadwheat_by_ID_variety",
                            factors="variety",
                            store_results=TRUE,
                            summaries=c("summary_count"), silent=TRUE)
data_book$rename_dataframe(data_name="breadwheat_by_ID_variety_by_variety", new_value="breadwheat_by_variety", label="")

# 7. Rename
#data_book$rename_column_in_data(data_name="breadwheat_by_ID_variety", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("firstassessment_"), pattern="firstassessment_", replacement="")
#data_book$rename_column_in_data(data_name="breadwheat_by_ID_variety", type="rename_with", .fn=stringr::str_replace, .cols=tidyselect::starts_with("lastassessment_"), pattern="lastassessment_", replacement="")

# 8. Create Rankings Object:
traits <- data_book$get_column_selection(data_name = "breadwheat_by_ID_variety", name = "traits_selection") #get_object (all_trial_vars)
traits <- unname(traits$conditions$C0$parameters$x)
breadwheat_by_ID_variety <- data_book$get_data_frame("breadwheat_by_ID_variety")
breadwheat_by_ID_variety <- breadwheat_by_ID_variety %>%
  tidyr::pivot_longer(cols = all_of(traits), names_to = "trait", values_to = "rank") %>%
  dplyr::group_by(id, trait) %>%
  dplyr::mutate(x = ifelse(any(is.na(rank)), 1, 0)) %>%
  dplyr::filter(x == 0)
rankings_list <- traits %>%
  purrr::map(~ {
    breadwheat_by_ID_variety %>%
      dplyr::filter(trait == .x) %>%
      gosset::rank_numeric(data = ., 
                           items = "variety", 
                           input = "rank", 
                           id = "id", 
                           ascending = TRUE)
  })
names(rankings_list) <- traits
data_book$add_object(data_name="breadwheat_by_ID_variety", object_name="rankings_list", object_type_label="structure", object_format="text", object=rankings_list)

[lilyclements]

I need to add in here new options!

1. The label should say "Transform and Autodefine Data"
2. The button can just say "Options"
3. Add to R Code:
   Pivot/transformation that gives data at Variety Level too (just Variety and Count by default)
   Remove qst_registration_ etc automatically from our variable names
   Set age as numeric by default
   Create rankings and grouped_rankings objects automatically