<jupyter_start><jupyter_text>Parallel between SQL & Pandas> Introduction to Pandas by drawing parallel between SQL & Pandas- toc: true - badges: true- comments: true- categories: [pandas,sql]- image: images/chart-preview.png<jupyter_code>import pandas as pd
movies = 'https://vega.github.io/vega-datasets/data/movies.json'<jupyter_output><empty_output><jupyter_text>SELECTIn SQL, SELECT is the operation which allows you to explore and manipulate data.  Select all columns```sqlSELECT TOP 5 * FROM movies```<jupyter_code>df = pd.read_json(movies) # load movies data
df.head() # Shows top 5 rows by default, you can pass a value asking more than 5 rows like so - df.head(10)<jupyter_output><empty_output><jupyter_text>Select few columns```sqlSELECT TOP 5 Title, [IMDB Rating] FROM movies```<jupyter_code>df[['Title','IMDB Rating']].head()<jupyter_output><empty_output><jupyter_text>WHERE```sqlSELECT * FROM movies WHERE [IMDB Rating] > 5```<jupyter_code>df[df['IMDB Rating'] > 5].head()
#An alternate method could be to use query method to query the columns of a dataframe with a boolean expression
df.query('`IMDB Rating` > 5').head()
# if columns have spaces, use backtick quoting<jupyter_output><empty_output><jupyter_text>WHERE - Logical operator AND```sqlSELECT * FROM movies WHERE [IMDB Rating] > 5 AND [MPAA Rating] = 'PG'```<jupyter_code>df[(df['IMDB Rating'] > 5) & (df['MPAA Rating']=='PG')].head()<jupyter_output><empty_output><jupyter_text>WHERE - Logical operator OR```sqlSELECT * FROM movies WHERE [MPAA Rating] = "PG" OR [MPAA Rating] = "PG-13"```<jupyter_code>df[(df['MPAA Rating'] == 'PG') | (df['MPAA Rating'] == 'PG-13')].head()<jupyter_output><empty_output><jupyter_text>WHERE - Logical operator NOT```sqlSELECT * FROM movies WHERE [Rotten Tomatoes Rating] IS NOT NULL```<jupyter_code>df[~df['Rotten Tomatoes Rating'].isnull()].head()<jupyter_output><empty_output><jupyter_text>GROUP BY```sqlSELECT [Major Genre],COUNT(*) FROM movies ORDER BY 2 DESC```<jupyter_code>df.groupby('Major Genre', as_index=False)['Title'].count()<jupyter_output><empty_output><jupyter_text>JOINJOINS are probably one of the most important operation in SQL, it allows you to combine two or more tables and perform operations on resulting dataset<jupyter_code>data = [['Slam',5],['Pirates',8],['Duel in the Sun',7]]
df_favmovies = pd.DataFrame(data, columns=['my_fav_movies','my_rating'])
df_favmovies.head()<jupyter_output><empty_output><jupyter_text>INNER JOIN```sqlSELECT m1.*,m2.*FROM movies m1INNER JOIN my_fav_movies m2ON m1.Title = m2.my_fav_movies```<jupyter_code>pd.merge(df,df_favmovies, left_on='Title', right_on='my_fav_movies', how='inner')<jupyter_output><empty_output><jupyter_text>LEFT OUTER JOIN```sqlSELECT m1.*,m2.*FROM movies m1LEFT OUTER JOIN my_fav_movies m2ON m1.Title = m2.my_fav_movies```<jupyter_code>pd.merge(df,df_favmovies, left_on='Title', right_on='my_fav_movies', how='left').head()<jupyter_output><empty_output><jupyter_text>Handling missing values```sqlSELECT Title, COUNT(Title)FROM moviesWHERE [Rotten Tomatoes Rating] IS NULLGROUP BY TItle```<jupyter_code>df.isnull()
# In my knowledge there is no equivalent functionality in SQL, you can achieve it by adding CASE statement for all columns
df.isnull().sum()
df[df.isnull().any(axis=1)]
#This code filters the datarame to only include rows where any of the columns (axis = 1 of te dataframe) are NULL
# Impute replacement values - i.e. replacing missing values with a "best guess" value.
# In this case we are saying if ratings are not present then lets just assume an average rating.
df['Rotten Tomatoes Rating Complete'] = df['Rotten Tomatoes Rating'].fillna(df['Rotten Tomatoes Rating'].mean())
df.head()
# If replacing value with a best guess is not acceptable, we can just remove the rows or columns.
# For our example, lets remove the rows (axis = 0) where any of the columns contain null values.
df_movies_withRT_Rating = df.dropna(axis=0, how='any')
df_movies_withRT_Rating.head()<jupyter_output><empty_output><jupyter_text>CASE```sqlSELECT *,CASE WHEN [Rotten Tomatoes Rating] >=70 Then 'True'Else 'False'End As goodFROM movies```<jupyter_code>good_movie = pd.Series(df['Rotten Tomatoes Rating'] >=70)
df = pd.concat([df, good_movie.rename("good")],axis=1)
df.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13529274550968043, 'fraction_numerical': 0.009330534173081408, 'mean_word_length': 7.391389432485323, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 61, 'https://': 1, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '388278', 'license': 'Apache-2.0', 'n_tokens_mistral': 1508, 'n_tokens_neox': 1429, 'n_words': 394, 'path': '_notebooks/2022-02-26-pandas-basics.ipynb', 'repo_name': 'av1nash/ds-blog', 'size': '4287'}