
*See my other [GitHub Repo](https://github.com/krozic/imdb-chrome-extension) for the chrome extension I built off this data.

# Summary

This application takes a chosen film as an input and creates a plot and table describing the rank of the film for the specific genres it is categorized in.

The intent of this project is to compare the average imdb ratings between genres and visualize the distribution of ratings for each genre in an effort to translate a film rating to a genre specific ranking. 

<div>
	![app](/images/imdb-rating/app.png)
</div>

# Data Sources

2 datasets, 2 APIs, and a web crawler comprise the backend data handling for this project:
- [IMDb open dataset](https://www.imdb.com/interfaces/)
- [Stefano Leone's IMDb extensive dataset](https://www.kaggle.com/stefanoleone992/imdb-extensive-dataset)
- [OMDb API](https://www.omdbapi.com/) (Third party API)
- [IMDb-API](https://imdb-api.com/) (Third party API)
- [IMDB website](https://www.imdb.com/) (BS4 web crawling)


# Methods

The tables 'title.basics.tsv', 'title.ratings.tsv' and 'IMDB movies.csv' were loaded into Microsoft SQL Server as TitleBasics, TitleRatings and TitleExtras respectively. These tables were used for exploratory data analysis and subsequent creation of the tables necessary for visualization (OMDb API was used to source the Rotten Tomatoes and Metacritic ratings). The database schema is shown below (created in [dbdiagram.io](https://dbdiagram.io/))

<div style={{'text-align': 'center'}}>
	![app](/images/imdb-rating/imdb_table_schema.png)
</div>

The python package `pyodbc` was then used to load the tables from this database into `pandas` for visualization in `matplotlib` and `plotly`. Dash was used for the frontend of the web application.



IMDB Ratings Visualization


\* ETL made with [draw.io](https://drive.google.com/file/d/1GGBgKkbAsrv0-ypzIxPXeZlYJxb4aL-O/view?usp=sharing)

{/* ## Spotify ETL Using Python, Apache Airflow, and MS SQL Server */}
# Summary
The Spotify API documentation is very well written and can be used as a walkthrough for a wide variety of project applications. In this project I set up a daily extraction of my recently played tracks using Python for the data processing and Apache Airflow as the scheduler. The resulting data is appended to a database on a local Microsoft SQL Server.

Here is the resulting table:

<div style={{margin: '2rem 2.5rem'}}>
	<Image 
		src={ '/images/recent-spotify/table.png' }
		alt='table'
		layout='responsive'
		width={601}
		height={285}
	/>
</div>

## Data Extraction
```python
headers = {  
    'Accept' : 'application/json',  
    'Content-Type' : 'application/json',  
    'Authorization' : 'Bearer {token}'.format(token=token)  
}  
  
today = datetime.now() + timedelta(hours=4)  
yesterday = today - timedelta(days=1)  
yesterday_unix_timestamp = int(yesterday.timestamp()) * 1000  
r = requests.get('https://api.spotify.com/v1/me/player/recently-played?after={time}'.format(time=yesterday_unix_timestamp), headers=headers)  
  
data = r.json()
```

## Data Transformation and Validation
```python
for song in data['items']:  
    primary_key.append(song['played_at'])  
    song_names.append(song['track']['name'])  
    artist_names.append(song['track']['artists'][0]['name'])  
    timestamps.append(song['played_at'][0:10])  
  
song_dict = {  
    'primary_key' : primary_key,  
    'song_name' : song_names,  
    'artist_name' : artist_names,  
    'timestamp' : timestamps  
}  
  
song_df = pd.DataFrame(song_dict, columns = ['primary_key', 'song_name', 'artist_name', 'timestamp'])
```

```python
def check_if_valid_data(df) -> bool:  
    if df.empty:  
        print('No songs downloaded')  
        quit()  
  
    if pd.Series(df['primary_key']).is_unique:  
        pass  
    else:  
        raise Exception('Primary Key Check Violated')  
  
    if df.isnull().values.any():  
        raise Exception('Null value found')  
  
    yesterday = datetime.now() - timedelta(hours=20)  
    timestamps = df['primary_key']  
    for timestamp in timestamps:  
        played_time = datetime.strptime(timestamp[:19], '%Y-%m-%dT%H:%M:%S')  
        if played_time < yesterday:  
            raise Exception('At least one song is not from the past 24 hrs')  
  
    return True
```

## Loading Data into MS SQL Server
Initiating table:
```python
driver = '{ODBC DRIVER 17 for SQL Server}'  
server = 'localhost\server_name'  
database = 'db_name'  
  
connection_string = '''  
Driver={driver};  
Server={server};  
Database={database};  
Trusted_Connection=yes;  
'''.format(  
    driver=driver,  
    server=server,  
    database=database  
)  
  
conn = pyodbc.connect(connection_string)  
cursor = conn.cursor()  

create_table = '''  
IF (  
    NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.TABLES  
		WHERE TABLE_NAME = 'my_played_tracks'        
		)    
)BEGIN  
    CREATE TABLE my_played_tracks (
		primary_key VARCHAR(200) NOT NULL PRIMARY KEY,
		song_name VARCHAR(200),
		artist_name VARCHAR(200),
		timestamp VARCHAR(200)
	)  
END;  
'''  
  
cursor.execute(create_table)  
conn.commit()
```

Loading Data:
```python
quoted = urllib.parse.quote_plus(connection_string)  
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))  
song_df.to_sql('my_played_tracks', engine, index=False, if_exists='append')

conn.close()
```

## Scheduling with Apache Airflow
Airflow was installed locally for this project by following the Apache [documentation](https://airflow.apache.org/docs/apache-airflow/stable/start/local.html).

```bash
airflow webserver -p 8080
```

```bash
airflow scheduler
```

Airflow Windows: https://towardsdatascience.com/run-apache-airflow-on-windows-10-without-docker-3c5754bb98b4

## OAuth 2.0 Authorization 
In order to extract the song information from Spotify's API we need an 'Access Token'. This can be acquired manually from [Spotify](https://developer.spotify.com/console/get-recently-played/). If you only require temporary access, you can skip this next part, however a code would have to be manually produced and copied into the program every time it runs.

In order to programatically request an 'Access Token', authorization must be granted through the [OAuth 2.0 framework](https://developer.spotify.com/documentation/general/guides/authorization/). This program follows the [Authorization Code Flow](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/). 3 pieces of information are required for the end product:
- client_id
- client_secret
- refresh_token

Getting these variables involves creating a Spotify Dev App and manually authorizing the app in a browser.

### Spotify Dev App
Spotify has an [App Guide](https://developer.spotify.com/documentation/general/guides/authorization/app-settings/) that describes how to create an App. The 'client_id' and 'client_secret' are both at the top of the app page. 

Remember to add a 'Redirect URI' to the app (I used https://localhost:8000/callback). It doesn't have to be a real website, it is just required to have a trusted destination for Spotify to send the 'authorization code' required for the next step.

### One-Time Manual User Authorization
The rest of this setup will follow the [Authorization Code Flow](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/) document. 

The goal is to acquire a 'Refresh Token' which will allow us to request and receive a new 'Access Token' programatically. In order to get this token a 'GET request' must be sent to the '/authorize' endpoint. This GET request can be as simple as a browser url:

```
https://accounts.spotify.com/authorize?client_id=<client_id_from_app>&scope=user-read-recently-played&response_type=code&redirect_uri=https%3A%2F%2Flocalhost%3A8888%2Fcallback
```

Parameters:
- client_id: ID from your newly created app
- response_type: code
- redirect_uri: The url added to the app settings, encoded [here](https://www.urlencoder.org/)
- scope: user-read-recently-played (or any [other scopes])
	- Use ''%20' as a space to separate multiple scopes

After building this URL, authorization is requested:



<div style={{margin: '0rem 7rem'}}>
	<Image 
	src={ '/images/recent-spotify/permission.png' }
	alt='permission'
	layout='responsive'
	width={623}
	height={744}
	/>
</div>

After the redirect, the 'authorization code' is appended to the url in the browser:


<div style={{margin: '0rem'}}>
	<Image 
	src={ '/images/recent-spotify/url.png' }
	alt='url'
	layout='responsive'
	width={778}
	height={35}
	/>
</div>

Run a `curl` command with the code to get an Access Token and a **Refresh Token**

```bash
curl -H "Authorization: Basic <base64 encoded client_id:secret_id>" \
	-H "Content-Type: application/x-www-form-urlencoded" \
	-d grant_type=authorization_code \
	-d code=AQBicXEN4K...qU_2-Pr0Q \
	-d redirect_uri=https%3A%2F%2Flocalhost%3A8888%2Fcallback \
	https://accounts.spotify.com/api/token
```

Parameters:
- Authorization: Basic \<base64 encoded client_id:secret_id>
	- Ex. paste your 'client_id:secret_id' string into [here](https://www.base64encode.org/)
- Content-Type: application/x-www-form-urlencoded
- grant_type=authorization_code
- code=AQBicXEN4Ks...WYAqU_2-Pr0Q
	- Given by Spotify in the redirect URL
- redirect_uri=https%3A%2F%2Flocalhost%3A8000%2Fcallback
	- Only used for verification

The response should look like this:
```json
{
   "access_token": "NgCXRK...MzYjw",
   "token_type": "Bearer",
   "scope": "user-read-private user-read-email",
   "expires_in": 3600,
   "refresh_token": "NgAagA...Um_SHo"
}
```

The 'access_token' can be used to authenticate connection the API, however it expires in 1 hour. The 'refresh_token', however, can be used along with the base64 encoded app credentials to get another 1 hour 'access_token'--giving unlimited access.

The code below is used to acquire a new 'access_token' at each run:

```python
def refresh(self):  
  
    query = 'https://accounts.spotify.com/api/token'  
  
    response = requests.post(query,  
                             data={'grant_type': 'refresh_token',  
                                   'refresh_token': refresh_token},  
                             headers={'Authorization': 'Basic ' + base_64})  
    return response.json()['access_token']
```

### Helpful Links
- [OAuth 2](https://developer.spotify.com/documentation/general/guides/authorization/)
- [App Auth](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/)
- [App setup](https://developer.spotify.com/documentation/general/guides/authorization/app-settings/)
- [Using App Access](https://developer.spotify.com/documentation/general/guides/authorization/use-access-token/)

For setting the scope of the returned data:
- [Scopes guide](https://developer.spotify.com/documentation/general/guides/authorization/scopes/)
- [Recently Played scope](https://developer.spotify.com/documentation/web-api/reference/#/operations/get-the-users-currently-playing-track)

Others Implementations:
- [Implementation](https://github.com/kylepw/spotify-api-auth-examples)
- [YT Url hacking](https://www.youtube.com/watch?v=yAXoOolPvjU)

Spotify Recently Played Songs


# Description

Statistics and research data was sourced from [Our World in Data](https://ourworldindata.org/covid-deaths) to visualize how covid-19 has spread over time and between countries. The data was explored and cleaned with Microsoft SQL and visualized in Tableau.

# Finished Product

<div id='image'>
	![Dashboard Screenshot](/images/covid-data-vis/dashboard_screenshot.png)
</div>

The interactive dashboard is hosted on Tableau Public and can be found [here](https://public.tableau.com/app/profile/kyle.rozic/viz/CovidVisualization_16319417831480/Dashboard1?publish=yes)

Kyle Rozic

MS SQL

All Tags

IMDB Ratings Visualization

Spotify Recently Played Songs

Covid-19 Data Dashboard