edited by RUSSEL H. BAm Kenyon College Gambier. OH 43022
the computer bulletinboard Analysis of Kinetic Data with a Spreadsheet Program John Henderson Jackm Community College Jackson. Mi 49201 Commercial spreadsheet programs provide chemists with a powerful tool for performing calculations on sets of data (1, 2). When combined with graphics features, they are particularly suitable to the repetitive calculations and graphing encountered in kinetics experiments, especially those that involve multiple runs. I have created spreadsheet templates that accept concentration versus time data for the several runs of an experiment, determine the least-squares line through the data points for each run, graph the data together with the least-squares line, and allow the user t o exclude points from the leastsquares calculation. After the user is satisfied with the fit of the straight line to the data, the template will perform whatever other ullculations are necessary to compare the results from the different runs and present the comparison in tables and graphs. The templates contain all of the required equations and data other than the actual concentration versus time observations but can be modified to require students to supply other data such as conceutrations, infinity points, and extinction coefficients. Although they are designed for specific experiments, the templates are generic enough to allow them to he adapted to many kinetics experiments. Using these templates, together with command macros that allow a complex series of program commands to he executed with a single user command, frees students from having to learn the full use of the spreadsheet program. After a brief introduction to the function of a spreadsheet program, students can use the program to analyze their data by following a handout and learn something of the power of such a program without having to devote time to learning the program's complex command language. Instructors who are interested in having their students use spreadsheet programs to write their own procedures could 'use these templates as introductions and models. The templates are written for the oroeram SunerCalcSa. which runs on the X&le IIe. enhanced 11; and IIQs.I am in the process of rewrltmg the templates for MS1)OS SuperCalc and fur the Marmtosh program Excel. All of the experiments involve determining the rates of a series of pseudo-first-order reactions and eomparingthe rates to arrive
..
A150
Journal of Chemical Education
at s mechanistic conclusion. The rates of the reactions are determined by following the change in concentration of a reactant or product using a Spectronic 20 spectrophotometer. One experiment is the acid potassium dichromate oxidation of four alcohols in which the difference in the rate constants for the reactions can he interpreted in terms of the relative steric relief in the transition state (3). The rate constants are determined by the integration method: graphing the logarithm of the concentration against time. The second experiment deals with enzyme kinetics and comparison of the different graphic methods of determining V,, and K M (4). The rate constants are determined by the initisl rate method: graphing concentration against time far a very small initial portion of the reaction. The authors of the original experiment have several other enzyme kinetics experiments (5, 6 ) for which the templates could be readily adapted. The third experiment uses the iodination of acetone to investigate the order of the reaction in iodine and in acetone. to demonstrate the general base catalysis ofthe reaction (7) and to demonstrate a primary deuterium isotope effect (8). The rate constant is determined by following the decrease in the concentration of a species (iodine) whose concentration does not affect the reaction rate. Thus the graph of its concentration against time is linear for the entire reaction since the other reactants are in large excess. Two sets of templates are available. One of them accepts data as absorbance versus time and the other as percent transmittance versus time. My students use the Seraphim Voltage ADC interface (9) to obtain absorbance versus time data in computer-readable form for use in the first version of the templates. Since it is mare convenient to read the Spectronic 20 as percent transmittance, the second version is better if students are entering their data manually into the templates. Besides the templates and instructions for students on using them, I have prepared student handouts and instructor's notes for the three experiments. All of the text materials are provided as Appleworks word-processing documents for easy modification. My second-year organic chemistry students have successfully used these templates for data analysis in all three kinetics experiments with both ahsorhance data acquired using the interface and percent transmittance data acquired manually. Their comments have been incorporated into the supporting materials for the experiments. All these materials are available from Project Seraphim.
Acknowledgment I am indebted to the Universitv of Michigan Srhrxd of Education and especially to its Dean, Carl Berger, for the facilities and support, both financial and moral, provided to me.
Hardware and Software for Interfacing Voltage Output Instruments with Apple II David F. Jackson S c b l of Education University of Michigan Ann Arbor, Mi 48109 John Henderson' Jackson Community College Jackson. MI 49201 Carl Berger, Jr Vohax. Inc. Troy. MI 48083 Carl Berger School of Education University 01 Michigan Ann Arbor. MI 48109 John K. Estell University of Illinois at Urbana-Champaign Urbana, IL 61801 With the encouragement of Project Seraphim we have designed an inexpensive, easy to construct, analog-tdigital conversion (ADC)interface that allows interfacing with any instrument with analog voltage output of up to t5 V. Although the Apple I1 series of computers can convert analog changes in resistance to digital signals using the game port, this intrinsic ability does not permit direct interfacing with the wide variety of instruments such as spectrometers and pH meters that output their data in the form of analog changes in voltage. To interface with such instruments voltage analog-tdigital conversion is required. We have also written easily modifiable software to gather data through this interface a t set time intervals and save it in a form that can be processed by other programs. Specifically we have interfaced the Apple with a Spectronic 20 spectrophotometer that has a O- to t l - V analog output and written software that uses this interface to record time versus concentration data from
Amor m whom correspondence should be BddreSSBd.
chemical kineties experiments for use with a spreadsheet program. This hardware and software combination has been successfully student tested in a community college organic chemistry course (10).
Hardware T h e ADC converter is based on an ADC0804 chip, which converts input in the range 0 to +5 V to 8-bit integer values (O255). Physical connection to the Apple is through a 16-pin DIP jumper cable, which can he plugged into the internal game port of the I1+, IIe or IIcs. The external 9-pin game port connections of the Apple IIe, IIc, and IIGs cannot he used because the interface uses one of the lines that is missing on the 9-pin connector. The exterior of the interface box provides push-button terminals for voltage input, fou; resistance inputs that allow access to standard game port functions. and two +5V Dower and mound eonncctlons Thecost of the components is $2030 per interface boa, depending on the quantny and source of the nrmpments Most of the componentsaw avmlahle at Radm Shack stores Thp two chips can he glhtained by mail order or at larger electronics supply stores. Manv instruments. includine the Snectronic 20. senerate s fun-scale voltaee out-
-
range of resolution when interfacing with such instruments a way of matching the instrument output to the input range of the chip is needed. A variable resistor placed across the AGND and %V..r terminals of the interface box can he adiusted to nrovide a reference rdtage for the chip matching the full-scale output of the inrtrument. In our esse a 1-kR multiturn IC potentiometer, adjusted to approximately 300 o, provided the necessary 0 to +1V range required for maximum digital resolution of data from the Spectronic 20.
Software Although our immediate objective was to create software for use in three specific kinetics experiments to collect data for use in a specific spreadsheet program, we have kept the software as simple, general and flexible as possible to obtain compatibility with all models of Annle .. I1 series comnuters and to allow customization requiring only a minimal knowledge of Applesoft BASIC.
Sampling Sampling intervals can range from a second or less to several days. For dealing with a noisy signal it is possible to record the average of a series of readings to obtain a single data point. The interval between readings is controlled by a delay loop, so no internal clock is required. The user can start, stop, and modify the data gathering process from the keyboard.
Calibration Since accurate correspondence between the machine reading and the digital reading of the computer and between the real time and the time recorded by the computer are critical in obtaining accurate results, we (Continued on page A154) Volume 65
Number 6
June 1988
A151
the computer bulletin board
program, LOTUS 1-2-3. In addition to its value as a simulator, the method would seem to have value as a teachine tool for instruction of many aspects of multidimenaional chromatographic/spectroscopic comhinations. For example, in GC-MS theconcepts of spectral clean-up and mass chromatograms are easily demonstrated. Other combination methuds (LC-UV. GC-IR. LC-MS) would work equallv well, with almost identical spreadsheet ihstructions. As examnles..the eoneeots of absorbance ratioing (If), wavelength chromatography 112). and derivative qxrtroscopy (13) in ultraviolet spectroscopy or chemigrams (plow ofintegrated absorbance versus spectrum numher) (14) in GC-IR data can he readily illustrated with spreadsheet simulation.
-
have included several routines for calibration of the instrument reading to the digital value recorded by the computer and for calibration of the timing loops of the software on a specific computer to time measured by stopwatch. Timing accuracy is as good as manual stopwatch reading. Reading accuracy is very gwd, but falls off in the upper and lower 15%of the range. Reading precision is Limited by the instrinsic 1part in 256 precision of the ADC chip. This is prohahly about half the mecision nossible with careful manual read& of the~oectronic20 meter hut is better than the precision possible in reading the rapidly changing display commonly experienced in kinetics experimentti. ~~~
.
Spreadsheet Organization
~~
An IBM PC-XT with 640-K core memory was used for all work described. LOTUS 12-3 (version 2.0)was purchased from Lotus Development Corporation and was used without modification. The spreadsheet is organized as a large matrix, with the rows identified by numbers and columns identified by letters. The contents of each cell, or address. can be numbers such as data.. alphanumerir lal& or calculations. In our particular application, the organization is as diagrammed in Figure I. The first several columns are simulated chromatographic profiles of individual components as a function of time (Fiwre 1, columns B-E). Each row representssome arbitrary time inerement. Although any numher or shape for these profiles may be employed, we frequently used a simple Gaussian:
Modification Although the program is set up to interface with the Speetronic 20 and to convert the percent transmittance data to ahsorhance, it has provisions for adding other instruments by specifying the calibration points and data transformations they require. Other default values may he altered temnorarilv from a menu. The constants ha"; been Rrouped withrn one sectlon of the program to allow easy permanent modrfica-
Data Format Data can be saved as time versus reading pairs in Data Interchange Format (DIF), comma-separated-value text files, or files compatible with the data analysis programs Scientific Plotter and Curve Fitter.* One of these formats can be read by mast spreadsheet or other data manipulation programs. Our software and instructions for building the ADC Interface Box will he available through Project SERAPHIM. -
-
~
-
~
~
~
~
~
~ ~
~
~
~
~
graphy-Mass Spectrometry Experlment with a ~ommercial Spreadsheet Program Arnlt Ghosh David S. Morison and Robert J. Anderegg3 Univmily of Maine Orono. ME 04469
Commercial spreadsheet pmgrams are becoming increasingly valuahle in lalwratury calculations and aimulationv (11. In our recent work on data reduction for gas rhromatographic mas* spectrometry rGC-MS), it herame neuesrarv to rimularr a GC-MS experiment as a means of investigating, in a controlled way, the effects of chromatographic resolution, column bleed, noise, and background interference on the data manipulation. We developed the simulation using a commercially available spreadsheet Published by Interactive Microware, kc., State College. PA. 3Auihor to whOm canespondence should be addressed.
A154
Journal of Chemical Education
where Y;,~is the value of the ith component in the tth time interval. A; is a relative maximum peak height value, o. is related GO peak width, and p . gives peak position. Once the equations are entered, the relative intensity of peaks can be increased or decreased, or the peaks can be broadened or sharpened ar about relative to one another by ad. ~moved ~ juating only A,. n,. and p , , rerpectively. The sum of all these curves zives a "total ion profile" and represents tKe resulting chromatogram (Fig. 1, column G ) . The mass spectrum of each pure component is listed in a raw across the bottom of thespreadsheet (Fig. 1, rows 80-83). Masses are listed sequentially across the top (row 1); each cell entry corresponds to a relative abundance (normalized to 100) for the ion at that mass in the pure compound. For example, the mass spectrum of 1-chloro-nonane consists of eight ions with masses (and normalized relative ahundances): mlz 41 (40% 43 (60%). 55 (45%),etc. This information is coded into Figure 1,Row 81. The balance of the spreadsheet is the data array that would result if four compounds were eluted, with the profiles indicated in columns B-E, from a GC and were directed into a repetitively scanning MS. Each row (Fig. 1, rows 2-77) represents a mass spectrum from one scan of the mass range. The relative abundance of each mass is obtained by summing the contrihutions to that mass from each compound. These contrihutions are computed by multiplying the abundance of the ion of interest in each of the pure
..
-
spectra (Fig.1, rows -3) by an inteusity term defmed by the wrresponding chromatographic profde (columns EE). The calcuiation is shown a t the bottom of F i e 1.The calculation is repeated for every rnh value a t each time point. Thin array is central to understanding the nature of GGMS data, since it illustrates the three-dimen-
sionality of a chromatographic spectmscopic experiment: the spectral resolution elements span each mw (rnlz, in this case), the time axis is represented as a descent down a column, and the spectral intensity is cammunicated by the numbers in the cells. Slicing the data array horizontally corresponds to sampling the mass spectrum a t some
point in time. If the chromatographic profiles of components overlap, the spectrum will represent amixhve of the two (or more) pure compounds, exactly as it would in a repetitively scanned spectrometer with scan time equal to the time inuement of one row. Slicing the data m a y vertically corresponds to ohservine a eiven rnlz as a function of time. In &MS. we would call this display a mass chromatogram (15).
Illustration Several features of the G G M S simulation can he illustrated with a simple example. Four components are involved, with chromatographic profdes indicated in Figure 1.These might wnespond to three anal y h and an increasing column hleed. The Grst three are variations of the Gaussian equation (eq 1) to provide different peak heights, widths, and positions by adjustment of Ai, oi, and pias described above. The column hleed is a simple exponential of the form:
y=IP
(2)
where I and n adjust the rate of rise of the curve. When these four profiles are summed, the total ion profile in Figure 1, column G results. To examine the mass spectrum at any point in thin chromatogram, one can simply graph the appropriate mw at the desired time internal. Flgue 1. Spreadsheet organization. lh8 numbers appeerlng In me may J2 m 277 repsent relatlve ian abundances.
Volume 65
(Continued on page A156)
Number 6
June 1988
A155
the computer bulletin board One exercise that can be readily demonstrated using the spreadsheat is the cleanup of maas spectra hy spectral or backgroundsubtraction. Toaooomplish this with the spreadsheeh one simply subtracts the cellentries of one row fromthose of another. In our spreadsheet, we set aside one row (Fig. 1,row 78) toreceivethis difference. We only need to specify which two spectra we would like to subtract (by row numbers) and row 18 is updated, and the corresponding graph can be viewed. In the example of Figure 1. the two comwnents elutine around A spec&m from s&n~11-28overla~badly. scan 22 results from a mixture of the two components (Fig. 2a). However, if we take a spectrum early in the peak (scan l l ) , which is predominantly from the first-eluting component, and subtract the spectrum from late in the peak (scan 2 8 , predominantly from the later-eluting material, we can get a relatively clean spectrum of compound 1 (Fig. 2b) that clmly matehes the spectrum of the pure material. Similarly,reversingthe order of subtraction (scan 28 - wan 17) provides a clean spectrum of compound 2 (Fig. 2c). In exactly the same way, the spectrum of the analyte tbat elutes on the rising column bleed can he obtained bv suhtractin.. for exam~le.acan 14 and s& 61. The o z y entries t h t need to be changed are the two scan numbers; the spreadsheet automatically performs the new subtraction and updates row 18.
Students often have difficulty understanding the idea and utility of a mass chromatogram. Normally, this is a data display in which the computer plots the intensity of a requested m/z value ss a function of time. The idea is readily grasped with the aid of the spreadsheet, however. The mass chromatogram is simply a slice vertically thmugh the data array; a graph of any one column. Figure 3 shorn the mass chromatogram of m/z 41 (Fig. 3 4 and the total ion profile (Fig. 3h). It is easy to see tbat analytes 2 and 3 have mlz 41, while analyk l and the "column bleed" do not. One can see how this form of selective data reduction helps to resalve overlapping peaks, decrease background, and improve the signal-tonoise ratio hv reducine chemical noise. Just as imoortani however. is notinn the difference 6etween's masscllomat&am and the result of a "single-ion monitoring" experiment. In theexperiment we have been simulating, the entire maas range is recorded at set time inte~als.A mass chromatogram is a selective display of a fraction of the collected data. In a single-ion monitoring exneriment. onlv one m h is recorded throuehbut the ektir; exmriment.. that is.. the data are collected selectively. A graph of that data set would look like Figure 3a but would represent the entire collected data set. Each type of experiment has its own advantages and disadvantages (16). ~~~~~
~~
.~
To simulate more complicated (and more realistic) chromatograms, one could increase the complexity of the profiles in columns B E (Fig. 1) to provide peaks that front or tail, small analyte peaks on the tail of a Large solvent peak, arelatively constant, hut high background, or random spectral noise. One would rarely need more than three or four profdes to illustrate any given situation;and the rapid spreadsheet, updating allows one to change profiles and peak positions with ease. In conclusion, commercial spreadsheet software can provide a versatile and powerful means of simulating a data set for GCMS or other chromatography~pectrosropy combinations. With relatively few commands, a variety of realistic chromatographic situations can he approximated. The spreadsheet helps to conceptualizethe multidimensionality of the data set and aids in the illustration of spectral dean-up and mass chromatograms.
Llteratwe Cited I. Lcvkav.J.S. J. Chrm. Edvr I987.M. JI rd,< 1 9 8 7 . ~ 1 3 7 2 ca.n A J :I. M-0n.T. J . . T w U .J A . Weal.C W. J. Chem Edvr
r h
~~~
5. Hmlbut,J.A.:Biahop,C. V.:Brit&in.P.C.:Pteheim, C. W. J. Chms. Educ 1975.52. la). 6 Hurlbut,J.A.;&111,T.N.;Pound,H.C.;Gcaves,J.L.J. Chern. Educ. 1913.50,149. 7. Waddin@m. M. D.; Meany, J. E. J. Chom. Edw. 1978, cc -. m "",
8. HadJ. J. Ckm. Edue.. in pras. 9. Jackson. D. F.: Hendamn. J.; Llerger, C., Jr.: Berger, C.; Eatcll, J. K. J. Chom.Edue..thef0Ulrvingpap.r. 10. Haderaon, J. J. Chem.Edue. pmeedinb paper 11. Yoat, R.: Stoveken,d.: Machao. W. J. Chmmfogr. 1111.,-.., ,.?A 1... M 2 . -..~
12. Denton. M. S.; DeAngeiia, T. P.; Yaeynych. A. M.: Heinernan. W. R: Gilberf T. W. Anal. Chem. 1976, 48, %24. 13. Meal.L.Am1. Chom. 1 9 8 6 . 5 8 . M 6 . 14. Mattson,D.R.;Julian,R.L. J.Chramofogr.Sei. 1979, 17,41@422. 15. Hitea. R. A.; Biemnon. K. A d . Chern. l970,42,2.855-
m.
16. And-,
Figve 3. (a)Maas drwnatcgam of m h 4 l and (b) t h l ion profile of simulated data set of Figure 1.
F l w 2. Mass specba of (a)scan 22, (b) scan 17 -scan 28, and (c)scan 28 scan 17 h o m simulated data set of F l g v e 1.
-
A156
Journal of Chemical Education
The simplest, but also most tedious, method of entering the data for the pure spectra into the spreadsheet is by manually typing the abundance values for each cell. Information can also he entered using the IMPORT command from a data fde elsewhere. In our work, we have interfaced the PC directly to the datasystem of a HewlettPackard 5985 B GC-MS system and can dump spectra directly from data files or spectral libraries to the PC and from there into the spreadsheet.
R. J. Am. Lob. 1985,17(9), W .
