Merging Datasets

Use pandas Merges to create a combined dataset from clean_08.csv and clean_18.csv.

Here are the four types of merges in pandas. Below, "key" refers to common columns in both dataframes that we're joining on.

  • Inner Join - Use intersection of keys from both frames.
  • Outer Join - Use union of keys from both frames.
  • Left Join - Use keys from left frame only.
  • Right Join - Use keys from right frame only.
In [1]:
# load datasets
import pandas as pd

df_08 = pd.read_csv('clean_08.csv')
df_18 = pd.read_csv('clean_18.csv')

Create combined dataset

In [2]:
# rename 2008 columns
df_08.rename(columns=lambda x: x[:10] + "_2008", inplace=True)
In [3]:
# view to check names
df_08.head()
Out[3]:
model_2008 displ_2008 cyl_2008 trans_2008 drive_2008 fuel_2008 veh_class_2008 air_pollut_2008 city_mpg_2008 hwy_mpg_2008 cmb_mpg_2008 greenhouse_2008 smartway_2008
0 ACURA MDX 3.7 6 Auto-S5 4WD Gasoline SUV 7.0 15.0 20.0 17.0 4 no
1 ACURA RDX 2.3 4 Auto-S5 4WD Gasoline SUV 7.0 17.0 22.0 19.0 5 no
2 ACURA RL 3.5 6 Auto-S5 4WD Gasoline midsize car 7.0 16.0 24.0 19.0 5 no
3 ACURA TL 3.2 6 Auto-S5 2WD Gasoline midsize car 7.0 18.0 26.0 21.0 6 yes
4 ACURA TL 3.5 6 Auto-S5 2WD Gasoline midsize car 7.0 17.0 26.0 20.0 6 yes
In [4]:
# merge datasets
df_combined = df_08.merge(df_18, left_on='model_2008', right_on='model', how='inner')
In [5]:
# view to check merge
df_combined.head()
Out[5]:
model_2008 displ_2008 cyl_2008 trans_2008 drive_2008 fuel_2008 veh_class_2008 air_pollut_2008 city_mpg_2008 hwy_mpg_2008 ... trans drive fuel veh_class air_pollution_score city_mpg hwy_mpg cmb_mpg greenhouse_gas_score smartway
0 ACURA RDX 2.3 4 Auto-S5 4WD Gasoline SUV 7.0 17.0 22.0 ... SemiAuto-6 2WD Gasoline small SUV 3.0 20.0 28.0 23.0 5 No
1 ACURA RDX 2.3 4 Auto-S5 4WD Gasoline SUV 7.0 17.0 22.0 ... SemiAuto-6 4WD Gasoline small SUV 3.0 19.0 27.0 22.0 4 No
2 AUDI A3 2.0 4 Man-6 2WD Gasoline station wagon 7.0 21.0 29.0 ... AMS-6 4WD Gasoline small car 7.0 24.0 31.0 27.0 6 No
3 AUDI A3 2.0 4 Man-6 2WD Gasoline station wagon 7.0 21.0 29.0 ... AMS-7 2WD Gasoline small car 7.0 26.0 35.0 29.0 6 No
4 AUDI A3 2.0 4 Auto-S6 2WD Gasoline station wagon 7.0 22.0 29.0 ... AMS-6 4WD Gasoline small car 7.0 24.0 31.0 27.0 6 No

5 rows × 26 columns

Save the combined dataset

In [6]:
df_combined.to_csv('combined_dataset.csv', index=False)

Results with Merged Dataset

Q5: For all of the models that were produced in 2008 that are still being produced now, how much has the mpg improved and which vehicle improved the most?

In [7]:
# load dataset
import pandas as pd
df = pd.read_csv('combined_dataset.csv')

1. Create a new dataframe, model_mpg, that contain the mean combined mpg values in 2008 and 2018 for each unique model

To do this, group by model and find the mean cmb_mpg_2008 and mean cmb_mpg for each.

In [8]:
model_mpg = df.groupby('model').mean()[['cmb_mpg_2008', 'cmb_mpg']]
In [9]:
model_mpg.head()
Out[9]:
cmb_mpg_2008 cmb_mpg
model
ACURA RDX 19.000000 22.500000
AUDI A3 23.333333 28.000000
AUDI A4 21.000000 27.000000
AUDI A6 19.666667 25.666667
AUDI A8 L 16.500000 22.000000

2. Create a new column, mpg_change, with the change in mpg

Subtract the mean mpg in 2008 from that in 2018 to get the change in mpg

In [10]:
model_mpg['mpg_change'] = model_mpg['cmb_mpg'] - model_mpg['cmb_mpg_2008']
In [11]:
model_mpg.head()
Out[11]:
cmb_mpg_2008 cmb_mpg mpg_change
model
ACURA RDX 19.000000 22.500000 3.500000
AUDI A3 23.333333 28.000000 4.666667
AUDI A4 21.000000 27.000000 6.000000
AUDI A6 19.666667 25.666667 6.000000
AUDI A8 L 16.500000 22.000000 5.500000

3. Find the vehicle that improved the most

Find the max mpg change, and then use query or indexing to see what model it is!

In [12]:
max_change = model_mpg['mpg_change'].max()
max_change
Out[12]:
16.53333333333334
In [13]:
model_mpg[model_mpg['mpg_change'] == max_change]
Out[13]:
cmb_mpg_2008 cmb_mpg mpg_change
model
VOLVO XC 90 15.666667 32.2 16.533333

Pandas also has a useful idxmax function you can use to find the index of the row containing a column's maximum value!

In [14]:
idx = model_mpg.mpg_change.idxmax()
idx
Out[14]:
'VOLVO XC 90'
In [15]:
model_mpg.loc[idx]
Out[15]:
cmb_mpg_2008    15.666667
cmb_mpg         32.200000
mpg_change      16.533333
Name: VOLVO XC 90, dtype: float64
In [ ]: