Skip to content

Instantly share code, notes, and snippets.

@ries9112
Created August 22, 2021 02:31
Show Gist options
  • Save ries9112/757329124c8af2176a97e548c9f3c0ef to your computer and use it in GitHub Desktop.
Save ries9112/757329124c8af2176a97e548c9f3c0ef to your computer and use it in GitHub Desktop.
R script that pulls all data for CV parcels, then collects data about their prices using opensea API
# 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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment