<jupyter_start><jupyter_text>List of champs<jupyter_code>with open('championsComplete.json') as json_file:  
    champs = json.load(json_file)
idchampions = {}
for champion in champs :
  id = champs[champion]['key']
  idchampions[id] = champion

idchampions['6']
champs['Urgot']
idchampions<jupyter_output><empty_output><jupyter_text>GAME<jupyter_code>game = spark.read.json("game.json")
game.show(3)
game.columns
game.printSchema()<jupyter_output>root
 |-- champions: array (nullable = true)
 |    |-- element: long (containsNull = true)
 |-- gameCreation: long (nullable = true)
 |-- gameDuration: long (nullable = true)
 |-- gameId: long (nullable = true)
 |-- participants: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- championId: long (nullable = true)
 |    |    |-- highestAchievedSeasonTier: string (nullable = true)
 |    |    |-- participantId: long (nullable = true)
 |    |    |-- spell1Id: long (nullable = true)
 |    |    |-- spell2Id: long (nullable = true)
 |    |    |-- stats: struct (nullable = true)
 |    |    |    |-- assists: long (nullable = true)
 |    |    |    |-- champLevel: long (nullable = true)
 |    |    |    |-- combatPlayerScore: long (nullable = true)
 |    |    |    |-- damageDealtToObjectives: long (nullable = true)
 |    |    |    |-- damageDealtToTurrets: long (nullable = true)
 |    |    |    |-- damageSelfMitigated: long (nullable = true)
 |    |    |    |--[...]<jupyter_text>Most, less and none played champ<jupyter_code>championIds2 = [df['championId'] for row in game.select("participants").collect() for df in row['participants']]  
champsFiltered = sc.parallelize(championIds2).map(lambda word: (word, 1)).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
mostPlayed = champsFiltered[0]
lessPlayed = champsFiltered[-1]
print("Most played: " + idchampions[str(mostPlayed[0])] + " " + str(mostPlayed[1]) + " times played")
print("Less played: " + idchampions[str(lessPlayed[0])] + " " + str(lessPlayed[1]) + " times played")
champsFiltered
plt.xlabel("Champion")
plt.ylabel("Number times played")

topChamps = 10

x = np.array([i+1 for i in range(topChamps)])

my_xticks = [idchampions[str(champsFiltered[i][0])] for i in range(topChamps)]
plt.xticks(x, my_xticks)

for i in range(topChamps):
  plt.bar(i+1, champsFiltered[i][1], width=0.8, align='center' )

fig_size = plt.rcParams["figure.figsize"]
fig_size[0] =10
fig_size[1] =10
plt.rcParams["figure.figsize"] = fig_size
plt.show()
nonPlayed = []
allPlayed = [idc[0] for idc in champsFiltered]
for id in idchampions.keys() :
  if int(id) not in allPlayed :
    nonPlayed.append(idchampions[id])
    
print("Non-played champions:\n")
nonPlayed<jupyter_output>Non-played champions:<jupyter_text>Get champions with most and less winrate<jupyter_code>numberGamesPerChamp = dict(champsFiltered)
championWins = []
championLoses = []

for row in game.select("teams", "participants").collect() :
  teamwin = 0
  teamlose = 0
  for team in row['teams']: #2 iterations
    if(team['win'] == 'Win'):
      teamwin = team['teamId']
    else :
      teamlose = team['teamId']
      
  for participant in row['participants']: #10 iterations
    if(participant['teamId'] == teamwin):
      championWins.append(participant['championId'])
    else:
      championLoses.append(participant['championId'])

winsFiltred = sc.parallelize(championWins).map(lambda word: (word, 1/numberGamesPerChamp[int(word)])).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
losesFiltred = sc.parallelize(championLoses).map(lambda word: (word, 1/numberGamesPerChamp[int(word)])).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
mostWinChamp = idchampions[str(winsFiltred[0][0])]
mostLosesChamp = idchampions[str(losesFiltred[0][0])]

winrate = "{0:.2f}".format(winsFiltred[0][1] * 100)
lossrate = "{0:.2f}".format(losesFiltred[0][1] * 100) 

print("Most winrate:   " + mostWinChamp + " " + str(winrate) + "% winrate")
print("Most loss rate: " + mostLosesChamp + " " + str(lossrate) + "% loss rate")
for i in range(len(winsFiltred)):
  wRateChamp = idchampions[str(winsFiltred[i][0])]
  wRateValue = "{0:.2f}".format(winsFiltred[i][1] * 100)
  print(wRateChamp + " " + str(wRateValue) + "% winrate")<jupyter_output>Sona 66.67% winrate
Rumble 66.67% winrate
Singed 66.67% winrate
Tristana 65.89% winrate
Ivern 64.29% winrate
Sejuani 64.00% winrate
Riven 63.73% winrate
Bard 63.04% winrate
Yorick 61.54% winrate
Quinn 61.36% winrate
Kalista 61.11% winrate
Ziggs 61.11% winrate
Nami 60.00% winrate
Taric 60.00% winrate
Gragas 60.00% winrate
Ekko 59.62% winrate
Varus 59.52% winrate
Brand 59.34% winrate
Talon 58.97% winrate
Tryndamere 58.93% winrate
Zed 58.92% winrate
TahmKench 58.70% winrate
Lissandra 58.33% winrate
Shen 58.06% winrate
Janna 58.00% winrate
Olaf 57.78% winrate
Xerath 57.69% winrate
Garen 57.61% winrate
Ashe 57.43% winrate
Heimerdinger 57.14% winrate
Corki 56.67% winrate
Nunu 56.67% winrate
Pantheon 56.52% winrate
Nidalee 56.25% winrate
Gnar 55.88% winrate
Amumu 55.56% winrate
Jinx 55.56% winrate
Sivir 55.42% winrate
Kayn 55.40% winrate
DrMundo 55.06% winrate
Fiora 54.55% winrate
Jax 54.39% winrate
Draven 54.35% winrate
Mordekaiser 54.29% winrate
Khazix 54.22% winrate
Nautilus 53.85% winrate[...]<jupyter_text>Get champion that killed most and died most<jupyter_code>killperchamp = [(df['championId'],int(df['stats']['kills'])) for row in game.select("participants").collect() for df in row['participants']]
deathsperchamp = [(df['championId'],int(df['stats']['deaths'])) for row in game.select("participants").collect() for df in row['participants']]  
killperchampFiltered = sc.parallelize(killperchamp).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
deathsperchampFiltered = sc.parallelize(deathsperchamp).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
print("Champion with more kills  -  " + idchampions[str(killperchampFiltered[0][0])] + " " + str(killperchampFiltered[0][1]) + " kills " + "{0:.2f}".format(int(killperchampFiltered[0][1])/numberGamesPerChamp[killperchampFiltered[0][0]]) + " kill/game (" + str(numberGamesPerChamp[killperchampFiltered[0][0]])  + " games)")
print("Champion with more deaths -  " + idchampions[str(deathsperchampFiltered[0][0])] + " " + str(deathsperchampFiltered[0][1]) + " deaths " + "{0:.2f}".format(int(deathsperchampFiltered[0][1])/numberGamesPerChamp[deathsperchampFiltered[0][0]]) + " death/game (" + str(numberGamesPerChamp[deathsperchampFiltered[0][0]])  + " games)")<jupyter_output>Champion with more kills  -  Zed 1764 kills 9.54 kill/game (185 games)
Champion with more deaths -  Yasuo 1743 deaths 7.39 death/game (236 games)<jupyter_text>Team color with most winrate<jupyter_code>blueWinRates = 0
for gamesito in game.select('teams').collect():
  if(gamesito['teams'][0]['win'] == 'Win'):
    blueWinRates += 1

print("Blue side winrate: " + str(blueWinRates) + " wins - " + "{0:.2f}".format(blueWinRates*100/game.count()) + "%") 
print("Red side winrate:  " + str(game.count() - blueWinRates) + " wins - " + "{0:.2f}".format((game.count() - blueWinRates)*100/game.count())  + "%")<jupyter_output>Blue side winrate: 486 wins - 51.37%
Red side winrate:  460 wins - 48.63%<jupyter_text>Top champs with most first kill<jupyter_code>firstBloodChamps = []

for part in game.select(explode('participants')).collect():
   if(part['col']['stats']['firstBloodKill']):
    firstBloodChamps.append(part['col']['championId'])

firstBloodTop = sc.parallelize(firstBloodChamps).map(lambda word: (word, 1)).reduceByKey(lambda a,b:a +b).sortBy(lambda x:x[1], ascending=False).collect()
plt.xlabel("Champion")
plt.ylabel("Number of kills")

topChamps = 10

x = np.array([i+1 for i in range(topChamps)])

my_xticks = [idchampions[str(firstBloodTop[i][0])] for i in range(topChamps)]
plt.xticks(x, my_xticks)

for i in range(topChamps):
  plt.bar(i+1, firstBloodTop[i][1], width=0.8, align='center' )

fig_size = plt.rcParams["figure.figsize"]
fig_size[0] =5
fig_size[1] =10
plt.rcParams["figure.figsize"] = fig_size
plt.legend(bbox_to_anchor=(1.05,1), loc=2, borderaxespad=0)
plt.show()
for i in range(len(firstBloodTop)):
  print("Top " + str(i+1) + ": " + idchampions[str(firstBloodTop[i][0])] + " with " + str(firstBloodTop[i][1]))<jupyter_output>Top 1: Yasuo with 30
Top 2: MasterYi with 27
Top 3: Jhin with 26
Top 4: Zed with 26
Top 5: Kaisa with 25
Top 6: LeeSin with 21
Top 7: Vayne with 21
Top 8: Tristana with 20
Top 9: Pyke with 19
Top 10: MissFortune with 18
Top 11: Caitlyn with 18
Top 12: Twitch with 17
Top 13: Lux with 17
Top 14: Akali with 16
Top 15: Jax with 16
Top 16: Morgana with 16
Top 17: Irelia with 16
Top 18: Jinx with 15
Top 19: Riven with 15
Top 20: Darius with 15
Top 21: Draven with 14
Top 22: Ezreal with 14
Top 23: Quinn with 13
Top 24: Khazix with 12
Top 25: Ashe with 11
Top 26: Katarina with 11
Top 27: Vi with 10
Top 28: Kayn with 10
Top 29: Rengar with 10
Top 30: JarvanIV with 10
Top 31: Lucian with 9
Top 32: Neeko with 9
Top 33: DrMundo with 9
Top 34: Kayle with 9
Top 35: Xerath with 9
Top 36: Tryndamere with 9
Top 37: Teemo with 9
Top 38: Sivir with 9
Top 39: Veigar with 9
Top 40: Hecarim with 8
Top 41: Thresh with 8
Top 42: Olaf with 8
Top 43: Blitzcrank with 8
Top 44: Garen with 7
Top 45: Pantheon with [...]<jupyter_text>When there are more games played<jupyter_code>from datetime import datetime
import time
datesCount = {}
datesCount['mati'] = 0
datesCount['migdia'] = 0
datesCount['tarda'] = 0
datesCount['nit'] = 0

#8 - 12 matí 
#13 - 15 migdia
#16 - 21 tarda
#22 - 7 nit

for row in game.select("gameCreation").collect():
  date = datetime.fromtimestamp(row['gameCreation']/1000.0)
  print(date.strftime('%Y-%m-%d %H:%M:%S'))
  h = date.hour
  if(h>= 8 and h<= 12):
    datesCount['mati'] = datesCount['mati'] + 1
  elif(h>=13 and h<=15):
    datesCount['migdia'] = datesCount['migdia'] + 1
  elif(h>=16 and h <=21):
    datesCount['tarda'] = datesCount['tarda'] + 1
  else:
    datesCount['nit'] = datesCount['nit'] + 1<jupyter_output><empty_output><jupyter_text>Get champions with best and worst average of creep/s<jupyter_code>summonersRiftGames = spark.read.json("game.json")
summonersRiftGames.show(1)

champsIds = summonersRiftGames.select(explode('participants.championId').alias('ChampionId'))
creepsPerChamps = summonersRiftGames.select(explode('participants.stats.neutralMinionsKilled').alias('Creeps'))
jngleEnemyPerChamps = summonersRiftGames.select(explode('participants.stats.neutralMinionsKilledEnemyJungle').alias('EnJngle'))
jngleAllyPerChamps = summonersRiftGames.select(explode('participants.stats.neutralMinionsKilledTeamJungle').alias('AllyJngle'))
from pyspark.sql.functions import monotonically_increasing_id, asc, desc

creepsPerChamps = creepsPerChamps.withColumn("id", monotonically_increasing_id())

jngleEnemyPerChamps = jngleEnemyPerChamps.withColumn("id", monotonically_increasing_id())

jngleAllyPerChamps = jngleAllyPerChamps.withColumn("id", monotonically_increasing_id())

tmpDF = creepsPerChamps.join(jngleEnemyPerChamps, "id","full")
creepsDF = tmpDF.join(jngleAllyPerChamps, "id","full")
creepsDF = creepsDF.sort(asc("id")).drop("id")

totalCreeps = creepsDF.withColumn('GameCreeps', sum(creepsDF[col] for col in creepsDF.columns))
totalCreeps = totalCreeps.drop("Creeps")
totalCreeps = totalCreeps.drop("EnJngle")
totalCreeps = totalCreeps.drop("AllyJngle")

totalCreeps = totalCreeps.withColumn("id", monotonically_increasing_id())
champsIds = champsIds.sort(asc("ChampionId"))
champs = champsIds.withColumn("id", monotonically_increasing_id())

champCreeps = champs.join(totalCreeps, "id")
champCreeps = champCreeps.drop("id").sort(asc("ChampionId"))
champCreeps.show(10)
champCreepsDict = {}
maxValue = 0
minValue = 1000
minId = 0
maxId= 0
for row in champCreeps.rdd.collect():
  if not row['ChampionId'] in champCreepsDict:
    champID = row['ChampionId']
    champCreepsDict[champID] = (row['GameCreeps'],1)
  else:
    champID = row['ChampionId']
    creeps = row['GameCreeps']
    creeps = creeps + champCreepsDict.get(row['ChampionId'])[0]
    counter = champCreepsDict.get(row['ChampionId'])[1] + 1
    champCreepsDict[champID] = (creeps,counter)
    
for key in champCreepsDict:
  tupla = champCreepsDict[key]
  value = tupla[0] / tupla[1]
  if(value > maxValue):
    maxValue = value
    maxId = key
  elif(value < minValue):
    minValue = value
    minId = key
  champCreepsDict[key] = value
  
print("Champion with most avg creeps per games is: " + idchampions[str(maxId)] + " with " + str(maxValue) + " creeps per game")
print("Champion with less avg creeps per games is: " + idchampions[str(minId)] + " with " + str(minValue) + " creeps per game")<jupyter_output>Champion with most avg creeps per games is: Zed with 68.95652173913044 creeps per game
Champion with less avg creeps per games is: Zac with 18.666666666666668 creeps per game
{'fraction_non_alphanumeric': 0.15016590786326106, 'fraction_numerical': 0.04521953854464079, 'mean_word_length': 5.294317629917436, 'pattern_counts': {'":': 1, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '221961', 'license': 'MIT', 'n_tokens_mistral': 5386, 'n_tokens_neox': 4896, 'n_words': 1090, 'path': 'notebooks/lolAnswers.ipynb', 'repo_name': 'Adria331/BigDataLol', 'size': '12959'}