This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Averaging multiple columns

Hi,

I have a file with five columns, from which I want the mean of the last four columns where the first rows of the first column are the same. For example, my file looks like this -

A 1 1 1 1
A 1 3 1 1
A 1 1 2 3
B 5 7 2 4
C 2 1 5 1
C 2 2 3 6

The desired output is -

A 1 1.7 1.3 1.7
B 5 7 2 4
C 2 1.5 4 3.5

Any help would be appreciated.

Thanks.

r shell
• 1,712 views • link
updated by zx8754 zx8754 • written by vinayjrao vinayjrao

2 answers 

import pandas as pd
df = pd.read_csv("tmp.txt", sep="\t", header=None)
df
    0   1   2   3   4
0   A   1   1   1   1
1   A   1   3   1   1
2   A   1   1   2   3
3   B   5   7   2   4
4   C   2   1   5   1
5   C   2   2   3   6

df.groupby(0).mean()

1   2   3   4
0               
A   1.0 1.666667    1.333333    1.666667
B   5.0 7.000000    2.000000    4.000000
C   2.0 1.500000    4.000000    3.500000

df.groupby(0).mean().to_csv("tmp_mean.txt", sep="\t", header = None)
• 0 views • link
written by GouthamAtla GouthamAtla

Thank you for your help. It works perfectly.

• 0 views • link
written by vinayjrao vinayjrao

Using R:

# example data
df1 <- read.table(text = "
A 1 1 1 1
A 1 3 1 1
A 1 1 2 3
B 5 7 2 4
C 2 1 5 1
C 2 2 3 6
", header = FALSE)

aggregate(df1[ -1 ], df1[ 1 ], FUN = mean)
#    V1 V2       V3       V4       V5
# 1  A  1 1.666667 1.333333 1.666667
# 2  B  5 7.000000 2.000000 4.000000
# 3  C  2 1.500000 4.000000 3.500000
• 0 views • link
written by zx8754 zx8754

Thank you for your reply. I am still working on it.

Will update once done.

• 0 views • link
written by vinayjrao vinayjrao

Log in to answer this question.

More posts like this