dividing dataframe column by matching index in another dataframe

Question

I have a dataframe like this:

        id1     name    id2   val 
0       1        'A'     1     4
1       1        'B'     1     1
2       2        'C'     3     1
. 
.
.

I have another dataframe that is as follows:

              new_val 
  1              2 
  3              4

I want to make the first dataframe as follows:

        id1     name    id2   val 
0       1        'A'     1     2.0
1       1        'B'     1     0.5
2       2        'C'     3     0.25
. 
.
.

What I want to do is divide the val column in the first dataframe with the value that matches the index to column id2. We see that id2 = 1 then we divide val = 4 by 2 since it corresponds to index 1. id2 = 3 then we divide val=1 by 4 to get 0.25.

I know I could add these into lists of tuples and perform the computation and reset the column, but is this possible with pandas functions? Using for loops for really large datasets would be really computationally expensive.

juanpa.arrivillaga · Accepted Answer · 2017-01-18 00:25:06Z

3

Hmm, this way might be less space efficient, but it should be faster than looping:

>>> df1
   id1 name  id2  val
0    1  'A'    1    4
1    1  'B'    1    1
2    2  'C'    3    1
>>> df2 = pd.DataFrame([2,4], index=[1,3])
>>> df2
   0
1  2
3  4

So, start by setting an index:

>>> df1.set_index('id2', inplace=True)

Then, using df2 which I assume is indexed properly:

>>> df1['divisor'] = df2
>>> df1
     id1 name  val  divisor
id2
1      1  'A'    4        2
1      1  'B'    1        2
3      2  'C'    1        4
>>> df1.val / df1.divisor
id2
1    2.00
1    0.50
3    0.25
dtype: float64

And finally, just to be complete:

>>> df1['val'] = df1.val / df1.divisor
>>> df1
     id1 name   val  divisor
id2
1      1  'A'  2.00        2
1      1  'B'  0.50        2
3      2  'C'  0.25        4
>>> df1.drop('divisor',inplace=True, axis=1)
>>> df1
     id1 name   val
id2
1      1  'A'  2.00
1      1  'B'  0.50
3      2  'C'  0.25

answered Jan 18, 2017 at 0:25

juanpa.arrivillaga

97.6k14 gold badges141 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike El Jackson Over a year ago

Thanks this works much better than what I originally did

piRSquared · Accepted Answer · 2017-01-18 01:03:04Z

3

Using map and /=

df1.val /= df1.id2.map(df2.new_val)
print(df1)

   id1 name  id2   val
0    1  'A'    1  2.00
1    1  'B'    1  0.50
2    2  'C'    3  0.25

answered Jan 18, 2017 at 1:03

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

Ted Petrou · Accepted Answer · 2017-01-18 00:42:09Z

2

There are a number of ways you can do this. You can first tack on the 'new_val' column from the second DataFrame to the first and then manipulate the columns from there.

df.join(df2, on='id2')

Which produces:

   id1 name  id2  val  new_val
0    1  'A'    1    4        2
1    1  'B'    1    1        2
2    2  'C'    3    1        4

And then operate on the columns

df_final['val'] = df_final['val'] / df_final['new_val']
df_final.drop('new_val', axis=1, inplace=True)

   id1 name  id2   val
0    1  'A'    1  2.00
1    1  'B'    1  0.50
2    2  'C'    3  0.25

And some one liners

df.assign(val=lambda x: (x.set_index('id2')['val'] / df2['new_val']).values)

df.set_index('id2', drop=False).assign(val=lambda x: x['val'] / df2['new_val']).reset_index(drop=True)

edited Jan 18, 2017 at 0:42

answered Jan 18, 2017 at 0:25

Ted Petrou

62.4k19 gold badges139 silver badges139 bronze badges

Collectives™ on Stack Overflow

dividing dataframe column by matching index in another dataframe

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related