צור קשר

צ׳יט שיט SQL ל-Pandas

מעוניינים להמיר את הידע שלכם ב-SQL לפקודות Pandas . להלן צ׳יט שיט המציג השוואה בין פקודות SQL נפוצות לבין המקבילות שלהן ב-Pandas, ומאפשר להבין בקלות איך לבצע פעולות אנליטיות שונות.

לפני שיוצאים לדרך – יש לציין: פעמים רבות ב-Pandas יש יותר מדרך אחת לביצוע אותה פעולה. בצ׳יט שיט המצורף, הדרך הישירה והפשוטה ביותר לרוב היא שמצויינת. 

Table of Contents

Basic SQL Statements

* df is a common variable name used to refer to a DataFrame object

SQL CommandPandas Equivalent
Select alldf
Select specific columnsdf[['column1', 'column2']]
Arithmetic operationsdf['column'] + value
String concatenationdf['col1'] + ' ' + df['col2']
Column aliasdf.rename(columns={'old_col': 'new_col'})
Distinct values of a single columndf['column'].unique()
Distinct values of multiple columnsdf.drop_duplicates(['column1', 'column2'])

Filter the Dataset

SQL CommandPandas Equivalent
WHERE column = valuedf.query("column == value")
WHERE column > valuedf.query("column > value")
WHERE column < valuedf.query("column < value")
WHERE column >= valuedf.query("column >= value")
WHERE column <= valuedf.query("column <= value")
WHERE column != valuedf.query("column != value")
WHERE column IN (value1, value2, …)df[df['column'].isin([value1, value2])]
WHERE column BETWEEN value1 AND value2df[(df['column'] >= value1) & (df['column'] <= value2)]
WHERE column LIKE 'pattern'df[df['column'].str.contains('pattern')]
WHERE column IS NULLdf[df['column'].isnull()]
WHERE column IS NOT NULLdf[df['column'].notnull()]
WHERE condition1 AND condition2df[(condition1) & (condition2)]
WHERE condition1 OR condition2df[(condition1) | (condition2)]

Sort the Result Set

SQL CommandPandas Equivalent
ORDER BY column ASCdf.sort_values(by='column')
ORDER BY column DESCdf.sort_values(by='column', ascending=False)
ORDER BY column1, column2 DESCdf.sort_values(by=['column1', 'column2'], ascending=[True, False])

Limit the Result Set

SQL CommandPandas Equivalent
SELECT … LIMIT Ndf.head(N)
SELECT … ORDER BY … LIMIT Ndf.sort_values(by='column').head(N)

Common Functions

String Related Functions

SQL CommandPandas Equivalent
SELECT RIGHT('hello', 2)df['column'].str[-2:]
SELECT LEFT('hello', 2)df['column'].str[:2]
SELECT LENGTH('hello')df['column'].str.len()
REPLACE('hello world', 'l', '*')df['column'].str.replace('l', '*')
REVERSE('hello')df['column'].apply(lambda x: x[::-1])
SUBSTRING('hello world', 2, 3)df['column'].str[1:4]
LOWER('HELLO')df['column'].str.lower()
UPPER('hello')df['column'].str.upper()
POSITION('e' IN 'hello')df['column'].str.find('e')

Numeric Functions

SQL CommandPandas Equivalent
ROUND(value, 1)df['column'].round(1)
FLOOR(value)df['column'].apply(np.floor)
CEIL(value)df['column'].apply(np.ceil)
ABS(value)df['column'].abs()
SQRT(value)df['column'].apply(np.sqrt)
POWER(x, y)df['column'] ** y

Converting Values using CAST

SQL CommandPandas Equivalent
CAST(value AS INT)df['column'].astype(int)
CAST(value AS CHAR)df['column'].astype(str)
CAST(value AS DATE)pd.to_datetime(df['column'])

Date Related Functions

SQL CommandPandas Equivalent
CURDATE()pd.Timestamp.now().date()
DATE_ADD(date, INTERVAL 1 YEAR)df['column'] + pd.DateOffset(years=1)
TIMESTAMPDIFF(unit, date1, date2)(df['date2'] – df['date1']).dt.days
YEAR(date)df['column'].dt.year
MONTH(date)df['column'].dt.month
DAY(date)df['column'].dt.day

Null Handling Functions

SQL CommandPandas Equivalent
IFNULL(column, value)df['column'].fillna(value)

Conditional Expressions

SQL CommandPandas Equivalent
CASE WHEN condition THEN result ELSE other_result ENDnp.where(condition, result, other_result)

Common Group Operations

SQL CommandPandas Equivalent
AVG(column)df['column'].mean()
MIN(column)df['column'].min()
MAX(column)df['column'].max()
SUM(column)df['column'].sum()
COUNT(*)df['column'].size
COUNT(column)df['column'].count()
COUNT(DISTINCT column)df['column'].nunique()
GROUP BY columndf.groupby('column').agg({'other_column': 'mean'})
HAVING conditiondf.groupby('column').filter(lambda x: condition)

Cumulative / Window Functions

SQL CommandPandas Equivalent
AVG() OVER ()df['column'].expanding().mean()
SUM() OVER ()df['column'].expanding().sum()
COUNT() OVER ()df['column'].expanding().count()
AVG() OVER (PARTITION BY …)df.groupby('partition_column')['column'].transform('mean')
SUM() OVER (PARTITION BY …)df.groupby('partition_column')['column'].transform('sum')
COUNT() OVER (PARTITION BY …)df.groupby('partition_column')['column'].transform('count')

Ranking Functions

SQL CommandPandas Equivalent
RANK() OVER (PARTITION BY … ORDER BY …)df['rank'] = df.groupby('partition_column')['order_column'].rank(method='min', ascending=True)
DENSE_RANK() OVER (PARTITION BY … ORDER BY …)df['dense_rank'] = df.groupby('partition_column')['order_column'].rank(method='dense', ascending=True)
ROW_NUMBER() OVER (PARTITION BY … ORDER BY …)df['row_number'] = df.groupby('partition_column')['order_column'].rank(method='first', ascending=True)

Other Analytic Functions

SQL CommandPandas Equivalent
LAG(column) OVER (PARTITION BY .. )df['lag'] = df.groupby('partition_column')['column'].shift(1)
LEAD(column) OVER (PARTITION BY .. )df['lead'] = df.groupby('partition_column')['column'].shift(-1)

Join Operations

SQL CommandPandas Equivalent
INNER JOINpd.merge(df1, df2, on='column')
LEFT JOINpd.merge(df1, df2, on='column', how='left')
RIGHT JOINpd.merge(df1, df2, on='column', how='right')
FULL OUTER JOINpd.merge(df1, df2, on='column', how='outer')

Set Operations

SQL CommandPandas Equivalent
UNIONpd.concat([df1, df2]).drop_duplicates()
UNION ALLpd.concat([df1, df2])
INTERSECTpd.merge(df1, df2)
EXCEPTdf1[~df1['column'].isin(df2['column'])]

Pivot and Unpivot

OperationSQL CommandPandas Equivalent
PivotPIVOT (SUM(value) FOR column IN (…))df.pivot_table(values='value', index='row_index', columns='column', aggfunc='sum')
Unpivot UNPIVOT(column FOR value IN (…))df.melt(id_vars=['row_index'], value_vars=['column1', 'column2'], var_name='column', value_name='value')

המדריך לדאטה-אנליסט

בואו לבקר במאגר המידע הגדול ביותר בעברית! גלו הרצאות וסשנים מרתקים של מומחים מהתעשייה, וצללו לעולם שלם של פוסטים מעמיקים בנושאים שונים.
לחץ כאן

קורסים קרובים

קורסי אונליין

קורסים חינמיים

דברו איתנו