How to subtract all rows in a dataframe with a row from another dataframe?

Question

I would like to subtract all rows in a dataframe with one row from another dataframe. (Difference from one row)

Is there an easy way to do this? Like df-df2)?

df = pd.DataFrame(abs(np.floor(np.random.rand(3, 5)*10)),
...                 columns=['a', 'b', 'c', 'd', 'e'])
df

Out[18]:
   a  b  c  d  e
0  8  9  8  6  4
1  3  0  6  4  8
2  2  5  7  5  6


df2 = pd.DataFrame(abs(np.floor(np.random.rand(1, 5)*10)),
...                 columns=['a', 'b', 'c', 'd', 'e'])
df2

   a  b  c  d  e
0  8  1  3  7  5

Here is an output that works for the first row, however I want the remaining rows to be detracted as well...

df-df2

    a   b   c   d   e
0   0   8   5  -1  -1
1 NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN

unutbu · Accepted Answer · 2014-02-28 11:23:13Z

18

Pandas NDFrames generally try to perform operations on items with matching indices. df - df2 only performs subtraction on the first row, because the 0 indexed row is the only row with an index shared in common.

The operation you are looking for looks more like a NumPy array operation performed with "broadcasting":

In [21]: df.values-df2.values
Out[21]: 
array([[ 0,  8,  5, -1, -1],
       [-5, -1,  3, -3,  3],
       [-6,  4,  4, -2,  1]], dtype=int64)

To package the result in a DataFrame:

In [22]: pd.DataFrame(df.values-df2.values, columns=df.columns)
Out[22]: 
   a  b  c  d  e
0  0  8  5 -1 -1
1 -5 -1  3 -3  3
2 -6  4  4 -2  1

answered Feb 28, 2014 at 11:23

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

Comments

Jeff · Accepted Answer · 2014-02-28 12:57:42Z

6

You can do this directly in pandas as well. (I used df2 = df.loc[[0]])

In [80]: df.sub(df2,fill_value=0)
Out[80]: 
   a  b  c  d  e
0  0  0  0  0  0
1  7  6  0  7  8
2  4  4  3  6  2

[3 rows x 5 columns]

answered Feb 28, 2014 at 12:57

Jeff

130k21 gold badges223 silver badges189 bronze badges

1 Comment

yash Over a year ago

I think the point is to make sure all the rows of df are being subtracted.

IPV3001 · Accepted Answer · 2014-02-28 13:09:02Z

1

Alternatively you could simply use the apply function on all rows of df.

df3 = df.apply(lambda x: x-df2.squeeze(), axis=1)
# axis=1 because it should apply to rows instead of columns
# squeeze because we would like to substract Series

answered Feb 28, 2014 at 13:09

IPV3001

3703 silver badges16 bronze badges

1 Comment

Jeff Over a year ago

this would be MUCH slower because you are aligning each row!

Collectives™ on Stack Overflow

How to subtract all rows in a dataframe with a row from another dataframe?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related