CODEWITHSUNDEEP
pythonpandasdataframesubtraction
dana111
dana111

Reputation: 469

How to subtract each row of a pandas DataFrame from a number?

Say I have a DataFrame:

ds = pd.DataFrame(np.abs(randn(3, 4)), index=[1,2,3], columns=['A','B','C','Average'])
ds
      A         B         C      Average
1  1.099679  0.042043  0.083903  0.410128
2  0.268205  0.718933  1.459374  0.758887
3  0.680566  0.538655  0.038236  1.169403

How do I subtract (and replace with the result) A, B and C in row one with the average in row 1?

Upvotes: 1

Views: 6676

Answers (2)

Alex Riley
Alex Riley

Reputation: 176978

One relatively simple way is to use the sub method (I'm assuming that Average is always the last column):

ds[ds.columns[:-1]].sub(ds.Average, axis=0)

This does the following:

  • ds[ds.columns[:-1]] is a DataFrame containing all but the last column (Average) of ds.

  • .sub(ds.Average, axis=0) subtracts the row-values in the Average column from the corresponding rows in the DataFrame.

To alter your original ds, make sure to rebind the relevant columns of ds to the new DataFrame of values:

ds[ds.columns[:-1]] = ds[ds.columns[:-1]].sub(ds.Average, axis=0)

Upvotes: 3

Woody Pride
Woody Pride

Reputation: 13965

How about

ds['A'] = ds['A'] - ds['Average']
ds['B'] = ds['B'] - ds['Average']
ds['C'] = ds['C'] - ds['Average']

Pandas is easy like that!

Oh, that does it for the entire DF. You only want it for the firs row is that right? 

ds.loc[1, 'A'] = ds.loc[1, 'A'] - ds.loc[1, 'Average']
ds.loc[1, 'B'] = ds.loc[1, 'B'] - ds.loc[1, 'Average']
ds.loc[1, 'C'] = ds.loc[1, 'C'] - ds.loc[1, 'Average']

or in a loop:

for col in ['A', 'B', 'C']:
    ds.loc[1, col] = df.loc[1, col] - ds.loc[1, 'Average']

and so on...

if you have thousands of columns then simply do:

for col in ds.columns:
    ds[col] = ds[col] - ds['Average']

Upvotes: 1

Related Questions