DEV Community

Dendi Handian
Dendi Handian

Posted on • Edited on

3

SQL Query into Pandas DataFrame - Part 3

After playing some aggregation and grouping in the last part, now we will play harder with table joins.

The Playground Database

We will be using the same SQLite database, but now we are going to use some tables. So get all the required csv files here

Preparing the DataFrame

import pandas as pd

albums_df = pd.read_csv("albums.csv")
artists_df = pd.read_csv("artists.csv")
Enter fullscreen mode Exit fullscreen mode

Join Queries into Pandas DataFrame

INNER JOIN:

SQL:

SELECT
    *
FROM albums
JOIN artists ON albums.ArtistId = artists.ArtistId
Enter fullscreen mode Exit fullscreen mode

or

SELECT
    *
FROM albums
INNER JOIN artists ON albums.ArtistId = artists.ArtistId
Enter fullscreen mode Exit fullscreen mode

Pandas:

# For the exact same column name on both table
albums_df.merge(artists_df, on='ArtistId')

# Defining the join column of each tables
albums_df.merge(artists_df, left_on='ArtistId', right_on='ArtistId')

# To make sure we use the INNER one
albums_df.merge(artists_df, left_on='ArtistId', right_on='ArtistId', how='inner')
Enter fullscreen mode Exit fullscreen mode

LEFT JOIN

SQL:

SELECT
    *
FROM albums
LEFT JOIN artists ON albums.ArtistId = artists.ArtistId
Enter fullscreen mode Exit fullscreen mode

Pandas:

albums_df.merge(artists_df, on='ArtistId', how='left')
Enter fullscreen mode Exit fullscreen mode

RIGHT JOIN

SQL:

SELECT
    *
FROM albums
RIGHT JOIN artists ON albums.ArtistId = artists.ArtistId
Enter fullscreen mode Exit fullscreen mode

Pandas:

albums_df.merge(artists_df, on='ArtistId', how='right')
Enter fullscreen mode Exit fullscreen mode

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay