20

I have a dataframe with rows indexed by chemical element type and columns representing different samples. The values are floats representing the degree of presence of the row element in each sample.

I want to compute the mean of each row and subtract it from each value in that specific row to normalize the data, and make a new dataframe of that dataset.

I tried using mean(1), which give me a Series object with the mean for each chemical element, which is good, but then I tried using subtract, which didn't work.

jeremy radcliff
  • 1,049
  • 3
  • 11
  • 27

2 Answers2

47

You could use DataFrame's sub method and specify that the subtraction should happen row-wise (axis=0) as opposed to the default column-wise behaviour:

df.sub(df.mean(axis=1), axis=0)

Here's an example:

>>> df = pd.DataFrame({'a': [1.5, 2.5], 'b': [0.25, 2.75], 'c': [1.25, 0.75]})
>>> df
     a     b     c
0  1.5  0.25  1.25
1  2.5  2.75  0.75

The mean of each row is straightforward to calculate:

>>> df.mean(axis=1)
0    1
1    2
dtype: float64

To de-mean the rows of the DataFrame, just subtract the mean values of rows from df like this:

>>> df.sub(df.mean(axis=1), axis=0)
     a     b     c
0  0.5 -0.75  0.25
1  0.5  0.75 -1.25
Alex Riley
  • 169,130
  • 45
  • 262
  • 238
  • 3
    I think the axis is really confusing. For example, `axis=1` means getting row-wise average, however, `axis=1` means subtract column-wise, am I misunderstanding the tradition how to use axis? – B.Mr.W. Nov 01 '14 at 21:06
  • @B.Mr.W. I agree it can be pretty confusing (I regularly get stuck and have to play around with axis numbers when dealing with NumPy/Pandas). Basically, `axis=1` just means "across the DataFrame". That can mean entries in a row (as in `df.mean(axis=1)`), or, since the names of the columns also go across the DataFrame, `axis=1` is used when column names need to be referred to. I think there are a couple of questions about this topic on Stack Overflow, one of which I answered [here](http://stackoverflow.com/a/25774395/3923281). – Alex Riley Nov 01 '14 at 21:29
8

Additionally to @ajcr's excellent answer, you might want to consider rearranging how you store your data.

The way you're doing it at the moment, with different samples in different columns, is the way it would be represented if you were using a spreadsheet, but this might not be the most helpful way to represent your data.

Normally, each column represents a unique piece of information about a single real-world entity. The typical example of this kind of data is a person:

id  name  hair_colour  Age
1   Bob   Brown        25

Really, your different samples are different real-world entities.

I would therefore suggest having a two-level index to describe each single piece of information. This makes manipulating your data in the way you want far more convenient.

Thus:

>>> df = pd.DataFrame([['Sn',1,2,3],['Pb',2,4,6]],
                      columns=['element', 'A', 'B', 'C']).set_index('element')
>>> df.columns.name = 'sample'
>>> df # This is how your DataFrame looks at the moment
sample   A  B  C
element         
Sn       1  2  3
Pb       2  4  6
>>> # Now make those columns into a second level of index
>>> df = df.stack()
>>> df
element  sample
Sn       A         1
         B         2
         C         3
Pb       A         2
         B         4
         C         6

We now have all the delicious functionality of groupby at our disposal:

>>> demean = lambda x: x - x.mean()
>>> df.groupby(level='element').transform(demean)
element  sample
Sn       A        -1
         B         0
         C         1
Pb       A        -2
         B         0
         C         2

When you view your data in this way, you'll find that many, many use cases which used to be multi-column DataFrames are in fact MultiIndexed Series, and you have much more power over how the data is represented and transformed.

LondonRob
  • 73,083
  • 37
  • 144
  • 201