Divide dataframe column by a specific cell

Tags: , ,



I want to divide a dataframe column by a specific cell in the same dataframe.

I have a dataframe like this:

 date      type          score   
 20201101  experiment1   30      
 20201101  experiment2   20      
 20201101  baseline      10      
 20201102  experiment1   60      
 20201102  experiment2   50      
 20201102  baseline      10      

I want to compute the score_ratio by dividing the score by the ‘baseline’ score of that date.

 date      type          score   score_ratio
 20201101  experiment1   30      3
 20201101  experiment2   20      2
 20201101  baseline      10      1
 20201102  experiment1   60      6
 20201102  experiment2   50      5
 20201102  baseline      10      1

The score_ratio for (date, type) = (20201101, experiment1) should be obtained by dividing its score by the score of (20201101, baseline). In this case, it should be 30 / 10 = 3. Similarly. for (20201101, experiment2), we should divide the score by the same thing, (20201101, baseline). For a different date, say (20201102, experiment1), it should be divided by the baseline of that date, (20201102, baseline).

How do I add this column with dataframe operations?

So far, I have this but am unsure of what expression I should be dividing by: df['score_ratio'] = df['score'].div(...)

Edit:

I get the error for the last line ValueError: Length of values does not match length of index

     ID    date        type          room    score         
0  id1     20201120    baseline      1     450.25    
0  id2     20201120    experiment1   1     -3637.24   
0  id3     20201121    baseline      1     200.00   
1  id4     20201121    experiment1   1     300.00   
2  id5     20201120    baseline      2     600.00   
3  id6     20201120    experiment1   2     800.00   


    _df = df.set_index('date', 'room')
    d = _df.query('type=="baseline"')
    print(_df['score'].div(d['score']).values)
    df['score_ratio'] = _df['score'].div(d['score']).values
            

Answer

 #Mask all whose type is baseline into a new datframe and merge to the main df
 g=pd.merge(df, df[df.type.eq('baseline')].drop(columns='type'),how='left', on='date', suffixes=('', '_right'))

 #Calculate the score_ratio and drop the extra column acquired during merge
    df=g.assign(score_ratio=g.score.div(g.score_right).astype(int)).drop(columns=['score_right'])

print(df)

     date         type  score    score_ratio
0  20201101  experiment1     30            3
1  20201101  experiment2     20            2
2  20201101     baseline     10            1
3  20201102  experiment1     60            6
4  20201102  experiment2     50            5
5  20201102     baseline     10            1

How it works

#New dataframe with baselines only
df1=df[df.type.eq('baseline')].drop(columns='type')

#Modified original dataframe with baselines added
g=pd.merge(df, df1,how='left', on='date', suffixes=('', '_right'))

#new column called score_ratio
g=g.assign(score_ratio=g.score.div(g.score_right).astype(int))

#drop column called score_right which was acquired during merge
g=g.drop(columns=['score_right'])


Source: stackoverflow