How to get the right data format for SQLite in R with rtweet?

Question

I have trouble to get the "right" data.frame format:

The goal is to store a constantly generated data.frame with rtweet dircetly into a SQLite database.

geocode <- c()
con <- dbConnect(RSQLite::SQLite(), "db_name")
tweets <- as.data.frame(c())

for (i in geocode){
  tweets <-  search_tweets(geocode = geocode, n = 18000, retryonratelimit = T, include_rts = F)
  dbWriteTable(con, "tweets", tweets, append = T)
}

This code gives me the following error:

Error: Can only bind lists of raw vectors (or NULL)

The data format is:

> class(tweets)
[1] "tbl_df"     "tbl"        "data.frame"

When I export the data with fwrite() into .csv and import everything via read.csv2 dbWriteTable() works perfectly fine…

df <- read.csv2("FILE_NAME/tweets.csv", header = T, sep = ",")
dbWriteTable(con, "tweets", df, append = T)

And the df format here is:

> class(df)
[1] "data.frame"

But using fwrite and read.csv2seems to be no option since my data.frame will be to big for the R memory.

And just using as.data.frame() is working neither. Even though it is a data.frame now...

> class(as.data.frame(tweets))
[1] "data.frame"

There has to be another way to get the format right.

Big thanks in advance for any help!

score 0 · Answer 1 · answered Dec 10 '21 at 08:26

This previous post may help.

It appears that the function has an issue with NULL values. You can either subset them out, or replace them with 'N/A' text.

for (i in geocode){
  tweets <-  search_tweets(geocode = geocode, n = 18000, retryonratelimit = T, include_rts = F)
  tweets[tweets == 'NULL'] = 'N/A'
  dbWriteTable(con, "tweets", tweets, append = T)
}

How to get the right data format for SQLite in R with rtweet?

1 Answers1