Each API is a relational database published one table at a time. Nothing is nested and nothing is pre-joined, so an analysis almost always means retrieving two or three tables and joining them yourself. This vignette maps the keys.
The shape of each module
Two identifiers do most of the work across all three modules:
-
id_programa— the program, which is the funding instrument as a whole. -
id_plano_acao— the action plan, which is one recipient’s share of it.
Everything else hangs off one of those.
TED
programa ──< plano_acao ──< plano_acao_meta ──< plano_acao_etapa
│ ├──< termo_execucao
├──< programa_acao_orcamentaria
└──< programa_beneficiario
├──< nota_credito ──< evento
├──< programacao_financeira ──< trf
├──< plano_acao_analise
└──< plano_acao_parecerplano_acao_etapa joins to plano_acao_meta
on id_meta, not directly to the plan. trf
joins to programacao_financeira on
id_programacao, and evento to
nota_credito on id_nota.
Fund-to-fund
programa ──< plano_acao ──< plano_acao_meta ──< plano_acao_meta_acao
│ ├──< empenho
├──< programa_beneficiario
├──< plano_acao_dado_bancario
└──< programa_gestao_agil
├──< plano_acao_destinacao_recursos
├──< plano_acao_historico
├──< termo_adesao ──< termo_adesao_historico
└──< relatorio_gestao ──< relatorio_gestao_acoes
└──< relatorio_gestao_analiseThe financial management tables form a chain of their own:
gestao_financeira_lancamentos joins to
gestao_financeira_categorias_despesa on
id_categoria_despesa_gestao_financeira, and
gestao_financeira_subtransacoes joins to the entries on
id_lancamento_gestao_financeira.
Three tables across the APIs carry a foreign key with an
_fk suffix rather than an id_ prefix:
plano_acao_analise_responsavel.plano_acao_analise_fk and
relatorio_gestao_analise_responsavel.relatorio_gestao_analise_fk
here, and plano_acao_parecer.plano_acao_hist_fk in TED.
Special transfers
programa_especial ──< plano_acao_especial ──< plano_trabalho_especial
├──< executor_especial ──< meta_especial
│ └──< finalidade_especial
├──< empenho_especial ──< documento_habil_especial
├──< relatorio_gestao_especial
└──< relatorio_gestao_novo_especialdocumento_habil_especial leads on to
ordem_pagamento_ordem_bancaria_especial through
id_dh, and from there to
historico_pagamento_especial through id_op_ob.
That is the payment trail: commitment, document, payment order,
history.
Note that meta_especial and
finalidade_especial hang off the executor
(id_executor), not off the plan.
A worked example
Which federal bodies decentralize the most money, and to what?
programas <- tg_get(
"ted", "programa",
.select = c(
"id_programa", "tx_nome_programa", "sigla_unidade_descentralizadora"
),
.limit = Inf
)
planos <- tg_get(
"ted", "plano_acao",
.select = c(
"id_plano_acao", "id_programa", "vl_total_plano_acao", "aa_ano_plano_acao"
),
.limit = Inf
)
planos |>
inner_join(programas, by = "id_programa") |>
group_by(sigla_unidade_descentralizadora) |>
summarise(planos = n(), total = sum(vl_total_plano_acao, na.rm = TRUE)) |>
arrange(desc(total))
#> # A tibble: 5 × 3
#> sigla_unidade_descentralizadora planos total
#> <chr> <int> <dbl>
#> 1 MDS 229 422595208242.
#> 2 MS 794 14472373138.
#> 3 FNDCT 154 13779027285.
#> 4 MIDR 603 5450415783.
#> 5 MAPA 284 2835531350.Retrieving both tables in full is reasonable here — 5500 and 6176 rows, six requests between them.
Check the join, do not assume it
A join that quietly drops rows looks exactly like a join that worked. Count before and after:
Every action plan in TED belongs to a program that is also published. That is worth knowing rather than assuming, because it is not true of every key in these APIs. Sampling two hundred values from each relationship in the diagrams above, most resolve completely, but three do not:
| Child | Parent | Resolved |
|---|---|---|
meta_especial.id_executor |
executor_especial.id_executor |
175 / 200 |
finalidade_especial.id_executor |
executor_especial.id_executor |
175 / 200 |
programa_acao_orcamentaria.id_programa |
programa.id_programa |
198 / 200 |
These are gaps in what the platform publishes, not in the retrieval.
An inner_join() will drop those rows silently; use
anti_join() to see them first, and decide deliberately.
If you are joining a filtered subset, fetch the other side with a matching filter rather than trimming afterwards:
planos_2024 <- tg_get(
"ted", "plano_acao",
aa_ano_plano_acao = 2024,
.select = c("id_plano_acao", "id_programa"),
.limit = Inf
)
notas <- tg_get(
"ted", "nota_credito",
id_plano_acao = in_(planos_2024$id_plano_acao),
.limit = Inf
)in_() sends the whole set to the service, so the
filtering happens there. Be aware that a very long list makes for a very
long URL; a few thousand values is usually fine, more than that is
better done by fetching the table and filtering locally.
Identifiers are doubles
Columns the API declares as bigint come back as
double, not integer, because a value beyond
.Machine$integer.max would become NA as an
integer. Joins between them work as expected, since both sides use the
same type. Be careful only if you convert one side yourself.
class(planos$id_plano_acao)
#> [1] "numeric"Repeated identifiers are real
Some of these tables are views with a join already baked in, so an
identifier that reads like a key can repeat.
fundoafundo/programa has 129 rows but 125 distinct
id_programa. Check before you use one as a key:
programas_ff <- tg_get("fundoafundo", "programa", .limit = Inf)
nrow(programas_ff)
#> [1] 129
n_distinct(programas_ff$id_programa)
#> [1] 125tg_tables() reports the primary key where the API
declares one, which it does for ten of the forty-eight tables. Where
primary_key is NA, no uniqueness is
guaranteed.