CODEWITHSUNDEEP
rdatabase
Paul Ledin
Paul Ledin

Reputation: 83

Can't write data frame to database

I can't really create a code example because I'm not quite sure what the problem is and my actual problem is rather involved. That said it seems like kind of a generic problem that maybe somebody's seen before.

Basically I'm constructing 3 different dataframes and rbinding them together, which is all as expected smooth sailing but when I try to write that merged frame back to the DB I get this error:

 Error in .External2(C_writetable, x, file, nrow(x), p, rnames, sep, eol,  : 
   unimplemented type 'list' in 'EncodeElement'

I've tried manually coercing them using as.data.frame() before and after the rbinds and the returned object (the same one that fails to write with the above error message) exists in the environment as class data.frame so why does dbWriteTable not seem to have got the memo?

Sorry, I'm connecting to a MySQL DB using RMySQL. The problem I think as I look a little closer and try to explain myself is that the columns of my data frame are themselves lists (of the same length), which sorta makes sense of the error. I'd think (or like to think anyways) that a call to as.data.frame() would take care of that but I guess not?

A portion of my str() since it's long looks like:

 .. [list output truncated]
 $ stcong            :List of 29809
..$ : int 3
..$ : int 8
..$ : int 4
..$ : int 2

I guess I'm wondering if there's an easy way to force this coercion?

Upvotes: 0

Views: 320

Answers (1)

joran
joran

Reputation: 173577

Hard to say for sure, since you provided so little concrete information, but this would be one way to convert a list column to an atomic vector column:

> d <- data.frame(x = 1:5)
> d$y <- as.list(letters[1:5])
> str(d)
'data.frame':   5 obs. of  2 variables:
 $ x: int  1 2 3 4 5
 $ y:List of 5
  ..$ : chr "a"
  ..$ : chr "b"
  ..$ : chr "c"
  ..$ : chr "d"
  ..$ : chr "e"
> d$y <- unlist(d$y)
> str(d)
'data.frame':   5 obs. of  2 variables:
 $ x: int  1 2 3 4 5
 $ y: chr  "a" "b" "c" "d" ...

This assumes that each element of your list column is only a length one vector. If any aren't, things will be more complicated, and you'd likely need to rethink your data structure anyhow.

Upvotes: 2

Related Questions