---
title: "Investment frequency and sizes"
description: >
  Tier 4 (investment analyses on `tstm_asset_loan` + `tstm_hh_mthspan`).
  Investment frequency and size tables (issue #9).
vignette: >
  %\VignetteIndexEntry{Investment frequency and sizes}
  %\VignetteEngine{quarto::html}
  %\VignetteEncoding{UTF-8}
knitr:
  opts_chunk:
    collapse: true
    comment: "#>"
---

We implement [PrjThaiHFID-#9](https://github.com/FanWangEcon/PrjThaiHFID/issues/9).

**Data workflow (independent of the bridge gateway vignette).** This vignette is
**not** tied to [`ffv_invest_loan_bridge.qmd`](ffv_invest_loan_bridge.qmd) or to
packaged `data/tstm_invest.rda` (which holds the MBF gateway’s three paper
ivars only). We lazy-load `tstm_hh_mthspan` and `tstm_asset_loan` from `data/`
(both use anonymized `tmid_hh` in column `id`; see `R/data.R` and
`data-raw/id_anonymize/`), then **regenerate** `tstm_invest` and
`tstm_invdates_uniq` via `ffp_hfid_invest_gateway()` on all `agg_BS_*` columns,
so `unique(tstm_invest$ivars)` is the full asset-definition set (~12 ivars), not
the three-ivar gateway snapshot.

**Unit of observation:** household x asset-definition `ivars` for the frequency tables; investment spell x balance-sheet window aggregation for the size tables.

## Outputs at a glance

This vignette produces summary tables only — it does **not** save any packaged dataset. Individual tables can be written to the package `res/res_invest_freq_sizes` folder by setting their entry in `ls_save_res` to `TRUE` (all default `FALSE`; the `res/` artifacts are git-ignored and saved only for local convenience). Two table families are generated:

- **Investment lumpiness / frequency:** distribution of the number of investments per household across asset definitions (`ivars`), with auxiliary shares.
- **Investment sizes:** windowed balance-sheet changes around investments (forward/backward windows), reported as prior-ratio statistics by asset class.

Inputs are the packaged [`tstm_asset_loan`](../reference/tstm_asset_loan.html) and [`tstm_hh_mthspan`](../reference/tstm_hh_mthspan.html); investments are regenerated in-file via `ffp_hfid_invest_gateway()` (full `agg_BS_*` set) rather than read from packaged `tstm_invest`.

## Pipeline structure

```{mermaid}
flowchart TD
  subgraph inputs [Packaged data inputs]
    assetLoan[tstm_asset_loan]
    mthspan[tstm_hh_mthspan]
  end
  subgraph regen [Regenerate investments]
    gw[ffp_hfid_invest_gateway full agg_BS set]
    assetLoan --> gw
    gw --> invest[tstm_invest]
    gw --> invdates[tstm_invdates_uniq]
  end
  subgraph freq [Investment frequency]
    mthspan --> dur[filter min duration it_hh_mth_nbr]
    invest --> cnt[count investments per hh and ivars]
    dur --> cnt
    cnt --> freqtab[lumpiness frequency table]
  end
  subgraph sizes [Investment sizes]
    invdates --> win[window functions forward backward]
    assetLoan --> ws[winstats balance aggregation]
    invest --> ws
    win --> ws
    ws --> ratio[prior-ratio stats by asset class]
    ratio --> sizetab[investment sizes table]
  end
```

```{r setup}
library(PrjThaiHFID)
library(dplyr)
library(tidyr)
library(stringr)
library(glue)
library(ggplot2)
library(kableExtra)
spn_pkg_root <- rprojroot::find_root(rprojroot::has_file("DESCRIPTION"))
spt_res <- file.path(spn_pkg_root, "res", "res_invest_freq_sizes")
ls_save_res <- list(
  bk_asset_count = FALSE,
  bk_invest_prior_ratio = FALSE,
  tb_all_m1mean = FALSE,
  tb_agri_m1mean = FALSE,
  tb_biz_m1mean = FALSE,
  tb_all_m12sum = FALSE,
  tb_agri_m12sum = FALSE,
  tb_biz_m12sum = FALSE
)
bs_style <- c("striped", "hover", "condensed", "responsive")
options(kable_styling_bootstrap_options = bs_style)
```

## Parameters

```{r}
# Choose latex for paper output
# st_kableformat <- "latex"
st_kableformat <- "html"
# Minimum month duration for a household to be counted
it_hh_mth_nbr <- 161
# Percentiles of interest
ar_fl_percentiles <- c(0.05, 0.10, 0.25, 0.50, 0.75, 0.9, 0.95)
```

## Load packaged inputs and regenerate investments

```{r}
# Packaged inputs (documented in R/data.R; anonymized tmid_hh in column id)
tstm_hh_mthspan <- tstm_hh_mthspan
tstm_asset_loan <- tstm_asset_loan

# Invest-only gateway on full asset file (not packaged data/tstm_invest)
ls_return <- PrjThaiHFID::ffp_hfid_invest_gateway(
  tstm_asset_loan,
  fl_sd_ithres = stats::qnorm(0.99),
  it_thres_invest_mth_gap = 2
)
tstm_invest <- ls_return$tstm_invest
tstm_invdates_uniq <- ls_return$tstm_invdates_uniq
```

## Implement table to show lumpiness of investments

This block generates [Table 1 issue 9](https://github.com/FanWangEcon/PrjThaiHFID/issues/9).

### Generate stats group 1

First, load in, househould start and end months (uoo=hh), and investment file (uoo=hhxivarxinvest), already done.

```{r}
# Check
ar_st_ivars <- unique(tstm_invest$ivars)
```

Second, from invest file, group by hh x ivar, and count the numver of investments.

```{r}
# Load files, already loaded tstm_hh_mthspan for hh start and end months
# investmetn file
st_id_ivar_n <- "id_ivar_n"
tstm_invest_idivar_n <- tstm_invest %>%
  group_by(id, ivars) %>%
  summarize(!!sym(st_id_ivar_n) := n())
# Check
unique(tstm_invest_idivar_n$ivars)
```

Third, merge with household startend file, replace NA with zero for each invest count.

```{r}
# Subsetting hh with min obs count
tstm_hh_mthspan_sel <- tstm_hh_mthspan %>%
  filter(month_n == it_hh_mth_nbr)
# Left join to investment counts
# For households without any investments, they will have ivars=NA
tstm_hh_mthspan_wrk <- tstm_hh_mthspan_sel %>%
  left_join(tstm_invest_idivar_n, by = "id")
# Reshape long to wide, to have all ivars as cols, replace NA by 0
# Follow https://fanwangecon.github.io/R4Econ/panel/widelong/htmlpdfr/fs_pivotwider.html
st_ivar_prefix <- "ivar"
st_ivar_prefix_d <- paste0(st_ivar_prefix, "_")
tstm_hh_mthspan_wrk_wide <- tstm_hh_mthspan_wrk %>%
  pivot_wider(
    id_cols = c("id"),
    names_from = ivars,
    names_prefix = st_ivar_prefix_d,
    values_from = st_id_ivar_n
  )
# Drop NA column, due to hh without any invest only ivar=NA
# creating an extra category, all 0 invest household still have
# all other ivars
tstm_hh_mthspan_wrk_wide <- tstm_hh_mthspan_wrk_wide %>%
  select(-dplyr::any_of("ivar_NA"))
# replace NA values by 0s
tstm_hh_mthspan_wrk_wide <- tstm_hh_mthspan_wrk_wide %>%
  mutate_at(
    vars(contains(st_ivar_prefix_d)),
    list(~ replace_na(., 0))
  )
# Reshape back to long
# Follow https://fanwangecon.github.io/R4Econ/panel/widelong/htmlpdfr/fs_pivotlonger.html
tstm_hh_mthspan_wrk_long <- tstm_hh_mthspan_wrk_wide %>%
  pivot_longer(
    cols = starts_with(st_ivar_prefix_d),
    names_to = c(st_ivar_prefix),
    names_pattern = paste0(st_ivar_prefix_d, "(.*)"),
    values_to = st_id_ivar_n
  )
```

Fourth, group by ivar and invest count, tally.

```{r}
# tally
tb_ivar_n_tally <- tstm_hh_mthspan_wrk_long %>%
  group_by(ivar, id_ivar_n) %>%
  tally() %>%
  spread(ivar, n)
# NA to zero
tb_ivar_n_tally <- tb_ivar_n_tally %>%
  mutate_at(vars(contains("agg_")), list(~ replace_na(., 0)))
# print
print(tb_ivar_n_tally, n = Inf)
```

Fifth, proportional version

```{r}
# Freq to shares
tb_ivar_n_tally_share <- tb_ivar_n_tally %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . / sum(.))
  )
# Check
colSums(tb_ivar_n_tally_share)
# print
print(round(tb_ivar_n_tally_share, 2), n = Inf)
```

### Generate stats group 2

Load in group 1 stats above, and compute weighted average overall for each ivar.

First, compute means including zeros. 

```{r}
# Prob * Val
tb_ivar_n_tally_mean <- tb_ivar_n_tally_share %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . * id_ivar_n)
  )
# Sum each column
tb_ivar_n_tally_mean <- tb_ivar_n_tally_mean %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ sum(.))
  ) %>%
  slice_head(n = 1) %>%
  select(contains("agg_"))
# Divide by number of years
tb_ivar_n_tally_mean_peryear <- tb_ivar_n_tally_mean %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . / (it_hh_mth_nbr / 12))
  )

# Print
print(tb_ivar_n_tally_mean)
print(tb_ivar_n_tally_mean_peryear)
```

Second, compute means excluding zeros. 

```{r}
# Recompute shares, after excluding zero
tb_ivar_n_tally_share_nozr <- tb_ivar_n_tally %>%
  filter(id_ivar_n != 0) %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . / sum(.))
  )
# print
colSums(tb_ivar_n_tally_share_nozr)
# Prob * Val
tb_ivar_n_tally_mean_nozr <- tb_ivar_n_tally_share_nozr %>%
  filter(id_ivar_n != 0) %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . * id_ivar_n)
  )
# Sum each column
tb_ivar_n_tally_mean_nozr <- tb_ivar_n_tally_mean_nozr %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ sum(.))
  ) %>%
  slice_head(n = 1) %>%
  select(contains("agg_"))
# Divide by number of years
tb_ivar_n_tally_mean_nozr_peryear <- tb_ivar_n_tally_mean_nozr %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ . / (it_hh_mth_nbr / 12))
  )

# Print
print(tb_ivar_n_tally_mean_nozr)
print(tb_ivar_n_tally_mean_nozr_peryear)
```

### Generate stats group 3 

First, Load in household monthly asset file, already loaded `tstm_asset_loan`.
 
Second, convert if household x monthly elements are non-zero to 1, zero keep as 0.

```{r}
tstm_asset_loan_bi <- tstm_asset_loan %>%
  select(id, month, one_of(ar_st_ivars)) %>%
  mutate(across(
    contains("agg_"),
    function(x) {
      ifelse(x > 0, 1, 0)
    }
  ))
```

Third group by, sum monthly binary if var exists, aggregate to household level, binary if sum > 1 or if sum = 0
```{r}
# Aggregate
tstm_asset_loan_bi_hh <- tstm_asset_loan_bi %>%
  group_by(id) %>%
  summarize_at(
    vars(contains("agg_")),
    list(~ sum(., na.rm = TRUE))
  )
# hh-level binary
tstm_asset_loan_bi_hh_bi <- tstm_asset_loan_bi_hh %>%
  mutate(across(
    contains("agg_"),
    function(x) {
      ifelse(x > 0, 1, 0)
    }
  ))
```

Fourth, merge with household startend file, condition as needed.

```{r}
tstm_asset_bi <- tstm_hh_mthspan_sel %>%
  left_join(tstm_asset_loan_bi_hh_bi, by = "id") %>%
  select(id, contains("agg_"))
```

Fifth, compute share of households with non-zero

```{r}
tstm_asset_bi_agg <- tstm_asset_bi %>%
  ungroup() %>%
  summarize_at(
    vars(contains("agg_")),
    list(~ sum(.) / n())
  )
```

### Table display

#### Table input processing

First, sorting instructions. 

2025-05-24 15:50:50, select:

1. Agricultural: agg_BS_1025
2. Business: agg_BS_3021
3. Land: agg_BS_1024
4. Household assets: agg_BS_3022
5. Agri + biz: agg_BS_1021
6. agri + biz + land: agg_BS_1012
7. Agri + biz + land + household: agg_BS_1011

```{r}
# # Variable sequence
# ar_st_vars_kbl <- c(
#   "id_ivar_n",
#   "agg_BS_10", "agg_BS_11", "agg_BS_12",
#   "agg_BS_23", "agg_BS_09"
# )
# Variable sequence
ar_st_vars_kbl <- c(
  "id_ivar_n",
  "agg_BS_1025", "agg_BS_3021",
  "agg_BS_1024", "agg_BS_3022",
  "agg_BS_1021", "agg_BS_1012", "agg_BS_1011"
)
```

Second, clean-up stats group 1.

```{r}
# Sum up rows where id_ivar_n >= 8 and create a single row for that, then remove individual rows >= 8
# Convert id_ivar_n to numeric for filtering
tb_ivar_n_tally_share <- tb_ivar_n_tally_share %>%
  mutate(id_ivar_n = as.numeric(id_ivar_n))

# Separate rows with id_ivar_n < 8 and >= 8
tb_ivar_n_tally_share_lt8 <- tb_ivar_n_tally_share %>%
  filter(id_ivar_n < 8) %>%
  mutate(id_ivar_n = as.character(id_ivar_n))

tb_ivar_n_tally_share_ge8 <- tb_ivar_n_tally_share %>%
  filter(id_ivar_n >= 8)

# Sum across columns for rows with id_ivar_n >= 8
if (nrow(tb_ivar_n_tally_share_ge8) > 0) {
  summed_row <- tb_ivar_n_tally_share_ge8 %>%
    summarize(across(where(is.numeric), sum, na.rm = TRUE)) %>%
    mutate(id_ivar_n = "8+")
  # Bind together
  tb_ivar_n_tally_share <- bind_rows(tb_ivar_n_tally_share_lt8, summed_row)
} else {
  tb_ivar_n_tally_share <- tb_ivar_n_tally_share_lt8
}

# Restore id_ivar_n as character for downstream compatibility
tb_ivar_n_tally_share <- tb_ivar_n_tally_share %>%
  mutate(id_ivar_n = as.character(id_ivar_n))
```

```{r}
tb_tally_cur <- tb_ivar_n_tally_share
# Round and add percentage signs
tb_tally_cur_wkr <- tb_tally_cur %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ paste0(
      format(round(., 3) * 100, nsmall = 1),
      "%"
    ))
  ) %>%
  mutate(id_ivar_n = as.character(id_ivar_n))
```

Third, clean-up and combine auxliary stats

```{r}
# Combine auxillary stats
tb_tally_aux1_cur <- bind_rows(
  tb_ivar_n_tally_mean_peryear %>% mutate(id_ivar_n = "Include 0s"),
  tb_ivar_n_tally_mean_nozr_peryear %>% mutate(id_ivar_n = "Exclude 0s")
) %>% mutate_at(
  vars(contains("agg_")),
  list(~ as.character(
    format(round(., 2), nsmall = 2)
  ))
)
# Auxillary share stasts
tb_tally_aux2_cur <- bind_rows(
  tstm_asset_bi_agg %>% mutate(id_ivar_n = "Share")
) %>% mutate_at(
  vars(contains("agg_")),
  list(~ paste0(
    format(round(., 3) * 100, nsmall = 1),
    "%"
  ))
)
```

Fourth, combine auxliary and main stats.

```{r}
# Combine auxillary stats
tb_tally_jnt <- bind_rows(
  tb_tally_cur_wkr,
  tb_tally_aux1_cur,
  tb_tally_aux2_cur
) %>% select(one_of(ar_st_vars_kbl))
```

#### Table final display

First, we define column names, which correspond to previously defined variable selection list.

```{r}
# Column names
# ar_st_col_names <- c(
#   "# of investments",
#   "Land + livestock + agri assets",
#   "Agri assets",
#   "Business + household assets",
#   "Business assets",
#   "Land + livest. + agri + biz + household",
#   "All (w/o household assets)",
#   "Agri + business assets"
# )
# 2025-05-24 15:53:38
ar_st_col_names <- c(
  "# of investments",
  "Agricultural",
  "Business",
  "Land",
  "Household",
  "Agricultural and business",
  "Agricultural, business, and land",
  "Agricultural, business, land, and household"
)
# "All (Land + livestock + agri + household + biz)",
# "Productive assets (Land + livestock + agri + biz)",
st_texsize <- "footnotesize"
ar_st_col_names <- paste0(
  "\\", st_texsize, "{", ar_st_col_names, "}"
)
# Define column groups, grouping the names above
# =1/3/2 are number of columns group title covers
ar_st_col_groups <- c(
  " " = 1,
  "Individual assets categories" = 4,
  "Joint assets definitions" = 3
)

# Second, we construct main table, and add styling.
f_bk_asset_count <- function(st_format) {
  bk_asset_count <- kbl(
    tb_tally_jnt,
    format = st_format,
    # escape = F,
    linesep = "",
    booktabs = T,
    align = "c",
    caption = "Percentage of households with number of investments, survey month 1 to 160",
    col.names = ar_st_col_names
  ) %>%
    # see https://cran.r-project.org/web/packages/kableExtra/vignettes/awesome_table_in_html.html#Bootstrap_table_classes
    kable_styling(
      bootstrap_options = c("striped", "hover", "condensed", "responsive"),
      full_width = F, position = "left"
    )

  # Third, we add in column groups.
  bk_asset_count <- bk_asset_count %>% add_header_above(ar_st_col_groups)

  # Fourth, we add in row groups.
  bk_asset_count <- bk_asset_count %>%
    pack_rows(
      "Share of households with different number of investments over 160 months",
      1, 9,
      latex_gap_space = "0.5em"
    ) %>%
    pack_rows(
      "Mean number of investments per year over 160 months",
      10, 11,
      latex_gap_space = "0.5em", hline_before = T
    ) %>%
    pack_rows(
      "Share of household having any month with non-zero assets over 160 months",
      12, 12,
      latex_gap_space = "0.5em", hline_before = T
    )

  # Fifth, column formatting.
  bk_asset_count <- bk_asset_count %>%
    column_spec(1, width = "2.5cm") %>%
    column_spec(2:8, width = "2cm")

  # FInal adjustments
  bk_asset_count <- gsub(bk_asset_count,
    pattern = paste0("\\textbackslash{}", st_texsize, "\\"),
    replacement = paste0("\\", st_texsize), fixed = TRUE
  )
  bk_asset_count <- gsub(bk_asset_count,
    pattern = "\\}",
    replacement = "}", fixed = TRUE
  )
  bk_asset_count <- gsub(bk_asset_count,
    pattern = "hline",
    replacement = "midrule", fixed = TRUE
  )

  return(bk_asset_count)
}

# Build for display and save a latex (+ csv) copy.
bk_asset_count <- f_bk_asset_count(st_kableformat)
ffp_save_res_table(
  f_bk_asset_count("latex"), "bk_asset_count", spt_res,
  df = tb_tally_jnt,
  bl_save = ls_save_res[["bk_asset_count"]]
)

# Sixth, display.
# pl_bk_asset_count <- bk_asset_count %>% as_image()
bk_asset_count
```

## Investment table to show sizes of investments

[Table 2 issue 9](https://github.com/FanWangEcon/PrjThaiHFID/issues/9).

### Prepare input data

Generate investment windows by unique investments

Generate in investment file (uoo=hhxivarxinvest), and load in windowed stats file (uoo=hhxivarxinvestxvarxwin) 12 month as 1 month window versions.

First, generate in investment file (uoo=hhxivarxinvest), and load in windowed stats file (uoo=hhxivarxinvestxvarxwin) 12 month as 1 month window versions.

```{r}
# 1 month window file, changes in capital-assets themselves (as well as cash/debt)
df_invwin_uniq_fw1bw1 <- PrjThaiHFID::ff_hfid_invest_window(
  df_invdates_uniq = tstm_invdates_uniq,
  it_window_forward = 1,
  it_window_backward = 1,
  bl_forward_include_imth = FALSE,
  bl_backward_include_imth = FALSE
)
# 12 month window file
df_invwin_uniq_fw12bw12 <- PrjThaiHFID::ff_hfid_invest_window(
  df_invdates_uniq = tstm_invdates_uniq,
  it_window_forward = 12,
  it_window_backward = 12,
  bl_forward_include_imth = FALSE,
  bl_backward_include_imth = FALSE
)
```

Second, generate mean and sum stats, short and long, respectively. 

```{r}
# Either sum or mean, not both
# 1 month window file, mean
df_invest_winstats_fw1bw1_mean <- PrjThaiHFID::ff_hfid_invest_winstats(
  df_asset_loan = tstm_asset_loan,
  df_invest = tstm_invest,
  df_invwin_uniq = df_invwin_uniq_fw1bw1,
  bl_compute_sum = FALSE,
  bl_compute_mean = TRUE
)
# 12 month window file, sum
df_invest_winstats_fw12bw12_sum <- PrjThaiHFID::ff_hfid_invest_winstats(
  df_asset_loan = tstm_asset_loan,
  df_invest = tstm_invest,
  df_invwin_uniq = df_invwin_uniq_fw12bw12,
  bl_compute_sum = TRUE,
  bl_compute_mean = FALSE
)
```

Third, combine output to list.

```{r}
# Sets of windowed files
ls_df_invest_winstats_fwbw <- list(
  # Compare against backward window capital assets
  fw1bw1mean_back = df_invest_winstats_fw1bw1_mean,
  # Compare against backward window revenue sums
  fw12bw12sum_back = df_invest_winstats_fw12bw12_sum
)
ar_svr_window_comp <- c("win_backward", "win_backward")
# Get length and names
it_df_count <- length(ls_df_invest_winstats_fwbw)
ar_st_df_names <- names(ls_df_invest_winstats_fwbw)
```

### Generate stats step 1, run window functions

Compute percentages.

- 12 month file: For revenue stats, based on 12 month file compute ratios of investments relative to last 12 months sum.
- 1 month file: Ratios of invest size var versus asset var.
Stack 12 month and 1month files together

```{r}
# Storage collection
ls_invest_win_shr <- vector(mode = "list", length = it_df_count)
names(ls_invest_win_shr) <- ar_st_df_names

# Loop over list of dataframes
for (it_df in seq(1, it_df_count)) {
  # Load in file
  st_df_name <- ar_st_df_names[it_df]
  df_invest_winstats_fwbw <- ls_df_invest_winstats_fwbw[[st_df_name]]
  svr_window_comp <- ar_svr_window_comp[it_df]

  # Compute proportions
  df_invest_winstats_shr <- df_invest_winstats_fwbw %>%
    # filter(blnc_vars == "IS_07") %>%
    filter(!!sym(svr_window_comp) != 0) %>%
    mutate(shr_stat = capital_invest / !!sym(svr_window_comp)) %>%
    select(
      id,
      ivars, hh_inv_asset_ctr, hh_inv_ctr,
      blnc_vars, blnc_agg_stats,
      # capital_invest, win_backward,
      shr_stat
    )

  # Store results
  ls_invest_win_shr[[st_df_name]] <- df_invest_winstats_shr
}

# df_invest_winstats_fw12bw12_shr <- df_invest_winstats_fw12bw12 %>%
#   # filter(blnc_vars == "IS_07") %>%
#   filter(win_backward != 0) %>%
#   mutate(shr_stat = capital_invest / win_backward) %>%
#   select(
#     id,
#     ivars, hh_inv_asset_ctr, hh_inv_ctr,
#     blnc_vars, blnc_agg_stats,
#     capital_invest, win_backward, shr_stat
#   )
```

### Generate stats step 2, ratios and within group percentiles

Compute within balance cheet and investment variable CDF.

```{r}
# Storage collection
ls_invest_win_pct <- vector(mode = "list", length = it_df_count)
names(ls_invest_win_pct) <- ar_st_df_names

# Loop over list of dataframes
for (it_df in seq(1, it_df_count)) {
  # Load in file
  st_df_name <- ar_st_df_names[it_df]
  df_invest_win_shr <- ls_invest_win_shr[[st_df_name]]

  # Generate within-group CDF
  df_invest_win_pct <- df_invest_win_shr %>%
    select(blnc_vars, ivars, shr_stat) %>%
    arrange(blnc_vars, ivars, shr_stat) %>%
    group_by(blnc_vars, ivars) %>%
    mutate(cdf = row_number() / n())

  # Store results
  ls_invest_win_pct[[st_df_name]] <- df_invest_win_pct
}

# # Generate within-group CDF
# df_invest_winstats_fw12bw12_pct <- df_invest_winstats_fw12bw12_shr %>%
#   arrange(blnc_vars, ivars, shr_stat) %>%
#   group_by(blnc_vars, ivars) %>%
#   mutate(cdf = row_number() / n())
```

Compute a subset of percentiles of interests.

```{r}
# Define strings
st_var_prefix <- "shr"
st_var_prefix_perc <- paste0(st_var_prefix, "_p")
svr_mean <- paste0(st_var_prefix, "_mean")

# Storage collection
ls_df_cross_pct <- vector(mode = "list", length = it_df_count)
names(ls_df_cross_pct) <- ar_st_df_names

# Loop over list of dataframes
for (it_df in seq(1, it_df_count)) {
  # Load in file
  st_df_name <- ar_st_df_names[it_df]
  df_invest_win_pct <- ls_invest_win_pct[[st_df_name]]

  # Generate within-group percentiles
  for (it_percentile_ctr in seq(1, length(ar_fl_percentiles))) {
    # Current within group percentile to compute
    fl_percentile <- ar_fl_percentiles[it_percentile_ctr]

    # Percentile and mean stats
    svr_percentile <- paste0(st_var_prefix_perc, round(fl_percentile * 100))

    # Frame with specific percentile
    df_within_percentiles_cur <- df_invest_win_pct %>%
      group_by(blnc_vars, ivars) %>%
      filter(cdf >= fl_percentile) %>%
      slice(1) %>%
      mutate(!!sym(svr_percentile) := shr_stat) %>%
      select(blnc_vars, ivars, one_of(svr_percentile))

    # Merge percentile frames together
    if (it_percentile_ctr > 1) {
      df_within_percentiles <- df_within_percentiles %>%
        left_join(df_within_percentiles_cur,
          by = c("blnc_vars" = "blnc_vars", "ivars" = "ivars")
        )
    } else {
      df_within_percentiles <- df_within_percentiles_cur
    }
  }

  # Add in within group mean
  df_within_percentiles_mean <- df_invest_win_pct %>%
    group_by(blnc_vars, ivars) %>%
    mutate(!!sym(svr_mean) := mean(shr_stat, na.rm = TRUE)) %>%
    slice(1)
  # Join to file
  df_within_percentiles <- df_within_percentiles %>%
    left_join(df_within_percentiles_mean,
      by = c("blnc_vars" = "blnc_vars", "ivars" = "ivars")
    ) %>%
    select(
      blnc_vars, ivars,
      one_of(svr_mean), contains(st_var_prefix_perc)
    )

  # Store results
  ls_df_cross_pct[[st_df_name]] <- df_within_percentiles
}

# display
print(ls_df_cross_pct)
```

### Table display

#### Table input processing

Get stats tables.

```{r}
# Compare against backward window capital assets
df_fw1bw1mean_back <- ls_df_cross_pct$fw1bw1mean_back %>% ungroup()
df_fw12bw12sum_back <- ls_df_cross_pct$fw12bw12sum_back %>% ungroup()
```

#### Review and select: Investment as share of prior month capital (level)

Most recent month, relationship between investment and investment. Potentially shows that not only are investments rare, they are sizable. 

Learned:

- very small and very large ratios are both possible 
- highlight results from: BS_1021	agg_BS_1021, median of 1.3, pretty large changes, largest among total asset.
  - include also components: BS_2021	agg_BS_2021
  - include also components: BS_3021	agg_BS_3021
  - could mention 1021 relative to 1011 as stats not in table but mentioned, but maybe also include: BS_1012	agg_BS_1021

```{r}
# All
df_all_m1mean <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_10"), str_detect(ivars, "BS_10"))
(df_all_m1mean) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_all_m1mean,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of prior month capital (all, level)"
  ),
  "tb_all_m1mean", spt_res,
  df = df_all_m1mean, bl_save = ls_save_res[["tb_all_m1mean"]]
)
# Agriculture
df_agri_m1mean <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_20"), str_detect(ivars, "BS_20"))
(df_agri_m1mean) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_agri_m1mean,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of prior month capital (agriculture, level)"
  ),
  "tb_agri_m1mean", spt_res,
  df = df_agri_m1mean, bl_save = ls_save_res[["tb_agri_m1mean"]]
)
# Business
df_biz_m1mean <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_30"), str_detect(ivars, "BS_30"))
(df_biz_m1mean) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_biz_m1mean,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of prior month capital (business, level)"
  ),
  "tb_biz_m1mean", spt_res,
  df = df_biz_m1mean, bl_save = ls_save_res[["tb_biz_m1mean"]]
)
```

Select subset of key stats to be displayed.

```{r}
# Results 1
df_m1mean_agri_bs2021 <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_2021"), str_detect(ivars, "BS_2021"))
# Results 2
df_m1mean_biz_bs3021 <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_3021"), str_detect(ivars, "BS_3021"))
# Results 3a
df_m1mean_all_bs1021 <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_1021"), str_detect(ivars, "BS_1021"))
# Results 3b
df_m1mean_all_bs1021v1012 <- df_fw1bw1mean_back %>%
  filter(str_detect(blnc_vars, "BS_1012"), str_detect(ivars, "BS_1021"))
```

#### Review and select: Investment as a share of revenue past 12 months (level)

Select revenue past 12 month sum vs investment shares. We look at overall, business, and agriculture in three groups. Are they "large" relative to revenue, if they are, they are presumably hard to pay off right away.

Findings:

- main: IS_1021	agg_BS_1021, note these are shares of revenue, they might seem low possibly, but firms would need to be highly profitable to be able to pay off debt with one year profits.
- details: 
  - IS_2021	agg_BS_2021	
  - IS_3021	agg_BS_3021
- aux:
  - IS_1012	agg_BS_1021, do not consider wage income, too confusing, profit vs revenue, all else is revenue, not profit.


```{r}
# All
df_all_m12sum <- df_fw12bw12sum_back %>%
  rowwise() %>%
  filter(str_detect(blnc_vars, "IS_10"), str_detect(ivars, "BS_10"))
(df_all_m12sum) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_all_m12sum,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of revenue past 12 months (all, level)"
  ),
  "tb_all_m12sum", spt_res,
  df = df_all_m12sum, bl_save = ls_save_res[["tb_all_m12sum"]]
)
# Agriculture
df_agri_m12sum <- df_fw12bw12sum_back %>%
  rowwise() %>%
  filter(str_detect(blnc_vars, "IS_20"), str_detect(ivars, "BS_20"))
(df_agri_m12sum) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_agri_m12sum,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of revenue past 12 months (agriculture, level)"
  ),
  "tb_agri_m12sum", spt_res,
  df = df_agri_m12sum, bl_save = ls_save_res[["tb_agri_m12sum"]]
)
# Business
df_biz_m12sum <- df_fw12bw12sum_back %>%
  rowwise() %>%
  filter(str_detect(blnc_vars, "IS_30"), str_detect(ivars, "BS_30"))
(df_biz_m12sum) %>%
  kable() %>%
  kable_styling()
ffp_save_res_table(
  kbl(df_biz_m12sum,
    format = "latex", booktabs = TRUE,
    caption = "Investment as share of revenue past 12 months (business, level)"
  ),
  "tb_biz_m12sum", spt_res,
  df = df_biz_m12sum, bl_save = ls_save_res[["tb_biz_m12sum"]]
)
```

Select subset of key stats to be displayed.

```{r}
# Results 1
df_m12sum_agri_bs2021 <- df_fw12bw12sum_back %>%
  filter(str_detect(blnc_vars, "IS_2021"), str_detect(ivars, "BS_2021"))
# Results 2
df_m12sum_biz_bs3021 <- df_fw12bw12sum_back %>%
  filter(str_detect(blnc_vars, "IS_3021"), str_detect(ivars, "BS_3021"))
# Results 3a
df_m12sum_all_bs1021 <- df_fw12bw12sum_back %>%
  filter(str_detect(blnc_vars, "IS_1021"), str_detect(ivars, "BS_1021"))
# Results 3b
df_m12sum_all_bs1021v1012 <- df_fw12bw12sum_back %>%
  filter(str_detect(blnc_vars, "IS_1012"), str_detect(ivars, "BS_1021"))
# Bind rows for select
```

#### Combine 1 and 12 month info, transpose, and prep

First, combine all rows together and combine `blnc_vars` and `ivars`. Do not resort, preserve existing orders. Drop the mean, not informative.

```{r}
# Bind rows for selected key results:
df_m1mean_m12_sum_jnt <- bind_rows(
  df_m1mean_agri_bs2021,
  df_m12sum_agri_bs2021,
  df_m1mean_biz_bs3021,
  df_m12sum_biz_bs3021,
  df_m1mean_all_bs1021v1012,
  df_m12sum_all_bs1021v1012,
  df_m1mean_all_bs1021,
  df_m12sum_all_bs1021
) %>%
  mutate(
    blnc_vars_ivars = paste0(ivars, "_d_", blnc_vars)
  ) %>%
  select(
    blnc_vars_ivars, -blnc_vars, -ivars,
    contains(st_var_prefix_perc)
  )
```

Second, reshape wide to long then to wide again to transpose.

```{r}
df_m1mean_m12_sum_jnt_trans <- df_m1mean_m12_sum_jnt %>%
  pivot_longer(
    cols = starts_with(st_var_prefix_perc),
    names_to = c("percentile"),
    names_pattern = paste0(st_var_prefix_perc, "(.*)"),
    values_to = "value"
  ) %>%
  pivot_wider(
    names_from = blnc_vars_ivars,
    values_from = value
  )
```

Third, percentage formatting.

```{r}
df_m1mean_m12_sum_jnt_trans <- df_m1mean_m12_sum_jnt_trans %>%
  mutate_at(
    vars(contains("agg_")),
    list(~ paste0(
      format(round(., 3) * 100,
        nsmall = 1,
        big.mark = ","
      ),
      "%"
    ))
  )
```
#### Table final display

First, we define column names, which correspond to previously defined variable selection list.

```{r}
# Column names
ar_st_col_names <- c(
  "Percentiles",
  "$\\frac{\\text{Invest size}}{\\text{Pre mth assets}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre 12 mth rev}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre mth assets}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre 12 mth rev}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre mth assets}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre 12 mth rev}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre mth assets}}$",
  "$\\frac{\\text{Invest size}}{\\text{Pre 12 mth rev}}$"
)
# Define column groups, grouping the names above
ar_st_col_groups <- c(
  " " = 1,
  "Invest/assets/rev" = 2,
  "Invest/assets/rev" = 2,
  "All prod assets/rev" = 2,
  "Agri + biz assets/rev" = 2
)
# Define column groups, grouping the names above
ar_st_col_groups_super <- c(
  " " = 1,
  "Agricultural" = 2,
  "Business" = 2,
  "Agricutural + business investments" = 4
)

# Second, we construct main table, and add styling.
f_bk_invest_prior_ratio <- function(st_format) {
  bk_invest_prior_ratio <- kbl(
    df_m1mean_m12_sum_jnt_trans,
    format = st_format,
    # escape = F,
    linesep = "",
    booktabs = T,
    align = "c",
    caption = "Ratio of investment to prior to investment month assets and year revenues.",
    col.names = ar_st_col_names
  ) %>%
    # see https://cran.r-project.org/web/packages/kableExtra/vignettes/awesome_table_in_html.html#Bootstrap_table_classes
    kable_styling(
      bootstrap_options = c("striped", "hover", "condensed", "responsive"),
      full_width = F, position = "left"
    )

  # Third, we add in row groups
  bk_invest_prior_ratio <- bk_invest_prior_ratio %>%
    add_header_above(ar_st_col_groups) %>%
    add_header_above(ar_st_col_groups_super)
  # %>%
  # # collapse_rows(columns = 2:5, latex_hline = "major", valign = "middle")

  # Fourth, we add in column groups.
  bk_invest_prior_ratio <- bk_invest_prior_ratio %>%
    pack_rows(
      "Bottom decile",
      1, 2,
      latex_gap_space = "0.5em"
    ) %>%
    pack_rows(
      "Quartiles",
      3, 5,
      latex_gap_space = "0.5em", hline_before = F
    ) %>%
    pack_rows(
      "Top decile",
      6, 7,
      latex_gap_space = "0.5em", hline_before = F
    )

  # Fifth, column formatting.
  bk_invest_prior_ratio <- bk_invest_prior_ratio %>%
    column_spec(1, width = "2.5cm") %>%
    column_spec(2:9, width = "2cm")

  # Final adjustments
  st_texcmd <- "frac"
  bk_invest_prior_ratio <- gsub(bk_invest_prior_ratio,
    pattern = paste0("\\textbackslash{}", st_texcmd, "\\"),
    replacement = paste0("\\", st_texcmd), fixed = TRUE
  )
  st_texcmd <- "text"
  bk_invest_prior_ratio <- gsub(bk_invest_prior_ratio,
    pattern = paste0("\\textbackslash{}", st_texcmd, "\\"),
    replacement = paste0("\\", st_texcmd), fixed = TRUE
  )
  bk_invest_prior_ratio <- gsub(bk_invest_prior_ratio,
    pattern = "\\}\\{",
    replacement = "}{", fixed = TRUE
  )
  bk_invest_prior_ratio <- gsub(bk_invest_prior_ratio,
    pattern = "\\}",
    replacement = "}", fixed = TRUE
  )
  bk_invest_prior_ratio <- gsub(bk_invest_prior_ratio,
    pattern = "\\$",
    replacement = "$", fixed = TRUE
  )

  return(bk_invest_prior_ratio)
}

# Build for display and save a latex (+ csv) copy.
bk_invest_prior_ratio <- f_bk_invest_prior_ratio(st_kableformat)
ffp_save_res_table(
  f_bk_invest_prior_ratio("latex"), "bk_invest_prior_ratio", spt_res,
  df = df_m1mean_m12_sum_jnt_trans,
  bl_save = ls_save_res[["bk_invest_prior_ratio"]]
)

# Sixth, display.
# pl_bk_asset_count <- bk_invest_prior_ratio %>% as_image()
bk_invest_prior_ratio
```
