<jupyter_start><jupyter_text>1.1 - Inner join Tables = DataFrames \Merging = Joining<jupyter_code>import pandas as pd
ward = pd.read_csv('Ward_Offices.csv')
print(ward.head())
print(ward.shape)
print(ward.columns)
wards = ward[['WARD','ALDERMAN','ADDRESS','ZIPCODE']].copy()
census = ward[['WARD','CITY','STATE','WARD PHONE','EMAIL']]<jupyter_output><empty_output><jupyter_text>> Merging tables  > Inner join<jupyter_code>wards_census = wards.merge(census,on='WARD')
wards_census.head()
print(wards_census.columns)<jupyter_output>Index(['WARD', 'ALDERMAN', 'ADDRESS', 'ZIPCODE', 'CITY', 'STATE', 'WARD PHONE',
       'EMAIL'],
      dtype='object')<jupyter_text>> Suffixes<jupyter_code>wards_census = wards.merge(census, on='WARD', suffixes=('_cen','_ward'))
print(wards_census.head())
print(wards_census.shape)<jupyter_output>WARD                    ALDERMAN                               ADDRESS  \
0    33  <NAME>            3001 West Irving Park Road   
1    17             <NAME>.                 1344 West 79th Street   
2    44              <NAME>  3223 North Sheffield Avenue, Suite A   
3    37                 <NAME>                5344 West North Avenue   
4     4             King, <NAME>.                  435 East 35th Street   

   ZIPCODE     CITY STATE      WARD PHONE                     EMAIL  
0  60618.0  Chicago    IL  (773) 840-7880         <EMAIL>  
1  60636.0  Chicago    IL  (773) 783-3672       <EMAIL>  
2  60657.0  Chicago    IL  (773) 525-6034  <EMAIL>  
3  60651.0  Chicago    IL  (773) 379-0960  <EMAIL>  
4  60616.0  Chicago    IL  (773) 536-8103  <EMAIL>  
(50, 8)<jupyter_text>1.2 - One to many relationships<jupyter_code>licenses = pd.read_csv('Business_Licenses.csv')
print(licenses.head())
print(licenses.shape)
ward_licenses = wards.merge(licenses, on='ward', suffixes=('_ward','_lic'))
print(ward_licenses.head())<jupyter_output><empty_output><jupyter_text>1.3 - Merging multiple DataFrames  > Theoretical merge<jupyter_code>grants_licenses = grants.merge(licenses, on='zip')
print(grants_licenses.loc[grants_licenses['business']=="REGGIE'S BAR & GRILL",['grant','company','account','ward','business']])<jupyter_output><empty_output><jupyter_text>> Single merge<jupyter_code>grants.merge(licenses, on=['address','zip'])<jupyter_output><empty_output><jupyter_text>> Merging multiple tables<jupyter_code>grants_licenses_ward = grants.merge(licenses, on=['address','zip']).merge(wards, on='ward', suffixes=('_bus','_ward'))
grants_licenses_ward.head()<jupyter_output><empty_output><jupyter_text>> Results<jupyter_code>import matplotlib.pyplot as plt
grant_licenses_ward.groupby('ward').agg('sum').plot(kind='bar', y='grant')
plt.show()<jupyter_output><empty_output><jupyter_text>> Merging even more<jupyter_code>'''
Three tables:
df1.merge(df2, on='col').merge(df3, on='col')

Four tables:
df1.merge(df2, on='col').merge(df3, on='col').merge(df4, on='col')
'''<jupyter_output><empty_output><jupyter_text>2.1 - Left join<jupyter_code>movies = pd.read_csv('tmdb-movies.csv')
movies.shape
movies_taglines = movies.merge(taglines, on='id', how='left')
print(movies_taglines.head())<jupyter_output><empty_output><jupyter_text>2.2 - Other joins  > Right Join<jupyter_code>tv_movies = movies.merge(tv_genre, how='right',left_on='id', right_on='movie_id')
print(tv_movies.head())<jupyter_output><empty_output><jupyter_text>> Outer Join<jupyter_code>family_comedy = family.merge(comedy, on='movie_id', how='outer',suffixes=('_fam','_com'))
print(family_comedy)<jupyter_output><empty_output><jupyter_text>2.3 - Merging a table to itself<jupyter_code>original_sequels = sequels.merge(sequels, left_on='sequel', right_on='id',suffixes=('_org','_seq'))
print(original_sequels.head())<jupyter_output><empty_output><jupyter_text>> Merging a table to itself with left join<jupyter_code>original_sequels = sequels.merge(sequels, left_on='sequel', right_on='id',how='left', suffixes=('_org','_seq'))
print(original_sequels.head())<jupyter_output><empty_output><jupyter_text>2.4 - Merging on indexes  > Setting an index<jupyter_code>movies = pd.read_csv('tmdb-movies.csv', index_col=['id'])
print(movies.head())<jupyter_output>imdb_id  popularity     budget     revenue  \
id                                                     
135397  tt0369610   32.985763  150000000  1513528810   
76341   tt1392190   28.419936  150000000   378436354   
262500  tt2908446   13.112507  110000000   295238201   
140607  tt2488496   11.173104  200000000  2068178225   
168259  tt2820852    9.335014  190000000  1506249360   

                      original_title  \
id                                     
135397                Jurassic World   
76341             Mad Max: Fury Road   
262500                     Insurgent   
140607  Star Wars: The Force Awakens   
168259                     Furious 7   

                                                     cast  \
id                                                          
135397  <NAME>|<NAME>|<NAME>|Vi...   
76341   <NAME>|<NAME>|<NAME>-Byrne|Nic...   
262500  <NAME>|<NAME>|<NAME>|Ansel...   
140607  <NAME>[...]<jupyter_text>> MultiIndex datasets<jupyter_code>samuel = pd.read_csv('samuel.csv',index_col=['movie_id','cast_id'])
print(samuel.head())
samuel_casts = samuel.merge(casts, on=['movie_id','cast_id'])
print(samuel_casts.head())
print(samuel_casts.shape)<jupyter_output><empty_output><jupyter_text>> Index merge with left_on and right_on<jupyter_code>movies_genres = movies.merge(movie_to_genres, left_on='id', left_index=True,right_on='movie_id', right_index=True)
print(movies_genres.head())<jupyter_output><empty_output><jupyter_text>3.1 - Filtering joins  > Step 1 - semi-join<jupyter_code>genres_tracks = genres.merge(top_tracks, on='gid')
print(genres_tracks.head())<jupyter_output><empty_output><jupyter_text>> Step 2 - semi-join<jupyter_code>genres['gid'].isin(genres_tracks['gid'])<jupyter_output><empty_output><jupyter_text>> Step 3 - semi-join<jupyter_code>genres_tracks = genres.merge(top_tracks, on='gid')
top_genres = genres[genres['gid'].isin(genres_tracks['gid'])]
print(top_genres.head())<jupyter_output><empty_output><jupyter_text>> Step 1 - anti-join<jupyter_code>genres_tracks = genres.merge(top_tracks, on='gid', how='left', indicator=True)
print(genres_tracks.head())<jupyter_output><empty_output><jupyter_text>> Step 2 - anti-join<jupyter_code>gid_list = genres_tracks.loc[genres_tracks['_merge'] == 'left_only','gid']
print(gid_list.head())<jupyter_output><empty_output><jupyter_text>> Step 3 - anti-join<jupyter_code>genres_tracks = genres.merge(top_tracks, on='gid', how='left', indicator=True)
gid_list = genres_tracks.loc[genres_tracks['_merge'] =='left_only','gid']
non_top_genres = genres[genres['gid'].isin(gid_list)]
print(non_top_genres.head())<jupyter_output><empty_output><jupyter_text>3.2 - Concatenate DataFrames together vertically  > Basic concatenation<jupyter_code>pd.concat([inv_jan, inv_feb, inv_mar])<jupyter_output><empty_output><jupyter_text>> Ignoring the index<jupyter_code>pd.concat([inv_jan, inv_feb, inv_mar],ignore_index=True)<jupyter_output><empty_output><jupyter_text>> Setting labels to original tables<jupyter_code>pd.concat([inv_jan, inv_feb, inv_mar], ignore_index=False,keys=['jan','feb','mar'])<jupyter_output><empty_output><jupyter_text>> Concatenate tables with different column names<jupyter_code>pd.concat([inv_jan, inv_feb],sort=True)
pd.concat([inv_jan, inv_feb],join='inner')
Simplied version ofthe .concat() method
Supports: ignore_index , and sort
Does Not Support: keys and join
    Always join = outer<jupyter_output><empty_output><jupyter_text>> Using append method<jupyter_code>inv_jan.append([inv_feb, inv_mar],ignore_index=True,sort=True)<jupyter_output><empty_output><jupyter_text>3.3 - Verifying integrity  > Merge validate: one_to_one<jupyter_code>tracks.merge(specs, on= 'tid',validate='one_to_one')<jupyter_output><empty_output><jupyter_text>> Merge validate: one_to_many<jupyter_code>albums.merge(tracks, on='aid',validate='one_to_many')<jupyter_output><empty_output><jupyter_text>> Verifying concatenation: example<jupyter_code>pd.concat([inv_feb, inv_mar],verify_integrity=False)
pd.concat([inv_feb, inv_mar],verify_integrity=True)<jupyter_output><empty_output><jupyter_text>4.1 - Using merge_ordered()  > Merging stock data<jupyter_code>import pandas as pd
pd.merge_ordered(appl, mcd, on='date', suffixes=('_aapl','_mcd'))<jupyter_output><empty_output><jupyter_text>> Forward fill example<jupyter_code>pd.merge_ordered(appl, mcd, on='date',suffixes=('_aapl','_mcd'),fill_method='ffill')<jupyter_output><empty_output><jupyter_text>4.2 - Using merge_asof()<jupyter_code>pd.merge_asof(visa, ibm, on='date_time',suffixes=('_visa','_ibm'))<jupyter_output><empty_output><jupyter_text>> merge_asof() example with direction<jupyter_code>pd.merge_asof(visa, ibm, on=['date_time'],suffixes=('_visa','_ibm'),direction='forward')<jupyter_output><empty_output><jupyter_text>4.3 - Selecting data with .query()  > Querying on a multiple conditions,"and","or"<jupyter_code>stocks.query('nike > 90 and disney < 140')
stocks.query('nike > 96 or disney < 98')<jupyter_output><empty_output><jupyter_text>> Using .query() to select text<jupyter_code>stocks_long.query('stock=="disney" or (stock=="nike" and close < 90)')<jupyter_output><empty_output><jupyter_text>4.4 - Reshaping data with .melt()<jupyter_code>social_fin_tall = social_fin.melt(id_vars=['financial','company'])
print(social_fin_tall.head(10))<jupyter_output><empty_output><jupyter_text>> Melting with value_vars<jupyter_code>social_fin_tall = social_fin.melt(id_vars=['financial','company'],value_vars=['2018','2017'])
print(social_fin_tall.head(9))<jupyter_output><empty_output><jupyter_text>> Melting with column names<jupyter_code>social_fin_tall = social_fin.melt(id_vars=['financial','company'],value_vars=['2018','2017'],var_name=['year'], value_name='dollars')
print(social_fin_tall.head(8))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14833836858006041, 'fraction_numerical': 0.04491440080563948, 'mean_word_length': 5.877423822714682, 'pattern_counts': {'":': 0, '<': 181, '<?xml version=': 0, '>': 215, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '40', 'dir': 'jupyter-structured-clean-dedup', 'id': '531820', 'license': 'MIT', 'n_tokens_mistral': 4015, 'n_tokens_neox': 3629, 'n_words': 503, 'path': '04. Joining Data with pandas/Joining Data with Pandas.ipynb', 'repo_name': 'vithep/Python_DataScience', 'size': '10112'}