-1

I have a data frame like this:

> head(dat_sg2)
               DwoC_2318_norm.1 DwoC_2318_norm.2 DwoC_2318_norm.3 DwoC_3395_norm.1 DwoC_3395_norm.2 DwoC_3395_norm.3 DwoC_6154_norm.1
Ku8QhfS0n_hIOABXuE         4.865523         4.806292         4.478393         4.539028         4.050325         4.440587         4.110421
Bx496XsFXiAlj.Eaeo         6.123590         6.423548         6.561369         5.856075         5.858094         5.930103         5.801459
W38p0ogk.wIBVRXllY         7.791964         7.648746         7.705958         7.561884         7.699504         7.676182         7.479021
QIBkqIS9LR5DfTlTS8         5.810877         5.579234         5.698071         5.088198         5.076525         5.367539         3.887972
BZKiEvS0eQ305U0v34         6.294961         6.358164         5.876450         5.414746         5.664350         5.924501         4.446681
6TheVd.HiE1UF3lX6g         5.268226         5.337910         5.420836         5.604646         5.007336         5.101670         5.590275

I need to get a data frame with mean between each 3 columns. So my desired result would have these 6 rows with 2 columns, say DwoC_2318 and DwoC_3395.

The output would look like this:

                    DwoC_2318_mean       DwoC_3395_mean
Ku8QhfS0n_hIOABXuE       4.716736           4.343313
Bx496XsFXiAlj.Eaeo       …                     …
W38p0ogk.wIBVRXllY       …                     …
QIBkqIS9LR5DfTlTS8       …                     …
BZKiEvS0eQ305U0v34       …                     …
6TheVd.HiE1UF3lX6g       …                     …

where:

4.716736=(4.865523+4.806292+4.478393)/3

Please note that my original data frame consists of 21 columns and about 20000 rows.

I guess I could use here R apply function with rowMeans but I don't know how to apply it to calculate means between each 3 columns.

I tried doing this on my full data frame (df) which has 15568 rows and 21 columns:

groups=c(1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,6,6,6,7,7,7)
x=apply(df,1,function(x) tapply(x, list(groups), mean))

but instead of getting in output 15568 rows and 7 columns I got:

7 rows and 15568 columns.

  • Please also show the output you would want to get for your example input file and if it's not obvious explain how the output values should be calculated (e.g. something like column 3 of the output is the mean value of columns 2, 3 and 4 of the input) – Bodo Apr 19 '19 at 19:50
  • thanks for pointing that out, I just added how the output would look like. – anikaM Apr 19 '19 at 20:02
0

I soloved it by transposing the data frame first, because it was easier for me to calculate means between each 3 rows. Later I transposed it back.

#read in data
df=read.table("DwoC", header=T)
#transpose it
df <- as.data.frame(t(df))
# remove .1,.2,...strings from row names, and save unique row names
rn=unique(gsub("\\..*","",rownames(df)))
n=3
# calculate means between each 3 rows
dd=aggregate(df,list(rep(1:(nrow(df)%/%n+1),each=n,len=nrow(df))),mean)[-1]
# transpose it back
dt <- as.data.frame(t(dd))
# rename columns as the names were lost during transpose step
names(dt)=rn 
0

Based on Calculate row means on subset of columns

> df = read.table('file')
> 
> data.frame(ID=df[,0], DwoC_2318_mean=rowMeans(df[1:3]), DwoC_3395_mean=rowMeans(df[4:6]))
                   DwoC_2318_mean DwoC_3395_mean
Ku8QhfS0n_hIOABXuE       4.716736       4.343313
Bx496XsFXiAlj.Eaeo       6.369502       5.881424
W38p0ogk.wIBVRXllY       7.715556       7.645857
QIBkqIS9LR5DfTlTS8       5.696061       5.177421
BZKiEvS0eQ305U0v34       6.176525       5.667866
6TheVd.HiE1UF3lX6g       5.342324       5.237884
> 
  • well I have more columns than I gave in sample data, so specifying exact column names wound't work – anikaM Apr 19 '19 at 22:17
0

As I'm not very good with R, I'll attempt an awk solution instead:

$ awk 'NR == 1 { next } { j=0; for (i = 2; i+2 <= NF; i+=3) m[++j] = ($(i+0)+$(i+1)+$(i+2))/3; $0 = $1; for (i=1; i<=j; ++i) $(i+1)=m[i]; print }' file
Ku8QhfS0n_hIOABXuE 4.71674 4.34331
Bx496XsFXiAlj.Eaeo 6.3695 5.88142
W38p0ogk.wIBVRXllY 7.71556 7.64586
QIBkqIS9LR5DfTlTS8 5.69606 5.17742
BZKiEvS0eQ305U0v34 6.17653 5.66787
6TheVd.HiE1UF3lX6g 5.34232 5.23788

Annotated awk script:

# Skip header
NR == 1 { next }

{
    j = 0

    # Go through the columns from column 2 onwards in groups of thee columns,
    # calculating the average of the group and store it in the array m.
    for (i = 2; i + 2 <= NF; i += 3)
        m[++j] = ($(i+0) + $(i+1) + $(i+2))/3

    # Rewrite the current row as the first column only.
    $0 = $1

    # Add the calculated averages as new columns after column 1.
    for (i = 1; i <= j; ++i)
        $(i+1) = m[i]

    print
}

The code assumes that the number of columns after column 1 is a multiple of three. If there are one or two trailing columns (as in the example), this data will be dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.