<jupyter_start><jupyter_text>Data analysis of the billboard 100 dataThis document is part of the showcase, where I replicate the same brief and simple analyses with different tools.This particular file focuses on data analysis (a few queries) of the billboard 100 data from the tidytuesday project.The data can be found in . They consist of two documents: *billboard.csv* contains information about the songs focusing on their position in the top100 list at different weeks. *audio_features.csv* contains information about specific attributes of the songs from spotify.For the specific analysis I will use **Python** and **Pyspark** (plus **Jupyter notebook**).We start by loading the pyspark package:<jupyter_code>from pyspark.sql import SparkSession<jupyter_output><empty_output><jupyter_text>Then we need to create a Spark Session:<jupyter_code>spark = SparkSession.builder.getOrCreate()<jupyter_output><empty_output><jupyter_text>We load the *billboard* dataset:<jupyter_code>from pyspark import SparkFiles

url = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2021/2021-09-14/billboard.csv"
spark.sparkContext.addFile(url)
billboard = spark.read.csv(SparkFiles.get("billboard.csv"), header=True)

# import pandas as pd
# df = spark.createDataFrame(pd.read_csv(url))<jupyter_output><empty_output><jupyter_text>And check its schema:<jupyter_code>billboard.printSchema()<jupyter_output>root
 |-- url: string (nullable = true)
 |-- week_id: string (nullable = true)
 |-- week_position: string (nullable = true)
 |-- song: string (nullable = true)
 |-- performer: string (nullable = true)
 |-- song_id: string (nullable = true)
 |-- instance: string (nullable = true)
 |-- previous_week_position: string (nullable = true)
 |-- peak_position: string (nullable = true)
 |-- weeks_on_chart: string (nullable = true)<jupyter_text>The schema indicates that everything is a string, so we have to fix it:<jupyter_code>from pyspark.sql.functions import col
from pyspark.sql.types import StringType,DateType,IntegerType,BooleanType

billboard = billboard.withColumn("week_position",col("week_position").cast(IntegerType())) \
    .withColumn("week_id",col("week_id").cast(DateType())) \
    .withColumn("instance",col("instance").cast(IntegerType())) \
    .withColumn("previous_week_position",col("previous_week_position").cast(IntegerType())) \
    .withColumn("peak_position",col("peak_position").cast(IntegerType())) \
    .withColumn("weeks_on_chart",col("weeks_on_chart").cast(IntegerType()))
billboard.printSchema()<jupyter_output>root
 |-- url: string (nullable = true)
 |-- week_id: date (nullable = true)
 |-- week_position: integer (nullable = true)
 |-- song: string (nullable = true)
 |-- performer: string (nullable = true)
 |-- song_id: string (nullable = true)
 |-- instance: integer (nullable = true)
 |-- previous_week_position: integer (nullable = true)
 |-- peak_position: integer (nullable = true)
 |-- weeks_on_chart: integer (nullable = true)<jupyter_text>We check indicatively a single row:<jupyter_code>billboard.show(1, truncate=False, vertical=True)<jupyter_output>-RECORD 0--------------------------------------------------------------------
 url                    | http://www.billboard.com/charts/hot-100/1965-07-17 
 week_id                | null                                               
 week_position          | 34                                                 
 song                   | Don't Just Stand There                             
 performer              | <NAME>                                         
 song_id                | Don't Just Stand There<NAME>                   
 instance               | 1                                                  
 previous_week_position | 45                                                 
 peak_position          | 34                                                 
 weeks_on_chart         | 4                                                  
only showing top 1 row<jupyter_text>And the summary statistics:<jupyter_code>billboard.describe().show(truncate=False, vertical=True)<jupyter_output>-RECORD 0-------------------------------------------------------------------------------------------
 summary                | count                                                                     
 url                    | 327895                                                                    
 week_position          | 327895                                                                    
 song                   | 327895                                                                    
 performer              | 327895                                                                    
 song_id                | 327895                                                                    
 instance               | 327729                                                                    
 previous_week_position | 295785                                                                    
 peak_position          | 327888                                                           [...]<jupyter_text>Also the column names:<jupyter_code>billboard.columns<jupyter_output><empty_output><jupyter_text>We can choose to transform the Spark dataset into a Pandas dataset:<jupyter_code>billboard.toPandas()<jupyter_output><empty_output><jupyter_text>For the first main query, our aim is to select only the songs that have reached the No 1 spot of the billboard and see how many weeks they have stayed at the billboard in total:<jupyter_code>import pyspark.sql.functions as sf

top_songs = billboard.filter("peak_position == 1")\
    .drop('url', 'peak_position', 'previous_week_position')\
    .groupBy("performer", "song").agg(sf.max('weeks_on_chart').alias('max_weeks'))\
    .sort(col("max_weeks").desc())
top_songs.show(10)<jupyter_output>+--------------------+--------------------+---------+
|           performer|                song|max_weeks|
+--------------------+--------------------+---------+
|           90210"")"|"The Right Kind O...|       97|
|        Big City"")"|"Kiss And Tell (F...|       95|
|                2"""|"Roc Ya Body ""Mi...|       94|
|        Big City"")"|"Century's End (F...|       92|
|           90210"")"|"Love Is (From ""...|       92|
|Master P Featurin...|"If I Could Chang...|       90|
|LMFAO Featuring L...|   Party Rock Anthem|       68|
|           90210"")"|"Saving Forever F...|       67|
|               Adele| Rolling In The Deep|       65|
|Brandy, Tamia, Gl...|"Missing You (Fro...|       62|
+--------------------+--------------------+---------+
only showing top 10 rows<jupyter_text>Alternatively, we can use SQL syntax. We start with a basic query that corresponds to extracting the first 10 lines of the billboard dataframe:<jupyter_code>billboard.createOrReplaceTempView("billboard_SQL")

sql_basic = spark.sql("SELECT * FROM billboard_SQL LIMIT 10")
sql_basic.show(5, truncate=False, vertical=True)<jupyter_output>-RECORD 0--------------------------------------------------------------------
 url                    | http://www.billboard.com/charts/hot-100/1965-07-17 
 week_id                | null                                               
 week_position          | 34                                                 
 song                   | Don't Just Stand There                             
 performer              | <NAME>                                         
 song_id                | Don't Just Stand TherePatty Duke                   
 instance               | 1                                                  
 previous_week_position | 45                                                 
 peak_position          | 34                                                 
 weeks_on_chart         | 4                                                  
-RECORD 1--------------------------------------------------------------------
 url                    | http://www.billboard.com/charts/hot-10[...]<jupyter_text>The same first main query with spark and SQL syntax is:<jupyter_code>sql_top10 = spark.sql("SELECT performer, song, MAX(weeks_on_chart) AS max_weeks\
  FROM billboard_SQL\
  WHERE (peak_position=1.0)\
  GROUP BY performer, song\
  ORDER BY max_weeks DESC\
  LIMIT 10")
sql_top10.show()<jupyter_output>+--------------------+--------------------+---------+
|           performer|                song|max_weeks|
+--------------------+--------------------+---------+
|           90210"")"|"The Right Kind O...|       97|
|        Big City"")"|"Kiss And Tell (F...|       95|
|                2"""|"Roc Ya Body ""Mi...|       94|
|        Big City"")"|"Century's End (F...|       92|
|           90210"")"|"Love Is (From ""...|       92|
|Master P Featurin...|"If I Could Chang...|       90|
|LMFAO Featuring L...|   Party Rock Anthem|       68|
|           90210"")"|"Saving Forever F...|       67|
|               Adele| Rolling In The Deep|       65|
|<NAME>ia, Gl...|"Missing You (Fro...|       62|
+--------------------+--------------------+---------+<jupyter_text>We now load the *audio_features* dataset and we fix the schema:<jupyter_code>url = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2021/2021-09-14/audio_features.csv"
spark.sparkContext.addFile(url)
audio_features = spark.read.csv(SparkFiles.get("audio_features.csv"), header=True)
audio_features.printSchema()
audio_features = audio_features.withColumn("spotify_track_duration_ms",col("spotify_track_duration_ms").cast(IntegerType())) \
    .withColumn("spotify_track_explicit",col("spotify_track_explicit").cast(BooleanType())) \
    .withColumn("danceability",col("danceability").cast(IntegerType())) \
    .withColumn("energy",col("energy").cast(IntegerType())) \
    .withColumn("key",col("key").cast(IntegerType())) \
    .withColumn("loudness",col("loudness").cast(IntegerType())) \
    .withColumn("mode",col("mode").cast(IntegerType())) \
    .withColumn("speechiness",col("speechiness").cast(IntegerType())) \
    .withColumn("acousticness",col("acousticness").cast(IntegerType())) \
    .withColumn("instrumentalness",col("instrumentalness").cast(IntegerType())) \
    .withColumn("liveness",col("liveness").cast(IntegerType())) \
    .withColumn("valence",col("valence").cast(IntegerType())) \
    .withColumn("tempo",col("tempo").cast(IntegerType())) \
    .withColumn("time_signature",col("time_signature").cast(IntegerType())) \
    .withColumn("spotify_track_popularity",col("spotify_track_popularity").cast(IntegerType()))
audio_features.printSchema()<jupyter_output>root
 |-- song_id: string (nullable = true)
 |-- performer: string (nullable = true)
 |-- song: string (nullable = true)
 |-- spotify_genre: string (nullable = true)
 |-- spotify_track_id: string (nullable = true)
 |-- spotify_track_preview_url: string (nullable = true)
 |-- spotify_track_duration_ms: integer (nullable = true)
 |-- spotify_track_explicit: boolean (nullable = true)
 |-- spotify_track_album: string (nullable = true)
 |-- danceability: integer (nullable = true)
 |-- energy: integer (nullable = true)
 |-- key: integer (nullable = true)
 |-- loudness: integer (nullable = true)
 |-- mode: integer (nullable = true)
 |-- speechiness: integer (nullable = true)
 |-- acousticness: integer (nullable = true)
 |-- instrumentalness: integer (nullable = true)
 |-- liveness: integer (nullable = true)
 |-- valence: integer (nullable = true)
 |-- tempo: integer (nullable = true)
 |-- time_signature: integer (nullable = true)
 |-- spotify_track_popularity: integer (nullable = true)<jupyter_text>For the second main query, our aim is to derive information about the peak position a song has reached in the billboard and the main spotify information. For this, we need to join the two datasets:<jupyter_code>df_merge_left = billboard.groupBy("performer", "song", "song_id").agg(sf.max('peak_position').alias('best_position'))
df_merge_right = audio_features.select('song_id', 'spotify_genre', 'danceability', 'energy', 'key', 'loudness', 'speechiness', 'acousticness', 'instrumentalness', 'liveness', 'valence', 'tempo')
df_merge = df_merge_left.join(df_merge_right,df_merge_left.song_id ==  df_merge_right.song_id,"inner") \
     .sort(col("performer").asc())
df_merge.show(5, truncate=False, vertical=True)<jupyter_output>-RECORD 0-------------------------------------------------------------------------------
 performer        | """Groove"" Holmes"                                                 
 song             | Misty                                                               
 song_id          | "Misty""Groove"" Holmes"                                            
 best_position    | 100                                                                 
 song_id          | "Misty""Groove"" Holmes"                                            
 spotify_genre    | ['instrumental soul', 'jazz funk', 'jazz organ', 'soul jazz']       
 danceability     | 0                                                                   
 energy           | 0                                                                   
 key              | 8                                                                   
 loudness         | -9                                                                  
 speechiness      | 0[...]
{'fraction_non_alphanumeric': 0.1623508218869624, 'fraction_numerical': 0.017113262778653455, 'mean_word_length': 1.7308874769419964, 'pattern_counts': {'":': 0, '<': 52, '<?xml version=': 0, '>': 52, 'https://': 2, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 65, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '252689', 'license': 'Apache-2.0', 'n_tokens_mistral': 3665, 'n_tokens_neox': 3374, 'n_words': 791, 'path': 'billb100_Python_Pyspark.ipynb', 'repo_name': 'themisbo/showcase', 'size': '13340'}