Joining data with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Merge the taxi_owners and taxi_veh tables\n",
    "taxi_own_veh = taxi_owners.merge(taxi_veh, on=\"vid\")\n",
    "\n",
    "# Print the column names of the taxi_own_veh\n",
    "print(taxi_own_veh.columns)\n",
    "\n",
    "\n",
    "# Merge the taxi_owners and taxi_veh tables setting a suffix\n",
    "taxi_own_veh = taxi_owners.merge(taxi_veh, on='vid', suffixes = ('_own','_veh'))\n",
    "\n",
    "# Print the column names of taxi_own_veh\n",
    "print(taxi_own_veh.columns)\n",
    "\n",
    "# Merge the taxi_owners and taxi_veh tables setting a suffix\n",
    "taxi_own_veh = taxi_owners.merge(taxi_veh, on='vid', suffixes=('_own','_veh'))\n",
    "\n",
    "# Print the value_counts to find the most popular fuel_type\n",
    "print(taxi_own_veh['fuel_type'].value_counts())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os, sys\n",
    "del sys.modules['os']\n",
    "import os\n",
    "\n",
    "os.chdir('C://Users//T0230575//Desktop//Data Management//PyCharmProjets_fm//DataCamp//Joining Data with Pandas')\n",
    "licenses = pd.read_pickle('licenses.p')\n",
    "\n",
    "\n",
    "# Merge the licenses and biz_owners table on account\n",
    "licenses_owners = licenses.merge(biz_owners, on='account')\n",
    "\n",
    "# Group the results by title then count the number of accounts\n",
    "counted_df = licenses_owners.groupby('title').agg({'account':'count'})\n",
    "\n",
    "# Sort the counted_df in desending order\n",
    "sorted_df = counted_df.sort_values(by='account', ascending=False)\n",
    "\n",
    "# Use .head() method to print the first few rows of sorted_df\n",
    "print(sorted_df.head())\n",
    "\n",
    "\n",
    "# Merge the ridership, cal, and stations tables\n",
    "ridership_cal_stations = ridership.merge(cal, on=['year','month','day']).merge(stations)\n",
    "\n",
    "\n",
    "\n",
    "# Merge the ridership, cal, and stations tables\n",
    "ridership_cal_stations = ridership.merge(cal, on=['year','month','day']).merge(stations, on='station_id')\n",
    "\n",
    "# Create a filter to filter ridership_cal_stations\n",
    "filter_criteria = ((ridership_cal_stations['month'] == 7) \n",
    "                   & (ridership_cal_stations['day_type'] == 'Weekday') \n",
    "                   & (ridership_cal_stations['station_name'] == 'Wilson'))\n",
    "\n",
    "# Use .loc and the filter to select for rides\n",
    "print(ridership_cal_stations.loc[filter_criteria, 'rides'].sum())\n",
    "\n",
    "\n",
    "\n",
    "# Merge licenses and zip_demo, on zip; and merge the wards on ward\n",
    "licenses_zip_ward = licenses.merge(zip_demo, on='zip').merge(wards, on='ward')\n",
    "\n",
    "# Print the results by alderman and show median income\n",
    "print(licenses_zip_ward.groupby('alderman').agg({'income':'median'}))\n",
    "\n",
    "\n",
    "# Merge land_use and census and merge result with licenses including suffixes\n",
    "land_cen_lic = land_use.merge(census,on='ward').merge(licenses, on='ward', suffixes=('_cen','_lic'))\n",
    "\n",
    "\n",
    "# Merge land_use and census and merge result with licenses including suffixes\n",
    "land_cen_lic = land_use.merge(census, on='ward') \\\n",
    "                    .merge(licenses, on='ward', suffixes=('_cen','_lic'))\n",
    "\n",
    "# Group by ward, pop_2010, and vacant, then count the # of accounts\n",
    "pop_vac_lic = land_cen_lic.groupby(['ward','pop_2010','vacant'], \n",
    "                                   as_index=False).agg({'account':'count'})\n",
    "\n",
    "\n",
    "# Merge land_use and census and merge result with licenses including suffixes\n",
    "land_cen_lic = land_use.merge(census, on='ward') \\\n",
    "                    .merge(licenses, on='ward', suffixes=('_cen','_lic'))\n",
    "\n",
    "# Group by ward, pop_2010, and vacant, then count the # of accounts\n",
    "pop_vac_lic = land_cen_lic.groupby(['ward','pop_2010','vacant'], \n",
    "                                   as_index=False).agg({'account':'count'})\n",
    "\n",
    "# Sort pop_vac_lic and print the results\n",
    "sorted_pop_vac_lic = pop_vac_lic.sort_values(by=['vacant','account','pop_2010'], \n",
    "                                             ascending=[False, True, False])\n",
    "\n",
    "# Print the top few rows of sorted_pop_vac_lic\n",
    "print(sorted_pop_vac_lic.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the movies table with the financials table with a left join\n",
    "movies_financials = movies.merge(financials, on='id', how='left')\n",
    "\n",
    "# Count the number of rows in the budget column that are missing\n",
    "number_of_missing_fin = movies_financials['budget'].isnull().sum()\n",
    "\n",
    "# Print the number of movies missing financials\n",
    "print(number_of_missing_fin)\n",
    "\n",
    "# Merge action_movies to the scifi_movies with right join\n",
    "action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',\n",
    "                                   suffixes=('_act','_sci'))\n",
    "\n",
    "# From action_scifi, select only the rows where the genre_act column is null\n",
    "scifi_only = action_scifi[action_scifi['genre_act'].isnull()]\n",
    "\n",
    "\n",
    "\n",
    "# Merge action_movies to the scifi_movies with right join\n",
    "action_scifi = action_movies.merge(scifi_movies, on='movie_id', how='right',\n",
    "                                   suffixes=('_act','_sci'))\n",
    "\n",
    "# From action_scifi, select only the rows where the genre_act column is null\n",
    "scifi_only = action_scifi[action_scifi['genre_act'].isnull()]\n",
    "\n",
    "# Merge the movies and scifi_only tables with an inner join\n",
    "movies_and_scifi_only = movies.merge(scifi_only, left_on='id', right_on='movie_id', how='inner')\n",
    "\n",
    "# Print the first few rows and shape of movies_and_scifi_only\n",
    "print(movies_and_scifi_only.head())\n",
    "print(movies_and_scifi_only.shape)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Use right join to merge the movie_to_genres and pop_movies tables\n",
    "genres_movies = movie_to_genres.merge(pop_movies, how='right', \n",
    "                                      left_on='movie_id', \n",
    "                                      right_on='id')\n",
    "\n",
    "# Count the number of genres\n",
    "genre_count = genres_movies.groupby('genre').agg({'id':'count'})\n",
    "\n",
    "# Plot a bar chart of the genre_count\n",
    "genre_count.plot(kind='bar')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge iron_1_actors to iron_2_actors on id with outer join using suffixes\n",
    "iron_1_and_2 = iron_1_actors.merge(iron_2_actors,\n",
    "                                     on='id',\n",
    "                                     how='outer',\n",
    "                                     suffixes=('_1','_2'))\n",
    "\n",
    "# Create an index that returns true if name_1 or name_2 are null\n",
    "m = ((iron_1_and_2['name_1'].isnull()) | \n",
    "     (iron_1_and_2['name_2'].isnull()))\n",
    "\n",
    "# Print the first few rows of iron_1_and_2\n",
    "print(iron_1_and_2[m].head())\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge the crews table to itself\n",
    "crews_self_merged = crews.merge(crews, on='id', how='inner',\n",
    "                                suffixes=('_dir','_crew'))\n",
    "\n",
    "# Create a boolean index to select the appropriate rows\n",
    "boolean_filter = ((crews_self_merged['job_dir'] == 'Director') & \n",
    "                  (crews_self_merged['job_crew'] != 'Director'))\n",
    "direct_crews = crews_self_merged[boolean_filter]\n",
    "\n",
    "# Print the first few rows of direct_crews\n",
    "print(direct_crews.head())\n",
    "\n",
    "\n",
    "\n",
    "# Merge sequels and financials on index id\n",
    "sequels_fin = sequels.merge(financials, on='id', how='left')\n",
    "\n",
    "# Self merge with suffixes as inner join with left on sequel and right on id\n",
    "orig_seq = sequels_fin.merge(sequels_fin, how='inner', left_on='sequel', \n",
    "                             right_on='id', right_index=True,\n",
    "                             suffixes=('_org','_seq'))\n",
    "\n",
    "# Add calculation to subtract revenue_org from revenue_seq \n",
    "orig_seq['diff'] = orig_seq['revenue_seq'] - orig_seq['revenue_org']\n",
    "\n",
    "# Select the title_org, title_seq, and diff \n",
    "titles_diff = orig_seq[['title_org','title_seq','diff']]\n",
    "\n",
    "# Print the first rows of the sorted titles_diff\n",
    "print(titles_diff.sort_values(by='diff', ascending=False).head())\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge employees and top_cust\n",
    "empl_cust = employees.merge(top_cust, on='srid', \n",
    "                                 how='left', indicator=True)\n",
    "\n",
    "# Select the srid column where _merge is left_only\n",
    "srid_list = empl_cust.loc[empl_cust['_merge'] == 'left_only', 'srid']\n",
    "\n",
    "# Get employees not working with top customers\n",
    "print(employees[employees['srid'].isin(srid_list)])\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge the non_mus_tck and top_invoices tables on tid\n",
    "tracks_invoices = non_mus_tcks.merge(top_invoices, on='tid', how='inner')\n",
    "\n",
    "# Use .isin() to subset non_mus_tcks to rows with tid in tracks_invoices\n",
    "top_tracks = non_mus_tcks[non_mus_tcks['tid'].isin(tracks_invoices['tid'])]\n",
    "\n",
    "# Group the top_tracks by gid and count the tid rows\n",
    "cnt_by_gid = top_tracks.groupby(['gid'], as_index=False).agg({'tid':'count'})\n",
    "\n",
    "# Merge the genres table to cnt_by_gid on gid and print\n",
    "print(cnt_by_gid.merge(genres, on='gid'))\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Concatenate the tracks\n",
    "tracks_from_albums = pd.concat([tracks_master,tracks_ride, tracks_st],\n",
    "                               sort=True)\n",
    "print(tracks_from_albums)\n",
    "\n",
    "\n",
    "\n",
    "# Concatenate the tracks so the index goes from 0 to n-1\n",
    "tracks_from_albums = pd.concat([tracks_master, tracks_ride, tracks_st],\n",
    "                               ignore_index=True,\n",
    "                               sort=True)\n",
    "print(tracks_from_albums)\n",
    "\n",
    "\n",
    "# Concatenate the tracks, show only columns names that are in all tables\n",
    "tracks_from_albums = pd.concat([tracks_master, tracks_ride, tracks_st],\n",
    "                               join='inner',\n",
    "                               sort=True)\n",
    "print(tracks_from_albums)\n",
    "\n",
    "\n",
    "# Concatenate the tables and add keys\n",
    "inv_jul_thr_sep = pd.concat([inv_jul,inv_aug,inv_sep], \n",
    "                            keys=['7Jul','8Aug','9Sep'])\n",
    "\n",
    "# Group the invoices by the index keys and find avg of the total column\n",
    "avg_inv_by_month = inv_jul_thr_sep.groupby(level=0).agg({'total':'mean'})\n",
    "\n",
    "# Bar plot of avg_inv_by_month\n",
    "avg_inv_by_month.plot(kind='bar')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "# Use the .append() method to combine the tracks tables\n",
    "metallica_tracks = tracks_ride.append([tracks_master, tracks_st], sort=False)\n",
    "\n",
    "# Merge metallica_tracks and invoice_items\n",
    "tracks_invoices = metallica_tracks.merge(invoice_items, on='tid', how='inner')\n",
    "\n",
    "print(tracks_invoices.info())\n",
    "# For each tid and name sum the quantity sold\n",
    "tracks_sold = tracks_invoices.groupby(['tid','name']).agg({'quantity':'sum'})\n",
    "\n",
    "# Sort in decending order by quantity and print the results\n",
    "print(tracks_sold.sort_values(by='quantity', ascending=False))\n",
    "\n",
    "\n",
    "\n",
    "# Concatenate the classic tables vertically\n",
    "classic_18_19 = pd.concat([classic_18, classic_19], ignore_index=True)\n",
    "\n",
    "# Concatenate the pop tables vertically\n",
    "pop_18_19 = pd.concat([pop_18, pop_19], ignore_index=True)\n",
    "\n",
    "# Merge classic_18_19 with pop_18_19\n",
    "classic_pop = classic_18_19.merge(pop_18_19, how='inner', on='tid')\n",
    "\n",
    "# Using .isin(), filter classic_18_19 rows where tid is in classic_pop\n",
    "popular_classic = classic_18_19[classic_18_19['tid'].isin(classic_pop['tid'])]\n",
    "\n",
    "# Print popular chart\n",
    "print(popular_classic)\n",
    "\n",
    "\n",
    "\n",
    "# Use merge_ordered() to merge gdp and sp500 on year and date\n",
    "gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on='year', right_on='date', \n",
    "                             how='left')\n",
    "# Print gdp_sp500\n",
    "print (gdp_sp500)\n",
    "\n",
    "\n",
    "\n",
    "# Use merge_ordered() to merge gdp and sp500, interpolate missing value\n",
    "gdp_sp500 = pd.merge_ordered(gdp, sp500, left_on='year', right_on='date', \n",
    "                             how='left',  fill_method='ffill')\n",
    "\n",
    "# Print gdp_sp500\n",
    "print (gdp_sp500)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Use merge_ordered() to merge inflation, unemployment with inner join\n",
    "inflation_unemploy = pd.merge_ordered(inflation,unemployment, on=\"date\", how=\"inner\")\n",
    "\n",
    "# Print inflation_unemploy \n",
    "print(inflation_unemploy)\n",
    "\n",
    "# Plot a scatter plot of unemployment_rate vs cpi of inflation_unemploy\n",
    "inflation_unemploy.plot(x='unemployment_rate',y=\"cpi\", kind='scatter')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Use merge_asof() to merge jpm and wells\n",
    "jpm_wells = pd.merge_asof(jpm, wells, on='date_time', \n",
    "                          suffixes=('', '_wells'), direction='nearest')\n",
    "\n",
    "# Use merge_asof() to merge jpm_wells and bac\n",
    "jpm_wells_bac = pd.merge_asof(jpm_wells, bac, on='date_time', \n",
    "                              suffixes=('_jpm', '_bac'), direction='nearest')\n",
    "\n",
    "# Compute price diff\n",
    "price_diffs = jpm_wells_bac.diff()\n",
    "\n",
    "# Plot the price diff of the close of jpm, wells and bac only\n",
    "price_diffs.plot(y=['close_jpm','close_wells','close_bac'])\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge gdp and recession on date using merge_asof()\n",
    "gdp_recession = pd.merge_asof(gdp,recession,on='date')\n",
    "print(gdp_recession)\n",
    "# Create a list based on the row value of gdp_recession['econ_status']\n",
    "is_recession = ['r' if s=='recession' else 'g' for s in gdp_recession['econ_status']]\n",
    "\n",
    "# Plot a bar chart of gdp_recession\n",
    "gdp_recession.plot(kind='bar', y='gdp', x='date', color=is_recession, rot=90)\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# Merge gdp and pop on date and country with fill\n",
    "gdp_pop = pd.merge_ordered(gdp, pop, on=['country','date'], fill_method='ffill')\n",
    "\n",
    "# Add a column named gdp_per_capita to gdp_pop that divides the gdp by pop\n",
    "gdp_pop['gdp_per_capita'] = gdp_pop['gdp'] / gdp_pop['pop']\n",
    "\n",
    "# Pivot data so gdp_per_capita, where index is date and columns is country\n",
    "gdp_pivot = gdp_pop.pivot_table('gdp_per_capita', 'date', 'country')\n",
    "\n",
    "# Select dates equal to or greater than 1991-01-01\n",
    "recent_gdp_pop = gdp_pivot.query('date >= \"1991-01-01\"')\n",
    "\n",
    "# Plot recent_gdp_pop\n",
    "recent_gdp_pop.plot(rot=90)\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "# Unpivot everything besides the year column\n",
    "ur_tall = ur_wide.melt(id_vars=['year'], var_name='month', \n",
    "                       value_name='unempl_rate')\n",
    "\n",
    "# Create a date column using the month and year columns of ur_tall\n",
    "ur_tall['date'] = pd.to_datetime(ur_tall['month'] + '-' + ur_tall['year'])\n",
    "\n",
    "# Sort ur_tall by date in ascending order\n",
    "ur_sorted = ur_tall.sort_values('date')\n",
    "\n",
    "# Plot the unempl_rate by date\n",
    "ur_sorted.plot(x='date', y='unempl_rate')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\n",
    "# Use melt on ten_yr, unpivot everything besides the metric column\n",
    "bond_perc = ten_yr.melt(id_vars='metric', var_name='date', value_name='close')\n",
    "\n",
    "# Use query on bond_perc to select only the rows where metric=close\n",
    "bond_perc_close = bond_perc.query('metric == \"close\"')\n",
    "\n",
    "# Merge (ordered) dji and bond_perc_close on date with an inner join\n",
    "dow_bond = pd.merge_ordered(dji, bond_perc_close, on='date', \n",
    "                            suffixes=('_dow', '_bond'), how='inner')\n",
    "\n",
    "# Plot only the close_dow and close_bond columns\n",
    "dow_bond.plot(y=['close_dow', 'close_bond'], x='date', rot=90)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}