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.
# load datasets
import pandas as pd
df_08 = pd.read_csv('clean_08.csv')
df_18 = pd.read_csv('clean_18.csv')
# rename 2008 columns
df_08.rename(columns=lambda x: x[:10] + "_2008", inplace=True)
# view to check names
df_08.head()
# merge datasets
df_combined = df_08.merge(df_18, left_on='model_2008', right_on='model', how='inner')
# view to check merge
df_combined.head()
Save the combined dataset
df_combined.to_csv('combined_dataset.csv', index=False)
# load dataset
import pandas as pd
df = pd.read_csv('combined_dataset.csv')
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.
model_mpg = df.groupby('model').mean()[['cmb_mpg_2008', 'cmb_mpg']]
model_mpg.head()
mpg_change
, with the change in mpg¶Subtract the mean mpg in 2008 from that in 2018 to get the change in mpg
model_mpg['mpg_change'] = model_mpg['cmb_mpg'] - model_mpg['cmb_mpg_2008']
model_mpg.head()
Find the max mpg change, and then use query or indexing to see what model it is!
max_change = model_mpg['mpg_change'].max()
max_change
model_mpg[model_mpg['mpg_change'] == max_change]
Pandas also has a useful idxmax
function you can use to find the index of the row containing a column's maximum value!
idx = model_mpg.mpg_change.idxmax()
idx
model_mpg.loc[idx]