Data Logging

Manipulating science data logs for manual processing with Excel or Numbers

High schools today make use of data loggers to acquire data from experiments. However, all to often the amount of data and format of the data collected is not well suited to further manual processing using spreadsheet applications.

Two ways to achieve a better result are:

• decimate the data log by evenly removing most of the data items. This provides us with a more manageable list of items. Decimation literally means retain 1 in 10 but we can make it configurable.
• provide items with a time stamp, since data loggers generally insert a header indicating the unit of time interval, and a count of that time interval, generally in seconds.

The following AppleScript provides a user with the ability to decimate a comma separated variable (.csv) formatted text file as well as prepend a field that converts a serial value into a hh:mm:ss format to make it easier to work with. Most data loggers can export a data log as a .csv file.

After the data file has been reformatted it can be directly dumped into either Excel or Numbers read for display. The main feature here is building the time formatted field from a serial number field. The end of line (eol) marker is for a Macintosh. If you want to make the resulting text file more PC friendly use ' property eol: ASCII character 13 & ASCII character 10'.

-- Data log file decimator and reformatter for a Datalogger
-- first worked on 2010-12-20, last worked on 2010-12-22, use Mac eol marker
property eol : ASCII character 10
property decimator : 10
property addHMS : false
set newfilePath to choose file with prompt "Data log source..." default location (path to desktop)
set fileRef to open for access newfilePath -- open the file
set {sec_cont, data_log, default_TID} to {0, "", AppleScript's text item delimiters}
set {time_hours, time_minutes, time_seconds} to {0, 0, 0}
set prepend to display dialog "Prepend time as HH;MM:SS to the data..." buttons {"Yes", "No"} default button 2
if (button returned of prepend) is "Yes" then set addHMS to true
set decimator to display dialog "Retain 1 item in every ..." default answer decimator buttons {"OK"} with icon caution
set decimator to text returned of result as integer

try -- to read the header line and push to output file
	set next_data to read fileRef until eol
	set data_log to next_data & eol
	if addHMS then set data_log to "Time(hh:mm:ss)" & "," & data_log
	set next_data to read fileRef until eol
	repeat -- until error or end of file 
		-- read scrub lines and decimate (keep 1 in 10)
		set scrub to decimator
		repeat until scrub is 0
			set next_data to read fileRef until eol
			set scrub to scrub - 1
		end repeat
		if addHMS then
			-- split to read each csv item
			set AppleScript's text item delimiters to {","}
			set sec_count to (text item 1 of next_data)
			-- reset TID as it is used in read/write context
			set AppleScript's text item delimiters to default_TID
			set time_seconds to characters -2 thru -1 of ("00" & (sec_count mod 60)) as string
			set time_minutes to characters -2 thru -1 of ("00" & (sec_count div 60)) as string
			set time_hours to characters -2 thru -1 of ("00" & (sec_count div 3600)) as string
			set the_time to (time_hours & ":" & time_minutes & ":" & time_seconds)
			set next_data to the_time & "," & next_data -- prepend
		end if
		set data_log to data_log & next_data
	end repeat
end try
close access fileRef
set newfilePath to choose file name with prompt ¬
	"Write to file..." default name "Decimated data log.cvs" default location (path to desktop)
set fileRef to (open for access newfilePath with write permission)
write data_log to fileRef
close access fileRef

Digital Storage Oscilloscope for high speed, short time interval data logging in schools

Digital Storage Oscilloscopes(DSO) are becoming relatively inexpensive compared to similarly equipped Analogue Cathode Ray Oscilloscopes(CRO). Many brands use firmware which is very similar and includes the ability to produce data logs compliant with comma separated variable) .csv format.

Although not able to replace dataloggers in schools for acquiring data over long intervals, they can be used to acquire digitised data where the more common data loggers have severe limitations:

• very short intervals, high speed transients - down to microseconds
• repetitive signals
• high voltage devices (using a x 10 probe or x 100)
• where custom digital filtering is desired during data acquisition

While there are many useful functions, such as frequency filtering, and realtime fast Fourier transform (frequency spectrum) display there exists the possibility for the DSO to record a data log and store it on a USB flash drive. Then the data log can be parsed (it's relatively simple process) on a Macintosh computer with resulting data list then used in calculations or displayed by a spreadsheet application such as Numbers or Excel.

While it would be easy to dump the raw xyzxyz.csv file into a spreadsheet manager, the header information is a bit messy to handle and would require some manual manipulation to provide a data log suitable for converting into a graph.

The AppleScript DSO file parser (based on the file format from a Digitech QC1932 from Jaycar Electronics) below demonstrates just how simple it is to extract out useful information for further processing. This initial version of the DSO file parser only extracts the salient values.

This DSO file pre-processor could be used in front of slabs of code that do one or more of

• rewrite a text file suitable for dumping in Excel for further processing or graphical display
• decimate the file data to make it more easily manually managed by students
• automatically open Excel or Numbers and automatically constructs an appropriate graph
• process the data to extract out salient features such as discontinuities, trends, patterns
• extract model frequencies using fast Fourier transforms
• other useful data processing (joining files, comparing files (correlation).

The main points here are:

• DSOs are realistically now a valuable and affordable resource for High Schools.
• DSOs can record digital data logs to analyse high speed events
• Inexpensive DSOs are almost as good as Analogue CROs in some aspects, and far superior in others ways -- Analogue CROs have a theoretical quantum sample error (0.00?%) way below a 10-bit (0.1%) or 8-bit(0.5%) digital sample.

-- Vers 0.1, DSO file Parser for Digital Storage Oscilloscope (Digitech QC1932)
-- I W Parker First worked on: 2010-11-16 Last worked on 2011-06-29
-- xxxxxxxx.CSV data capture file
-- file must be (re)-named "TestFile-01.csv" and stored on desktop
-- currently only parses file containing just Channel 1 data
-- requires Satimage OSAX to be installed
--product is a list of data.

property eol : (ASCII character 13) & (ASCII character 10) -- PC text line format
set {data_list, data_format} to {{}, "##.#####"} -- 5 decimal places
set current_line to ""

-- alter to allow for file navigator
set folder_loc to path to desktop as string
set data_file to open for access (folder_loc & "TestFile-01.csv")

-- -------------------START OF FILE HEADER ------------------------------
-- Line 1 := <file_lines> (not needed), <data_source> (CH1)
set current_line to read data_file before eol using delimiter ","
set {file_lines, data_source} to {item 2 of current_line, item 5 of current_line}

--Line 2 := <sample_interval>, <sample_X>,<sample_Y>
set current_line to read data_file before eol using delimiter ","
set sample_interval to item 2 of current_line
set {sample_X, sample_Y} to {item 4 of current_line, item 5 of current_line}

--Line 3 := <sample_trigger>
-- begin data listing
set current_line to read data_file before eol using delimiter ","
set sample_trigger to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 4 := <vertical_units_dim>
set current_line to read data_file before eol using delimiter ","
set vertical_units_dim to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 5 := <vertical_scale_dim>
set current_line to read data_file before eol using delimiter ","
set vertical_scale_dim to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 6 := <vertical_offset>
set current_line to read data_file before eol using delimiter ","
set vertical_offset to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 7 := <horizontal_units>
set current_line to read data_file before eol using delimiter ","
set horizontal_units to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 8 := <horizontal_scale>
set current_line to read data_file before eol using delimiter ","
set horizontal_scale to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

--line 9 :=<DSO_model>
set current_line to read data_file before eol using delimiter ","
set DSO_model to item 2 of current_line
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

-- lines 10,11, only {X,Y} useful
set current_line to read data_file before eol using delimiter ","
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}
--
set current_line to read data_file before eol using delimiter ","
set {data_x, data_Y} to items 4 thru 5 of current_line
set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
set data_list to data_list & {{data_x, data_Y}}

-- ------------------END OF HEADER ------------------------------

-- accumulate {X,Y} data pairs to the end of the file (could be up to 4000 lines!)
repeat
	try
		set current_line to read data_file before eol using delimiter ","
		set {data_x, data_Y} to items 4 thru 5 of current_line
		set {data_x, data_Y} to {format data_x into data_format, format data_Y into data_format}
		set data_list to data_list & {{data_x, data_Y}}
	on error
		exit repeat -- detect error or eof then exit
	end try
end repeat
close data_file
-- result is data list {{X1,Y1},{X2,Y2},...}

Simple Data Logging using an Arduino Compatible TwentyTen

The Australian produced Freetronics TwentyTen version of the Arduino is a nice implementation. And it's relatively inexpensive. Here is a practical idea to begin to use it for data logging especially in a classroom.

You will need:

• 1 x Freetronics TwentyTen
• 1 x MiniUSB to USB cable (comes with the TwentyTen)
• 1 x Installed version of the Arduino Integrated Development Environment

• 1. Install Arduino IDE (currently up to version 022)
• 2. Connect the TwentyTen to an unused USB port
• on my machine it appear as device "/dev/cu.usbserial-A6005vp6"
• 3. Start the Arduino IDE
• 4. Copy the Arduino "sketch" (Arduino's name for C program) below into the Arduino IDE

//
// Ian W. Parker , first worked on 2011-04-23, last worked on 2011-07-17
// very simple design for a datalogger using ADC port 0 on Arduino
// _____________________________________________________________________
//                                 manifests
// _____________________________________________________________________
    const int a_input = A0;           // full range (10 bit: 0 to 1023)
    const int numread = 5;            // size of vector of readings
    const int numread_max = 10;       // max size of vector of readings
    const int log_count_max = 100;    // restrict log to send 100 items
    int log_count_delay = 100;        // ms delay between measurements
    int log_count_index = 0;          // logger data item counter
    int readings[numread_max];        // vector of readings
    int index = 0;                    // index of current reading
    int total = 0;                    // running total
    int average = 0;                  // average
//  _____________________ ONE TIME SETUP _______________________________
    void setup() {
// initialise digital pin 13 as an output.
        pinMode(13,OUTPUT);		  // LED  on Freetronics board
// set up serial interface to USB
          Serial.begin(9600);
// output header to the system
          Serial.println(" Logger Vers. 0.1");
          Serial.println("(c) 2011 Ian Parker \n");
   // clear reading vector
          for (index = 0; index < numread; index++)
            readings[index] = 0;
}
    void loop() { //______________ LOOP _______________________
 // accumulate numread readings
         average = accumulate_data(); delay(log_count_delay);
         if (log_count_index < log_count_max) { // limit data emitted
             log_count_index += 1; data_to_serial();
     }
}
// _________________________ accumulate data _____________________
    int accumulate_data() {
        digitalWrite(13, HIGH);    // set LED ON- start reading
       total = 0;
       for (index = 0; index < numread; index++) {
            readings[index] = analogRead(a_input); total += readings[index];
       }
      digitalWrite(13, LOW);    	// set LED OFF- finished reading
// do average over values
      return (total/numread);
}
// ___________________ Serial Port support ________________________
    void data_to_serial() {
        // all the formatting needed to send a csv line to the serial port
         Serial.print(log_count_index,DEC); // decimal integer format
         Serial.print(",");
         Serial.println(average, DEC);  // eol (cr-lf) appended automatically
         }

• 6. Upload the sketch to your connected Arduino- this will pre-compile, then send in seconds.
• 7. Ensure SerialPort X OSAX is installed in your Mac so you can use the SerialPort instructions.
• 8. Copy the AppleScript below to the AppleScript Editor. This AppleScript receives the data via the USB cable.
• 9. Run the AppleScript. You can see what has been processed in the Events, Replies, Result sub-pane.
• 10. Think of some good uses for this.

-- Ian W. Parker first worked on: 2011-04-23, last worked on: 2011-07-17
-- AppleScript to communication with Arduino compatible "TwentyTen"
-- requires SerialPort X OSAX, and will take about 15 s to execute
-- Unlike using the Serial Monitor in Arduino IDE the data is
-- accumulated in a csv string called Readings before display.
-- You only get to see the contents when the logging's done.

property arduinoPort : ""
set {bufferCount, Readings} to {0, ""}
--
set arduinoPort to connect()
try -- to obtain data from the serial port
	delay 20 -- = 100 readings ± 1 of number of readings (approx due to timing errors)
	set oneReading to serialport read arduinoPort
	-- accumulate readings
	set Readings to Readings & oneReading
	-- normal close the port
	serialport close arduinoPort
on error -- close the port
	serialport close
end try
say "100 items were recorded"
Readings

-- ----------------------------------------------------------------------
-- ----- connect to TWENTYTEN Arduino Device ----------------
-- ----------------------------------------------------------------------
on connect()
	set arduinoPort to serialport list
	-- this may not be correct for your system
	if arduinoPort begins with "/dev/cu.usbserial-A6005vp6" then -- we see a "TwentyTen"
		say "connected to Twenty Ten"
	else
		say "Error-not connected"
	end if
	set arduinoPort to serialport open first item in arduinoPort
	if arduinoPort = -1 then
		say " cannot communicate with Twenty Ten"
	else
		-- startup on buffer not empty
		repeat until (serialport bytes available arduinoPort)
		end repeat
		say "starting data logging, please wait"
		return arduinoPort
	end if
end connect

We have a truly asynchronous link here- really, two independent computers "talking". And if you got this far you would have heard your Mac tell you what it's doing!

• If you can't establish the link, try pulling the USB then reconnecting. This will power-on reset the Arduino and it will send more data.

To make this very short I've not considered what to do if things don't work but I'm sure by now you will have seen the potential of this especially in science lessons.