Home > Mobile >  Turn the json response of an httr GET into a dataframe in R
Turn the json response of an httr GET into a dataframe in R

Time:12-04

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

CodePudding user response:

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
  • Related