如何将ANBIMA API返回的长格式Tibble转换为宽格式？

我从ANBIMA API获取了一个tibble，数据包含两列：一列是要作为列名的变量名（name列），另一列是对应的值（value列）。数据共有15个变量，name列会重复出现，只有value列的值不同。我尝试把name列转成列表、过滤唯一值后用spread函数转宽格式，但第一步就没法创建有效列表，得到的是只有一个观测的字符型列表。

数据示例（dput输出） #

dput(head(dados, 20))
structure(list(name = c("tipo_titulo", "expressao", "data_vencimento", 
"data_referencia", "codigo_selic", "data_base", "taxa_compra", 
"taxa_venda", "taxa_indicativa", "intervalo_min_d0", "intervalo_max_d0", 
"intervalo_min_d1", "intervalo_max_d1", "pu", "desvio_padrao", 
"tipo_titulo", "expressao", "data_vencimento", "data_referencia", 
"codigo_selic"), value = c("LTN", "Taxa (% a.a.)/252", "2023-01-01", 
"2022-12-06", "100000", "2016-01-20", "13.7107", "13.6952", "13.7027", 
"13.5377", "13.9248", "13.5338", "13.9233", "990.36449", "0.00259307377643", 
"NTN-F", "Taxa (% a.a.)/252", "2023-01-01", "2022-12-06", "950199"
)), row.names = c(NA, 20L), class = c("tbl_df", "tbl", "data.frame"
))

获取数据的代码 #

library(httr)
library(jsonlite)
library(tidyverse)
library(stringr)
library(tibble)
library(bizdays)

client_id <- ''
client_secret <- ''

token_anbima <- base64_enc(str_c(client_id, ':', client_secret))

resposta_anbima <- POST("https://api.anbima.com.br/oauth/access-token",
                        add_headers("Authorization" = str_c("Basic ", token_anbima)), 
                        body = list(grant_type = "client_credentials"),
                        encode = 'form'
                        )

resposta_anbima$status

access_token <- str_sub(content(resposta_anbima, "text"), start = 18, end = 29)

data_tpf <- preceding(Sys.Date(), 'Brazil/ANBIMA')

teste <- GET(str_c('https://api-sandbox.anbima.com.br/feed/precos-indices/v1/titulos-publicos/mercado-secundario-TPF?data=',
                   data_tpf),
             add_headers("client_id" = client_id,
                         "access_token" = access_token))

dados <- enframe(unlist(content(teste)))

解决方案 #

核心问题是你没有先对重复的name列进行分组——每15行对应一个完整的观测条目，直接转宽会因为没有分组标识导致数据混乱。按以下步骤处理即可：

1. 添加分组标识 #

通过行号生成分组ID，把每15行归为一组，确保每个完整的观测被分到同一组：

dados <- dados %>%
  mutate(group_id = ceiling(row_number() / 15))

2. 用pivot_wider转宽格式 #

tidyverse现在推荐用pivot_wider替代spread，它能更灵活地处理这类数据：

dados_wide <- dados %>%
  pivot_wider(
    id_cols = group_id,
    names_from = name,
    values_from = value
  )

# 查看转换后的宽格式数据
head(dados_wide)

额外优化：更可靠的token提取 #

你之前用str_sub提取access_token的方式容易出错，直接解析JSON响应更稳妥：

token_content <- fromJSON(content(resposta_anbima, "text"))
access_token <- token_content$access_token

内容的提问来源于stack exchange，提问作者Carlos Eduardo Barros