1

I have a file with below data:

APPLE,FRUIT,1KG,RED,1660.00|1680.00|1700.00|1720.00|1740.00|1760.00|1800.00|1900.00|1920.00|1520.00|1320.00|1340.00|1360.00|1380.00|1400.00|1420.00|1440.00|1460.00|1480.00|1940.00|940.00|1880.00|1820.00|1840.00|1860.00|1780.00|1500.00|1020.00|1040.00|1060.00|1080.00|1100.00|1120.00|1140.00|1160.00|1180.00|1200.00|1220.00|1240.00|1260.00|1280.00|1300.00|960.00|980.00|1000.00|1540.00|1560.00|1580.00|1600.00|1620.00|1640.00,1429.100000
ORANGE,FRUIT,2KG,GREEN,1660.00|1680.00|1700.00|1720.00|1740.00|1760.00|1900.00|1920.00|1500.00|1320.00|1340.00|1360.00|1380.00|1400.00|1420.00|1440.00|1460.00|1940.00|1800.00|940.00|1880.00|1820.00|1840.00|1860.00|1780.00|1480.00|1020.00|1040.00|1060.00|1080.00|1100.00|1120.00|1140.00|1160.00|1180.00|1200.00|1220.00|1240.00|1260.00|1280.00|1300.00|960.00|980.00|1000.00|1520.00|1540.00|1560.00|1580.00|1600.00|1620.00|1640.00,1432.100000

I want to check each value in column 5(delimiter to get column 5 is ","). Compare the same with column 6 and get the least difference element into the new file.

Expected output is:

APPLE,FRUIT,1KG,RED,1420.00
ORANGE,FRUIT,2KG,GREEN,1440.00

My file has more than 200 rows, looking for a one liner command to do the same.

  • 4
    Your name is 'programmer'... well what have you 'programmed' on your own so far? Where are you stuck? – Panki Mar 14 at 9:04
  • 1
    I cooked up a oneline solution, but it's in python and the tags state bash. – Panki Mar 14 at 9:16
  • 1
    @Panki please don't post answers as comments. – terdon Mar 14 at 9:34
  • 1
    @Panki if it's a one-liner, then sure, you may as well. – terdon Mar 14 at 9:38
  • 1
    Yes it needs to process the file line by line. I don't really know what other solution there would be. – Panki Mar 14 at 9:45
1

Here is my Python solution.

with open('/path/to/file', 'r') as f:
    for line in f:
        x=line.split(',')
        y=x[4].split('|')
        print(','.join(x[:4]) + ',' + str(min(y, key=lambda z: abs(float(z)-float(x[5])))))

If you have something like Pyed Piper, also known as pyp, you can run this straight from the terminal prompt: https://code.google.com/archive/p/pyp/

  • Thanks for the share. I am able to get the desired result with this. – Programmer Mar 14 at 10:08
2

As an AWK solution is requested, I will give one.

awk -F, 'NF>4{
        n=split($5,a,"|")
        l=a[0];
        for(i=1;i<n;i++)
                if((a[i]-$6)^2<(l-$6)^2)
                        l=a[i];
        print $1","$2","$3","$4","l                                                                                                                                                                                
}' input > output
  • 1
    +1, clever use of ^2 to get around the absence of an abs function in awk – iruvar Mar 15 at 19:25

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.