<jupyter_start><jupyter_text>SQL<jupyter_code>import sqlite3 as sq3
import pandas as pd
import json
# importing json file
with open('some_movies.json') as f:
    data = json.load(f)
    
# creating a datafram
df = pd.json_normalize(data,sep = "_")

movies = df[['id','title','revenue','budget','belongs_to_collection_name','release_date']].copy()
movies['release_date'] = pd.to_datetime(df['release_date'])
movies['revenue'] = df['revenue']/1000000
movies['budget'] = df['budget']/1000000

#creating a second dataframe
votes = df[['id','vote_count','vote_average']].copy()

# convert genre column in a separate dataframe
genres = pd.json_normalize(data = data,
                          record_path = 'genres',
                          #meta='id',
                          record_prefix = 'genre_')
genres = pd.json_normalize(data = data,
                          record_path = 'genres',
                          record_prefix = 'genre_',
                          meta='id')
genres.head(2)
prod = pd.json_normalize(data=data,
                         record_path = 'production_companies',
                         record_prefix = 'comp_',
                         meta = 'id')
con = sq3.connect('movies.db')
movies.to_sql('Movies',# nome da tabela
              con,
              index=False)

votes.to_sql('Votes',con,index=False)

genres.to_sql('Genres',con,index=False)

prod.to_sql('Prod',con,index=False)
con.execute('Select * FROM sqlite_master').fetchall()
con.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name").fetchall()
con.close()<jupyter_output><empty_output><jupyter_text>Loading Data from SQLite Databases into DataFrames<jupyter_code>import pandas as pd
import sqlite3 as sq3
con = sq3.connect("movies.db")
con.execute("Select * FROM sqlite_master").fetchall()
df = pd.read_sql("SELECT * FROM Movies",con,
                 index_col = "id",
                 parse_dates = "release_date")
df
genres = pd.read_sql("SELECT * FROM Genres",
                     con,
                     index_col = "id"
                    )
con.close()<jupyter_output><empty_output><jupyter_text>Some Simple SQL Queries<jupyter_code>import pandas as pd
import sqlite3 as sq3<jupyter_output><empty_output><jupyter_text>Connecting<jupyter_code>con = sq3.connect("movies.db")<jupyter_output><empty_output><jupyter_text>Reading the Movies Tables<jupyter_code>pd.read_sql("SELECT * FROM Movies",con)<jupyter_output><empty_output><jupyter_text>The backslash is to writte in another line<jupyter_code>pd.read_sql("SELECT * \
             FROM Movies",con)<jupyter_output><empty_output><jupyter_text>Selecting specific columns<jupyter_code>pd.read_sql("SELECT id, revenue,release_date FROM Movies",con)<jupyter_output><empty_output><jupyter_text>Operating over columns with pandas<jupyter_code>pd.read_sql("SELECT sum(revenue) FROM Movies",con)
pd.read_sql("SELECT count(title) FROM Movies",con)
# this method dont count repetitions
pd.read_sql("SELECT count(belongs_to_collection_name) FROM Movies",con)
pd.read_sql("SELECT count(*) FROM Movies",con)
pd.read_sql("SELECT avg(budget) FROM Movies",con)<jupyter_output><empty_output><jupyter_text>Operating over columns with sqlite<jupyter_code>con.execute("SELECT sum(revenue) FROM Movies").fetchall()[0][0]
pd.read_sql("SELECT * FROM Movies WHERE id = 597",con)
pd.read_sql("SELECT * FROM Movies WHERE revenue > 2000",con)
pd.read_sql("SELECT * FROM Movies WHERE \
             revenue > 1500 AND budget < 200 ",con)
pd.read_sql("SELECT * FROM Movies WHERE \
             revenue > 1500 AND budget < 200 ",con)
pd.read_sql("SELECT MIN(budget) FROM Movies \
             WHERE revenue > 1250",con)
pd.read_sql("SELECT DISTINCT title FROM Movies",con)
pd.read_sql("SELECT * FROM Movies ORDER BY budget DESC",con)
pd.read_sql("SELECT * FROM Movies WHERE \
             title LIKE 'Avengers%'",con)
pd.read_sql("SELECT * FROM Movies WHERE \
             belongs_to_collection_name IS NULL",con)
pd.read_sql("SELECT * FROM Movies WHERE \
             belongs_to_collection_name IS NOT NULL",con)
con.close()<jupyter_output><empty_output><jupyter_text>Join SQL Queries<jupyter_code>import pandas as pd
import sqlite3 as sq3<jupyter_output><empty_output><jupyter_text>Connecting<jupyter_code>con = sq3.connect("movies.db")
pd.read_sql("SELECT * FROM Movies", con)
pd.read_sql("SELECT * FROM Votes", con)
pd.read_sql("SELECT * \
             FROM Movies \
             JOIN Votes \
             ON Movies.id-Votes.id",con)
pd.read_sql("SELECT Movies.id, Movies.title, Votes.vote_average \
             FROM Movies \
             JOIN Votes \
             ON Movies.id=Votes.id",con,index_col="id")
pd.read_sql("SELECT Movies.id, Movies.title, Votes.vote_average \
             FROM Movies \
             JOIN Votes \
             ON Movies.id=Votes.id \
             WHERE Votes.vote_average > 8",con,index_col="id")
pd.read_sql("SELECT Movies.id, Movies.title, Votes.vote_average \
             FROM Movies \
             JOIN Votes \
             ON Movies.id=Votes.id \
             WHERE Votes.vote_average > 8 \
             ORDER BY Movies.budget ASC",con,index_col="id")
con.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12222436763854026, 'fraction_numerical': 0.010812898242904035, 'mean_word_length': 3.393553859202714, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 51, 'https://': 0, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '350173', 'license': 'MIT', 'n_tokens_mistral': 1763, 'n_tokens_neox': 1700, 'n_words': 335, 'path': 'Project_05_Materials/.ipynb_checkpoints/main-checkpoint.ipynb', 'repo_name': 'emdemor/PythonDataScienceWithPandas_Exercises', 'size': '5179'}