Encre vers Insights Comparaison des requêtes SQL et Python à l’aide de l’analyse de la librairie

Comparaison des requêtes SQL et Python avec analyse de la librairie.

Quelle approche est meilleure pour votre analyse exploratoire des données?

SQL est le pain quotidien de la boîte à outils de tout scientifique des données – la capacité de récupérer rapidement des données à partir d’une source de données pour l’analyse est une compétence essentielle pour toute personne travaillant avec de grandes quantités de données. Dans cet article, je voulais donner quelques exemples de quelques requêtes de base que j’utilise généralement en SQL, au cours d’un processus d’EDA. Je comparerai ces requêtes avec des scripts similaires en Python qui produisent le même résultat, en comparant les deux approches.

Pour cette analyse, j’utiliserai des données synthétiques sur les livres les mieux notés de l’année dernière d’une chaîne hypothétique de librairies (la librairie Total Fiction). Un lien vers le dossier GitHub de ce projet peut être trouvé ici, où je détaille l’exécution de l’analyse.

En passant — bien que je me concentre principalement sur les requêtes SQL dans cet article, il est intéressant de noter que ces requêtes peuvent être intégrées assez facilement avec Python en utilisant la bibliothèque pandaSQL (comme je l’ai fait pour ce projet). Cela peut être vu en détail dans le notebook Jupyter sur le lien GitHub de ce projet, mais la structure de cette requête se présente généralement comme suit:

query = """SELECT * FROM DATA"""output = sqldf(query,locals())output

PandaSQL est une bibliothèque très pratique pour ceux qui ont une plus grande familiarité avec les requêtes SQL que la manipulation typique des ensembles de données Pandas – et est souvent beaucoup plus facile à lire, comme je le montrerai ici.

• Programmation avec l’IA
• Reconnaissance d’entité avec LLM Une évaluation complète
• Êtes-vous un développeur efficace? Alors l’IA est après votre travail

Le jeu de données

Un extrait du jeu de données peut être vu ci-dessous – il y a des colonnes pour le titre du livre et l’année de publication, le nombre de pages, les genres, la note moyenne du livre, l’auteur, le nombre d’unités vendues et les revenus du livre.

Analyse des revenus par décennie

Disons que je veux savoir quelle décennie a publié les livres les plus rentables pour la librairie. Le jeu de données d’origine n’a pas de colonne indiquant la décennie de publication des livres – cependant, il est relativement simple d’ajouter ces données aux données. J’exécute une sous-requête pour diviser l’année en utilisant la division entière et multiplier par 10 pour obtenir les données de la décennie, avant d’agréger et de faire la moyenne des votes par décennie. Ensuite, je trie les résultats par revenu total pour obtenir les décennies les plus rentables des livres publiés dans la librairie.

WITH bookshop AS(SELECT TITLE, YEARPUBLISHED,(YEARPUBLISHED/10) * 10 AS DECADE,NUMPAGES, GENRES, RATING, AUTHOR, UNITSSOLD,REVENUEfrom df)SELECT DECADE, SUM(REVENUE) AS TOTAL_REVENUE,ROUND(AVG(REVENUE),0) AS AVG_REVENUEFROM bookshopGROUP BY DECADEORDER BY TOTAL_REVENUE DESC

En comparaison, une sortie équivalente en Python ressemblerait à quelque chose comme le code ci-dessous. J’applique une fonction lambda qui effectue la division entière et renvoie la décennie, puis j’agrège les votes par décennie et trie le résultat par revenu total.

# creating df bookshopbookshop = df.copy()bookshop['Decade'] = (bookshop['YearPublished'] // 10) * 10# group by decade, agg revenue by sum and meanresult = bookshop.groupby('DECADE') \                 .agg({'Revenue': ['sum', 'mean']}) \                 .reset_index()result.columns = ['Decade', 'Total_Revenue', 'Avg_Revenue']# sorting by decaderesult = result.sort_values('Total_Revenue')

Notez le plus grand nombre d’étapes distinctes qu’il y a dans le script Python pour obtenir le même résultat – les fonctions sont maladroites et difficiles à comprendre au premier coup d’œil. En comparaison, le script SQL est beaucoup plus clair dans sa présentation et beaucoup plus facile à lire.

Je peux maintenant prendre cette requête et la visualiser pour avoir une idée des tendances des revenus des livres au fil des décennies, en configurant un graphique matplotlib à l’aide du script suivant – les graphiques en barres montrent les revenus totaux par décennie, avec un graphique en nuage de points sur l’axe secondaire pour montrer les revenus moyens des livres.

# Création de l'axe y principal (revenu total)
fig, ax1 = plt.subplots(figsize=(15, 9))
ax1.bar(agg_decade['DECADE'], agg_decade['TOTAL_REVENUE'], 
        width = 0.4, align='center', label='Revenu total (dollars)')
ax1.set_xlabel('Décennie')
ax1.set_ylabel('Revenu total (dollars)', color='blue')
# Ajustement des lignes de grille sur l'axe y principal
ax1.grid(color='blue', linestyle='--', linewidth=0.5, alpha=0.5)
# Création de l'axe y secondaire (revenu moyen)
ax2 = ax1.twinx()
ax2.scatter(agg_decade['DECADE'], agg_decade['AVG_REVENUE'], 
            marker='o', color='red', label='Revenu moyen (dollars)')
ax2.set_ylabel('Revenu moyen (dollars)', color='red')
# Ajustement des lignes de grille sur l'axe y secondaire
ax2.grid(color='red', linestyle='--', linewidth=0.5, alpha=0.5)
# Réglage des mêmes limites d'axe y pour ax1 et ax2
ax1.set_ylim(0, 1.1*max(agg_decade['TOTAL_REVENUE']))
ax2.set_ylim(0, 1.1*max(agg_decade['AVG_REVENUE']))
# Combinaison des légendes pour les deux axes
lines, labels = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc='upper left')
# Définition du titre
plt.title('Revenu total et moyen par décennie')
# Affichage du graphique
plt.show()

La visualisation peut être vue ci-dessous – les livres publiés dans les années 1960 sont apparemment les plus rentables pour la librairie, générant plus de 192 000 $ de revenus pour la librairie Total Fiction. En comparaison, les livres de la liste des années 1900 sont plus rentables en moyenne, bien qu’ils ne se soient pas aussi bien vendus que les livres des années 1960.

Le revenu moyen des livres suit une tendance similaire au revenu total pour toutes les décennies des livres publiés – à l’exception des livres des années 1900 et 1980, qui sont plus rentables en moyenne mais pas globalement.

Analyse de l’auteur

Maintenant, supposons que je veuille obtenir des données sur les 10 meilleurs auteurs de la liste, classés par leur revenu total généré. Pour cette requête, je veux connaître le nombre de livres qu’ils ont écrits qui figurent sur la liste, le revenu total qu’ils ont généré avec ces livres, leur revenu moyen par livre et la note moyenne de ces livres dans la librairie. Une question assez simple à répondre en utilisant SQL – je peux utiliser une instruction de comptage pour obtenir le nombre total de livres qu’ils ont écrits, et des instructions de moyenne pour obtenir le revenu moyen et la note par auteur. Ensuite, je peux regrouper ces instructions par auteur.

SELECT AUTHOR,COUNT(TITLE) AS NUM_BOOKS,SUM(REVENUE) AS TOTAL_REVENUE,ROUND(AVG(REVENUE),0) AS AVG_REVENUE,ROUND(AVG(RATING),2) AS AVG_RATING_PER_BOOKFROM bookshopGROUP BY AUTHORORDER BY TOTAL_REVENUE DESCLIMIT 10

Un script Python équivalent ressemblerait à ceci – à peu près la même longueur, mais beaucoup plus complexe pour le même résultat. Je regroupe les valeurs par auteur avant de spécifier comment agréger chaque colonne dans la fonction agg, puis je trie les valeurs par revenu total. Encore une fois, le script SQL est beaucoup plus clair en comparaison.

result = bookshop.groupby('Author') \                 .agg({                     'Title': 'count',                     'Revenue': ['sum', 'mean'],                     'Rating': 'mean'                 }) \                 .reset_index()result.columns = ['Author', 'Num_Books', 'Total_Revenue',                   'Avg_Revenue', 'Avg_Rating_per_Book']# Tri par revenu totalresult = result.sort_values('Total_Revenue', ascending=False)# top 10result_top10 = result.head(10)

Le résultat de cette requête peut être consulté ci-dessous — Ava Mitchell est en tête, avec un chiffre d’affaires total de plus de 152 000 $ provenant de la vente de ses livres. Emma Hayes arrive en deuxième position avec plus de 85 000 $, suivi de près par Liam Parker avec plus de 83 000 $.

En visualisant cela avec matplotlib en utilisant le script suivant, nous pouvons générer des diagrammes en barres du chiffre d’affaires total avec des points de données montrant le chiffre d’affaires moyen par auteur. La note moyenne par auteur est également tracée sur un axe secondaire.

# Création de la figure et de l'axe
fig1, ax1 = plt.subplots(figsize=(15, 9))

# Tracé du diagramme en barres du chiffre d'affaires total
ax1.bar(agg_author['Author'], agg_author['TOTAL_REVENUE'], width=0.4, align='center', color='silver', label='Chiffre d\'affaires total (Dollars)')
ax1.set_xlabel('Auteur')
ax1.set_xticklabels(agg_author['Author'], rotation=-45, ha='left')
ax1.set_ylabel('Chiffre d\'affaires total (Dollars)', color='blue')

# Réglage des lignes de grille sur l'axe y primaire
ax1.grid(color='blue', linestyle='--', linewidth=0.5, alpha=0.5)

# Création du diagramme de dispersion du chiffre d'affaires moyen
ax1.scatter(agg_author['Author'], agg_author['AVG_REVENUE'], marker="D", color='blue', label='Chiffre d\'affaires moyen par livre (Dollars)')

# Création du diagramme de dispersion de la note moyenne sur l'axe secondaire
ax2 = ax1.twinx()
ax2.scatter(agg_author['Author'], agg_author['AVG_RATING_PER_BOOK'], marker='^', color='red', label='Note moyenne par livre')
ax2.set_ylabel('Note moyenne', color='red')

# Réglage des lignes de grille sur l'axe y secondaire
ax2.grid(color='red', linestyle='--', linewidth=0.5, alpha=0.5)

# Combinaison des légendes pour les deux axes
lines, labels = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines + lines2, labels + labels2, loc='upper right')

# Définition du titre
plt.title('Top 10 des auteurs par chiffre d\'affaires, note')

# Affichage du graphique
plt.show()

En exécutant cela, nous obtenons le graphique suivant :

Ce graphique indique clairement une assertion — le chiffre d’affaires ne corréle pas avec la note moyenne de chaque auteur. Ava Mitchell a le chiffre d’affaires le plus élevé mais se situe dans la moyenne en termes de note pour les auteurs cités ci-dessus. Olivia Hudson obtient la meilleure note moyenne tout en se classant 8e en termes de votes totaux ; il n’y a pas de tendance observable entre le chiffre d’affaires d’un auteur et sa popularité.

Comparaison de la longueur du livre et du chiffre d’affaires

Enfin, supposons que je veuille montrer comment le chiffre d’affaires des livres diffère en fonction de leur longueur. Pour répondre à cette question, je souhaite d’abord diviser les livres en 4 catégories égales en fonction des quartiles de leur longueur, ce qui donnera une meilleure idée des tendances générales du chiffre d’affaires par rapport à la longueur du livre.

Tout d’abord, je définis les quartiles en SQL, en utilisant une sous-requête pour générer ces valeurs, avant de trier les livres dans ces catégories en utilisant une instruction case when.

WITH PERCENTILES AS (    SELECT         PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY NUMPAGES)         AS PERCENTILE_25,        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY NUMPAGES)         AS MEDIAN,        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY NUMPAGES)         AS PERCENTILE_75    FROM bookshop)SELECT     TITLE, TITLE, REVENUE, NUMPAGES,    CASE        WHEN NUMPAGES< (SELECT PERCENTILE_25 FROM PERCENTILES)         THEN 'Quartile 1'        WHEN NUMPAGES BETWEEN (SELECT PERCENTILE_25 FROM PERCENTILES)         AND (SELECT MEDIAN FROM PERCENTILES) THEN 'Quartile 2'        WHEN NUMPAGES BETWEEN (SELECT MEDIAN FROM PERCENTILES)         AND (SELECT PERCENTILE_75 FROM PERCENTILES) THEN 'Quartile 3'        WHEN NUMPAGES > (SELECT PERCENTILE_75 FROM PERCENTILES)         THEN 'Quartile 4'    END AS PAGELENGTH_QUARTILEFROM bookshopORDER BY REVENUE DESC

Alternativement (pour les dialectes SQL qui ne prennent pas en charge les fonctions de percentile, comme SQLite), les quartiles peuvent être calculés séparément avant de les entrer manuellement dans l’instruction “case when”.

-- Pour le dialecte SQLiteSELECT TITLE, REVENUE, NUMPAGES,CASEWHEN NUMPAGES < 318 THEN 'Quartile 1'WHEN NUMPAGES BETWEEN 318 AND 375 THEN 'Quartile 2'WHEN NUMPAGES BETWEEN 375 AND 438 THEN 'Quartile 3'WHEN NUMPAGES > 438 THEN 'Quartile 4'END AS PAGELENGTH_QUARTILEFROM bookshopORDER BY REVENUE DESC

En exécutant cette même requête en Python, je définis les percentiles en utilisant numpy avant d’utiliser la fonction “cut” pour trier les livres dans leurs compartiments, puis de trier les valeurs par longueur des livres en pages. Comme précédemment, ce processus est nettement plus complexe que le script équivalent en SQL.

# Définir les percentiles en utilisant numpypercentiles = np.percentile(bookshop['NumPages'], [25, 50, 75])# Définir les limites des compartiments en utilisant les percentiles calculésbin_edges = [-float('inf'), *percentiles, float('inf')]# Définir les étiquettes pour les compartimentsbucket_labels = ['Quartile 1', 'Quartile 2', 'Quartile 3', 'Quartile 4']# Créer la colonne 'RUNTIME_BUCKET' en fonction des limites des compartiments et des étiquettesbookshop['RUNTIME_BUCKET'] = pd.cut(bookshop['NumPages'], bins=bin_edges,                                 labels=bucket_labels)result = bookshop[['Title', 'Revenue',                'NumPages', 'PAGELENGTH_QUARTILE']].sort_values(by='NumPages',                                                           ascending=False)

Le résultat de cette requête peut être visualisé sous forme de diagrammes en boîte à moustaches à l’aide de seaborn – un extrait du script utilisé pour générer les diagrammes en boîte à moustaches peut être vu ci-dessous. Notez que les compartiments de temps d’exécution ont été triés manuellement dans l’ordre correct pour les présenter correctement.

# Définir le style des graphiquessns.set(style="whitegrid")# Définition de l'ordre des compartiments de revenuspagelength_bucket_order = ['Quartile 1', 'Quartile 2',                         'Quartile 3', 'Quartile 4']# Créer le diagramme en boîteplt.figure(figsize=(16, 10))sns.boxplot(x='PAGELENGTH_QUARTILE', y='Revenue',             data=pagelength_output, order = pagelength_bucket_order,             showfliers=True)# Ajouter des étiquettes et un titreplt.xlabel('Quartile de longueur de page')plt.ylabel('Revenu (Dollars)')plt.title('Diagramme en boîte du revenu par compartiment de longueur de page')# Afficher le graphiqueplt.show()

Les diagrammes en boîte peuvent être visualisés ci-dessous – notez que le revenu médian pour chaque quartile de longueur de livre augmente à mesure que les livres deviennent plus longs. Cela suggère que les livres plus longs sont plus rentables à la librairie.

De plus, la plage du 4ème quartile est beaucoup plus large par rapport aux autres quartiles, ce qui indique qu’il y a plus de variation dans le point de prix pour les livres plus grands.

Réflexions finales et applications supplémentaires

En conclusion, l’utilisation de SQL pour les requêtes d’analyse de données est généralement beaucoup plus simple que l’utilisation d’opérations équivalentes en Python ; le langage est plus facile à écrire que les requêtes en Python, tout en étant capable de produire les mêmes résultats. Je ne dirais pas que l’un est meilleur que l’autre – j’ai utilisé une combinaison des deux langages dans cette analyse – mais je pense que l’utilisation d’une combinaison des deux langages peut produire une analyse de données plus efficace et plus efficace.

Par conséquent, étant donné la plus grande clarté dans l’écriture des requêtes SQL par rapport aux requêtes en Python, je pense qu’il est beaucoup plus naturel d’utiliser cela lors de la réalisation de l’EDA initiale d’un projet. SQL est beaucoup plus facile à lire et à écrire, comme je l’ai montré dans cet article, ce qui le rend particulièrement avantageux pour ces premières tâches exploratoires. Je l’utilise souvent lorsque je commence un projet, et je recommanderais cette approche à toute personne qui a déjà une bonne compréhension des requêtes SQL.

We will continue to update IPGirl; if you have any questions or suggestions, please contact us!