exceldatatableexportnetlogobehaviorspace

NetLogo: Exporting table results into CSV


The objective of this model is to explore potential dispersal patterns of grey wolves in the Northern Rocky Mountains. In the model, grey wolves are given a ph-memory attribute which corresponds to a spatial data table.

extensions [ gis table csv]

wolves-own [
  ...
  ph-memory ;; wolves' patch-hunting memory, table includes the patch's hash-id (KEY) and pack-id (VALUE)
  ...   
]

to initialize-wolf  [  new-pack-id  ]
    ...
    set ph-memory     table:make
    ...
end

to setup
  clear-all
  setup-gis

  file-open (word "Ph-memory-for-run-" behaviorspace-run-number ".csv")
  ...
end

to go
  if not any? wolves [stop]
  ask wolves [    
    where-am-i
  ...
  file-write (table:to-list ph-memory)
end

to where-am-i
 let patch-hash-id ([hash-id] of patch-here)       ;;Updates the hash-id of the patch the wolf is currently on
    if not table:has-key? ph-memory patch-hash-id
    [table:put ph-memory patch-hash-id pack-id]             
end

When I open the Excel file to view the results, the entire table is exported into a SINGLE cell. Unfortunately, this makes data analysis moot since I cannot manipulate the data easily.

excel output

My question is: Is it possible to export the data table results into excel and have the data broken into individual cells/ discrete data pairs (e.g. [ patch-hash-id, pack-id] )? I started to manually re-format the data into columns, but this is extremely tedious!

ideal results

Does someone have a recommendation of how I can export the data in an effective manner?

Any help would be greatly appreciated!


Solution

  • Two problems here. file-write doesn't put a carriage return at the end of its output, so successive file-writes string everything on one long line. Also, Excel expects a CSV file, one where the values on each line are separated by commas, and table:to-list generates a list of lists of id/value pairs, but does not separate the values with commas. The CSV extension does that nicely with csv:to-string, and file-print provides the carriage returns. The following code should show how it all fits together.

    extensions [table csv]
    globals [ph-memory]
    
    to setup
      clear-all
      set ph-memory table:from-list [[1 2] [3 4] [5 6]]
      reset-ticks
    end
    
    to go
      file-open "c:/users/cstaelin/desktop/testfile.csv"
      file-print csv:to-string table:to-list ph-memory
      file-close
    end
    

    After 4 ticks the csv file looks like

    1,2
    3,4
    5,6
    1,2
    3,4
    5,6
    1,2
    3,4
    5,6
    1,2
    3,4
    5,6
    

    and Excel opens it properly.