<jupyter_start><jupyter_text>Homework 3: NEO4J Graph DATABASE<jupyter_code>from neo4j import GraphDatabase, basic_auth
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import os 
from IPython.display import display, HTML

display(HTML(data="""
<style>
    div#notebook-container    { width: 100%; }
    div#menubar-container     { width: 65%; }
    div#maintoolbar-container { width: 99%; }
</style>
"""))
#Alessandro
driver = GraphDatabase.driver('bolt://localhost:7687', auth = basic_auth('neo4j', 'hw03'), encrypted=False)
session = driver.session()<jupyter_output><empty_output><jupyter_text>Graph ![Graph](graph.jpeg)  Q1: TOP 10 songs<jupyter_code>result_q1 = list(session.run("MATCH (p:songs_stat) \
WHERE  p.popularity > 80 RETURN p.name, p.popularity \
ORDER BY p.popularity DESC LIMIT 10;" ))
result_q1
title_song = []
p_score       = []
for item in range(len(result_q1)):
    title_song.append(result_q1[item][0])
    p_score.append(result_q1[item][1])
    
d_1 = {'Title Song': title_song, 'Popularity':p_score}
data_1 = pd.DataFrame(d_1)
data_1
plt.figure(figsize=[20,8])
plt.bar(title_song,p_score, width = 0.5, color='#0504aa',alpha=0.7)
plt.ylim(90, max(p_score)+.5)
plt.grid(axis='y', alpha=0.75)
plt.xlabel('Songs',fontsize=15)
plt.ylabel('Popularity',fontsize=15)
plt.xticks(fontsize=15, rotation=90)
plt.yticks(fontsize=15)
plt.ylabel('Popularity',fontsize=15)
plt.title('TOP 10 Songs',fontsize=15)
plt.show()<jupyter_output><empty_output><jupyter_text>Q2:  Popular artists with more than 50 songs<jupyter_code>result_q2 = list(session.run("\
MATCH (a:artist_not_popular) \
WHERE a.count_a>50 and a.popularity_a>55 \
RETURN a.artists_a, a.count_a, a.popularity_a \
ORDER BY a.popularity_a DESC LIMIT 10;"))
result_q2
artists    = []
count      = []
popularity = []
for item in range(len(result_q2)):
    artists.append(result_q2[item][0])
    count.append(result_q2[item][1])
    popularity.append(result_q2[item][2])
    
d_2 = {'Artists not Popular': artists,'Count': count  ,'Popularity Artist':popularity}
data_2 = pd.DataFrame(d_2)
data_2
plt.figure(figsize=[20,8])
plt.plot(artists, popularity, '-x', color = 'red')
plt.plot(artists,count,'-o')
plt.grid(axis='y', alpha=0.75)
plt.xticks(fontsize=15, rotation=90)
plt.yticks(fontsize=15)
plt.ylabel('Popularity',fontsize=15)
plt.xlabel('Artists',fontsize=15)
plt.title('Artists with more than 50 songs',fontsize=15)
plt.legend(['Popularity', 'count'])
plt.show()<jupyter_output><empty_output><jupyter_text>Q3: Artists with most songs that are explicit<jupyter_code>result_q3 = list(session.run("\
MATCH (e:songs_explicit)-[:HAS_DATA_E]->(s:songs_stat) \
RETURN e.artists, count(s.popularity) as explicit_songs \
ORDER BY explicit_songs DESC LIMIT 10;"))
artists    = []
d_songs      = []
for item in range(len(result_q3)):
    artists.append(result_q3[item][0])
    d_songs.append(result_q3[item][1])
d_3 = {'Explicit Artist': artists,'Explicit Songs': d_songs}
data_3 = pd.DataFrame(d_3)
data_3
fig, ax = plt.subplots(figsize=(20, 8), subplot_kw=dict(aspect="equal"))
recipe = artists
data = d_songs
wedges, texts = ax.pie(data, wedgeprops=dict(width=0.5), startangle=-40)
bbox_props = dict(boxstyle="square,pad=0.3", fc="w", ec="k", lw=0.72)
kw = dict(arrowprops=dict(arrowstyle="-"),
          bbox=bbox_props, zorder=0, va="center")
for i, p in enumerate(wedges):
    ang = (p.theta2 - p.theta1)/2. + p.theta1
    y = np.sin(np.deg2rad(ang))
    x = np.cos(np.deg2rad(ang))
    horizontalalignment = {-1: "right", 1: "left"}[int(np.sign(x))]
    connectionstyle = "angle,angleA=0,angleB={}".format(ang)
    kw["arrowprops"].update({"connectionstyle": connectionstyle})
    ax.annotate(recipe[i], xy=(x, y), xytext=(1.35*np.sign(x), 1.4*y),
                horizontalalignment=horizontalalignment, **kw)
plt.show()<jupyter_output><empty_output><jupyter_text>Q4: Viral songs per artists<jupyter_code>result_q4 = list(session.run("\
MATCH (s:songs_popular) \
RETURN s.artists, count(s.name) as tot_songs \
ORDER BY tot_songs DESC LIMIT 10;"))
result_q4
artists    = []
p_songs    = []

for item in range(len(result_q4)):
    artists.append(result_q4[item][0])
    p_songs.append(result_q4[item][1])
    
d_4 = {'Artists': artists, 'Viral songs': p_songs}
data_4 = pd.DataFrame(d_4)
data_4
plt.figure(figsize=[20,8])
plt.bar(artists,p_songs, width = 0.5, color='#0504ff',alpha=0.7)
plt.ylim(0,11)
plt.grid(axis='y', alpha=0.75)
plt.ylabel('Number of popular songs',fontsize=15)
plt.xticks(fontsize=15, rotation=90)
plt.yticks(fontsize=15)
plt.title('Viral songs per artists',fontsize=15)
plt.show()<jupyter_output><empty_output><jupyter_text>Q5: Artist with popular songs who have never made explicit songs<jupyter_code>result_q5 = list(session.run("\
MATCH (p:songs_popular)-[:BY]->(a:artist_popular) \
WHERE NOT EXISTS {MATCH (a)<-[:BY]-(e:songs_explicit)} \
RETURN  DISTINCT a.artists_a;"))
result_q5
artists    = []
for item in range(len(result_q5)):
    artists.append(result_q5[item][0])
    
d_5 = {'Artists with no explicit songs': artists}
data_5 = pd.DataFrame(d_5)
data_5<jupyter_output><empty_output><jupyter_text>Q6: Most popular songs by Billie Eilish<jupyter_code>result_q6 = list(session.run("\
MATCH (p:songs_popular)-[:HAS_DATA]->(s:songs_stat) \
WHERE p.artists='Billi<NAME>' \
RETURN p.name, s.popularity \
ORDER BY s.popularity DESC"))
result_q6
p_songs    = []
popularity = []

for item in range(len(result_q6)):
    p_songs.append(result_q6[item][0])
    popularity.append(result_q6[item][1])
    
d_6 = {'Popular songs (by Billie Eilish)': p_songs,'Popularity': popularity}
data_6 = pd.DataFrame(d_6)
data_6<jupyter_output><empty_output><jupyter_text>Q7: New Ranking Score<jupyter_code>result_q7 = list(session.run("\
MATCH (s:songs_stat) \
WITH max(s.energy) as max_energy, max(s.liveness) as max_liveness, max(s.tempo) as max_tempo, max(s.speechiness) as max_speechiness \
MATCH (s:songs_stat)<-[:HAS_DATA]-(x) \
RETURN DISTINCT  s.popularity*((s.energy/max_energy)+(s.liveness/max_liveness)+(s.tempo/max_tempo)+(s.speechiness/max_speechiness)) as score, s.name \
ORDER BY score DESC LIMIT 15;"))
result_q7
title_song = []
score       = []
for item in range(len(result_q7)):
    title_song.append(result_q7[item][1])
    score.append(result_q7[item][0])
d_7 = {'title_song':title_song, 'score':score}
data_7 = pd.DataFrame(d_7)
data_7
plt.figure(figsize=[20,8])
plt.scatter(title_song,score,color='green', marker = ",")
plt.grid(axis='y', alpha=0.75)
plt.xticks(fontsize=10, rotation=90)
plt.yticks(fontsize=15)
plt.ylabel('Score',fontsize=15)
plt.title('New Score Ranking',fontsize=15)
plt.show()<jupyter_output><empty_output><jupyter_text>Q8: AVG popularity of the songs by Taylor Swift compared to the popularity of the year of realese<jupyter_code>result_q8 = list(session.run("\
MATCH (p:songs_not_popular)-[:PUBLISHED]->(y:year) \
WHERE p.artists = '<NAME>' \
RETURN p.year, avg(p.popularity) as pop_mean,y.popularity_y \
ORDER BY p.year;"))
result_q8
p_year       = []
pop_mean     = []
popularity_y = []
for item in range(len(result_q8)):
    p_year.append(result_q8[item][0])
    pop_mean.append(result_q8[item][1])
    popularity_y.append(result_q8[item][2])
d_8 = {'Year': p_year, 'Popularity avg songs (by Taylor Swift)':pop_mean, 'Year Popularity':popularity_y}
data_8 = pd.DataFrame(d_8)
data_8
plt.figure(figsize=[20,8])
plt.plot(p_year, pop_mean, '-o')
plt.plot(p_year,popularity_y,'-v')
plt.ylim(0,100)
plt.grid(axis='y', alpha=0.75)
plt.xlabel('Year',fontsize=15)
plt.xticks(fontsize=10, rotation=90)
plt.yticks(fontsize=15)
plt.ylabel('popularity',fontsize=15)
plt.title(' AVG popularity of the songs by Taylor Swift compared to the popularity of the realesed year',fontsize=15)
plt.legend(['Popularity avg of the songs by <NAME>', 'Popularity year'])
plt.show()<jupyter_output><empty_output><jupyter_text>Q9: number of popular and not popular songs by popular artists<jupyter_code>result_q9 = list(session.run("\
MATCH (p:songs_popular)-[:BY]->(a:artist_popular)<-[:BY]-(n:songs_not_popular) \
RETURN a.artists_a, count(DISTINCT p.name) as popular_songs, count(DISTINCT n.name) as not_popular_songs;"))
result_q9
artist       = []
popular_songs     = []
not_popular_songs = []
for item in range(len(result_q9)):
    artist.append(result_q9[item][0])
    popular_songs.append(result_q9[item][1])
    not_popular_songs.append(result_q9[item][2])
d_9 = {'Artists': artist, 'popular songs':popular_songs, 'not popular songs':not_popular_songs}
data_9 = pd.DataFrame(d_9)
data_9<jupyter_output><empty_output><jupyter_text>Q10: percentage of songs' keys in the year 2020<jupyter_code>result_q10 = list(session.run("\
MATCH (x)-[:HAS_DATA]->(s:songs_stat) \
WHERE x.year=2020 WITH TOFLOAT(COUNT(*))/100 as d, COLLECT(s.key) AS stats UNWIND stats AS stat WITH d, stat \
RETURN stat, COUNT(*) AS tot, COUNT(*)/d AS percentage ORDER BY stat;"))
result_q10
key      = []
tot     = []
perc = []
for item in range(len(result_q10)):
    key.append(result_q10[item][0])
    tot.append(result_q10[item][1])
    perc.append(result_q10[item][2])
d_10 = {'Key':key, 'Total songs':tot, 'Percentage':perc}
data_10 = pd.DataFrame(d_10)
data_10.sort_values(by=['Total songs'])
plt.figure(figsize=[20,8])
plt.grid(axis='y', alpha=0.75)
x_1  = np.array([0,1, 2, 3, 4, 5, 6, 7,8,9,10,11])
my_xticks = ["B", "C", "C#", "D", "D#", "E", "F", "F#", "G", "G#", "A", "A#"]
plt.xticks(x_1, my_xticks, rotation = 0)
plt.bar(key,tot, width = 0.5, color='blue',alpha=0.7)
plt.ylabel('Frequency',fontsize=15)
plt.xticks(fontsize=15)
plt.yticks(fontsize=15)
plt.title("percentage of songs' keys in the year 2020",fontsize=15)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16864853686278564, 'fraction_numerical': 0.038051907765484436, 'mean_word_length': 6.106539309331374, 'pattern_counts': {'":': 1, '<': 53, '<?xml version=': 0, '>': 59, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '283142', 'license': 'Apache-2.0', 'n_tokens_mistral': 4124, 'n_tokens_neox': 3838, 'n_words': 701, 'path': 'Neo4j/queries.ipynb', 'repo_name': 'qtt-alessandro/MySQL-vs-Neo4j-Project', 'size': '9685'}