Helium Hotspot Analysis

Setup Environment

Get the Cincinnati hotspots information from Helium. Data was download via API call. Install the packages for using API in R

#install.packages(c("httr", "jsonlite"))
#install.packages('tidyverse')
#install.packages("car")
#install.packages("geosphere")
#install.packages("maps")
#install.packages("reshape")
#install.packages("maptools")
#install.packages("leaflet")
#install.packages("magritt")
#install.packages("mapview")#install Mapview package to allow plotting on Cincinnati Map
#install.packages('corrgram') uncomment if first use the package
#install.packages('sqldf') uncomment if first use the package
#install.packages("reshape2")
#install.packages("hrbrthemes")
#install.packages("viridis")
#install.packages("dylyr")
library(mapview)
library(leaflet)
library(magrittr)
library(geosphere)
library(httr)
library(jsonlite)
library(tidyverse)
library(glue)
library(sqldf)
library(corrgram)
library(ggplot2)
library(car)
library(maps)
library(maptools)
library(stringr) ##labels long column lables
library(GGally)
library(lubridate)##load lubridate from tidyverse to add columns for dates groups to work off of.  
library(reshape2)
library(hrbrthemes)#create more descriptive plots.
library(viridis)
library(dplyr)
library(readr)

Get dataset via API calls

Summary for cincinnati

Find the city of Cincinnati location in the blockchain directory. Note that the string of characters is the city code. The city code was originally found using “GET https://api.helium.io/v1/cities/?search="Cincinnati””

cincinnati = GET("https://api.helium.io/v1/cities/Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz")

cincinnati_conv = fromJSON(rawToChar(cincinnati$content),flatten = TRUE) %>% 
  data.frame()
names(cincinnati_conv)

##  [1] "data.short_state"   "data.short_country" "data.short_city"   
##  [4] "data.online_count"  "data.offline_count" "data.long_state"   
##  [7] "data.long_country"  "data.long_city"     "data.hotspot_count"
## [10] "data.city_id"

str(cincinnati_conv)

## 'data.frame':    1 obs. of  10 variables:
##  $ data.short_state  : chr "OH"
##  $ data.short_country: chr "US"
##  $ data.short_city   : chr "Cincinnati"
##  $ data.online_count : int 361
##  $ data.offline_count: int 124
##  $ data.long_state   : chr "Ohio"
##  $ data.long_country : chr "United States"
##  $ data.long_city    : chr "Cincinnati"
##  $ data.hotspot_count: int 485
##  $ data.city_id      : chr "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz"

Get a list of Hotspots in cincinnati

This API call will return a list of all of the hotspots in Cincinnati. This list will include additional data about the hotspot, like location, name owner, etc. The timestamps are all in an ISO format (ISO 8601)

Cincinnati_hotspot = GET("https://api.helium.io/v1/cities/Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz/hotspots")


Cincinnati_hotspot_conv = fromJSON(rawToChar(Cincinnati_hotspot$content),flatten = TRUE)%>% 
  data.frame()

Cincinnati_hotspot_list<- 
  Cincinnati_hotspot_conv %>% 
  select( -c(data.status.listen_addrs)) # remove list under status
str(Cincinnati_hotspot_list)

## 'data.frame':    485 obs. of  30 variables:
##  $ data.lng                  : num  -84.3 -84.5 -84.5 -84.6 -84.3 ...
##  $ data.lat                  : num  39.1 39.1 39.2 39.1 39.3 ...
##  $ data.timestamp_added      : chr  "2022-02-23T00:09:01.000000Z" "2022-02-22T15:32:26.000000Z" "2022-02-21T20:55:33.000000Z" "2022-02-18T20:51:08.000000Z" ...
##  $ data.reward_scale         : num  0.543 0.379 0.541 0.539 0.539 ...
##  $ data.payer                : chr  "14sKWeeYWQWrBSnLGq79uRQqZyw3Ldi7oBdxbF6a54QboTNBXDL" "13v9iGhjvQUtVaZXcFFRCEbL1nPR4R8QJowBgMUcaGM2v1aV6mn" "13ENbEQPAvytjLnqavnbSAzurhGoCSNkGECMx7eHHDAfEaDirdY" "13v9iGhjvQUtVaZXcFFRCEbL1nPR4R8QJowBgMUcaGM2v1aV6mn" ...
##  $ data.owner                : chr  "13FkG7u3hFFGYPPdHrZTKKJEBcFdeAeeM9rp63uSoJLvtqPwcoN" "13mjr6DUmpAdwRadpgu3LdhRrPEJ9MGgXv8Yq4VLM5Cm6hhyjbY" "13FpUKSYmTCFbdRxdqvXrNacUtbLtyX9qDRKqLnYWUs6ktj92Tk" "14jf4Au9HaXeU56Ngoaaq4jbi6UnWhbTCR3DLC86gWMYoyaVshJ" ...
##  $ data.nonce                : int  1 1 1 2 1 1 1 1 1 1 ...
##  $ data.name                 : chr  "happy-bamboo-mule" "modern-saffron-crane" "curved-red-stork" "zany-strawberry-gibbon" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a930421fffff" "882a930f03fffff" "882a931897fffff" "882a930881fffff" ...
##  $ data.location             : chr  "8c2a930420143ff" "8c2a930f0234bff" "8c2a931896689ff" "8c2a930880c83ff" ...
##  $ data.last_poc_challenge   : int  1239701 1239526 1239718 1239537 1239832 1239730 1239630 NA NA 1239577 ...
##  $ data.last_change_block    : int  1239701 1239886 1239847 1239883 1239883 1239883 1239851 1228708 1228584 1239844 ...
##  $ data.gain                 : int  40 12 58 80 40 20 105 80 80 23 ...
##  $ data.elevation            : int  10 0 4 17 0 4 5 3 20 0 ...
##  $ data.block_added          : int  1238106 1237545 1236350 1231706 1230506 1230409 1228768 1228707 1228583 1228443 ...
##  $ data.block                : int  1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 ...
##  $ data.address              : chr  "11vCtvQL5NaCGL8Vyfk5RDVojVXFwLmU8BzBLxwei6G9dJWCUEu" "112gzVB4qYLgMHbftBS7hBxjCERiKAti2coE57NHzHhm4uQCwzjN" "112ckn3wfdzDkbBwY5vxr5Z56PU9r8V4Ld9NedZAU1L5w7DtLkHr" "11dCU4QDULWg52eu7FTUrCFca4MCruSgm5wFHHa7GtYFUgnF6s6" ...
##  $ data.status.timestamp     : chr  NA NA NA "2022-02-21T00:36:53.756000Z" ...
##  $ data.status.online        : chr  "online" "online" "online" "online" ...
##  $ data.status.height        : int  NA NA NA 1235030 1233943 1237529 1238925 NA NA 1237955 ...
##  $ data.geocode.short_street : chr  "Beechmont Ave" "Mc Farland St" "Winton Rd" "Pedretti Ave" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Beechmont Avenue" "Mc Farland Street" "Winton Road" "Pedretti Avenue" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...

Rewars for hotspots

Reward for the last five days

This API call will return the rewards granted to a hotspot from last five days.

rewardlist =list()
lst <- Cincinnati_hotspot_list$data.address

for (owner_addr in lst) {
  reward_url <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/rewards/sum")
  
  reward_res <- GET(reward_url, query = list(max_time=Sys.Date(), min_time=as.Date(Sys.Date())-5))
  
  
  reward_res_conv <-  fromJSON(rawToChar(reward_res$content),flatten = TRUE)%>% 
    data.frame() %>% 
    mutate(owner_address = owner_addr)
   if (status_code(reward_res) == 200){
  rewardlist[[owner_addr]] <- reward_res_conv}
else next
 
}
  reward_data=do.call(rbind,rewardlist)
  str(reward_data)

## 'data.frame':    479 obs. of  10 variables:
##  $ meta.min_time: chr  "2022-02-18T00:00:00Z" "2022-02-18T00:00:00Z" "2022-02-18T00:00:00Z" "2022-02-18T00:00:00Z" ...
##  $ meta.max_time: chr  "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" ...
##  $ data.total   : num  0 0.00255 0.01361 0.27648 0.01879 ...
##  $ data.sum     : int  0 255335 1361200 27647903 1878757 4901172 14216351 0 0 5127827 ...
##  $ data.stddev  : num  0 0 0.000957 0.007669 0.00025 ...
##  $ data.min     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median  : num  0 0.00255 0.0026 0.00661 0.00275 ...
##  $ data.max     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg     : num  0 0.00255 0.00272 0.00987 0.00268 ...
##  $ owner_address: chr  "11vCtvQL5NaCGL8Vyfk5RDVojVXFwLmU8BzBLxwei6G9dJWCUEu" "112gzVB4qYLgMHbftBS7hBxjCERiKAti2coE57NHzHhm4uQCwzjN" "112ckn3wfdzDkbBwY5vxr5Z56PU9r8V4Ld9NedZAU1L5w7DtLkHr" "11dCU4QDULWg52eu7FTUrCFca4MCruSgm5wFHHa7GtYFUgnF6s6" ...

  #View(reward_data)

All rewards since hotspot added

This API call will return the rewards granted since hotspot first added to current date.

rewardalllist =list()
ralst <- Cincinnati_hotspot_list$data.address
for (owner_addr in ralst) {
  rewardall_url <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/rewards/sum")
  
  rewardall_res <- GET(rewardall_url, query = list(max_time=Sys.Date(), min_time= Cincinnati_hotspot_list$data.timestamp_added[Cincinnati_hotspot_list$data.address==owner_addr]))
  
  
  rewardall_res_conv <-  fromJSON(rawToChar(rewardall_res$content),flatten = TRUE)%>% 
    data.frame() %>% 
    mutate(owner_address = owner_addr)
  if(ncol(rewardall_res_conv) ==10){
  rewardalllist[[owner_addr]] <- rewardall_res_conv
     
    }
   
else next
 
}
  reward_data_all=do.call(rbind,rewardalllist)
  str(reward_data_all)

## 'data.frame':    422 obs. of  10 variables:
##  $ meta.min_time: chr  "2022-02-22T15:32:00Z" "2022-02-21T20:55:00Z" "2022-02-18T20:51:00Z" "2022-02-18T01:43:00Z" ...
##  $ meta.max_time: chr  "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" ...
##  $ data.total   : num  0.00255 0.01361 0.27648 0.01879 0.04901 ...
##  $ data.sum     : num  255335 1361200 27647903 1878757 4901172 ...
##  $ data.stddev  : num  0 0.000957 0.007669 0.00025 0.000739 ...
##  $ data.min     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median  : num  0.00255 0.0026 0.00661 0.00275 0.00274 ...
##  $ data.max     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg     : num  0.00255 0.00272 0.00987 0.00268 0.00288 ...
##  $ owner_address: chr  "112gzVB4qYLgMHbftBS7hBxjCERiKAti2coE57NHzHhm4uQCwzjN" "112ckn3wfdzDkbBwY5vxr5Z56PU9r8V4Ld9NedZAU1L5w7DtLkHr" "11dCU4QDULWg52eu7FTUrCFca4MCruSgm5wFHHa7GtYFUgnF6s6" "112oba4vuffULgTvG2y445jfhQ8fv5z2YprYfqqqq4N56YPD4roL" ...

Basic hotspot analysis

Additions Over Time

1.Add year

Breaking out Year from data.timestamp_added. Legend based off that column is too long on the map. Year allows manageable comparison over time.

Cinci_date_break_down <- Cincinnati_hotspot_list %>% 
  mutate(YR_added = year(data.timestamp_added)
  )

2.Visualation on hotspots adding during the year

use mapview to display hotspots in Cincinnati and they year they were added. Signicant amount added during the year 2021

mapview(Cinci_date_break_down, xcol = "data.lng", ycol = "data.lat", zcol = 'YR_added', crs = 4269, grid = FALSE)

Comparision of online and offline

1.Add year and quarter to prepare data

create new object to add a new column of year and quarter.

Cinci_date_break_down <- Cinci_date_break_down %>% 
  mutate(YRQTR_added = quarter(data.timestamp_added, type = "year.quarter"))
on_vs_off <- Cinci_date_break_down %>%
  select(data.timestamp_added,  data.name, data.status.timestamp, data.status.online, YRQTR_added)
On_vs_off_graph <- on_vs_off %>% select(data.status.online, YRQTR_added) 

2.graph of how many are online vs how many are offline

On_vs_off_graph %>% ggplot() +
  geom_bar(aes(x = YRQTR_added)) +
  facet_grid('data.status.online') 

## Hotspots not getting challenges ### 1.creating new object to pull certain variables for looking at challenges

challenges <- Cinci_date_break_down %>% 
  select(data.lng, data.lat, data.last_poc_challenge, YRQTR_added)

2.chalenges with NA are interpreted as not having any challenges. Replacing NA with 0

na_total <- is.na(challenges$data.last_poc_challenge)     
head(na_total)

## [1] FALSE FALSE FALSE FALSE FALSE FALSE

challenges$data.last_poc_challenge[na_total] <- 0
head(challenges$data.last_poc_challenge)

## [1] 1239701 1239526 1239718 1239537 1239832 1239730

3.mutating challenges to have a new column challenge. New column is set to have 0 set to no, as in not challended, and >0 as yes, as in it was challenged.

challenges <- challenges %>%
  mutate(Challenge = ifelse(data.last_poc_challenge == 0, "no", "yes"))

4.Map to review units that did not have challenges

mapview(challenges, xcol = "data.lng", ycol = "data.lat", zcol = 'Challenge', crs = 4269, grid = FALSE)

5.table showing total not challenged vs challenged

challenge_table <- challenges %>% select(Challenge)
table(challenge_table)

## challenge_table
##  no yes 
##  36 449

6.capturing all the observations that were not challenged

not_challenged <- challenge_table %>%
  filter(Challenge == "no")

7.capturing all the observations that were challenged

yes_challenge <- challenge_table %>%
  filter(Challenge == "yes")

8.Percent of units that were not challenged overall

Per_not_challenged <- nrow(not_challenged) / (nrow(yes_challenge) + nrow(not_challenged))
glue(round(Per_not_challenged * 100, 4), "% were not challenged")

## 7.4227% were not challenged

9.Percent of units that were challenged overall

Per_challenged <- nrow(yes_challenge) / (nrow(yes_challenge) + nrow(not_challenged))
glue(round(Per_challenged * 100, 4), "% were challenged")

## 92.5773% were challenged

10.New Data filtering down to units not challenged

chal_time <- challenges %>% 
  select(YRQTR_added, Challenge) %>% 
  filter(Challenge == "no") %>% 
  group_by(YRQTR_added) %>% 
  summarise(count = n())

head(chal_time)

## # A tibble: 4 x 2
##   YRQTR_added count
##         <dbl> <int>
## 1       2021.     1
## 2       2021.    12
## 3       2021.     8
## 4       2022.    15

11.New data filtered down to units that were challenged.

chal_time2 <- challenges %>% 
  select(YRQTR_added, Challenge) %>% 
  filter(Challenge == "yes") %>% 
  group_by(YRQTR_added) %>% 
  summarise(count = n())

12.Graph showing challenged vs unchallenged

plot(chal_time2, type="l", col="red")
lines(chal_time, col="green")

### 13.Joining the challenged and unchallenged to display in table

chal_join <- chal_time %>% full_join(chal_time2, by = "YRQTR_added") %>% 
  arrange(YRQTR_added)

14.changing the names of the joined table to display

names(chal_join) <- c("YR-QTR", "not chal", "chal")

chal_join

## # A tibble: 10 x 3
##    `YR-QTR` `not chal`  chal
##       <dbl>      <int> <int>
##  1    2019.         NA     4
##  2    2020.         NA     1
##  3    2020.         NA     3
##  4    2020.         NA     4
##  5    2020.         NA     4
##  6    2021.          1     9
##  7    2021.         NA    44
##  8    2021.         12   133
##  9    2021.          8   172
## 10    2022.         15    75

Multiple owners

1.selecting out owner and name data and saving it to owners

owners <- Cinci_date_break_down %>%
  select(data.owner, data.name) %>%
  group_by(data.owner) %>%
  summarise(count = n()) %>% 
  arrange(desc(count))

2.total number of rows in owners

nrow(owners)

## [1] 278

3.displaying first 10 rows of owners showing the ones that own the most

head(owners) 

## # A tibble: 6 x 2
##   data.owner                                          count
##   <chr>                                               <int>
## 1 13WxqbFEcXJAJ8N3iHRxaJExzBmstyFr9FwEamnNDjMiMmqi2ah    19
## 2 13qVDLBEK4ua88fiTMvXXnaioM8PqUNUpg5xmxWXV1pqaDdA9u4    15
## 3 14jf4Au9HaXeU56Ngoaaq4jbi6UnWhbTCR3DLC86gWMYoyaVshJ    14
## 4 13yqXGWMpcoifTTYBnmLiwYQUxTMsXbNMNYgUqwxUYB3kMSP185    12
## 5 13yq34cc8RpvdMeUr9QvCWQ6Qbrr3v8amee32iv9xQy1MA8eo49    10
## 6 13BFAEUbkfQ4EMAVjrj1NQtNgQ6NCpbPaSvEMtWotpVRyhK2cTH     9

4.creating multi owners to identify people that own more than one

multi_owners <- owners %>% 
  filter(count > 1)

5.number of owners that own more than 1

nrow(multi_owners)

## [1] 78

6.displaying summary of multi owners. Mean number of units owned is 3.675 with a max of 19

summary(multi_owners)

##   data.owner            count       
##  Length:78          Min.   : 2.000  
##  Class :character   1st Qu.: 2.000  
##  Mode  :character   Median : 2.000  
##                     Mean   : 3.654  
##                     3rd Qu.: 3.750  
##                     Max.   :19.000

10.percentage of owners that own more than one unit

glue(round(nrow(multi_owners) / nrow(owners), 4) * 100, " Percent of owners with more than one")

## 28.06 Percent of owners with more than one

11.percent of owners that own multiple units that own more than just 2

glue(round(nrow(filter(multi_owners, count > 2)) / nrow(multi_owners), 4) * 100, " Percent of owners with multiple units that own more than 2")

## 43.59 Percent of owners with multiple units that own more than 2

Rewards analysis

1.Create a plot of the rate that hotspots have been added in Cincinnati

cincinnati_hotspot_cumulative <- Cincinnati_hotspot_list %>%
                                   arrange(data.timestamp_added) %>%
                                      mutate(hotspot_count = 1) %>%
                                        mutate(hot_spot_csum=cumsum(hotspot_count)) %>%
                                            separate(data.timestamp_added, 
                                                sep = "T", 
                                                 into = c("data.timestamp_added", "b"))

cincinnati_hotspot_cumulative$data.timestamp_added <- as.Date(cincinnati_hotspot_cumulative$data.timestamp_added, format = "%Y-%m-%d")


ggplot(cincinnati_hotspot_cumulative, aes(x=data.timestamp_added, y=hot_spot_csum)) + geom_point()

2.PLOT ALL REWARDS VERSUS HOTSPOT START TIME

reward_data_all_con <- mutate(reward_data_all, all_rewards = as.numeric(data.total))
cincinnati_hotspot_cumulative <-mutate(cincinnati_hotspot_cumulative, address = as.character(data.address))

cincinnati_hotspot_cumulative2 <- sqldf('select 
                       a.*
                      ,b.all_rewards
                      from cincinnati_hotspot_cumulative a inner join reward_data_all_con b on a.address = b.owner_address
                      ')


str(cincinnati_hotspot_cumulative2)

## 'data.frame':    422 obs. of  35 variables:
##  $ data.lng                  : num  -84.4 -84.4 -84.4 -84.4 -84.4 ...
##  $ data.lat                  : num  39.1 39.3 39.2 39.1 39.1 ...
##  $ data.timestamp_added      : Date, format: "2019-10-31" "2019-12-23" ...
##  $ b                         : chr  "14:23:48.000000Z" "19:28:01.000000Z" "16:27:30.000000Z" "13:26:49.000000Z" ...
##  $ data.reward_scale         : num  0.5 1 0.436 0.217 0.218 ...
##  $ data.payer                : chr  "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" ...
##  $ data.owner                : chr  "14a6rPCaLupjbkebc3vWu6rbkyhceodEs4KGGJaHdfMR37Ymyey" "137bHtLnVukLBtYqM8eBfHTU88kgFsCVzSBhskGJ3QZqvNnwmY4" "14a6rPCaLupjbkebc3vWu6rbkyhceodEs4KGGJaHdfMR37Ymyey" "13VnNoPmNef8FarH3oZipw8hKeefFcutTuAo8sFoM4FCkKXQ6vS" ...
##  $ data.nonce                : int  4 1 10 1 1 1 3 1 1 1 ...
##  $ data.name                 : chr  "flat-strawberry-tardigrade" "bright-holographic-squirrel" "large-graphite-beetle" "tall-lipstick-stallion" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a930507fffff" "882a93035dfffff" "882a9302ebfffff" "882a9300adfffff" ...
##  $ data.location             : chr  "8c2a930507b47ff" "8c2a93035c2e7ff" "8c2a9302ead93ff" "8c2a9300ad1e1ff" ...
##  $ data.last_poc_challenge   : int  1030478 384825 1239523 1239508 1239557 1239601 1239429 1239405 1239544 1016282 ...
##  $ data.last_change_block    : int  1078682 1019454 1239628 1239851 1239755 1239606 1239819 1239405 1239889 1019454 ...
##  $ data.gain                 : int  12 12 12 12 12 12 40 12 12 12 ...
##  $ data.elevation            : int  0 0 10 0 0 0 6 0 0 0 ...
##  $ data.block_added          : int  100549 147080 394130 493419 519347 542098 747476 750618 752007 752453 ...
##  $ data.block                : int  1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 ...
##  $ data.address              : chr  "112fRz5v9QDj9ruCKNmKLL2u8R4Ln3aD6fVUMLjzHCG6kMD5vjpE" "112wGDfDkYHcnKFNSAzUwvht4Nj19pkPuuFBoiRMfDBZftjBbEzp" "112KDeRRbq8kZcSmfEUKpZ1wWGXprDQ7xc4A4uuwrLTkD1cH3Mub" "116ffxD47VkSawx8wFeJVsdHizLePgNZoNB3CcNPnT5K85fGT1w" ...
##  $ data.status.timestamp     : chr  "2021-10-30T07:51:51.193000Z" "2022-02-16T21:35:58.479000Z" "2022-02-21T12:51:34.890000Z" "2022-02-22T11:37:57.355000Z" ...
##  $ data.status.online        : chr  "offline" "offline" "online" "online" ...
##  $ data.status.height        : int  1074162 1225765 1235824 1237286 1235215 1236269 1231642 1238004 1232203 1014463 ...
##  $ data.geocode.short_street : chr  "Beacon St" "Evendale Commons Dr" "Euclid Ave" "Portsmouth Ave" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Beacon Street" "Evendale Commons Drive" "Euclid Avenue" "Portsmouth Avenue" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...
##  $ hotspot_count             : num  1 1 1 1 1 1 1 1 1 1 ...
##  $ hot_spot_csum             : num  2 4 8 10 12 14 18 19 20 21 ...
##  $ address                   : chr  "112fRz5v9QDj9ruCKNmKLL2u8R4Ln3aD6fVUMLjzHCG6kMD5vjpE" "112wGDfDkYHcnKFNSAzUwvht4Nj19pkPuuFBoiRMfDBZftjBbEzp" "112KDeRRbq8kZcSmfEUKpZ1wWGXprDQ7xc4A4uuwrLTkD1cH3Mub" "116ffxD47VkSawx8wFeJVsdHizLePgNZoNB3CcNPnT5K85fGT1w" ...
##  $ all_rewards               : num  3878.7 49.8 377.4 335.9 286.7 ...

cincinnati_hotspot_cumulative3 <- cincinnati_hotspot_cumulative2 %>%
                      filter(!is.na(all_rewards)) %>%
                          filter(all_rewards > 50)
                                        
str(cincinnati_hotspot_cumulative3)

## 'data.frame':    43 obs. of  35 variables:
##  $ data.lng                  : num  -84.4 -84.4 -84.4 -84.4 -84.6 ...
##  $ data.lat                  : num  39.1 39.2 39.1 39.1 39.3 ...
##  $ data.timestamp_added      : Date, format: "2019-10-31" "2020-06-30" ...
##  $ b                         : chr  "14:23:48.000000Z" "16:27:30.000000Z" "13:26:49.000000Z" "18:05:43.000000Z" ...
##  $ data.reward_scale         : num  0.5 0.436 0.217 0.218 0.538 ...
##  $ data.payer                : chr  "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" "14fzfjFcHpDR1rTH8BNPvSi5dKBbgxaDnmsVPbCjuq9ENjpZbxh" ...
##  $ data.owner                : chr  "14a6rPCaLupjbkebc3vWu6rbkyhceodEs4KGGJaHdfMR37Ymyey" "14a6rPCaLupjbkebc3vWu6rbkyhceodEs4KGGJaHdfMR37Ymyey" "13VnNoPmNef8FarH3oZipw8hKeefFcutTuAo8sFoM4FCkKXQ6vS" "13VnNoPmNef8FarH3oZipw8hKeefFcutTuAo8sFoM4FCkKXQ6vS" ...
##  $ data.nonce                : int  4 10 1 1 1 3 1 1 1 2 ...
##  $ data.name                 : chr  "flat-strawberry-tardigrade" "large-graphite-beetle" "tall-lipstick-stallion" "straight-vanilla-jellyfish" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a930507fffff" "882a9302ebfffff" "882a9300adfffff" "882a9300adfffff" ...
##  $ data.location             : chr  "8c2a930507b47ff" "8c2a9302ead93ff" "8c2a9300ad1e1ff" "8c2a9300ad087ff" ...
##  $ data.last_poc_challenge   : int  1030478 1239523 1239508 1239557 1239601 1239429 1239405 1239544 1016282 1239723 ...
##  $ data.last_change_block    : int  1078682 1239628 1239851 1239755 1239606 1239819 1239405 1239889 1019454 1239883 ...
##  $ data.gain                 : int  12 12 12 12 12 40 12 12 12 58 ...
##  $ data.elevation            : int  0 10 0 0 0 6 0 0 0 9 ...
##  $ data.block_added          : int  100549 394130 493419 519347 542098 747476 750618 752007 752453 752618 ...
##  $ data.block                : int  1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 ...
##  $ data.address              : chr  "112fRz5v9QDj9ruCKNmKLL2u8R4Ln3aD6fVUMLjzHCG6kMD5vjpE" "112KDeRRbq8kZcSmfEUKpZ1wWGXprDQ7xc4A4uuwrLTkD1cH3Mub" "116ffxD47VkSawx8wFeJVsdHizLePgNZoNB3CcNPnT5K85fGT1w" "112vtLBk8KB7Z9PQR9YsbpiZX5edbNnmdxwATMgPwijV1Rjw73Rs" ...
##  $ data.status.timestamp     : chr  "2021-10-30T07:51:51.193000Z" "2022-02-21T12:51:34.890000Z" "2022-02-22T11:37:57.355000Z" "2022-02-21T03:32:24.382000Z" ...
##  $ data.status.online        : chr  "offline" "online" "online" "online" ...
##  $ data.status.height        : int  1074162 1235824 1237286 1235215 1236269 1231642 1238004 1232203 1014463 1236323 ...
##  $ data.geocode.short_street : chr  "Beacon St" "Euclid Ave" "Portsmouth Ave" "Victoria Ave" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Beacon Street" "Euclid Avenue" "Portsmouth Avenue" "Victoria Avenue" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...
##  $ hotspot_count             : num  1 1 1 1 1 1 1 1 1 1 ...
##  $ hot_spot_csum             : num  2 8 10 12 14 18 19 20 21 22 ...
##  $ address                   : chr  "112fRz5v9QDj9ruCKNmKLL2u8R4Ln3aD6fVUMLjzHCG6kMD5vjpE" "112KDeRRbq8kZcSmfEUKpZ1wWGXprDQ7xc4A4uuwrLTkD1cH3Mub" "116ffxD47VkSawx8wFeJVsdHizLePgNZoNB3CcNPnT5K85fGT1w" "112vtLBk8KB7Z9PQR9YsbpiZX5edbNnmdxwATMgPwijV1Rjw73Rs" ...
##  $ all_rewards               : num  3879 377 336 287 387 ...

ggplot(cincinnati_hotspot_cumulative3, aes(x=data.timestamp_added, y=hot_spot_csum, color=all_rewards, size =all_rewards))+
  geom_point() +
  scale_colour_gradient(low = "steelblue", high = "yellow") +
  theme_bw() +
    labs(y="total rewards over life of hotspot ($HNT)", x="hotspot initiation date", subtitle="Scatter plot of cumulative hotspots in cincinnati filtered to greater than 50 $HNT earned with color scaling to earnings")

### 3.PLOT ALL REWARDS AGAINST LOCATION

cincinnati_hotspot_cumulative4 <- cincinnati_hotspot_cumulative2 %>%
  filter(!is.na(all_rewards)) %>%
  filter(all_rewards > 10)


 mapview(cincinnati_hotspot_cumulative2, xcol = "data.lng", ycol = "data.lat", cex = 'all_rewards', zcol = 'all_rewards', crs = 4269, grid = FALSE) 

Reward for three month interval

Get reward data

1.RUN LOOPS OVER TIME

rewardlistopt =list()
lstopt <- Cincinnati_hotspot_list$data.address
lst2opt <- Cincinnati_hotspot_list$data.timestamp_added

2.February 2022

Cincinnati_hotspot_list_feb22 <- Cincinnati_hotspot_list %>% 
  filter(data.timestamp_added < "2022-02-01")
rewardlistopt =list()
lstopt <- Cincinnati_hotspot_list_feb22$data.address

for (owner_addr in lstopt) {
  reward_urlopt <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/rewards/sum")
  reward_resopt <- GET(reward_urlopt, query = list(max_time="2022-02-07", min_time="2022-02-01"))
  
  
  reward_res_convopt <-  fromJSON(rawToChar(reward_resopt$content),flatten = TRUE)%>% 
    data.frame() %>% 
    mutate(owner_address = owner_addr)
   if (status_code(reward_resopt) == 200){
  #if(ncol(reward_res_convopt)==10){
  rewardlistopt[[owner_addr]] <- reward_res_convopt
}
else next
 
}
 
reward_data_feb22=do.call(rbind,rewardlistopt)
  str(reward_data_feb22)

## 'data.frame':    308 obs. of  10 variables:
##  $ meta.min_time: chr  "2022-02-01T00:00:00Z" "2022-02-01T00:00:00Z" "2022-02-01T00:00:00Z" "2022-02-01T00:00:00Z" ...
##  $ meta.max_time: chr  "2022-02-07T00:00:00Z" "2022-02-07T00:00:00Z" "2022-02-07T00:00:00Z" "2022-02-07T00:00:00Z" ...
##  $ data.total   : num  0.6515 1.6309 0.1301 0 0.0467 ...
##  $ data.sum     : int  65151932 163085508 13007053 0 4667388 0 0 175498198 77944796 11197950 ...
##  $ data.stddev  : num  0.00854 0.00597 0.0049 0 0.00672 ...
##  $ data.min     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median  : num  0.01044 0.0069 0.00709 0 0.00381 ...
##  $ data.max     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg     : num  0.01229 0.00788 0.00765 0 0.00778 ...
##  $ owner_address: chr  "112QRGp1hyNv2PVAa4jgDQj6uhqbKYTsYgyNPtov1PCF7ZYFRYP4" "112vk8YczZH3YKwGq8YX6SmFK37ynzqxQ7cc2fKsEDgXiFwRum49" "11PZWJh33ycHMEJ5W2QdxQ8Hin9WbvwhQ2kTn6SyiULEzhduEu4" "112RTQMaCFwdpLfUdxtXPjTnEa2rrjAj4ZRmRz3dfkEUJrs5kybm" ...

3.November 2021

  Cincinnati_hotspot_list_nov21 <- Cincinnati_hotspot_list %>% 
    filter(data.timestamp_added < "2021-11-01")
  rewardlistopt =list()
  lstopt <- Cincinnati_hotspot_list_nov21$data.address
  
  for (owner_addr in lstopt) {
    reward_urlopt <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/rewards/sum")
    reward_resopt <- GET(reward_urlopt, query = list(max_time="2021-11-07", min_time="2021-11-01"))
    
    
    reward_res_convopt <-  fromJSON(rawToChar(reward_resopt$content),flatten = TRUE)%>% 
      data.frame() %>% 
      mutate(owner_address = owner_addr)
    if (status_code(reward_resopt) == 200){
  #if(ncol(reward_res_convopt)==10){
  rewardlistopt[[owner_addr]] <- reward_res_convopt
}
else next
    
  }
  reward_data_nov21=do.call(rbind,rewardlistopt)
  str(reward_data_nov21)

## 'data.frame':    235 obs. of  10 variables:
##  $ meta.min_time: chr  "2021-11-01T00:00:00Z" "2021-11-01T00:00:00Z" "2021-11-01T00:00:00Z" "2021-11-01T00:00:00Z" ...
##  $ meta.max_time: chr  "2021-11-07T00:00:00Z" "2021-11-07T00:00:00Z" "2021-11-07T00:00:00Z" "2021-11-07T00:00:00Z" ...
##  $ data.total   : num  0.465 0.0208 0.2325 0.3872 0.093 ...
##  $ data.sum     : int  46498023 2081491 23247149 38724145 9298048 222921294 51222160 108989238 61111680 0 ...
##  $ data.stddev  : num  0.037729 0.000582 0.038296 0.017534 0.019953 ...
##  $ data.min     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median  : num  0.02478 0.00686 0.00664 0.01449 0.0064 ...
##  $ data.max     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg     : num  0.03577 0.00694 0.02583 0.01936 0.01162 ...
##  $ owner_address: chr  "118upDKxZyXdmUwr6oJJrga3e6yakcHNExrvaw7a91YRdCUnkNV" "11kaXC3vERSPHXy5AMFubUJuuPf1zq8B5wRHGgJZo733uYsyrA4" "11k7x8LUwi2TXMzcBt35bnwYx3amDEyU5YQsoJqP5wBBAh1i5mF" "11bofd6MEdbauuFMS82sxezYSwHNQ1yjbKwkmWfzbfkGESXtL2U" ...

4.August 2021

  Cincinnati_hotspot_list_aug21 <- Cincinnati_hotspot_list %>% 
    filter(data.timestamp_added < "2021-08-01")
  rewardlistopt =list()
  lstopt <- Cincinnati_hotspot_list_aug21$data.address
  
  for (owner_addr in lstopt) {
    reward_urlopt <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/rewards/sum")
    reward_resopt <- GET(reward_urlopt, query = list(max_time="2021-08-07", min_time="2021-08-01"))
    
    
    reward_res_convopt <-  fromJSON(rawToChar(reward_resopt$content),flatten = TRUE)%>% 
      data.frame() %>% 
      mutate(owner_address = owner_addr)
   if (status_code(reward_resopt) == 200){
  #
  rewardlistopt[[owner_addr]] <- reward_res_convopt
}
else next
    
  }
  reward_data_aug21=do.call(rbind,rewardlistopt)
  str(reward_data_aug21)

## 'data.frame':    97 obs. of  10 variables:
##  $ meta.min_time: chr  "2021-08-01T00:00:00Z" "2021-08-01T00:00:00Z" "2021-08-01T00:00:00Z" "2021-08-01T00:00:00Z" ...
##  $ meta.max_time: chr  "2021-08-07T00:00:00Z" "2021-08-07T00:00:00Z" "2021-08-07T00:00:00Z" "2021-08-07T00:00:00Z" ...
##  $ data.total   : num  4.587 0.973 4.475 3.826 0.348 ...
##  $ data.sum     : int  458705346 97274138 447458643 382589495 34763000 0 240987734 72370918 0 11004567 ...
##  $ data.stddev  : num  0.1162 0.0807 0.0784 0.1129 0.151 ...
##  $ data.min     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median  : num  0.109 0.0317 0.0717 0.0923 0.0117 ...
##  $ data.max     : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg     : num  0.1349 0.0695 0.0932 0.1196 0.0869 ...
##  $ owner_address: chr  "11p4jU5j4sPRcFxBe2DsmqUDhrE2ATMferJ4uAXUzkFavGCm5Wo" "11uQLNimNrKkWRHm6wpFU9LELu3WF98fZbvmZNfbLZjy3XJ25TA" "11Giu8RCE5ExRzaetYHp7KhSjJBVAC5wjbovgm24XrBjmuoKPfA" "112j2TRjLdfTKyidabxstQb9tVnpAkFnEVsCssrUuLFA4yEFEP4Z" ...

Perform joins to merge the data into a single data file

rename columns that will be joined since they were originally all the same name.

  names(reward_data_feb22)[names(reward_data_feb22) == 'data.total'] <- 'total_feb22'
  names(reward_data_nov21)[names(reward_data_nov21) == 'data.total'] <- 'total_nov21'
  names(reward_data_aug21)[names(reward_data_aug21) == 'data.total'] <- 'total_aug21'

  
  
  ####perform left joins one at a time
  time_rewards_total <-reward_data_feb22 %>%
                          select(total_feb22, owner_address)
  

  time_rewards_total <- sqldf('select 
                       a.*
                      ,b.total_nov21 
                      from time_rewards_total a left join reward_data_nov21 b on a.owner_address = b.owner_address
                      ')

  time_rewards_total <- sqldf('select 
                       a.*
                      ,b.total_aug21 
                      from time_rewards_total a left join reward_data_aug21 b on a.owner_address = b.owner_address
                      ')

Create a boxplot of the results.

1.First convert the values from characters to numeric

time_rewards_total_num <- mutate(time_rewards_total, total_feb22 = as.numeric(total_feb22), 
                                                total_nov21 = as.numeric(total_nov21),
                                                total_aug21 = as.numeric(total_aug21)) 

2.Perform a melt to stack all of the data into a single column that includes the name of the source next to it.

stacked_rewards <- melt(time_rewards_total,id.vars='owner_address', value.=c('reward_data_feb22','reward_data_nov21','reward_data_aug21', 'reward_data_may21'), na.rm = TRUE)

3.Creating Plot

stacked_rewards %>%
  ggplot( aes(x=variable, y=value, fill=variable)) +
  geom_boxplot()  +
  scale_fill_viridis(discrete = TRUE, alpha=0.6) +
  geom_jitter(color="black", size=0.4, alpha=0.9) +
  theme_ipsum() +
  theme(
    legend.position="none",
    plot.title = element_text(size=11)
  ) +
  ggtitle("A BOXPLOT OF REWARDS IN $HNT PER WEEK PER HOTSPOT AT SELECT 3 MONTH INTERVALS") +
  xlab("")

### 4.Creating viloin plot

stacked_rewards %>%
  ggplot( aes(x=variable, y=value, fill=variable)) +
  geom_violin()  +
  scale_fill_viridis(discrete = TRUE, alpha=0.6) +
  geom_jitter(color="black", size=0.4, alpha=0.9) +
  theme_ipsum() +
  theme(
    legend.position="none",
    plot.title = element_text(size=11)
  ) +
  ggtitle("A VIOLIN PLOT OF REWARDS IN $HNT PER WEEK PER HOTSPOT AT SELECT 3 MONTH INTERVALS") +
  xlab("")

### 5.Remove zeros and recreate a plot

str(stacked_rewards)

## 'data.frame':    549 obs. of  3 variables:
##  $ owner_address: chr  "112QRGp1hyNv2PVAa4jgDQj6uhqbKYTsYgyNPtov1PCF7ZYFRYP4" "112vk8YczZH3YKwGq8YX6SmFK37ynzqxQ7cc2fKsEDgXiFwRum49" "11PZWJh33ycHMEJ5W2QdxQ8Hin9WbvwhQ2kTn6SyiULEzhduEu4" "112RTQMaCFwdpLfUdxtXPjTnEa2rrjAj4ZRmRz3dfkEUJrs5kybm" ...
##  $ variable     : Factor w/ 3 levels "total_feb22",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ value        : num  0.6515 1.6309 0.1301 0 0.0467 ...

data_no_zero <- stacked_rewards %>%
                  filter(value > 0 )
str(data_no_zero)

## 'data.frame':    408 obs. of  3 variables:
##  $ owner_address: chr  "112QRGp1hyNv2PVAa4jgDQj6uhqbKYTsYgyNPtov1PCF7ZYFRYP4" "112vk8YczZH3YKwGq8YX6SmFK37ynzqxQ7cc2fKsEDgXiFwRum49" "11PZWJh33ycHMEJ5W2QdxQ8Hin9WbvwhQ2kTn6SyiULEzhduEu4" "11URkSsZFwoThiQ41HkKpwNLsbDy9FPkhEcNQaXUqFAzh3wXEFA" ...
##  $ variable     : Factor w/ 3 levels "total_feb22",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ value        : num  0.6515 1.6309 0.1301 0.0467 1.755 ...

6.Viloin plot

data_no_zero %>%
  ggplot( aes(x=variable, y=value, fill=variable)) +
  geom_violin()  +
  scale_fill_viridis(discrete = TRUE, alpha=0.6) +
  geom_jitter(color="black", size=0.4, alpha=0.9) +
  theme_ipsum() +
  theme(
    legend.position="none",
    plot.title = element_text(size=11)
  ) +
  ggtitle("A VIOLIN PLOT OF REWARDS IN $HNT PER WEEK PER HOTSPOT AT SELECT 3 MONTH INTERVALS") +
  xlab("")

### 7.Frequency density plot

freq_dens<- ggplot(stacked_rewards, aes(x = value))
freq_dens + geom_density(aes(color = variable))

Finding possible factors affecting the reward

Variables related with hotspots

There following variables potentially affecting the reward earned by the hotspot. Total Hotspots owned by same owner –> more hotspots owned then earn more rewards During of owning the hotspot –> Longer keeping the hotspot on line earn more rewards Antenna height and quality–> Better Antenna earn more rewards ### 1. Merge rewards data and hotspots’ data

rewardcoall <- merge(Cincinnati_hotspot_list, reward_data_all, by.x="data.address", by.y="owner_address")  
str(rewardcoall)

## 'data.frame':    422 obs. of  39 variables:
##  $ data.address              : chr  "1119JdnRNSWzH5bWnPneATheBRjWE83wzKiJtk6rEifkzjpcwdS" "1119rzMAKsGXCHqXvCSxRRKeaBwfj7yaqwSP3E3v3DDcJMMhTdz" "1121b2xjPGpjqSbu2UVbMsRZvYw5QVTV8suj94BswWPs6rqnCsvN" "1121WJq74i5V5ATgHBsNKYtzxRcySi3xsAsw7XXHjX5cx2mZgq67" ...
##  $ data.lng                  : num  -84.6 -84.5 -84.5 -84.5 -84.5 ...
##  $ data.lat                  : num  39.1 39.1 39.2 39.2 39.1 ...
##  $ data.timestamp_added      : chr  "2021-09-28T21:43:27.000000Z" "2021-11-14T01:21:05.000000Z" "2021-12-12T01:42:48.000000Z" "2021-12-06T03:44:08.000000Z" ...
##  $ data.reward_scale         : num  1 0.435 0.435 0.217 0.398 ...
##  $ data.payer                : chr  "14NBXJE5kAAZTMigY4dcjXSMG4CSqjYwvteQWwQsYhsu2TKN6AF" "14sKWeeYWQWrBSnLGq79uRQqZyw3Ldi7oBdxbF6a54QboTNBXDL" "14h2zf1gEr9NmvDb2U53qucLN2jLrKU1ECBoxGnSnQ6tiT6V2kM" "13Zni1he7KY9pUmkXMhEhTwfUpL9AcEV1m2UbbvFsrU9QPTMgE3" ...
##  $ data.owner                : chr  "131uHmCt1cDfRaDqJeZYNqPrLRtSdeP61fwvsaTxeLR7ZuG8RuN" "132XNC7BBGXwbrgVec2ybbBLDLXerrDmKGCeeq5tnSSm4XuvfUp" "136eBB27p7R7hPkG8UaA18DM9wVAieX4BtJEyvTppMEg9DDvTZd" "14VYsgSneY5Xgwmzps6Vsx1kYPcgcMdwj3XHMX2DMEr7VvCWGRS" ...
##  $ data.nonce                : int  2 1 1 1 2 1 1 11 2 2 ...
##  $ data.name                 : chr  "ambitious-plum-albatross" "petite-menthol-lizard" "atomic-satin-crane" "refined-chocolate-rhino" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a93725dfffff" "882a9301b1fffff" "882a930159fffff" "882a9301e1fffff" ...
##  $ data.location             : chr  "8c2a93725c21bff" "8c2a9301b16ddff" "8c2a9301581b3ff" "8c2a9301e1aebff" ...
##  $ data.last_poc_challenge   : int  1239754 1239269 1239896 1239776 1239856 1239460 1238234 1239635 1239521 1239832 ...
##  $ data.last_change_block    : int  1239851 1239896 1239896 1239819 1239886 1239819 1238404 1239883 1239692 1239883 ...
##  $ data.gain                 : int  58 40 23 12 30 80 23 40 58 23 ...
##  $ data.elevation            : int  12 5 7 0 11 15 10 8 3 4 ...
##  $ data.block_added          : int  1030951 1097763 1135812 1127154 1196595 1196792 1219587 1019490 1033974 928303 ...
##  $ data.block                : int  1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 ...
##  $ data.status.timestamp     : chr  "2022-02-20T01:43:22.084000Z" "2022-02-21T03:08:17.306000Z" "2022-02-23T10:19:22.925000Z" "2022-02-22T01:33:33.234000Z" ...
##  $ data.status.online        : chr  "online" "online" "online" "online" ...
##  $ data.status.height        : int  1233545 1235194 1238759 1236643 1225700 1227211 1232261 1238788 1231602 1238071 ...
##  $ data.geocode.short_street : chr  "Palisades Point" "Woodburn Ave" "Avalon St" "Sullivan Ave" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Palisades Point" "Woodburn Avenue" "Avalon Street" "Sullivan Avenue" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...
##  $ meta.min_time             : chr  "2021-09-28T21:43:00Z" "2021-11-14T01:21:00Z" "2021-12-12T01:42:00Z" "2021-12-06T03:44:00Z" ...
##  $ meta.max_time             : chr  "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" ...
##  $ data.total                : num  18.388 6.162 2.825 1.24 0.747 ...
##  $ data.sum                  : num  1.84e+09 6.16e+08 2.82e+08 1.24e+08 7.47e+07 ...
##  $ data.stddev               : num  0.0196 0.00464 0.00516 0.00243 0.00411 ...
##  $ data.min                  : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median               : num  0.01062 0.00399 0.00511 0.00351 0.00307 ...
##  $ data.max                  : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg                  : num  0.01794 0.00591 0.00726 0.00434 0.00473 ...

2. Get total hotspots by owner

Totalhotspots <- sqldf(
  '
  select "data.owner",count(distinct "data.address") as total_hotspots,sum("data.total") as total_rewards
  
  from rewardcoall
  where [data.status.online]="online"
  group by "data.owner"
  
 ' 
)

3.Merge data from total spots with whole data set.

allrewarddata <-  merge(Totalhotspots,rewardcoall, by ="data.owner") 
allrewarddata <-allrewarddata %>% 
  mutate( Year_month_added =format(as.Date(allrewarddata$data.timestamp_added), "%Y-%m"), 
          TotalDaysOwned =strtoi(difftime( Sys.Date(),as.Date(allrewarddata$data.timestamp_added), units ="days")))
str(allrewarddata)

## 'data.frame':    366 obs. of  43 variables:
##  $ data.owner                : chr  "12x4RxJ1wPwF8W8cSQCQrJj5CywQkyvXCVY72CzAfoY9j6moR9p" "12x8AHLiUhVucKxLdyXiF71kTTsYgx8h1PFkyrmmt75qnRJBC9M" "12xdBvDrdDE4sACdA9JJb66QPZTijVFgLnAyAskYuBVyPhGsQTY" "12xdBvDrdDE4sACdA9JJb66QPZTijVFgLnAyAskYuBVyPhGsQTY" ...
##  $ total_hotspots            : int  1 1 2 2 2 2 1 1 1 1 ...
##  $ total_rewards             : num  7.69 5.2 20.22 20.22 24.72 ...
##  $ data.address              : chr  "112KER5BG7YAVoFTjST7AoJUAbgdUxK51vLdwkQgGYMQWmaYpEnZ" "11XEqY3nDN5bSK9ntUHFTPfaVCxdqEEojbBGavXW7F7tcsV8SXJ" "11iG3s6FCKpYhjYzrSoTvzYxYRE9w88bgzj7KmgrywSBDRMBc8p" "11VLLmhBKeTm4Co8ipMeqWatvFUGe3RUWg7qwEYS6UK7BHqUxbX" ...
##  $ data.lng                  : num  -84.6 -84.6 -84.5 -84.6 -84.4 ...
##  $ data.lat                  : num  39.1 39.1 39.1 39.1 39.2 ...
##  $ data.timestamp_added      : chr  "2021-11-10T00:39:52.000000Z" "2021-11-24T00:03:51.000000Z" "2021-12-06T18:47:07.000000Z" "2021-12-07T20:15:54.000000Z" ...
##  $ data.reward_scale         : num  0.539 0.539 0.367 0.539 0.435 ...
##  $ data.payer                : chr  "14sKWeeYWQWrBSnLGq79uRQqZyw3Ldi7oBdxbF6a54QboTNBXDL" "14sKWeeYWQWrBSnLGq79uRQqZyw3Ldi7oBdxbF6a54QboTNBXDL" "13Zni1he7KY9pUmkXMhEhTwfUpL9AcEV1m2UbbvFsrU9QPTMgE3" "13Zni1he7KY9pUmkXMhEhTwfUpL9AcEV1m2UbbvFsrU9QPTMgE3" ...
##  $ data.nonce                : int  2 1 2 1 1 2 2 1 1 3 ...
##  $ data.name                 : chr  "early-green-hippo" "large-ocean-cuckoo" "rhythmic-coffee-duck" "festive-gunmetal-stallion" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a9308b3fffff" "882a93089dfffff" "882a930f03fffff" "882a93089bfffff" ...
##  $ data.location             : chr  "8c2a9308b29e1ff" "8c2a93089d063ff" "8c2a930f03089ff" "8c2a93089b653ff" ...
##  $ data.last_poc_challenge   : int  1239499 1239626 1239371 1239664 1239903 1239591 1239624 1239869 1239483 1239479 ...
##  $ data.last_change_block    : int  1239886 1239819 1239883 1239883 1239903 1239894 1239908 1239895 1239908 1239479 ...
##  $ data.gain                 : int  80 40 80 58 40 58 58 12 120 58 ...
##  $ data.elevation            : int  10 13 30 11 4 7 8 0 6 23 ...
##  $ data.block_added          : int  1092006 1109636 1128034 1129627 1139617 1119662 1008572 1051148 1126881 1039577 ...
##  $ data.block                : int  1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 1239910 ...
##  $ data.status.timestamp     : chr  "2022-02-23T20:48:35.222000Z" "2022-01-29T19:36:58.753000Z" "2022-02-22T18:22:48.105000Z" "2022-02-21T11:48:05.478000Z" ...
##  $ data.status.online        : chr  "online" "online" "online" "online" ...
##  $ data.status.height        : int  1239441 1203904 1237726 1235750 1232520 1225530 1234698 1237444 1236596 1232882 ...
##  $ data.geocode.short_street : chr  "Greenwell Ave" "Suire Ave" "Mc Farland St" "Murdock Ave" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Greenwell Avenue" "Suire Avenue" "Mc Farland Street" "Murdock Avenue" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...
##  $ meta.min_time             : chr  "2021-11-10T00:39:00Z" "2021-11-24T00:03:00Z" "2021-12-06T18:47:00Z" "2021-12-07T20:15:00Z" ...
##  $ meta.max_time             : chr  "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" "2022-02-23T00:00:00Z" ...
##  $ data.total                : num  7.69 5.2 5.81 14.41 9.84 ...
##  $ data.sum                  : num  7.69e+08 5.20e+08 5.81e+08 1.44e+09 9.84e+08 ...
##  $ data.stddev               : num  0.01111 0.00951 0.00526 0.00896 0.00871 ...
##  $ data.min                  : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.median               : num  0.00574 0.00421 0.00423 0.00482 0.00935 ...
##  $ data.max                  : num  0 0 0 0 0 0 0 0 0 0 ...
##  $ data.avg                  : num  0.0107 0.00934 0.00619 0.00877 0.01112 ...
##  $ Year_month_added          : chr  "2021-11" "2021-11" "2021-12" "2021-12" ...
##  $ TotalDaysOwned            : int  105 91 79 78 71 85 163 134 80 142 ...

4.Use ggplot and regression model to check the relationship between total hotspots owned regarding rewards bet

Correlation Coefficient between variables

	data.total	total_hotspots	data.reward_scale	data.elevation	data.gain	TotalDaysOwned
data.total	1.0000000	-0.0475148	-0.0043955	0.0127391	-0.0799943	0.5833086
total_hotspots	-0.0475148	1.0000000	-0.0094343	0.0288246	0.3834827	-0.2232872
data.reward_scale	-0.0043955	-0.0094343	1.0000000	0.1240565	-0.0385873	-0.0265246
data.elevation	0.0127391	0.0288246	0.1240565	1.0000000	0.2210155	-0.0331626
data.gain	-0.0799943	0.3834827	-0.0385873	0.2210155	1.0000000	-0.2396277
TotalDaysOwned	0.5833086	-0.2232872	-0.0265246	-0.0331626	-0.2396277	1.0000000

5.Regression model for analysis Total rewards with Height and gain.

ggplot(data=allrewarddata, mapping = aes(x = data.gain, y = data.total)) + 
  geom_point(aes(size = data.elevation)) +
  theme_light()+
  theme(legend.position = "left")+
  
  xlab("Antenna gain (in dBi)")+
  ylab("Total Rewards")+
  guides(size=guide_legend(title="Antenna Height"))

antennaModel <- lm(data.total ~data.elevation + data.gain ,allrewarddata)
summary(antennaModel)

## 
## Call:
## lm(formula = data.total ~ data.elevation + data.gain, data = allrewarddata)
## 
## Residuals:
##    Min     1Q Median     3Q    Max 
## -113.7  -42.9  -27.3   -9.9 3813.9 
## 
## Coefficients:
##                Estimate Std. Error t value Pr(>|t|)   
## (Intercept)     74.1801    24.1434   3.072  0.00228 **
## data.elevation   0.1417     0.2334   0.607  0.54423   
## data.gain       -0.7803     0.4776  -1.634  0.10316   
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 219.9 on 363 degrees of freedom
## Multiple R-squared:  0.007475,   Adjusted R-squared:  0.002006 
## F-statistic: 1.367 on 2 and 363 DF,  p-value: 0.2562

antennaModel$coefficients

##    (Intercept) data.elevation      data.gain 
##     74.1800880      0.1416659     -0.7803333

antenres_hat <- antennaModel$residuals

anten_hist <- ggplot(data.frame(antenres_hat), aes(antenres_hat)) +geom_histogram()
anten_hist

## `stat_bin()` using `bins = 30`. Pick better value with `binwidth`.

hist(antenres_hat)

shapiro.test(antenres_hat)

## 
##  Shapiro-Wilk normality test
## 
## data:  antenres_hat
## W = 0.16558, p-value < 2.2e-16

Witness for a hotspot

Get witness

Witness fot the last five days

Witness is another factor for getting rewards. Calculate the distances from a hotspot to a witness. Witnesses are used as proof that a hotspot is real and is doing what it says it is doing.

witnesslist =list()
    

  
 witnesslst <- Cincinnati_hotspot_list$data.address
  
  for (owner_addr in witnesslst) {
    witness_url <- glue("https://api.helium.io/v1/hotspots/{owner_addr}/witnesses")
    witness_res <- GET(witness_url)
    
    
   
   
   if(status_code(witness_res) == 200){
     
      witness_res_conv <-  fromJSON(rawToChar(witness_res$content),flatten = TRUE)%>% 
      data.frame()
           if("data.status.listen_addrs" %in% colnames(witness_res_conv)==TRUE ){
             
             #print(paste0(owner_addr,"with listen_addrs"))
     witness_res_final <- witness_res_conv %>%
       select(-c(data.status.listen_addrs)) %>%
       mutate(owner_address = owner_addr)
             }
    else{
      #print(paste0(owner_addr,"without listen_addrs"))
      witness_res_final <- witness_res_conv %>%

        mutate(owner_address = owner_addr)
    }
  witnesslist[[owner_addr]] <- witness_res_final
   } 
    else next
    
     
  
    
   
  }
  
  
  witness_data <- do.call(rbind,witnesslist)
  # 
  str(witness_data)

## 'data.frame':    6596 obs. of  31 variables:
##  $ data.lng                  : num  -84.3 -84.3 -84.3 -84.5 -84.5 ...
##  $ data.lat                  : num  39.1 39.1 39.1 39.2 39.2 ...
##  $ data.timestamp_added      : chr  "2021-12-24T18:29:51.000000Z" "2021-10-12T15:31:14.000000Z" "2021-07-08T02:42:08.000000Z" "2022-01-21T16:59:49.000000Z" ...
##  $ data.reward_scale         : num  0.54 0.541 0.541 0.539 0.435 ...
##  $ data.payer                : chr  "13y2EqUUzyQhQGtDSoXktz8m5jHNSiwAKLTYnHNxZq2uH5GGGym" "13ENbEQPAvytjLnqavnbSAzurhGoCSNkGECMx7eHHDAfEaDirdY" "14sKWeeYWQWrBSnLGq79uRQqZyw3Ldi7oBdxbF6a54QboTNBXDL" "14NBXJE5kAAZTMigY4dcjXSMG4CSqjYwvteQWwQsYhsu2TKN6AF" ...
##  $ data.owner                : chr  "14Fu87PxNmnR2hQ8W6fo36iRqeZ7PuLjuCpo5fWpdKP3rmjpcjn" "12xxwr2KNRSpcnaXejnxNG5a5n4WnDXVhxJqEq9mYwjCC6Rw5HP" "14FQMJTKtpJaWxhzQMsTzAwGv6ErQ9rjRyyufpggmfT9veQXyTY" "14ZdAm8p1NMjqpN3qSxKG3P51nSHnVGMpYGCSzQxxC7enKrQgt9" ...
##  $ data.nonce                : int  3 1 1 2 3 4 8 1 2 1 ...
##  $ data.name                 : chr  "polished-cinnabar-shark" "steep-gauze-stork" "recumbent-burgundy-gecko" "alert-seaweed-eagle" ...
##  $ data.mode                 : chr  "full" "full" "full" "full" ...
##  $ data.location_hex         : chr  "882a930429fffff" "882a930421fffff" "882a930423fffff" "882a931893fffff" ...
##  $ data.location             : chr  "8c2a930429635ff" "8c2a930421a5dff" "8c2a930423ad3ff" "8c2a93189292dff" ...
##  $ data.last_poc_challenge   : int  1239480 1239869 1239667 1239876 1239481 1239733 1239745 1239476 1239548 1239634 ...
##  $ data.last_change_block    : int  1239692 1239895 1239667 1239900 1239888 1239819 1239894 1239628 1239883 1239913 ...
##  $ data.gain                 : int  58 12 40 58 58 50 60 40 100 80 ...
##  $ data.elevation            : int  11 0 0 0 6 9 4 5 12 10 ...
##  $ data.block_added          : int  1152080 1051148 910914 1191884 1180269 1114115 1058294 1008349 1178137 1170868 ...
##  $ data.block                : int  1239914 1239914 1239914 1239914 1239914 1239914 1239914 1239914 1239914 1239914 ...
##  $ data.address              : chr  "11KYmZaUWV8LtmbHEffB3XyUEmxU3zVMmZsHvNFP2NycqSbBVw3" "112FMPpAnFtQ6dp2uihQsosKXDnvLNYhEXDf1yYMDXHnhkdX8atp" "112B2QZfhmt85eYuwhsK8oLB6h996QyQx7T4gq7dpisUho5qHRCv" "118ZU8GKPadPnFc2EpZRroyQXXzPAzLACPCY52DD2WgbjKWPeyJ" ...
##  $ data.status.timestamp     : chr  "2022-02-14T16:08:36.491000Z" "2022-02-22T14:04:39.757000Z" "2022-01-28T09:59:18.012000Z" "2022-02-23T01:19:04.356000Z" ...
##  $ data.status.online        : chr  "online" "online" "online" "online" ...
##  $ data.status.height        : int  1225273 1237444 1202042 1238174 1223803 1238107 1237071 1235229 1232647 1229375 ...
##  $ data.geocode.short_street : chr  "Rathman Pl" "Sigma Cir" "Pamela Rd" "Winton Rd" ...
##  $ data.geocode.short_state  : chr  "OH" "OH" "OH" "OH" ...
##  $ data.geocode.short_country: chr  "US" "US" "US" "US" ...
##  $ data.geocode.short_city   : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.long_street  : chr  "Rathman Place" "Sigma Circle" "Pamela Road" "Winton Road" ...
##  $ data.geocode.long_state   : chr  "Ohio" "Ohio" "Ohio" "Ohio" ...
##  $ data.geocode.long_country : chr  "United States" "United States" "United States" "United States" ...
##  $ data.geocode.long_city    : chr  "Cincinnati" "Cincinnati" "Cincinnati" "Cincinnati" ...
##  $ data.geocode.city_id      : chr  "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" "Y2luY2lubmF0aW9oaW91bml0ZWQgc3RhdGVz" ...
##  $ owner_address             : chr  "11vCtvQL5NaCGL8Vyfk5RDVojVXFwLmU8BzBLxwei6G9dJWCUEu" "11vCtvQL5NaCGL8Vyfk5RDVojVXFwLmU8BzBLxwei6G9dJWCUEu" "11vCtvQL5NaCGL8Vyfk5RDVojVXFwLmU8BzBLxwei6G9dJWCUEu" "112ckn3wfdzDkbBwY5vxr5Z56PU9r8V4Ld9NedZAU1L5w7DtLkHr" ...

  # View(witness_data)
  # 

Distance between witness and hotspot

Use the geosphere package to calculate distances between witnesses Merge hotspot data,reward data and witness data.

reward_witness_all <- sqldf(
  
  '
  select a.owner_address, b.[data.lng] as long1, b.[data.lat] as lat1, b.[data.owner] as owner,a.[data.lng] as long2, a.[data.lat] as lat2, a.[data.address] as witness_address, a.[data.owner] as witness_owner,c.[data.total] as total_rewards
  from witness_data a inner join Cincinnati_hotspot_list b
  on a.owner_address=b.[data.address]
  inner join reward_data c on a.owner_address=c.owner_address
  
  '
  
)
   
witness_distance <- reward_witness_all %>%

mutate(distance = (distHaversine(cbind(reward_witness_all$long1, reward_witness_all$lat1),
                                              cbind(reward_witness_all$long2, reward_witness_all$lat2)))/1609) %>% 
filter(distance <100)

Number of witness

Assumption that if there is more witness, then more rewards can be earned.

total_witness <- sqldf(
  
  '
  select a.owner_address,c.[data.lng] as owner_long,c.[data.lat] as owner_lat, count(a.[data.address]) as total_witness
  from witness_data a inner join Cincinnati_hotspot_list c on a.owner_address=c.[data.address]
  group by  a.owner_address,c.[data.lng],c.[data.lat]
  '
  
)
rewardwit <-  merge(reward_data,total_witness, by="owner_address") 

Analysis of witness

insepect the relationship between distance, number of witness

witness_all <- merge(witness_distance,rewardwit,by="owner_address") %>% 
  filter(distance <100)

TotalWitnessplot <- scatterplot(rewardwit$total_witness,rewardwit$data.total,
     main="Total witness vs Rewards for the last five days",
     xlab ="Total Witness",
     ylab ="Total rewards")

Distanceplot <- scatterplot(witness_all$distance,witness_all$data.total,
     main="Distance vs Rewards for the last five days",
     xlab ="Distance",
     ylab ="Total rewards")

cor(witness_all %>% select(distance,total_witness,data.total))

##                distance total_witness data.total
## distance      1.0000000     0.1030427  0.1017963
## total_witness 0.1030427     1.0000000  0.5254143
## data.total    0.1017963     0.5254143  1.0000000

ggpairs(witness_all %>% select(distance,total_witness,data.total)
        , columnLabels =c("Distance", "Total Witness", "Total Rewards"))

mapview(rewardwit, xcol = "owner_long", ycol = "owner_lat", zcol ="data.total" , crs = 4269, grid = FALSE,
        cex ='total_witness', legend = TRUE, layer.name ="Total Rewards")

witnessModel <- lm(data.total ~distance+total_witness,witness_all)
summary(witnessModel)

## 
## Call:
## lm(formula = data.total ~ distance + total_witness, data = witness_all)
## 
## Residuals:
##      Min       1Q   Median       3Q      Max 
## -0.65322 -0.23330 -0.05684  0.16999  1.05950 
## 
## Coefficients:
##                Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   0.2504234  0.0076811  32.602  < 2e-16 ***
## distance      0.0029982  0.0006617   4.531 5.97e-06 ***
## total_witness 0.0065241  0.0001333  48.961  < 2e-16 ***
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 0.3025 on 6455 degrees of freedom
## Multiple R-squared:  0.2784, Adjusted R-squared:  0.2781 
## F-statistic:  1245 on 2 and 6455 DF,  p-value: < 2.2e-16

numberofWitness <- 10*witnessModel$coefficients[3]

paste0("For each 10 increase in witness, the rewards during last five days will increase ", round(10*numberofWitness,2), " rewards", sep = "")

## [1] "For each 10 increase in witness, the rewards during last five days will increase 0.65 rewards"

Size of the circles of map–> Total witness for the hotspots Fill color for the circles of map –> Total Rewards for the last five days

Plot of market price of $HNT in dollars and cumulative hotpots in cincinnati.

#getwd()

HNT_USD <- read_csv("E://Data//Downloads//HELIUMtoUSD_historical_price.csv")

## Rows: 617 Columns: 7
## -- Column specification --------------------------------------------------------
## Delimiter: ","
## dbl  (5): Open, High, Low, Close, Adj Close
## date (1): Date
## 
## i Use `spec()` to retrieve the full column specification for this data.
## i Specify the column types or set `show_col_types = FALSE` to quiet this message.

#view(HNT_USD)
HNT_USD <- HNT_USD %>%
  select(Date, Close)



#view(HNT_USD)
HNT_USD<- as.data.frame(HNT_USD)



#view(HNT_USD)



HNT_USD$Date <- as.Date(HNT_USD$Date, format = "%Y-%m-%d")



#view(HNT_USD)



names(cincinnati_hotspot_cumulative)[names(cincinnati_hotspot_cumulative) == 'data.timestamp_added'] <- 'timestamp_added'



HNT_USD <- sqldf('select
a.*
,b.hot_spot_csum
from HNT_USD a left join cincinnati_hotspot_cumulative b on a.Date=b.timestamp_added
')



coeff = 10



Close_color <- "#69b3a2"
C_color <- rgb(0.2, 0.6, 0.9, 1)



ggplot(HNT_USD, aes(x=Date)) +
  
  geom_line ( aes(y=Close), size=1, , color=Close_color) +
  geom_point ( aes(y=hot_spot_csum / coeff), size=2, color=C_color) +
  
  scale_y_continuous(
    
    # Features of the first axis
    name = "Closing market price of $HNT in $USD",
    
    # Add a second axis and specify its features
    sec.axis = sec_axis(~.*coeff, name="cumulative hotspots")
  ) +
  
  theme_bw() +
  
  theme(
    axis.title.y = element_text(color = Close_color, size=13),
    axis.title.y.right = element_text(color = C_color, size=13)
  ) +
  
  ggtitle("Cumulative Hotspots In Cincinnati And Market Price of Helium In $HNT in $USD")

## Warning: Removed 407 rows containing missing values (geom_point).