Korean National Assembly Database

import pandas as pd

# 22대 Full Master (전 생애주기 타임스탬프 포함)
master = pd.read_parquet("data/processed/master_bills_22.parquet")
print(f"22대: {len(master):,} bills, {len(master.columns)} variables")

# 위원회 회의 기록 (1:N)
meetings = pd.read_parquet("data/processed/committee_meetings_22.parquet")

# 17-21대 Full Master
for age in range(17, 22):
    df = pd.read_parquet(f"data/processed/master_bills_{age}.parquet")
    print(f"{age}대: {len(df):,} bills, {len(df.columns)} variables")

# 법률안만 필터링
laws = master[master["bill_kind"] == "법률안"]

# 의원 발의만
member_bills = laws[laws["ppsr_kind"] == "의원"]

# 특정 위원회
health = laws[laws["committee_nm"] == "보건복지위원회"]

# 처리 완료된 법안만
processed = laws[laws["status"] != "계류중"]

# 가결된 법안만
enacted = laws[laws["enacted"] == 1]

# publ_mona_cd를 파싱하여 edge list 생성
edges = []
for _, row in member_bills.iterrows():
    if pd.notna(row["publ_mona_cd"]) and pd.notna(row["rst_mona_cd"]):
        co_sponsors = row["publ_mona_cd"].split(",")
        for cs in co_sponsors:
            edges.append({
                "bill_id": row["bill_id"],
                "from": row["rst_mona_cd"],
                "to": cs.strip(),
                "passed": row["passed"],
            })

edge_df = pd.DataFrame(edges)
print(f"Edges: {len(edge_df):,}")  # 수십만 개의 공동발의 관계

# 17-22대 통합
frames = []
for age in range(17, 23):
    suffix = "" if age == 22 else "_lite"
    path = f"data/processed/master_bills_{age}{suffix}.parquet"
    df = pd.read_parquet(path)
    frames.append(df)

all_bills = pd.concat(frames, ignore_index=True)
print(f"Total: {len(all_bills):,} bills across {all_bills['age'].nunique()} assemblies")

# 대수별 가결률 추이
trend = all_bills.groupby("age").agg(
    total=("bill_id", "count"),
    enacted=("enacted", "sum"),
).assign(rate=lambda x: x["enacted"] / x["total"] * 100)
print(trend)

import sqlite3

conn = sqlite3.connect("data/processed/master_bills_22.sqlite")

# 위원회별 통과율
query = """
SELECT committee_nm,
       COUNT(*) as total,
       SUM(enacted) as enacted,
       ROUND(SUM(enacted) * 100.0 / COUNT(*), 1) as rate
FROM bills
WHERE bill_kind = '법률안' AND committee_nm IS NOT NULL
GROUP BY committee_nm
HAVING total >= 50
ORDER BY rate DESC
"""
pd.read_sql(query, conn)

library(arrow)
library(dplyr)
library(tidyr)
library(ggplot2)

# 22대 Full Master
master <- read_parquet("data/processed/master_bills_22.parquet")
cat(sprintf("22대: %s bills, %d variables\n", format(nrow(master), big.mark=","), ncol(master)))

# 위원회 회의 기록
meetings <- read_parquet("data/processed/committee_meetings_22.parquet")

# 17-22대 통합
all_bills <- bind_rows(
  lapply(17:22, function(age) read_parquet(sprintf("data/processed/master_bills_%d.parquet", age)))
)
cat(sprintf("Total: %s bills\n", format(nrow(all_bills), big.mark=",")))

library(tidyplots)  # 또는 ggplot2

trend <- all_bills %>%
  group_by(age) %>%
  summarise(
    total = n(),
    enacted = sum(enacted),
    passed = sum(passed),
    .groups = "drop"
  ) %>%
  mutate(
    enact_rate = enacted / total * 100,
    pass_rate = passed / total * 100
  )

# tidyplots 방식
trend %>%
  pivot_longer(cols = c(enact_rate, pass_rate),
               names_to = "measure", values_to = "rate") %>%
  tidyplot(x = factor(age), y = rate, color = measure) %>%
  add_data_points(size = 3) %>%
  add_line() %>%
  adjust_colors(c("#D55E00", "#0072B2")) %>%
  adjust_labels(x = "국회 대수", y = "가결률 (%)") %>%
  remove_title() %>%
  adjust_size(width = 150, height = 90) %>%
  save_plot("output/passage_trend.pdf")

cmt_stats <- master %>%
  filter(bill_kind == "법률안", !is.na(committee_nm)) %>%
  group_by(committee_nm) %>%
  summarise(
    total = n(),
    enacted = sum(enacted),
    passed = sum(passed),
    avg_days = mean(days_to_proc, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  filter(total >= 50) %>%
  mutate(enact_rate = enacted / total * 100) %>%
  arrange(desc(total))

# 가결률 수평 막대
cmt_stats %>%
  tidyplot(x = enact_rate, y = reorder(committee_nm, enact_rate)) %>%
  add_barstack_absolute() %>%
  add_reference_lines(x = median(cmt_stats$enact_rate), linetype = "dashed") %>%
  adjust_colors("#57068C") %>%
  adjust_labels(x = "가결률 (%)", y = "") %>%
  remove_title() %>%
  adjust_size(width = 150, height = 120) %>%
  save_plot("output/committee_rates.pdf")

library(survival)
library(fixest)

# 처리까지의 생존 데이터 구성
surv_data <- master %>%
  filter(bill_kind == "법률안", ppsr_kind %in% c("의원", "정부", "위원장")) %>%
  mutate(
    # 관측 종료: 처리일 또는 현재 날짜
    event = as.integer(!is.na(proc_dt)),
    duration = as.numeric(
      difftime(coalesce(proc_dt, Sys.Date()), ppsl_dt, units = "days")
    )
  ) %>%
  filter(duration >= 0)

# Kaplan-Meier by proposer type
km <- survfit(Surv(duration, event) ~ ppsr_kind, data = surv_data)
plot(km, col = c("#E69F00", "#56B4E9", "#009E73"),
     xlab = "Days", ylab = "Survival probability",
     lwd = 2, mark.time = FALSE)
legend("topright", levels(factor(surv_data$ppsr_kind)),
       col = c("#E69F00", "#56B4E9", "#009E73"), lwd = 2)

# Cox PH model
cox <- coxph(Surv(duration, event) ~ ppsr_kind + committee_nm, data = surv_data)
summary(cox)

library(fixest)

# 공동발의자 수 파싱
reg_data <- master %>%
  filter(bill_kind == "법률안", ppsr_kind == "의원") %>%
  mutate(
    n_cosponsors = stringr::str_extract(proposer_text, "\\d+") %>% as.integer(),
    month = format(ppsl_dt, "%Y-%m")
  ) %>%
  filter(!is.na(n_cosponsors))

# Linear probability model with committee FE
m1 <- feols(enacted ~ n_cosponsors | committee_nm, data = reg_data)
m2 <- feols(passed ~ n_cosponsors | committee_nm, data = reg_data)

etable(m1, m2, se = "hetero",
       headers = c("Enacted", "Passed (broad)"),
       notes = "Committee FE included. Robust SE.")