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.

12
  • 4
    Your name is 'programmer'... well what have you 'programmed' on your own so far? Where are you stuck?
    – Panki
    Commented Mar 14, 2019 at 9:04
  • 1
    I cooked up a oneline solution, but it's in python and the tags state bash.
    – Panki
    Commented Mar 14, 2019 at 9:16
  • 1
    @Panki please don't post answers as comments.
    – terdon
    Commented Mar 14, 2019 at 9:34
  • 1
    @Panki if it's a one-liner, then sure, you may as well.
    – terdon
    Commented Mar 14, 2019 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
    Commented Mar 14, 2019 at 9:45

2 Answers 2

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
    +1, clever use of ^2 to get around the absence of an abs function in awk
    – iruvar
    Commented Mar 15, 2019 at 19:25
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/

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

You must log in to answer this question.

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