CODEWITHSUNDEEP
csvspreadsheetnetlogo
goodgest
goodgest

Reputation: 418

netlogo: no " " in csv spreadsheet since NetLogo 6.0.3

I want to use the syntax to substitute "#N/A" instead of the calculated value 0, but "" is not displayed in the csv file in NetLogo 6.0.3 (This is displayed ⇒ #N/A. I want to calculate the average value by mixing "#N/A" with numerical data in Excel, but #N/A is displayed as calculation result. If "#N/A" is displayed as a csv file, it could be calculated with Excel. In NetLogo 6.0.1, this was possible. What should I do with NetLogo 6.0.3?

Upvotes: 0

Views: 130

Answers (2)

Bryan Head
Bryan Head

Reputation: 12580

The "correct" way to do this is to handle it in excel by ignoring N/As in your average. That way, you preserve those values as N/As and so have to be conscious about how you deal with them. You can do this by calculating the average with something like =AVERAGE(IF(ISNUMBER(A2:A5), A2:A5)) and then entering with ctrl+shift+enter instead of just enter. That, of course, is kind of annoying.

To solve it on the netlogo side, report the value "\"#N/A\"" instead of "#N/A". That will preserve the quotes when you import into excel. Alternatively, you could output pretty much any other string other than "#N/A". For instance, reporting "not-a-number" would make it a string, or even just using an empty string. The quotes you see in excel are actually part of the string, not just indicators that the field is a string. In general, fields in CSV don't have a type. Excel just interprets what it can as a number. It treats the exact field of #N/A as special, so modifying it in any way (not just adding quotes around it) will prevent it from interpreting in that special way.

It's also worth noting that this was a bug in previous versions of NetLogo (I'm assuming you're using BehaviorSpace here; the CSV extension has always worked this way). There was no way to output a string without having a quote at the beginning and end of the string. That is, the string value itself would have quotes in it. This behavior is a consequence of fixing it. Now, you can output true #N/A values if you want to, which there was no way of doing before.

Upvotes: 3

Luke C
Luke C

Reputation: 10316

Maybe this will work for you. Assuming you have the csv extension enabled:

extensions [ csv ]

You can use a reporter that replaces 0 values in a list (or list of lists) with the string value "#NA" (or "N/A" if you want, but for me #NA is what works with Excel). 

to-report replace-zeroes [ list_ ]
  if list_ = [] [ report [] ]
  let out map [ i ->
    ifelse-value is-list? i 
    [ replace-zeroes i ]
    [ ifelse-value ( i != 0 ) [ i ] [ "#NA" ] ]
  ] list_
  report out
end

As a quick check:

to test
  ca  
  ; make fake list of lists for csv output
  let fake n-values 3 [ i -> n-values 5 [ random 4 ] ]

  ; replace the 0 values with the NA values
  let replaced replace-zeroes fake

  ; print both the base and 0-replaced lists
  print fake
  print replaced

  ; export to csv
  csv:to-file "replaced_out.csv" replaced
  reset-ticks
end

Observer output (random):

[[0 0 2 2 0] [3 0 0 3 0] [2 3 2 3 1]]
[[#NA #NA 2 2 #NA] [3 #NA #NA 3 #NA] [2 3 2 3 1]]

Excel output:

enter image description here

Upvotes: 2

Related Questions