0

I'm stuck at a problem which I need to remove rows from file based on its own SD. I tried awk but hasn't figured it out yet.

Here's the input file:

A x 50
B y 100
C q 34
D ua 80

I want to first calculate the mean and SD of the third column, which can be done by command like

awk '{s+=$3; ss+=$3^2} END{print mean=s/NR, SD=sqrt(ss/NR-m^2)}' file

However, I want to further remove rows based on the SD - for instance, I want only rows with higher than 0.5 SD above mean, it would be something like

awk '$3 > m + 0.5*n' file > fileout

Is there any way to chain these two awk into one? If not, are there any other methods?

Thank you very much!

2 Answers 2

0
$ awk 'NR==FNR{ s+=$3; ss+=$3^2; nr=NR; next }
       FNR==1 { mean=s/nr; sd=sqrt(ss/nr-mean^2) }
       $3> mean+(0.5*sd)' infile infile
B y 100
D ua 80
-1

I recommend you to utilize this with python:

import pandas as pd
import numpy as np

# Read the input file into a pandas DataFrame
input = pd.read_csv('file', delimiter=' ', header=None)

# Calculate the mean and standard deviation
mean = input[2].mean()
sd = input[2].std()

# Filter rows based on the condition
filtered = df[df[2] > mean + 0.5 * sd]

# Write the filtered DataFrame to an output file
filtered.to_csv('outfile', sep=' ', header=False, index=False)

The code reads the file into a pandas DataFrame, calculates the mean and standard deviation, filters the rows based on the condition, and finally writes the filtered DataFrame to the outfile.

If you are unfamiliar with Python, you can copy the following commands and paste them into the Online Python Code Editor.

## Prepare your input file
lines = ['A x 50', 'B y 100', 'C q 34', 'D ua 80']

with open('file', 'w') as file:
    for line in lines:
        file.write(line + '\n')
        
## The command to remove rows from input based on its SD       
import pandas as pd
import numpy as np

# Read the input 
input = pd.read_csv('file', delimiter=' ', header=None)

# Calculate the mean and standard deviation
mean = input[2].mean()
sd = input[2].std()

# Filter rows based on your condition
fileout = input[input[2] > mean + 0.5 * sd]

# Print the output
print(fileout)

Notice this command will prepare the input data.

2
  • 2
    this looks like to me an AI generated answer. since to calculate the mean, you need first read the entire records in a file then do filtering $3 > mean + 0.5*SD; Additionally SD won't be initialized until the END block is executed, so you are just doing $3 > mean + 0.5*0 which is just $3 > mean filtration. which totally it's wrong result at all. Jun 29, 2023 at 6:29
  • I got the issue; thank you for sharing your point of view in every aspect. I generally recommend Python to utilize statistics projects. Moreover, up to this moment, I believe awk cannot utilize this outcome. However, I will update the answer if I achieved any progress. Please feel free to check the Python command. All the best. Jun 29, 2023 at 19:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .