python - New df column that is current df1 value divided by df2 value

Question

Consider these 2 dataframes:

df1 - Nationally observed mpgs

state   make    model   fuel    mpg
FL  honda   fit diesel  43
FL  honda   fit gas 33
FL  vw  golf    diesel  48
FL  vw  golf    gas 35
FL  ford    fiesta  diesel  40
FL  ford    fiesta  gas 36
FL  toyota  corolla diesel  44
FL  toyota  corolla gas 38

df2 -CAFE standards

make    model   fuel    mpg
honda   fit diesel  43
honda   fit gas 33
vw  golf    diesel  48
vw  golf    gas 35
ford    fiesta  diesel  40
ford    fiesta  gas 36
toyota  corolla diesel  44
toyota  corolla gas 38
nissan  sentra  diesel  39
nissan  sentra  gas 29

I want to make a new column in df1['avg'] that is the observed mpg divided by the CAFE Std for make, model, fuel.

Here is the approach I was trying by brute force:

make_list = ['ford', 'nissan']
model_list = ['focus', 'sentra']
fuel_list = ['gas', 'diesel']

df3 = df2.loc[df2['make'].isin(make_list)]
df3 = df2.loc[df2['model'].isin(model_list)]
df3 = df2.loc[df2['fuel'].isin(fuel_list)]
goal = df3.iloc[0]['mpg']
print goal

for make in make_list:
    for model in model_list:
        for fuel in fuel_list:
            df1['avg'] = df1['mpg'] / goal

This is actually for something way bigger than this but I threw these together to demonstrate. --Thanks - this is my first post/question so be gentle.

标签： pythonpandasdataframe

Answer 1

这是一种方式。诀窍是设置索引以创建和映射从一个数据帧到另一个数据帧的系列。

请注意，您的数据df1似乎df2完全对齐，因此每行的比率为 1.0。

idx = ['make', 'model', 'fuel']

s = df2.set_index(idx)['mpg'].dropna()

df1['std'] = df1.set_index(idx).index.map(s.get)
df1['ratio'] = df1['mpg'] / df1['std']

print(df1)

  state    make    model    fuel  mpg  std  ratio
0    FL   honda      fit  diesel   43   43    1.0
1    FL   honda      fit     gas   33   33    1.0
2    FL      vw     golf  diesel   48   48    1.0
3    FL      vw     golf     gas   35   35    1.0
4    FL    ford   fiesta  diesel   40   40    1.0
5    FL    ford   fiesta     gas   36   36    1.0
6    FL  toyota  corolla  diesel   44   44    1.0
7    FL  toyota  corolla     gas   38   38    1.0