Comment on how NaN should be represented in CSV

[rweigel]

I think most CSV readers accept "NaN" and NaN. We should comment on this.

[jbfaden]

My two cents is that only NaN be allowed, and "NaN" is a string. That said CSV doesn't really have typeness (float vs string), and the quotes are more to allow strings which contain commas. I've seen many CSV files which do not quote strings, so the typeness really needs to come from the info.

[rweigel]

Conclusion

Use NaN for server (not quoted, so consistent with not being a string). Most widely accepted by the software surveyed (link to this issue).

For clients, expect NaN, nan, "NaN", "nan".

"NaN" and "nan" allowed in JSON

Results

# np and pd parse to IEEE-754 nan
csv_data = '1,2,NaN,4'
csv_data = '1,2,"NaN",4'
csv_data = '1,2,nan,4'
csv_data = '1,2,"nan",4'

# np parses to IEEE-754 nan
csv_data = '1,2,Nan,4'
csv_data = '1,2,"Nan",4'
csv_data = '1,2,naN,4'
csv_data = '1,2,"naN",4'

[rweigel]

import pandas as pd
from io import StringIO
import numpy as np
import json

x = '{"data": [1, 2, null, 4]}'
data = json.loads(x)
print(data)
print(type(data['data'][2]))
exit()

# np and pd parse to IEEE-754 nan
csv_data = '1,2,NaN,4'
csv_data = '1,2,"NaN",4'
csv_data = '1,2,nan,4'
csv_data = '1,2,"nan",4'

# np parses to IEEE-754 nan
csv_data = '1,2,Nan,4'
csv_data = '1,2,"Nan",4'
csv_data = '1,2,naN,4'
csv_data = '1,2,"naN",4'

# Simulate the CSV content
csv_data = '1,2,NaN,4'
# Read the CSV line into a numpy array
arr = np.genfromtxt(StringIO(csv_data), delimiter=',')
print(arr)
print(arr.dtype)

# Read the CSV line into a DataFrame
df = pd.read_csv(StringIO(csv_data), header=None)
print(df)
print(df.dtypes)

[jbfaden]

Here's IDL:

IDL> double("4.5")
       4.5000000000000000
IDL> double("NaN")
                      NaN
IDL> double("nan")
                      NaN
IDL> double("nsn")
% Type conversion error: Unable to convert given STRING to Double.
% Detected at: $MAIN$          
       0.0000000000000000

[rweigel]

Action item:

Try https://hapi-server.org/servers/#server=CDAWeb&dataset=DMSP-F06_SSJ_PRECIPITATING-ELECTRONS-IONS&parameters=ELE_COUNTS_BKG&start=1987-01-01T00:00:00Z&stop=1987-01-03T00:00:00.000Z&return=script&format=idl

in clients.

[rweigel]

MATLAB

% Pass
data = textscan('1,2,NaN,3', '%f', 'Delimiter', ',');
assert(isnan(data{1}(3)));

% Pass
data = textscan('1,2,nan,3', '%f', 'Delimiter', ',');
assert(isnan(data{1}(3)));

% Pass
data = textscan('1,2,Nan,3', '%f', 'Delimiter', ',');
assert(isnan(data{1}(3)));

% Fail
data = textscan('1,2,"NaN",3', '%f', 'Delimiter', ',');
%assert(length(data{1})==4); % Fails, data{1} = [1, 2]

% Pass
data = textscan('1,2,"NaN","nan",3', '%f', 'Delimiter', ',', 'TreatAsEmpty', {'"NaN"', '"nan"'});
assert(isnan(data{1}(3)) && isnan(data{1}(4)));

[rweigel]

Julia

using Test
import Pkg
#Pkg.add("CSV")
using CSV

# Helper function to read CSV data from a string, returning a Vector
function read_csv_data(str)
    io = IOBuffer(str)
    tbl = CSV.File(io; header=false)
    # CSV.File returns a table with columns named Column1, Column2, ...
    # We want a vector of values from the first row
    row = first(tbl)
    return [row[i] for i in propertynames(row)]
end

# Pass: NaN
data = read_csv_data("1,2,NaN,3")
println(data)
@test data[3] === NaN || isnan(data[3])

# Pass: NaN
data = read_csv_data("1,2,nan,3")
println(data)
@test data[3] === NaN || isnan(data[3])

# Pass: NaN
data = read_csv_data("1,2,Nan,3")
println(data)
@test data[3] === NaN || isnan(data[3])

# Pass: "NaN"
data = read_csv_data("1,2,\"NaN\",3")
println(data)
@test data[3] === NaN || isnan(data[3])

# Pass: "nan"
data = read_csv_data("1,2,\"nan\",3")
println(data)
@test data[3] === NaN || isnan(data[3])

# Pass: "Nan"
data = read_csv_data("1,2,\"Nan\",3")
println(data)
@test data[3] === NaN || isnan(data[3])

[rweigel]

Check PySPEDA

[rweigel]

Jeremy would use a custom parser:

For line = "1,2,NaN,3", split line, then for each element Double.parseDouble("NaN") if quoted, would need to remove quotes.