R script that pulls all data for CV parcels, then collects data about their prices using opensea API

CV_API_pull.R

# https://wiki.cryptovoxels.com/en/External-API

# Pull data from opensea using vox tokenIDs pulled in grt_vox_pull_tokenID.R
library(jsonlite)
library(httr)
library(tidyverse)

# example
url <- 'https://www.cryptovoxels.com/api/parcels.json'
# Get the data from the url
vox_prices <- content(GET(url))

# Construct dataset
vox = data.frame(x=NA)

vox$id = vox_prices$parcels[[1]]$id
# now remove "x" variable
vox = select(vox, -x)
# rest of columns
vox$height = vox_prices$parcels[[1]]$height
vox$area = vox_prices$parcels[[1]]$area
vox$address = vox_prices$parcels[[1]]$address
vox$suburb = vox_prices$parcels[[1]]$suburb
vox$island = vox_prices$parcels[[1]]$island
vox$name = vox_prices$parcels[[1]]$name
vox$colors = vox_prices$parcels[[1]]$colors
vox$geometry_type = vox_prices$parcels[[1]]$geometry$type
vox$coordinates_x1 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[1]][[1]]
vox$coordinates_y1 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[1]][[2]]
vox$coordinates_x2 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[2]][[1]]
vox$coordinates_y2 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[2]][[2]]
vox$coordinates_x3 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[3]][[1]]
vox$coordinates_y3 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[3]][[2]]
vox$coordinates_x4 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[4]][[1]]
vox$coordinates_y4 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[4]][[2]]
vox$coordinates_x5 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[5]][[1]]
vox$coordinates_y5 = vox_prices$parcels[[1]]$geometry$coordinates[[1]][[5]][[2]]
vox$distance = vox_prices$parcels[[1]]$distance
vox$price = vox_prices$parcels[[1]]$price
vox$owner = vox_prices$parcels[[1]]$owner
vox$owner_name = vox_prices$parcels[[1]]$owner_name
vox$hash = vox_prices$parcels[[1]]$hash
vox$x1 = vox_prices$parcels[[1]]$x1
vox$x2 = vox_prices$parcels[[1]]$x2
vox$y1 = vox_prices$parcels[[1]]$y1
vox$y2 = vox_prices$parcels[[1]]$y2
vox$z1 = vox_prices$parcels[[1]]$z1
vox$z2 = vox_prices$parcels[[1]]$z2
vox$ligthmap_status = vox_prices$parcels[[1]]$lightmap_status
vox$sandbox = vox_prices$parcels[[1]]$sandbox


for (i in 1:length(vox_prices$parcels)){
  
  print(i)
  
  # Construct dataset
  temp = data.frame(x=NA)
  
  temp$id = vox_prices$parcels[[i]]$id
  # now remove "x" variable
  temp = select(temp, -x)
  # rest of columns
  temp$height = vox_prices$parcels[[i]]$height
  temp$address = vox_prices$parcels[[i]]$address
  temp$suburb = vox_prices$parcels[[i]]$suburb
  temp$island = vox_prices$parcels[[i]]$island
  temp$name = vox_prices$parcels[[i]]$name
  temp$colors = vox_prices$parcels[[i]]$colors
  temp$geometry_type = vox_prices$parcels[[i]]$geometry$type
  temp$coordinates_x1 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[1]][[1]]
  temp$coordinates_y1 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[1]][[2]]
  temp$coordinates_x2 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[2]][[1]]
  temp$coordinates_y2 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[2]][[2]]
  temp$coordinates_x3 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[3]][[1]]
  temp$coordinates_y3 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[3]][[2]]
  temp$coordinates_x4 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[4]][[1]]
  temp$coordinates_y4 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[4]][[2]]
  temp$coordinates_x5 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[5]][[1]]
  temp$coordinates_y5 = vox_prices$parcels[[i]]$geometry$coordinates[[1]][[5]][[2]]
  temp$area = vox_prices$parcels[[i]]$area
  temp$distance = vox_prices$parcels[[i]]$distance
  temp$price = vox_prices$parcels[[i]]$price
  temp$owner = vox_prices$parcels[[i]]$owner
  temp$owner_name = vox_prices$parcels[[i]]$owner_name
  temp$hash = vox_prices$parcels[[i]]$hash
  temp$x1 = vox_prices$parcels[[i]]$x1
  temp$x2 = vox_prices$parcels[[i]]$x2
  temp$y1 = vox_prices$parcels[[i]]$y1
  temp$y2 = vox_prices$parcels[[i]]$y2
  temp$z1 = vox_prices$parcels[[i]]$z1
  temp$z2 = vox_prices$parcels[[i]]$z2
  temp$ligthmap_status = vox_prices$parcels[[i]]$lightmap_status
  temp$sandbox = vox_prices$parcels[[i]]$sandbox
  
  
  # Now append data to vox dataset
  vox = plyr::rbind.fill(vox, temp)
  
  
}


# Good, but issue with all 0 prices. 
#SO use the `id` field to pull data from opensea instead


# Now iterate over options and pull sale data from opensea

tokenid = vox$id
# disable scientific notation
options(scipen=9999)


for (i in 1:length(tokenid)){
  print(i)
  # build current url
  url <- paste0("https://api.opensea.io/api/v1/assets?asset_contract_addresses=0x79986af15539de2db9a5086382daeda917a9cf0c&token_ids=",tokenid[[i]])
  # get temp data
    current_id = content(GET(url))
    
    # if(purrr::!is_null(nrow(current_id))){
    
    # Any sale?
    # print(temp$assets[[1]]$last_sale$transaction$timestamp)
    
    # Check if any data returned
    #if("assets" %in% colnames(current_id)){
    
    # Construct dataset
    temp = data.frame(x=NA)
    
    temp$id = tokenid[[i]]
    # now remove "x" variable
    temp = select(temp, -x)
    # rest of columns
    temp$token_id = current_id$assets[[1]]$token_id
    temp$num_sales = current_id$assets[[1]]$num_sales
    temp$image_url = current_id$assets[[1]]$image_url
    temp$name = current_id$assets[[1]]$name
    temp$description = current_id$assets[[1]]$description
    temp$marketplace_link = current_id$assets[[1]]$external_link
    # temp$smart_contract = current_id$assets[[1]]$asset_contract$address
    # temp$smart_contract_type = current_id$assets[[1]]$asset_contract$asset_contract_type
    # temp$contract_created_date = current_id$assets[[1]]$asset_contract$created_date
    temp$owner_name = current_id$assets[[1]]$owner$user$username[[1]]
    temp$owner_address = current_id$assets[[1]]$owner$address
    # temp$owner_config = current_id$assets[[1]]$owner$config
    # temp$owner_discord_id = current_id$assets[[1]]$owner$discord_id
    temp$opensea_url = current_id$assets[[1]]$permalink
    
    # price info
    temp$payment_token = current_id$assets[[1]]$last_sale$payment_token$symbol[[1]]
    temp$usd_price = current_id$assets[[1]]$last_sale$payment_token$usd_price[[1]]
    temp$last_sale_total_price = current_id$assets[[1]]$last_sale$total_price[[1]]
    # transaction info
    temp$last_sale_from_username = current_id$assets[[1]]$last_sale$transaction$from_account$user$username[[1]]
    temp$last_sale_from_address = current_id$assets[[1]]$last_sale$transaction$from_account$address[[1]]
    temp$last_sale_timestamp = current_id$assets[[1]]$last_sale$transaction$timestamp[[1]]
    temp$last_sale_to_username = current_id$assets[[1]]$last_sale$transaction$to_account$user$username[[1]]
    temp$last_sale_to_address = current_id$assets[[1]]$last_sale$transaction$to_account$address[[1]]
    
    # More columns
    temp$last_sale_outcome = current_id$assets[[1]]$last_sale$event_type
    temp$last_sale_timestamp = current_id$assets[[1]]$last_sale$event_timestamp
    temp$last_sale_auction_type = current_id$assets[[1]]$last_sale$auction_type
    temp$last_sale_price = current_id$assets[[1]]$last_sale$total_price
    temp$current_rate_mana_eth = current_id$assets[[1]]$last_sale$payment_token$eth_price
    temp$current_rate_mana_usd = current_id$assets[[1]]$last_sale$payment_token$usd_price
    # More fields
    temp$last_sale_timestamp = current_id$assets[[1]]$last_sale$event_timestamp
    # temp$last_sale_block_hash = current_id$assets[[1]]$last_sale$transaction$block_hash
    temp$last_sale_block_number = current_id$assets[[1]]$last_sale$transaction$block_number
    temp$last_sale_from_address = current_id$assets[[1]]$last_sale$transaction$from_account$address
    temp$last_sale_to_address = current_id$assets[[1]]$last_sale$transaction$to_account$address
    temp$last_sale_transaction_id = current_id$assets[[1]]$last_sale$transaction$id
    temp$last_sale_transaction_timestamp = current_id$assets[[1]]$last_sale$transaction$timestamp
    temp$last_sale_transaction_hash = current_id$assets[[1]]$last_sale$transaction$transaction_hash
    temp$last_sale_transaction_index = current_id$assets[[1]]$last_sale$transaction$transaction_index
    temp$last_sale_transaction_created_timestamp = current_id$assets[[1]]$last_sale$created_date
    temp$last_sale_quantity = current_id$assets[[1]]$last_sale$quantity
    temp$top_bid = current_id$assets[[1]]$top_bid
    temp$listing_date = current_id$assets[[1]]$listing_date
    temp$is_presale = current_id$assets[[1]]$is_presale
    
    # Add values back in from CV API! Opensea doesn't have coordinates
    temp$address = vox[i,'address']
    temp$suburb = vox[i, 'suburb'] 
    temp$island = vox[i, 'island']
    temp$name = vox[i, 'name']
    temp$height = vox[i,'height']
    temp$area = vox[i,'area']
    temp$colors = vox[i, 'colors']
    temp$geometry_type = vox[i, 'geometry_type']
    temp$coordinates_x1 = vox[i, 'coordinates_x1']
    temp$coordinates_y1 = vox[i, 'coordinates_y1']
    temp$coordinates_x2 = vox[i, 'coordinates_x2']
    temp$coordinates_y2 = vox[i, 'coordinates_y2']
    temp$coordinates_x3 = vox[i, 'coordinates_x3']
    temp$coordinates_y3 = vox[i, 'coordinates_y3']
    temp$coordinates_x4 = vox[i, 'coordinates_x4']
    temp$coordinates_y4 = vox[i, 'coordinates_y4'] 
    temp$coordinates_x5 = vox[i, 'coordinates_x5']
    temp$coordinates_y5 = vox[i, 'coordinates_y5']
    # more info
    temp$x1 = vox[i, 'x1']
    temp$x2 = vox[i, 'x2']
    temp$y1 = vox[i, 'y1'] 
    temp$y2 = vox[i, 'y2']
    temp$z1 = vox[i, 'z1']
    temp$z2 = vox[i, 'z2']
    
    # Append results
    if (i == 1){
      cv = temp
    }
    if (i > 1){
    cv = plyr::rbind.fill(cv, temp)
    }
    
    # wait one second
    Sys.sleep(1.2)
    
    
    # check if price was missed
    if(!("last_sale_total_price" %in% colnames(temp))){
      print(paste('Missing price for:', tokenid[[i]]))
    }
}

# Adjust price in ETH
cv = mutate(cv, 
                  price_ETH = as.numeric(last_sale_total_price)/1000000000000000000,
                  # also add date
                  date = substr(last_sale_transaction_timestamp,1,10))
# calculate volume
cv = mutate(cv, volume = area * height)

# Calculate ETH price by unit of measurement
cv = cv %>% mutate(ETH_per_unit_volume = price_ETH/volume)


# Get rid of nulls
cv_sales = cv %>% filter(!is.na(last_sale_price))

# Distinct results
cv_sales = distinct(cv_sales, .keep_all=TRUE)


# Fix data
# remove usd_price - doesn't show actual price I want
# Fix data
# remove usd_price - doesn't show actual price I want
cv_sales = cv_sales %>% select(-usd_price, -last_sale_price, -current_rate_mana_eth, 
                               -last_sale_outcome, -is_presale, -current_rate_mana_usd, -id)


# Make price_ETH the first column
cv_sales = select(cv_sales, price_ETH, everything())

# Clean strings
cv_sales$name = str_replace_all(cv_sales$name, "[^[:alnum:]]", "")



# write data to csv
write.csv(cv_sales, 'cv_sales.csv')
# also full data
write.csv(cv, 'cv.csv')