Python Pandas: applying a specific function to each row

user_6638511 0
Viewed 103

I'm trying to apply a form of normalization to the data I have. I wish to subtract the median of each row from each value in the dataframe. What I have so far:

# Generate sample data
data = { "sample_name": ["s1", "s2", "s3", "s4", "s5", "s6"],
        "group_name": ["g1", "g1", "g1", "g2", "g2", "g2"],
        'col1':[1, 22, 3, 45, 31, 53],  
        'col2':[30, 21, 10, 42, 56, 20],
        'col3':[78, 25, 33, 87, 20, 19],
        'col4':[11, 23, 14, 98, 55, 66],
        'col5':[19, 29, 39, 49, 59, 69],
       } 
df = pd.DataFrame(data) 

# calculate medians of each row
median_ls = list(df.median(axis=1))
# [19.0, 23.0, 14.0, 49.0, 55.0, 53.0]

The expected result is:

-18,11,59,-8,0
-1,-2,2,0,6
-11,-4,19,0,25
-4,-7,38,49,0
-24,1,-35,0,4
0,-33,-34,13,16

I've looked at df.apply(<function>, axis=1), but have been unable to work out the syntax of how to iteratively apply a row-specific function across rows.

pandas python
3 Answers

Use DataFrame.select_dtypes for get numeric columns and subtract by DataFrame.sub with axis=1:

df1 = df.select_dtypes(np.number).sub(df.median(axis=1), axis=0)
print (df1)
   col1  col2  col3  col4  col5
0 -18.0  11.0  59.0  -8.0   0.0
1  -1.0  -2.0   2.0   0.0   6.0
2 -11.0  -4.0  19.0   0.0  25.0
3  -4.0  -7.0  38.0  49.0   0.0
4 -24.0   1.0 -35.0   0.0   4.0
5   0.0 -33.0 -34.0  13.0  16.0

If need assign back output use:

cols = df.select_dtypes(np.number).columns
df[cols] = df[cols].sub(df.median(axis=1), axis=0)
print (df)
  sample_name group_name  col1  col2  col3  col4  col5
0          s1         g1 -18.0  11.0  59.0  -8.0   0.0
1          s2         g1  -1.0  -2.0   2.0   0.0   6.0
2          s3         g1 -11.0  -4.0  19.0   0.0  25.0
3          s4         g2  -4.0  -7.0  38.0  49.0   0.0
4          s5         g2 -24.0   1.0 -35.0   0.0   4.0
5          s6         g2   0.0 -33.0 -34.0  13.0  16.0

Another idea is select all rows without first 2 by DataFrame.iloc:

df.iloc[:, 2:] = df.iloc[:, 2:].sub(df.median(axis=1), axis=0)
print (df)
  sample_name group_name  col1  col2  col3  col4  col5
0          s1         g1 -18.0  11.0  59.0  -8.0   0.0
1          s2         g1  -1.0  -2.0   2.0   0.0   6.0
2          s3         g1 -11.0  -4.0  19.0   0.0  25.0
3          s4         g2  -4.0  -7.0  38.0  49.0   0.0
4          s5         g2 -24.0   1.0 -35.0   0.0   4.0
5          s6         g2   0.0 -33.0 -34.0  13.0  16.0
user_29010020

Try:

df.sub(df.median(axis=1), axis=0)
user_74103830

i allowed myself to use only numeric part

import pandas as pd
# Generate sample data
data = {
    "sample_name": ["s1", "s2", "s3", "s4", "s5", "s6"],
    "group_name": ["g1", "g1", "g1", "g2", "g2", "g2"],
    'col1':[1, 22, 3, 45, 31, 53],
    'col2':[30, 21, 10, 42, 56, 20],
    'col3':[78, 25, 33, 87, 20, 19],
    'col4':[11, 23, 14, 98, 55, 66],
    'col5':[19, 29, 39, 49, 59, 69],
   }
keys = ['col1','col2','col3','col4','col5']
df = pd.DataFrame(data)
print(df)
# calculate medians of each row
median_ls = list(df.median(axis=1))
# [19.0, 23.0, 14.0, 49.0, 55.0, 53.0]
print(median_ls)
print(df[keys].subtract(median_ls, axis=0))

result:

   col1  col2  col3  col4  col5
0 -18.0  11.0  59.0  -8.0   0.0
1  -1.0  -2.0   2.0   0.0   6.0
2 -11.0  -4.0  19.0   0.0  25.0
3  -4.0  -7.0  38.0  49.0   0.0
4 -24.0   1.0 -35.0   0.0   4.0
5   0.0 -33.0 -34.0  13.0  16.0
user_43661540
Related