0

Disclaimer: I know that this question has been asked before. The answer provided in this answer worked for me in the past, but for some reason has stopped now.

I am pulling Marketing email statistics from the Mailchimp API. I have been doing this for the last half year or so. However, in the past 2 months, I believe the structure of what I pull has changed and thus, my code no longer works and I cannot figure out why. I believe it has something to do with the nested data frames within my list of data frames that I receive.

Here is an example of my code and the resulting list of data frames. I have removed sensitive information from my code and image:

library(httr)
library(jsonlite)
library(plyr)

#Opens-----------
opens1 <- GET("https://us4.api.mailchimp.com/3.0/reports/***ReportNumber***/sent-to?count=4000",authenticate('***My Company***', '***My-Password***'))
opens1 <- content(opens1,"text")
opens1 <- fromJSON(opens1)

enter image description here

Then I run opens1 <- ldply(opens1, data.frame), and I receive the following error:

Error in allocate_column(df[[var]], nrows, dfs, var) : 
Data frame column 'merge_fields' not supported by rbind.fill

I tried using and looking up rbind.fill() and the other methods described in the linked answer at the top of my post, to no avail. What am I interpreting incorrectly about the merge_fields variable, or am I way off, and how do I correct it?

I'm just trying to get one data frame of all of the variables from the opens1 list.

Thanks for any and all help, and please, feel free to ask any clarification questions!

Community
  • 1
  • 1
medavis6
  • 843
  • 10
  • 32
  • If you actually want help you'll need to include some sort of [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input data (pictures of data aren't helpful). That data doesn't look very flat. Not sure what the expected output would even be. – MrFlick Oct 11 '16 at 14:48
  • @MrFlick I would produce reproducible sample data if I could, but the large part of my question is dealing with what I am even looking at here. When I try to run `dput()`, even for just 1 row, I get an unwieldy amount of data from the Console. I guess I'm assuming that before the change to the API, the resulting `opens1` was a simple flat list of data frames, but has now devolved into sub-data frames. My question is how can I work with this structure of a list of data frames with sub-data frames (or can I even, for that matter)? – medavis6 Oct 11 '16 at 14:57

1 Answers1

1

On a quick glance, this seems to work for me:

library(httr)
campaign_id <- "-------"
apikey = "------"
url <- sprintf("https://us1.api.mailchimp.com/3.0/reports/%s/sent-to", campaign_id)
opens <- GET(url, query = list(apikey = apikey, count = 4000L))
lst <- rjson::fromJSON(content(opens, "text"))
df <- dplyr::bind_rows(
  lapply(lst$sent_to, function(x) 
    as.data.frame(t(unlist(x)), stringsAsFactors = F)
))
lukeA
  • 53,097
  • 5
  • 97
  • 100
  • Thanks for the answer suggestion. I was able to run everything successfully up to the last section where the `lapply()` function provided me with the following error: `Error in eval(substitute(expr), envir, enclos) : Can not automatically convert from character to logical in column "V1".` – medavis6 Oct 11 '16 at 15:13
  • 1
    Maybe try `rjson::fromJSON`? Seems more reliable in terms of consistent outcome (?) – lukeA Oct 11 '16 at 15:51
  • That did the trick! Accepted your answer. My only follow up question because you are very knowledgeable on the subject, if you have time, is using offset in the accepted answer. When I try to query 4000 records, it throws an error. Basically, I found anything <= 1250 was fine. However, when I tried to incorporate offset >= 500, it also threw the 500 error. Because I have ~3000 records to query, what's the best way to grab all of them? – medavis6 Oct 11 '16 at 15:56
  • 1
    I cannot replicate it. My list is ~2000 subscribers, and if I set count to `4000`, then `length(lst$sent_to)` is 1979, which is the number of recipients that I contacted. Maybe you ran in some sort of rate limit? I'd ask the mailchimp support on that. (I seldomly use the API, so I do not know much about it...) – lukeA Oct 11 '16 at 16:03
  • 1
    Thanks for checking it out. Much appreciated for all your help today! – medavis6 Oct 11 '16 at 16:05