qsv stats regards numbers with thousands separators as string

[ggrothendieck]

Not sure if this is a feature or bug but I wanted to get the sum of all numeric columns but those columns which actually represent numbers but have comma thousands and millions separators are regarded as string. For example
consider the us-states-example.csv file. This code below will bring the file up in csvlens showing sums at the bottom of all numeric columns on Windows. The problem is that Population and Area actually represent numbers but are regarded as string columns. Only Sales Tax has a sum because the others have comma separators in them. (continued after code)

(type us-states-example.csv
qsv stats us-states-example.csv | ^
qsv search -v qsv__ | ^
qsv select sum | ^
qsv behead | ^
qsv transpose ) | ^
qsv lens

Other things that would be nice for qsv stats would be to have a flag for specifying which stats you want and some better way to get rid of the rows that starts with qsv__. What are they for? They don't have any values in this example. If I run xsv stats instead of qsv stats I don't get the qsv__ rows.

[jqnatividad]

You can remove the separators using qsv apply operations currencytonum

qsv/tests/test_apply.rs

Lines 1881 to 1970 in fdb9ff7

	fn apply_ops_currencytonum() {
	let wrk = Workdir::new("apply");
	wrk.create(
	"data.csv",
	vec![
	svec!["money"],
	svec!["$10.00"],
	svec!["$-10.00"],
	svec!["$ 12 500.00"],
	svec!["12,500.00"],
	svec!["1,250,000"],
	svec!["$5"],
	svec!["0"],
	svec!["5"],
	svec!["$0.25"],
	svec!["$ 10.05"],
	svec!["¥10,000,000.00"],
	svec!["£423.56"],
	svec!["€120.00"],
	svec!["֏99,999.50"],
	svec!["€300 999,55"],
	svec!["This is not money. Leave untouched."],
	svec!["₱1,234,567.89"],
	svec!["₽234,567.89"],
	svec!["₪ 567.89"],
	svec!["₩ 567.89"],
	svec!["₩ 89,123.0"],
	svec!["ƒ 123,456.00"],
	svec!["฿ 789,123"],
	svec!["₫ 456"],
	svec!["123,456.00 $"],
	svec!["USD 10,000"],
	svec!["EUR 1234.50"],
	svec!["JPY 9,999,999.99"],
	svec!["RMB 6543.21"],
	svec!["$10.0099"],
	svec!["$10.0777"],
	svec!["$10.0723"],
	svec!["$10.8723"],
	svec!["$10.77777"],
	svec!["$10.777"],
	],
	);
	let mut cmd = wrk.command("apply");
	cmd.arg("operations")
	.arg("currencytonum")
	.arg("money")
	.arg("data.csv");
	let got: Vec<Vec<String>> = wrk.read_stdout(&mut cmd);
	let expected = vec![
	svec!["money"],
	svec!["10.00"],
	svec!["-10.00"],
	svec!["12500.00"],
	svec!["12500.00"],
	svec!["1250000.00"],
	svec!["5.00"],
	svec!["0"],
	svec!["5.00"],
	svec!["0.25"],
	svec!["10.05"],
	svec!["10000000.00"],
	svec!["423.56"],
	svec!["120.00"],
	svec!["99999.50"],
	svec!["300999.55"],
	svec!["This is not money. Leave untouched."],
	svec!["1234567.89"],
	svec!["234567.89"],
	svec!["567.89"],
	svec!["567.89"],
	svec!["89123.00"],
	svec!["123456.00"],
	svec!["789123.00"],
	svec!["456.00"],
	svec!["123456.00"],
	svec!["10000.00"],
	svec!["1234.50"],
	svec!["9999999.99"],
	svec!["6543.21"],
	svec!["10.01"],
	svec!["10.08"],
	svec!["10.07"],
	svec!["10.87"],
	svec!["10.78"],
	svec!["10.78"],
	];
	assert_eq!(got, expected);
	}

qsv select 'Population (2019)' us-states-example.csv | 
qsv apply operations currencytonum 'Population (2019)' | 
qsv behead | 
qsv lens

The qsv__ rows are "dataset stats".

See https://github.com/dathere/qsv/wiki/Supplemental#stats-command-output-explanation

As for removing "dataset stats", I'll add an option to suppress them. I added them because I needed them for other projects that use qsv, as it allows me to skip calling qsv count, headers and diff separately; and its used by other "smart" qsv commands that use the dataset stats to make them work smarter/faster.

For now, you can remove the qsv__ rows by using slice:

# slice the last four rows from stats output
qsv stats us-states-example.csv  |  ^
qsv slice -s -4 --invert | ^
qsv lens

Moving this issue to Discussion.

[ggrothendieck]

The problem with apply is that you have to know ahead of time which columns to use. The pipeline shown at the beginning of this thread works without knowing that so it could be used on any csv file rather than having a file-specific pipeline for each file. It already works as is on any file but it only works if the numeric columns have no thousands separators..

[jqnatividad]

FYI, apply's column argument supports select syntax.

qsv select 'Population (2019)' us-states-example.csv | 
qsv apply operations currencytonum 1  |
qsv lens

[ggrothendieck]

That requires that the user specify the columns but I was looking for something generic that could be applied to any file with just the file name as input but thinking about it some more we can just remove all commas; however, the problem becomes that it seems that qsv stats will not accept stdin:

qsv stats us-states-example.csv  <-- ok
type us-states-example.csv | qsv stats <-- gives error
type us-states-example.csv | xsv stats <-- using xsv it works

As I was hoping to do this without generating an intermediate file. This generates an error at the qsv stats line.

@echo off
(type %1
qsv replace , "" %1 | ^
qsv stats | ^
qsv search -v qsv__ | ^
qsv select sum | ^
qsv behead | ^
qsv transpose ) | ^
qsv lens

At the expense of using xsv in addition to qsv this works. Note the xsv stats line and now we don't need the qsv search line either. Would be nice if we could do this entirely in qsv.

@echo off
(type %1
qsv replace , "" %1 | ^
xsv stats | ^
qsv select sum | ^
qsv behead | ^
qsv transpose ) | ^
qsv lens

[jqnatividad]

Can you try this?

@echo off
set QSV_SKIPFORMAT_CHECK "1"
(type %1
qsv replace , "" %1 | ^
qsv stats | ^
qsv select sum | ^
qsv behead | ^
qsv transpose ) | ^
qsv lens

I added CSV format checking in a previous release, but I forgot to skip it if accepting input from stdin.

[ggrothendieck]

With this minor modification it works!

@echo off
setlocal
set QSV_SKIP_FORMAT_CHECK=1
(type %1
qsv replace , "" %1 | ^
qsv stats | ^
qsv select sum | ^
qsv behead | ^
qsv transpose ) | ^
qsv lens

[jqnatividad]

I also fixed it in 3.1.1 so that you won't need to specify QSV_SKIP_FORMAT_CHECK when using stdin with stats.

ff8957e