# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Merge DataFrames in Pandas
# ---
# Copied From : https://github.com/justmarkham/pandas-videos/blob/master/pandas_merge.ipynb  
# Reference : https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html
#
# ## Selecting a Function
# ---
#  1. ```df1.append(df2)```: stacking vertically
#  2. ```pd.concat([df1,df2])```:
#    - Stacking mainly horizontally or vertically
#    - simple inner/outer joins on indexes
#  3. ```df1.join(df2)1```: inner/outer/left/right joins on indexes
#  4. ```pd.merge(df1, df2)```: many joins on multiple columns
#  
# ---
#
# ## Joining ( Merging ) DataFrames
# Using the [MovieLens 100k data](http://grouplens.org/datasets/movielens/), let's create two DataFrames:
#
# - **movies**: shows information about movies, namely a unique **movie_id** and its **title**
# - **ratings**: shows the **rating** that a particular **user_id** gave to a particular **movie_id** at a particular **timestamp**

import pandas as pd

# ## Movies

movie_cols = ['movie_id','title']
col_type = {'movie_id':int, 'title':str}
movies = pd.read_csv('movies.csv', names = movie_cols, usecols=[0,1],header=0)
movies.head(10)


movies.shape

movies.movie_id.nunique()

# ## Ratings

rating_cols = ['user_id', 'movie_id', 'rating', 'timestamp']
col_type = {'userid':int, 'movieid':int, 'rating':float, 'timestamp':int}
ratings = pd.read_csv('ratings.csv', names=rating_cols, dtype=col_type, header =0)
ratings.head()

# +
# pd.read_csv?
# -

ratings.shape

ratings.movie_id.nunique()

ratings[ratings.movie_id ==1].head()

# ### Merging Movies and Ratings
# ---
# Let's pretend that we want to examine the ratings DataFrame, but we want to know the title of each movie rather than its movie_id. The best way to accomplish this objective is by "joining" (or "merging") the DataFrames using the Pandas merge function:

movies.columns

ratings.columns

movie_ratings = pd.merge(movies, ratings)
movie_ratings.columns

movie_ratings.head()

movie_ratings.shape

# Here's what just happened:
#
# - Pandas noticed that movies and ratings had one column in common, namely movie_id. This is the "key" on which the DataFrames will be joined.
# - The first movie_id in movies is 1. Thus, Pandas looked through every row in the ratings DataFrame, searching for a movie_id of 1. Every time it found such a row, it recorded the user_id, rating, and timestamp listed in that row. In this case, it found 452 matching rows.
# - The second movie_id in movies is 2. Again, Pandas did a search of ratings and found 131 matching rows.
# - This process was repeated for all of the remaining rows in movies.  
#
# At the end of the process, the movie_ratings DataFrame is created, which contains the two columns from movies (movie_id and title) and the three other colums from ratings (user_id, rating, and timestamp).
#
# - movie_id 1 and its title are listed 452 times, next to the user_id, rating, and timestamp for each of the 452 matching ratings.
# - movie_id 2 and its title are listed 131 times, next to the user_id, rating, and timestamp for each of the 131 matching ratings.
# - And so on, for every movie in the dataset.

print(movies.shape)
print(ratings.shape)
print(movie_ratings.shape)

# ## Few Conditions...
# ---
#
# ### If two tables (dataframes) do not have same column name then... specify the column name 

#Update the column name
movies.columns = ['m_id', 'title']
movies.columns

# +
#Check for the columns in the ratings dataframe

ratings.columns
# -

pd.merge(movies, ratings, left_on='m_id', right_on='movie_id').head()
#Hidden Note : If multiple columns needs to be used on join condition use list
#pd.merge(movies, ratings, left_on=['m_id','2ndcolumn'],right_on=['movie_id','anothercolumn']).head()

# ## If index join should be considered... then specify that ... simple ... 

ratings= ratings.set_index('movie_id')
ratings.head()

# pd.merge(movies, ratings, left_index=True, right_index=True).head()
pd.merge(movies, ratings, left_on='m_id', right_index=True).head()

# ## Four Types of Joins
# ---
# There are actually four types of joins supported by the Pandas merge function. Here's how they are described by the documentation:
#
# - **inner**: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys
# - **outer**: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically
# - **left**: use only keys from left frame, similar to a SQL left outer join; preserve key order
# - **right**: use only keys from right frame, similar to a SQL right outer join; preserve key order  
#
# The default is the "inner join", which was used when creating the movie_ratings DataFrame.
#
# It's easiest to understand the different types by looking at some simple examples:

# ### Example DataFrames A and B

# +

A = pd.DataFrame({'color': ['green', 'yellow', 'red'], 'num':[1, 2, 3]})
A
# -

B = pd.DataFrame({'color': ['green', 'yellow', 'pink'], 'size':['S', 'M', 'L']})
B

# ### Inner join
# ---
# Only include observations found in both A and B:

pd.merge(A, B, how='inner')

# ### Outer join
# ---
# Include observations found in either A or B:

pd.merge(A, B, how='outer')

# ### Left join
# ---
# Include all observations found in A:

# +

pd.merge(A, B, how='left')
# -

# ### Right join
# ---
# Include all observations found in B:

pd.merge(A, B, how='right')

StarcoderdataJupyterScriptsDedupFiltered