使用fread加载与write.csv保存时文件大小暴涨问题求助

Let’s break down how to fix both your file size explosion and the read.csv.sql column mismatch issue, plus optimize your daily workflow to handle large Twitter datasets smoothly.

1. Fixing File Size Bloat #

Your file jumping from 19GB to 60GB+ is almost entirely due to using write.csv to store your data. Here’s why and how to fix it:

• write.csv is inefficient for large data: It’s a plain-text writer that adds unnecessary quotes to most columns and stores every value as human-readable text—this is terrible for space, especially with text-heavy tweet data.
• Switch to fwrite (data.table): It’s purpose-built for speed and efficiency. Replace your write.csv line with:

  fwrite(base, "tweets.csv", quote = "auto") # Only quotes columns that need it
  

  This will cut down file size significantly while keeping readability intact.
• Use a binary format for maximum compression: For even better results, switch to formats like fst or feather—they’re designed for large tabular data and compress far better than CSV:

  # Install first: install.packages("fst")
  fst::write_fst(base, "tweets.fst", compress = 100) # Max compression
  # Read back later:
  base <- fst::read_fst("tweets.fst")
  

• Stop loading the entire dataset daily: The biggest memory drain is loading 3M+ rows every day just to append 25k new ones. Use a SQLite database instead—you can append new data without reading the whole file:

  library(DBI)
  library(RSQLite)
  
  # Connect to a SQLite database (creates it if it doesn't exist)
  con <- dbConnect(SQLite(), "tweets.db")
  
  # Append new daily tweets to the database table
  dbWriteTable(con, "tweets", datos, append = TRUE, row.names = FALSE)
  
  # When you need to analyze the full dataset later:
  full_data <- dbReadTable(con, "tweets")
  
  # Always close the connection when done
  dbDisconnect(con)
  

  This eliminates the memory overload from loading the entire old dataset every day.

2. Fixing the read.csv.sql Column Error & Properly Reading Large Files #

The "expected 29 columns but found 6" error happens because read.csv.sql struggles with unescaped special characters (like commas, newlines, or quotes) in tweet text. Here’s how to fix it:

• Tweak read.csv.sql parameters: Tell it to handle quoted text correctly:

  library(sqldf)
  base <- read.csv.sql("tweets.csv", 
                       sql = "SELECT * FROM file",
                       header = TRUE,
                       quote = '"',
                       escape = '"',
                       sep = ",")
  

  This tells the parser to recognize double quotes as text delimiters and escape any internal quotes, which should resolve the column mismatch.
• Check for corrupted rows: Since your write.csv process was interrupted, your CSV might have incomplete rows at the end. Use fread to handle messy data automatically:

  base <- fread("tweets.csv", fill = TRUE, verbose = TRUE)
  

  fill = TRUE fills missing columns with NA, and verbose = TRUE will alert you to any problematic rows.
• Stick with fread for large CSV reads: It’s faster, more memory-efficient, and handles messy data better than read.csv or read.csv.sql for most large dataset use cases.

Bonus: Optimize Your Daily Workflow #

To avoid future issues, adjust your daily routine to:

1. Download new tweets with rtweet
2. Clean and select columns as you already do
3. Append directly to a SQLite database or binary file (no need to load the entire old dataset)
4. Only load the full dataset when you need to run analysis (not every day)

内容的提问来源于stack exchange，提问作者Juan Antonio Guevara Gil