startrek_wikidata_midpoint.R

# Wikidata/R script to find the midpoint of all current star trek tv/films

library(WikidataQueryServiceR)

query <- '
SELECT DISTINCT ?item ?itemLabel ?duration ?startrekLabel
WHERE
{
 #star trek films
 {?item wdt:P31 wd:Q61283808.}
 UNION
 {?startrek wdt:P31 wd:Q62573441.  #all star trek series
 ?item wdt:P179 ?startrek.} #members of star trek series
 ?item wdt:P1191 ?date. #get broadcast date (will create dupes if displayed)

 MINUS { ?item wdt:P31 wd:Q21664088. } #exclude two parter entries
 OPTIONAL {?item wdt:P2047 ?duration.} #allow no durations to examine furtherdown
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ASC(?date)
'

df <- query_wikidata(query)

missing <- df[(is.na(df$duration)),]

df <- df[(!is.na(df$duration)),]
all_time <- sum(df$duration)

target <- all_time * 0.5

duration = 0
row = 0

for (row in 1:nrow(df)) {
  if (duration + df$duration[row] > target){
    time_index = target-duration
    break
  }
  duration = duration + df$duration[row]
}

episode = df$itemLabel[row]
series = df$startrekLabel[row]
minutes = floor(time_index)
seconds = (time_index %% 1) * 60 

cat("Missing Episodes:")
missing$itemLabel

cat(paste("Current mid-point episode is",series,":", episode))
cat(paste("Mid point is at", minutes,"minutes and",seconds,"seconds."))