[SOLVED] Script to multiply 29th field in CSV file by 1,56

dugan · 07-25-2012, 08:58 AM

In Python (untested):

Code:

from csv import csv
from os import remove

new_rows = []
with open('file.csv') as f:
    csv_reader = reader(f)
    for row in reader:
        new_rows.append(row)
        new_rows[-1][29] = str(float(row[29]) * 1.56)

remove('files.csv')

with open('file.csv', 'w') as f:
    csv_reader = writer(f)
    csv_writer.writerows(new_rows)

grail · 07-25-2012, 09:23 AM

Unfortunately Wim's solution will not work, as is evident when tested on the file provided. It suffers from greedy regex.

If you have the latest version of gawk (4+) this is quite easy to overcome:

Code:

awk 'BEGIN{FPAT="([^,]+)|(\"[^\"]+\")"}NR > 1 && $29 = sprintf("%.2f", gensub(/[.]/,",","1",$29) * 1,56)' file

With an older version you will need to process each line with a loop and reconstruct the fields, tiresome but possible so advise if you need such a solution?

knilux · 07-26-2012, 04:39 AM

When I checked for comma's in the fields, I did not find any because I took another part of the file with shorter descriptions. After testing I got these errors and used them to cut and paste. I should have done that before, but then again, you learn something each day.

I will test Grail's latest solution and come back here. The installed version of gawk is 3.1.6.

Has anyone tested Dugan's solution?

knilux · 07-26-2012, 05:24 AM

I tested the script twice with different files, but it only seems to remove the first line. But maybe this is because I have an older version of gawk? I will see if I can get a newer one installed.

knilux · 07-26-2012, 06:13 AM

I am afraid a newer version can not be installed. According to the gawk site version 4 is used for SuSE 12.1. This machine runs 11.3 and has gawk 3.1.6-31.1 installed. Updating gives errors because of missing lib's. Is version 3 not capable of doing this?

schneidz · 07-26-2012, 06:47 AM

if all the fields are encapsulated in quotes you can use that as the field separator. you just gotta' make sure there arent any quotes inside of any fields.

knilux · 07-26-2012, 08:16 AM

Quote:

Originally Posted by schneidz

if all the fields are encapsulated in quotes you can use that as the field separator. you just gotta' make sure there arent any quotes inside of any fields.

I have checked the file but could not find any. Importing it in a spreadsheet works fine when I tell it that the quotes are textseparators. I think it is safe to assume there are no quotes in the fields

Wim Sturkenboom · 07-26-2012, 08:50 AM

Opening it in a spreadsheet should work straight away with the comma as separator; you might have to set some additional parameters.

Both openoffice and excel do not give problems with your sample file.

schneidz · 07-26-2012, 08:53 AM

Quote:

Originally Posted by knilux

I have checked the file but could not find any. Importing it in a spreadsheet works fine when I tell it that the quotes are textseparators. I think it is safe to assume there are no quotes in the fields

\ok, so try it with awk and hope for the best.\

knilux · 07-26-2012, 09:06 AM

Quote:

Originally Posted by schneidz

\ok, so try it with awk and hope for the best.\

I tried before, but it does not work. Probably because my gawk has an older version. Is there no way to use version 3 instead of 4?

grail · 07-26-2012, 10:34 AM

Right ... so take 3. Yes my previous was only designed to work on 4 and above.

I feel schneidz has let the light in though:

Code:

awk 'BEGIN{OFS=FS="\",\""}(NR > 1 && $29 = sprintf("%.2f", gensub(/[.]/,",","1",$29) * 1,56)) || NR == 1' file

The NR stuff is just so we ignore the first line as it is the header and will not wish to be multiplied by anything. The last NR will still print this line

knilux · 07-27-2012, 05:43 AM

Thanks Grail. This line begins to do something. It does work on the 29th column but, strangely enough, not on all numbers and the outcome is not correct. Also, when a number is less than 1 it rounds it down to 0. (I had to change the 1,56 to 1.56)

I hope you have any clue. It seems to be more difficult than we anticipated. But thanks for your help and patience so far.

grail · 07-27-2012, 10:53 AM

I am not sure I follow? have you new data that it is not working with? I did only peruse the results on your current data but most seemed to be good.

Also, if you are now using 1.56, the gensub is no longer needed as it will replace the dot with a comma and I would assume then treat the value like a string and remove everything after the
first non-number, ie the comma.

knilux · 07-30-2012, 01:28 AM

Yes, I tested the script with another file. That is, I extracted another part of exactly the same big file. It should not make any difference. The data has the same format as the one you tested. I have attached it for your reference.

I tested it with the comma in the 1,56 and with a dot. The results were not the exactly the same but both are not correct.

grail · 07-30-2012, 02:25 AM

Well I am still a little confused on whether or not the locale is playing any part at all, but the following works for me using 1.56:

Code:

awk 'BEGIN{OFS=FS="\",\""}(NR > 1 && $29 = sprintf("%.2f", $29 * 1.56)) || NR == 1' testbestand.csv.txt