如何合并多个DataFrame并将结果转换为JSON结构

Got it, let's work through this problem step by step. You've got three large DataFrames (each ~50k rows, 7-10 columns) that share common identifiers like memberID and tripID, and you want to combine them into one dataset, then turn it into a nested JSON where each member's trips are grouped under their ID. Here's a practical, efficient solution using R's tidyverse and jsonlite packages.

Step 1: Prepare and Merge Your DataFrames #

First, let's mimic your data setup (I'll add unique columns to df2 and df3 to show how mixed column structures work):

# Load required packages
library(dplyr)
library(jsonlite)

# Your provided df1
df1 <- data.frame(
  memberID = c('001','002','002','003','003','003'),
  tripID = c('111','122','123','314','315','316'),
  distance = c(4.2,3.1,2.6,3.3,4.4,5.1),
  duration = c(1.1,2.3,4.6,3.2,1.1,9.7)
)

# Example df2 with an extra column (e.g., travel mode)
df2 <- data.frame(
  memberID = c('001','002','002','003','003','003'),
  tripID = c('111','122','123','314','315','316'),
  mode = c('bike','bus','bike','subway','bus','bike')
)

# Example df3 with another unique column (e.g., trip cost)
df3 <- data.frame(
  memberID = c('001','002','002','003','003','003'),
  tripID = c('111','122','123','314','315','316'),
  cost = c(2.5,1.2,2.0,3.0,1.5,2.2)
)

# Merge all DataFrames by shared identifiers (memberID + tripID)
merged_df <- df1 %>%
  left_join(df2, by = c("memberID", "tripID")) %>%
  left_join(df3, by = c("memberID", "tripID"))

If all your DataFrames have identical column structures, you could use bind_rows() instead—but left_join() is safer if columns vary between frames, as it ensures rows are matched correctly by the shared IDs.

Step 2: Reshape into a Nested Structure #

To get the JSON structure you likely want (each member has a list of their trips), we'll nest the trip-level data under each memberID:

nested_df <- merged_df %>%
  group_by(memberID) %>%
  nest(trips = -memberID) # Nest all columns except memberID into a "trips" list column

This creates a compact DataFrame where each row is a unique memberID, and the trips column holds a mini-DataFrame of all that member's trip details.

Step 3: Convert to JSON #

Finally, use jsonlite::toJSON() to turn the nested DataFrame into clean, human-readable JSON:

# Convert to formatted JSON
final_json <- toJSON(nested_df, pretty = TRUE, auto_unbox = TRUE)

# Print to verify the output
cat(final_json)

Sample JSON Output: #

[
  {
    "memberID": "001",
    "trips": [
      {
        "tripID": "111",
        "distance": 4.2,
        "duration": 1.1,
        "mode": "bike",
        "cost": 2.5
      }
    ]
  },
  {
    "memberID": "002",
    "trips": [
      {
        "tripID": "122",
        "distance": 3.1,
        "duration": 2.3,
        "mode": "bus",
        "cost": 1.2
      },
      {
        "tripID": "123",
        "distance": 2.6,
        "duration": 4.6,
        "mode": "bike",
        "cost": 2.0
      }
    ]
  },
  {
    "memberID": "003",
    "trips": [
      {
        "tripID": "314",
        "distance": 3.3,
        "duration": 3.2,
        "mode": "subway",
        "cost": 3.0
      },
      {
        "tripID": "315",
        "distance": 4.4,
        "duration": 1.1,
        "mode": "bus",
        "cost": 1.5
      },
      {
        "tripID": "316",
        "distance": 5.1,
        "duration": 9.7,
        "mode": "bike",
        "cost": 2.2
      }
    ]
  }
]

Performance Tips for Large Datasets #

Since you're working with ~150k total rows:

• If memory is tight, swap dplyr for data.table—it's optimized for fast joins and nesting with large datasets.
• Drop any unnecessary columns before merging to reduce memory overhead.
• Use toJSON(..., dataframe = "rows") explicitly to guarantee consistent row-based nesting (though the default should work here).

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