木も見て森も見たい（６）～自治体財務データから見えるもの～

節別合計の集計で使用したコード▼

# ディレクトリの設定
setwd("/Users/tm/Dropbox/_lg/A区/")

# 所属マスタの作成
shozoku <-
    c("学校支援課", "学校施設課", "学務課") %>%
    as_tibble() %>%
    rename(課 = 1) %>%
    group_nest()

# 施設マスタの作成
shisetsu <-
    # 事前に抽出した中学校一覧を読み込む
    read_excel("./200922_中学校一覧_Using_Adobe_Pages from kannrikeikaku.xlsx") %>%
    # 「名称」「延床面積」だけ残す
    select(1, 9) %>%
    # 列名称変更
    rename(延床面積 = 2) %>%
    # 施設名の頭にくっ付いている番号を分離
    separate(名称, c("番号", "名称"), sep = "  ") %>%
    # 配賦列を挿入
    mutate(紐付け先 = "配賦")

shisetsu_join <-
  shisetsu %>%
    group_nest(紐付け先)

# 施設辞書の作成：摘要欄から施設名を抽出するために辞書を作成
shisetsu_dic <-
  shisetsu  %>%
    select(名称, 番号) %>%
    # 重複を削除
    distinct() %>%
    # 列を行に置換
    pivot_wider(names_from = 番号, values_from = 名称) %>%
    # 1レコードに統合
    unite("辞書", 1 : 36, sep = "|") %>%
    chuck(1) %>% as.vector()

# 執行伝票の読み込み
  denpyo_all <-
      read_csv("./200511_data.csv") %>%
      # 名称変更
      rename(摘要 = 11, 支払額 = `支払額（円）`) %>%
      # 部・項  で絞り込み
      filter(部 == "学校運営部") %>%
      filter(項 == "中学校費") %>%
      # 小学校にも配賦すべき？伝票を除外
      filter(!str_detect(摘要, "小・中学校")) %>%
      filter(!str_detect(摘要, "小中学校"))

# 検算用
setsu_all <-
  denpyo_all %>%
      group_nest(節) %>%
      mutate(合計額 = map_dbl(data, ~ sum(.$支払額))) %>%
      select(-data)

  # # 作表
  # setsu_all %>%  
  #   datatable(caption = "節別合計", rownames = F,
  #             options = list(
  #               pageLength = 30,
  #               # dom = 't',
  #               scrollX = F,
  #               scrollCollapse = F)) %>%    
  #     formatCurrency(columns = c('合計額'), currency = "", mark = ",", digits = 0)


# 施設別集計
denpyo_junior <-    
  denpyo_all %>%
    # 摘要から中学校名を抽出；map_chrを使うこと！
    mutate(紐付け先 = map_chr(摘要, ~ str_extract(., shisetsu_dic))) %>%
    # 摘要に学校名が入っていなかった伝票に「配賦」を挿入
    mutate_at(vars(紐付け先),  ~ replace_na(., "配賦")) %>%
    group_nest(紐付け先) %>%
    # 配賦が必要な伝票にのみ施設名称を紐付け
    left_join(shisetsu , by = "紐付け先") %>%
    group_nest(紐付け先) %>%
    mutate(総面積 = map_dbl(data, ~ sum(.$延床面積))) %>%
    unnest() %>%
    unnest() %>%
    # 按分比率の計算
    mutate(按分比率 = 延床面積 / 総面積) %>%
    # 配賦金額の計算
    mutate(配賦額 = 支払額 * 按分比率) %>%
    # 施設名称の統合
    mutate(紐付け先 = if_else(str_detect(紐付け先, "配賦"), 名称, 紐付け先)) %>%
    mutate_at(vars(配賦額), ~ if_else(is.na(配賦額), 支払額, 配賦額))

  # 節別：作表
  denpyo_junior %>%
    group_nest(節) %>%
    mutate(節合計額 = map_dbl(data, ~ sum(.$配賦額))) %>%
    select(-data) %>%

# テーブル表示
    datatable(caption = "節別合計", rownames = F,
              options = list(
                # columnDefs = list(list(targets = c(0 : 2), width = 50)),
                pageLength = 30,
                # dom = 't',
                # scrollX = F,
                scrollCollapse = F)) %>%
      formatCurrency(columns = c('節合計額'), currency = "", mark = ",", digits = 0)