1

Hi I used the httr library to get some data from an API. Using this code

URL <- "https://spotpet.api.hasoffers.com/Apiv3/json?NetworkToken=XXXXXXXXXXXXXXXXX&Target=Report&Method=getStats&fields[]=Stat.date&fields[]=Stat.offer_id&fields[]=Stat.affiliate_id&fields[]=Affiliate.company&fields[]=Stat.impressions&fields[]=Stat.unique_clicks&fields[]=Stat.suspicious_clicks&fields[]=Stat.conversions&fields[]=Stat.gross_clicks&fields[]=Stat.payout&fields[]=Stat.revenue&fields[]=Stat.source&fields[]=Stat.sale_amount&filters[Stat.date][conditional]=GREATER_THAN_OR_EQUAL_TO&filters[Stat.date][values]=2020-12-01&totals=0"

data <- GET(URL)

if I was getting a csv file I was able to turn it into a dataframe with this code

new_data <- read.csv(text=content(data, type = "text", encoding="UTF-8"))

but this time the response is in json format

how would I change this code to turn the json content into a dataframe?

here is the json response, sorry it is long but want to post it all so you guys can try it

the results of

dput(content(data))

list(request = list(Target = "Report", Format = "json", Service = "HasOffers", 
Version = "2", NetworkToken = "xxxxxxxxxxxxxxx", 
Method = "getStats", fields = list("Stat.date", "Stat.offer_id", 
    "Stat.affiliate_id", "Affiliate.company", "Stat.impressions", 
    "Stat.unique_clicks", "Stat.suspicious_clicks", "Stat.conversions", 
    "Stat.gross_clicks", "Stat.payout", "Stat.revenue", "Stat.source", 
    "Stat.sale_amount"), filters = list(Stat.date = list(
    conditional = "GREATER_THAN_OR_EQUAL_TO", values = "2020-12-01")), 
totals = "0"), response = list(status = 1L, httpStatus = 200L, 
data = list(page = 1L, current = 50L, count = 16901L, pageCount = 339L, 
    data = list(list(Stat = list(date = "2020-12-01", offer_id = "18", 
        affiliate_id = "2", impressions = "0", unique_clicks = "1", 
        suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
        payout = "0.00000", revenue = "0.00000", source = "leadday0quoteform", 
        sale_amount = "0.00000"), Affiliate = list(company = "SPOT INTERNAL EMAIL")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "29", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "FB3.14", 
            sale_amount = "0.00000"), Affiliate = list(company = "Spot Pet Facebook")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "30", impressions = "0", unique_clicks = "5", 
            suspicious_clicks = "0", conversions = "4", gross_clicks = "7", 
            payout = "4.00000", revenue = "0.00000", source = "CMBIO", 
            sale_amount = "0.00000"), Affiliate = list(company = "Spot Pet Instagram")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "41", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "2", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "CesarArc", 
            sale_amount = "0.00000"), Affiliate = list(company = "Kendago")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "41", impressions = "0", unique_clicks = "2", 
            suspicious_clicks = "0", conversions = "4", gross_clicks = "3", 
            payout = "2.00000", revenue = "0.00000", source = "CesarArcCTA5", 
            sale_amount = "0.00000"), Affiliate = list(company = "Kendago")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "279", impressions = "51", unique_clicks = "0", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "0", 
            payout = "0.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "Cornelius Advisory Services")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "287", impressions = "0", unique_clicks = "20", 
            suspicious_clicks = "7", conversions = "12", 
            gross_clicks = "27", payout = "12.00000", revenue = "0.00000", 
            source = "", sale_amount = "0.00000"), Affiliate = list(
            company = "Cesarsway.com")), list(Stat = list(
            date = "2020-12-01", offer_id = "18", affiliate_id = "287", 
            impressions = "0", unique_clicks = "20", suspicious_clicks = "0", 
            conversions = "0", gross_clicks = "20", payout = "0.00000", 
            revenue = "0.00000", source = "CM-EM", sale_amount = "0.00000"), 
            Affiliate = list(company = "Cesarsway.com")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "287", impressions = "0", unique_clicks = "17", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "17", 
            payout = "0.00000", revenue = "0.00000", source = "cesarsway", 
            sale_amount = "0.00000"), Affiliate = list(company = "Cesarsway.com")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "287", impressions = "0", unique_clicks = "23", 
            suspicious_clicks = "2", conversions = "9", gross_clicks = "25", 
            payout = "8.00000", revenue = "0.00000", source = "cwemailindoctrination1", 
            sale_amount = "0.00000"), Affiliate = list(company = "Cesarsway.com")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "287", impressions = "0", unique_clicks = "25", 
            suspicious_clicks = "0", conversions = "4", gross_clicks = "26", 
            payout = "4.00000", revenue = "0.00000", source = "cwemailindoctrination2", 
            sale_amount = "0.00000"), Affiliate = list(company = "Cesarsway.com")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "289", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "partnerslogo", 
            sale_amount = "0.00000"), Affiliate = list(company = "WoofTrax")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "312", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "Benefit Hub")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "312", impressions = "0", unique_clicks = "11", 
            suspicious_clicks = "6", conversions = "22", 
            gross_clicks = "28", payout = "12.00000", revenue = "0.00000", 
            source = "EB.BenefitHub", sale_amount = "29.41000"), 
            Affiliate = list(company = "Benefit Hub")), list(
            Stat = list(date = "2020-12-01", offer_id = "18", 
              affiliate_id = "319", impressions = "0", unique_clicks = "1", 
              suspicious_clicks = "1", conversions = "0", 
              gross_clicks = "1", payout = "0.00000", revenue = "0.00000", 
              source = "EB.ARO", sale_amount = "0.00000"), 
            Affiliate = list(company = "ARO")), list(Stat = list(
            date = "2020-12-01", offer_id = "18", affiliate_id = "335", 
            impressions = "1", unique_clicks = "0", suspicious_clicks = "0", 
            conversions = "0", gross_clicks = "0", payout = "0.00000", 
            revenue = "0.00000", source = "", sale_amount = "0.00000"), 
            Affiliate = list(company = "Rich Kleiner")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "336", impressions = "0", unique_clicks = "2", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "2", 
            payout = "0.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "Spot the Rescue")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "343", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "2", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "EB.Insperity", 
            sale_amount = "0.00000"), Affiliate = list(company = "Insperity")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "348", impressions = "0", unique_clicks = "2", 
            suspicious_clicks = "0", conversions = "3", gross_clicks = "2", 
            payout = "2.00000", revenue = "0.00000", source = "EB.Abenity", 
            sale_amount = "38.00000"), Affiliate = list(company = "Abenity")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "367", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "1", conversions = "0", gross_clicks = "4", 
            payout = "0.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "Tarek")), 
        list(Stat = list(date = "2020-12-01", offer_id = "18", 
            affiliate_id = "373", impressions = "0", unique_clicks = "24", 
            suspicious_clicks = "6", conversions = "49", 
            gross_clicks = "34", payout = "126.00000", revenue = "0.00000", 
            source = "eb.perkspot", sale_amount = "130.42000"), 
            Affiliate = list(company = "PerkSpot")), list(
            Stat = list(date = "2020-12-01", offer_id = "18", 
              affiliate_id = "394", impressions = "0", unique_clicks = "1", 
              suspicious_clicks = "0", conversions = "4", 
              gross_clicks = "1", payout = "2.00000", revenue = "0.00000", 
              source = "", sale_amount = "61.30000"), Affiliate = list(
              company = "Vcheck Global")), list(Stat = list(
            date = "2020-12-01", offer_id = "21", affiliate_id = "298", 
            impressions = "0", unique_clicks = "32", suspicious_clicks = "5", 
            conversions = "20", gross_clicks = "34", payout = "160.00000", 
            revenue = "0.00000", source = "", sale_amount = "167.43000"), 
            Affiliate = list(company = "Benzinga")), list(
            Stat = list(date = "2020-12-01", offer_id = "21", 
              affiliate_id = "320", impressions = "0", unique_clicks = "135", 
              suspicious_clicks = "14", conversions = "74", 
              gross_clicks = "178", payout = "945.00000", 
              revenue = "0.00000", source = "ConsumerVoice", 
              sale_amount = "128.58000"), Affiliate = list(
              company = "Consumer Voice")), list(Stat = list(
            date = "2020-12-01", offer_id = "21", affiliate_id = "332", 
            impressions = "0", unique_clicks = "68", suspicious_clicks = "1", 
            conversions = "57", gross_clicks = "74", payout = "221.00000", 
            revenue = "0.00000", source = "", sale_amount = "157.04000"), 
            Affiliate = list(company = "Ad Practitioners, LLC")), 
        list(Stat = list(date = "2020-12-01", offer_id = "21", 
            affiliate_id = "357", impressions = "0", unique_clicks = "6", 
            suspicious_clicks = "1", conversions = "3", gross_clicks = "10", 
            payout = "18.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "Ad Practitioners, LLC  2")), 
        list(Stat = list(date = "2020-12-01", offer_id = "21", 
            affiliate_id = "358", impressions = "0", unique_clicks = "95", 
            suspicious_clicks = "6", conversions = "24", 
            gross_clicks = "117", payout = "308.75000", revenue = "0.00000", 
            source = "", sale_amount = "0.00000"), Affiliate = list(
            company = "Ad Practitioners, LLC 3")), list(Stat = list(
            date = "2020-12-01", offer_id = "21", affiliate_id = "385", 
            impressions = "0", unique_clicks = "5", suspicious_clicks = "1", 
            conversions = "4", gross_clicks = "5", payout = "20.00000", 
            revenue = "0.00000", source = "", sale_amount = "0.00000"), 
            Affiliate = list(company = "Apollo Interactive")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "292", impressions = "0", unique_clicks = "150", 
            suspicious_clicks = "19", conversions = "106", 
            gross_clicks = "177", payout = "1326.00000", 
            revenue = "0.00000", source = "", sale_amount = "310.47000"), 
            Affiliate = list(company = "Natural Intelligence")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "292", impressions = "0", unique_clicks = "0", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "3", 
            payout = "0.00000", revenue = "0.00000", source = "naturalint", 
            sale_amount = "0.00000"), Affiliate = list(company = "Natural Intelligence")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "295", impressions = "0", unique_clicks = "35", 
            suspicious_clicks = "2", conversions = "22", 
            gross_clicks = "41", payout = "315.00000", revenue = "0.00000", 
            source = "", sale_amount = "52.54000"), Affiliate = list(
            company = "Better Impression")), list(Stat = list(
            date = "2020-12-01", offer_id = "22", affiliate_id = "295", 
            impressions = "0", unique_clicks = "0", suspicious_clicks = "0", 
            conversions = "0", gross_clicks = "1", payout = "0.00000", 
            revenue = "0.00000", source = "betterim", sale_amount = "0.00000"), 
            Affiliate = list(company = "Better Impression")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "297", impressions = "0", unique_clicks = "312", 
            suspicious_clicks = "37", conversions = "229", 
            gross_clicks = "353", payout = "4460.00000", 
            revenue = "0.00000", source = "", sale_amount = "331.24000"), 
            Affiliate = list(company = "Search Vision")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "297", impressions = "0", unique_clicks = "0", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "searchvision", 
            sale_amount = "0.00000"), Affiliate = list(company = "Search Vision")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "301", impressions = "0", unique_clicks = "299", 
            suspicious_clicks = "8", conversions = "176", 
            gross_clicks = "402", payout = "2550.00000", 
            revenue = "0.00000", source = "", sale_amount = "516.91000"), 
            Affiliate = list(company = "Wickfire")), list(
            Stat = list(date = "2020-12-01", offer_id = "22", 
              affiliate_id = "301", impressions = "0", unique_clicks = "0", 
              suspicious_clicks = "0", conversions = "0", 
              gross_clicks = "1", payout = "0.00000", revenue = "0.00000", 
              source = "wickfire", sale_amount = "0.00000"), 
            Affiliate = list(company = "Wickfire")), list(
            Stat = list(date = "2020-12-01", offer_id = "22", 
              affiliate_id = "302", impressions = "0", unique_clicks = "1", 
              suspicious_clicks = "0", conversions = "0", 
              gross_clicks = "1", payout = "0.00000", revenue = "0.00000", 
              source = "", sale_amount = "0.00000"), Affiliate = list(
              company = "365 Pet Insurance")), list(Stat = list(
            date = "2020-12-01", offer_id = "22", affiliate_id = "302", 
            impressions = "0", unique_clicks = "5", suspicious_clicks = "0", 
            conversions = "4", gross_clicks = "5", payout = "36.00000", 
            revenue = "0.00000", source = "365", sale_amount = "0.00000"), 
            Affiliate = list(company = "365 Pet Insurance")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "317", impressions = "0", unique_clicks = "2", 
            suspicious_clicks = "0", conversions = "2", gross_clicks = "2", 
            payout = "4.00000", revenue = "0.00000", source = "eb.bennie", 
            sale_amount = "29.58000"), Affiliate = list(company = "Bennie")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "324", impressions = "0", unique_clicks = "233", 
            suspicious_clicks = "15", conversions = "113", 
            gross_clicks = "251", payout = "1887.00000", 
            revenue = "0.00000", source = "", sale_amount = "107.60000"), 
            Affiliate = list(company = "LendEDU")), list(
            Stat = list(date = "2020-12-01", offer_id = "22", 
              affiliate_id = "326", impressions = "0", unique_clicks = "43", 
              suspicious_clicks = "2", conversions = "4", 
              gross_clicks = "44", payout = "60.00000", revenue = "0.00000", 
              source = "", sale_amount = "0.00000"), Affiliate = list(
              company = "Consumer Affairs")), list(Stat = list(
            date = "2020-12-01", offer_id = "22", affiliate_id = "326", 
            impressions = "0", unique_clicks = "0", suspicious_clicks = "0", 
            conversions = "0", gross_clicks = "1", payout = "0.00000", 
            revenue = "0.00000", source = "consumeraffairs", 
            sale_amount = "0.00000"), Affiliate = list(company = "Consumer Affairs")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "331", impressions = "0", unique_clicks = "149", 
            suspicious_clicks = "12", conversions = "69", 
            gross_clicks = "185", payout = "1254.00000", 
            revenue = "0.00000", source = "", sale_amount = "142.65000"), 
            Affiliate = list(company = "How Stuff Works")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "331", impressions = "0", unique_clicks = "0", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "hsw", 
            sale_amount = "0.00000"), Affiliate = list(company = "How Stuff Works")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "334", impressions = "0", unique_clicks = "104", 
            suspicious_clicks = "5", conversions = "72", 
            gross_clicks = "116", payout = "700.00000", revenue = "0.00000", 
            source = "", sale_amount = "69.08000"), Affiliate = list(
            company = "Natural Intelligence MOBILE")), list(
            Stat = list(date = "2020-12-01", offer_id = "22", 
              affiliate_id = "351", impressions = "0", unique_clicks = "3", 
              suspicious_clicks = "0", conversions = "1", 
              gross_clicks = "3", payout = "10.00000", revenue = "0.00000", 
              source = "EB.AccessPerks", sale_amount = "0.00000"), 
            Affiliate = list(company = "Access Development")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "363", impressions = "0", unique_clicks = "444", 
            suspicious_clicks = "19", conversions = "228", 
            gross_clicks = "475", payout = "1135.00000", 
            revenue = "0.00000", source = "", sale_amount = "48.06000"), 
            Affiliate = list(company = "Natural Intelligence FB")), 
        list(Stat = list(date = "2020-12-01", offer_id = "22", 
            affiliate_id = "365", impressions = "0", unique_clicks = "17", 
            suspicious_clicks = "0", conversions = "4", gross_clicks = "17", 
            payout = "28.00000", revenue = "0.00000", source = "", 
            sale_amount = "0.00000"), Affiliate = list(company = "The Super Collies")), 
        list(Stat = list(date = "2020-12-01", offer_id = "23", 
            affiliate_id = "290", impressions = "0", unique_clicks = "18", 
            suspicious_clicks = "1", conversions = "9", gross_clicks = "20", 
            payout = "100.00000", revenue = "0.00000", source = "", 
            sale_amount = "34.39000"), Affiliate = list(company = "Pets Rule LLC")), 
        list(Stat = list(date = "2020-12-01", offer_id = "26", 
            affiliate_id = "267", impressions = "0", unique_clicks = "1", 
            suspicious_clicks = "0", conversions = "0", gross_clicks = "1", 
            payout = "0.00000", revenue = "0.00000", source = "spoteb", 
            sale_amount = "0.00000"), Affiliate = list(company = "Spot Employee Benefits"))), 
    dbSource = "branddb"), errors = list(), errorMessage = NULL))

when I do this piece of code I get this response

test <- rjson::fromJSON(httr::content(data, type = 'text'))
test


$response$data$data[[50]]
$response$data$data[[50]]$Stat
$response$data$data[[50]]$Stat$date
[1] "2020-12-01"

$response$data$data[[50]]$Stat$offer_id
[1] "26"

$response$data$data[[50]]$Stat$affiliate_id
[1] "267"

$response$data$data[[50]]$Stat$impressions
[1] "0"

$response$data$data[[50]]$Stat$unique_clicks
[1] "1"

$response$data$data[[50]]$Stat$suspicious_clicks
[1] "0"

$response$data$data[[50]]$Stat$conversions
[1] "0"

$response$data$data[[50]]$Stat$gross_clicks
[1] "1"

$response$data$data[[50]]$Stat$payout
[1] "0.00000"

$response$data$data[[50]]$Stat$revenue
[1] "0.00000"

$response$data$data[[50]]$Stat$source
[1] "spoteb"

$response$data$data[[50]]$Stat$sale_amount
[1] "0.00000"


$response$data$data[[50]]$Affiliate
$response$data$data[[50]]$Affiliate$company
[1] "Spot Employee Benefits"

if I do this piece of code I get this response

test2 <- test$response$data$data
lapply(test2, list2DF)

         Stat     Affiliate
1  2020-12-01 Pets Rule LLC
2          23 Pets Rule LLC
3         290 Pets Rule LLC
4           0 Pets Rule LLC
5          18 Pets Rule LLC
6           1 Pets Rule LLC
7           9 Pets Rule LLC
8          20 Pets Rule LLC
9   100.00000 Pets Rule LLC
10    0.00000 Pets Rule LLC
11            Pets Rule LLC
12   34.39000 Pets Rule LLC

[[50]]
         Stat              Affiliate
1  2020-12-01 Spot Employee Benefits
2          26 Spot Employee Benefits
3         267 Spot Employee Benefits
4           0 Spot Employee Benefits
5           1 Spot Employee Benefits
6           0 Spot Employee Benefits
7           0 Spot Employee Benefits
8           1 Spot Employee Benefits
9     0.00000 Spot Employee Benefits
10    0.00000 Spot Employee Benefits
11     spoteb Spot Employee Benefits
12    0.00000 Spot Employee Benefits

its closer to what I need but not there yet

if I run just run test2 I get this

[[50]]
[[50]]$Stat
[[50]]$Stat$date
[1] "2020-12-01"

[[50]]$Stat$offer_id
[1] "26"

[[50]]$Stat$affiliate_id
[1] "267"

[[50]]$Stat$impressions
[1] "0"

[[50]]$Stat$unique_clicks
[1] "1"

[[50]]$Stat$suspicious_clicks
[1] "0"

[[50]]$Stat$conversions
[1] "0"

[[50]]$Stat$gross_clicks
[1] "1"

[[50]]$Stat$payout
[1] "0.00000"

[[50]]$Stat$revenue
[1] "0.00000"

[[50]]$Stat$source
[1] "spoteb"

[[50]]$Stat$sale_amount
[1] "0.00000"


[[50]]$Affiliate
[[50]]$Affiliate$company
[1] "Spot Employee Benefits"

the ideal result would be

            stats columns                   affiliate column
date        offer_id affiliate_id......     Affiliate company
2020-12-01    26        267  .........       Spot Employee Benefits
.
.
.

Thanks

Juan Lozano
  • 635
  • 1
  • 6
  • 17
  • what is the `length(test)`? in my end it shows 2. – Onyambu Dec 03 '21 at 02:54
  • @Onyambu in my end it also shows 2 – Juan Lozano Dec 03 '21 at 03:16
  • Then `lapply(test, list2DF)` should work – Onyambu Dec 03 '21 at 04:31
  • @Onyambu I get this error following your last suggestion: Error in FUN(X[[i]], ...) : cannot replicate NULL to a non-zero length – Juan Lozano Dec 03 '21 at 04:51
  • Why did you delete your original question and are asking the same thing again? – deschen Dec 03 '21 at 05:28
  • @deschen still no luck, I tried your code with the 2 things I have, 'test' and 'data' and that where my response is stored, 'test' contains a list and 'data' contains the result I pasted at the end of the question, I tried this 'my_data <- jsonlite::fromJSON(test)$response$data$data' and also 'my_data <- jsonlite::fromJSON(data)$response$data$data' and I got this error, Error: Argument 'txt' must be a JSON string, URL or file – Juan Lozano Dec 03 '21 at 06:01
  • What is the exact name of the object you stored your response to, i.e. where do you have this long `{"request":{"Target":"Report","Format":"json","Service…` thing? – deschen Dec 03 '21 at 06:15
  • @deschen I copied and pasted that from the webpage response when I paste the URL in the browser, when I do it with code, I first get that object called data that you see at the top in the code, that is a list of 10, from there I run the code 'test <- rjson::fromJSON(httr::content(data, type = 'text'))' and I end with the code you see at the bottom. I am adding more edits to the question, your suggestion did got me a little bit closer to what I need but still not there, but to your question the long line is a copy from the webpage response, so that text exactly I dont have it. – Juan Lozano Dec 03 '21 at 06:40
  • I don't understand why the error. It works on my end – Onyambu Dec 03 '21 at 06:40
  • Can you post the result of `dput(content(data))`? – deschen Dec 03 '21 at 06:43
  • And you say my answer got you a bit closer. So waht did you have now as your „temporary“ result and what exactly should your ideal result look like? Not only describe, please show it as code what you‘d expext. – deschen Dec 03 '21 at 06:45
  • What if you don‘t run this last line with lapply? Justcthe line with test2? – deschen Dec 03 '21 at 06:52
  • And again. Show us how you exactly you want your result to look like? – deschen Dec 03 '21 at 06:54
  • And also: Can you post the result of dput(content(data))? – deschen Dec 03 '21 at 06:56
  • @deschen I added the ideal output, does not need to be exact but something I can later covert to that, right now I dont see column names and I also pasted the output you wanted – Juan Lozano Dec 03 '21 at 07:07
  • See my updated answer. – deschen Dec 03 '21 at 07:16

2 Answers2

2

So this is exactly the reason why a reproducible example with dput along with the expected outcome is ALWAYS helpful. You can do:

library(tidyverse)
library(rrapply)

response <- content(data)

my_data <- rrapply(response$response$data$data, how = "melt") %>%
  select(-L2) %>%
  pivot_wider(names_from = L3,
              values_from = value) %>%
  select(-L1)

which gives:

# A tibble: 50 x 13
   date       offer_id affiliate_id impressions unique_clicks suspicious_clicks conversions gross_clicks payout   revenue source                   sale_amount company    
   <chr>      <chr>    <chr>        <chr>       <chr>         <chr>             <chr>       <chr>        <chr>    <chr>   <chr>                    <chr>       <chr>      
 1 2020-12-01 18       2            0           1             0                 0           1            0.00000  0.00000 "leadday0quoteform"      0.00000     SPOT INTER~
 2 2020-12-01 18       29           0           1             0                 0           1            0.00000  0.00000 "FB3.14"                 0.00000     Spot Pet F~
 3 2020-12-01 18       30           0           5             0                 4           7            4.00000  0.00000 "CMBIO"                  0.00000     Spot Pet I~
 4 2020-12-01 18       41           0           1             0                 2           1            0.00000  0.00000 "CesarArc"               0.00000     Kendago    
 5 2020-12-01 18       41           0           2             0                 4           3            2.00000  0.00000 "CesarArcCTA5"           0.00000     Kendago    
 6 2020-12-01 18       279          51          0             0                 0           0            0.00000  0.00000 ""                       0.00000     Cornelius ~
 7 2020-12-01 18       287          0           20            7                 12          27           12.00000 0.00000 ""                       0.00000     Cesarsway.~
 8 2020-12-01 18       287          0           20            0                 0           20           0.00000  0.00000 "CM-EM"                  0.00000     Cesarsway.~
 9 2020-12-01 18       287          0           17            0                 0           17           0.00000  0.00000 "cesarsway"              0.00000     Cesarsway.~
10 2020-12-01 18       287          0           23            2                 9           25           8.00000  0.00000 "cwemailindoctrination1" 0.00000     Cesarsway.~
# ... with 40 more rows
deschen
  • 10,012
  • 3
  • 27
  • 50
0
library(tidyverse)
response <- GET(URL) %>%
    content()

tibble(response) %>%
  unnest_wider(response) ### purrr package

This has been the fastest route for me, although it's prone to errors, especially if there are NAs. Unless you explicitly define the column type of your tibble like this.

response %>% {
  tibble(
    Target = map_chr(Target, .default = NA),
    Format = map_chr(Format, .default = NA),
    Service = map_chr(Service, .default = NA),
    ... other columns here ...
    data = map(data, .default = NA),
    errors = map_lgl(errors, .default = NA),
    errorMessage = map(errorMessage, .default = NA),
  )
}
Jeff Parker
  • 1,809
  • 1
  • 18
  • 28