如何用jsonlite解析JSON时指定列转为POSIXct并解决时间丢失问题？

Let’s work through this step by step—here’s why your code is returning all NAs and how to fix it:

1. Broken JSON parsing: Using map(strJson, ~fromJSON(.)) is the first issue. strJson is a single JSON string, so map will iterate over every character instead of parsing the full object. This creates a messy, unstructured dataset, and your 10th column isn’t actually the time field you’re targeting.

2. Mismatched datetime format: The format string '%H:%M:%S %d %b %Y' doesn’t match the CME API’s data structure. The API returns trade dates as MM/DD/YYYY (like 04/06/2018) and time values (such as lastTradeTime) as separate HH:MM:SS strings—they aren’t combined in the format you specified.

3. Missing timezone: CME data is tied to Chicago time, so omitting the timezone can cause parsing errors or incorrect datetime values.

Corrected Code #

Here’s the revised approach to properly parse the JSON and convert your datetime field:

library(rvest)
library(jsonlite)

# Fetch and parse the JSON correctly
url <- "http://www.cmegroup.com/CmeWS/mvc/Settlements/Futures/Settlements/5081/FUT?tradeDate=04/06/2018&strategy=DEFAULT&pageSize=500"
strJson <- html_text(read_html(url))
json_data <- fromJSON(strJson)

# Extract the actual settlements table (this is the core data you want)
mData <- json_data$settlements

# First, confirm your target time column—run this to check all column names:
# colnames(mData)

# Assuming you want the `lastTradeTime` column (adjust if your target column differs)
# Combine trade date and time into a single datetime string
datetime_str <- paste(mData$tradeDate, mData$lastTradeTime)

# Convert to POSIXct with the correct format and Chicago timezone
dat <- as.POSIXct(datetime_str, format = "%m/%d/%Y %H:%M:%S", tz = "America/Chicago")

# Check the result
head(dat)

Quick Notes #

• If some rows have empty lastTradeTime values, those will show as NA in dat—this is expected. Use na.omit(dat) to filter those out if needed.
• Always double-check the column name with colnames(mData) to ensure you’re targeting the right time field (your original 10th column was invalid due to the broken JSON parsing).

内容的提问来源于stack exchange，提问作者stakowerflol