Dear los111,
Since you're already using TableCurve, you can take advantage of its
built-in production facility to calculate the Xmax value for many data
sets at once.
First, your data must be arranged in an Excel spreadsheet in columns,
with X and Y values in alternating rows. In other words, X values for the
first data set should be in column A, and Y values for the first data set
in column B; then X values for the second data set in column C, with Y
values for the second data set in column D; and so forth. If the X values
are identical for every data set, you can list them just once in column A,
then put the Y values for each data set in columns B, C, and so on.
If your spreadsheet cannot be easily rearranged in one of these formats,
show me the layout in a Clarification Request -- either by posting a
fragment of the spreadsheet or by linking to the entire file -- and I'll
see if I can give you a Python script to automatically rearrange the data.
I have uploaded three data sets of my own to the following location,
to assist you in following my walkthrough of the procedure.
Sample Data
http://plg.uwaterloo.ca/~mlaszlo/answers/sample_data.xls
Once your spreadsheet is ready, start TableCurve and select
"Import..." from the File menu. A window will appear with a list of the
columns in your spreadsheet. Select column A and click on "X Values",
then select column B and click on "Y Values". Click "OK" to continue. In
the next window, you can choose names for the data set and variables,
or just click "OK" to accept the defaults and continue.
Now generate your fit curve as usual. I used only the plain Gaussian curve
among the peak equations, with 500 maximum iterations and 6 significant
digits. Once you've configured your curve, click "Fit" and then, in
the next window, click the "Graph Start" button. Even if you perform
additional configuration, you must go to the Graph Start window -- you
can get there from the main window by selecting the first entry under
the Review menu -- in order to generate the report.
Once you're happy with the curve and you're at the Graph Start window,
look at the lowermost row of buttons at the left side of the window. The
rightmost of these buttons, which says "Production Facility" when the
cursor hovers over it, is the one you want. Click it, then select the
"Excel 95/97/200" radio button, which is second from the top. Click the
"Select" button immediately below it to choose the Excel file you imported
earlier, then select "First worksheet", assuming that your data is indeed
on the top layer of the spreadsheet. Otherwise, select the appropriate
layer. Then select either the "XY Pairs" or "Single X" radio button,
depending on which format you employed in your spreadsheet. I used XY
Pairs in mine because the X values are different for each data set.
In the "Output" pane, look at the checkboxes listed under "Write Report"
and make sure only the one labeled "Numeric Summary" is checked. Uncheck
all the others. You can then deselect the "Write Report" checkbox. Now
check the "Write Processed Data to Excel File" checkbox and click the
"Select" button below it to choose a name and location for the output
file. For "Sets Per Sheet", select 1, and for "XLS Format", select
95. You should avoid the 97/2000 output format because it is flawed.
Once you've clicked OK, TableCurve processes all the data sets and writes
the results to the specified output file, which you can open in Excel
once the processing is done. You will see that the numerical summary
for each data set is written to its own layer.
The Xmax value will be in cell B5 of each layer. This is because the
maximum value of a Gaussian curve is also its mean value, and the mean
is given by parameter c. In the spreadsheet, this is the third of the
parameter values in the second column, and should therefore fall in cell
B5 if you faithfully followed every step. The output resulting from my
own data is at the following location.
Sample Summaries
http://plg.uwaterloo.ca/~mlaszlo/answers/sample_summaries.xls
If you run into any difficulties with the procedure I outline above,
please advise me through a Clarification Request so that I can make sure
your needs are fully met. Please do not rate this answer until you have
succeeded in carrying out the entire procedure and arrived at the results
you desire.
Regards,
leapinglizard
Search strategy:
mean maximum gaussian distribution
://www.google.com/search?hs=sMN&hl=en&lr=&client=firefox-a&rls=org.mozilla%3Aen-US%3Aofficial&q=mean+maximum+gaussian+distribution&btnG=Search |
Request for Answer Clarification by
los111-ga
on
29 May 2006 09:36 PDT
Dear leapinglizard-ga,
No, that's not the answer I want.
I clearly said that I didn't have a problem calculating it for one for
few sets of numbers. I do not understand why you explain the process I
know step by step.
My problem is how to do 5000 sets in a first speed. I have 5000 Excel
sheets and just opening 5000 takes more than a few hours.
|
Request for Answer Clarification by
los111-ga
on
29 May 2006 10:03 PDT
Dear leapinglizard-ga,
Please read my question. I CLEARLY said this:
"I can do this easily by off-the-shelf statistic program, Sigma Plot
Table Curve, but doing so one by one is way too slow. I need to do in
bulk (thousands of them) quickly."
I have no problem doing it. I know how to use Sigma Plot or Table Curve.
And you aswered the process I already know in a detail.
But you never answered to my question, how to do it quickly if you
have 5000 sets of numbers.
And remember, my first request is if you can write a problem for this
task. You never answred to that request either.
|
Clarification of Answer by
leapinglizard-ga
on
29 May 2006 10:28 PDT
All of your data sets can be processed in one shot if they are present
in the same Excel file, since the Production Facility explicitly
provides for multiple data sets. I can easily write you a Python
script that unifies the separate Excel files into a single file. All I
need to know is the format of the individual files. If you could
describe this format, or, better yet, link to a few of these files so
that I can examine them myself, I will provide the unification script
in short order.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
30 May 2006 12:28 PDT
Thank you for your quick reply. If you can write a Python prgram, that
may solve the problem (but I need the original code too.) All my excel
sheet has X numbers in the first column, Y numbers in the second
column. Some has only 10 rows, some has 30-50 rows. I assume that
doing 50 sheets or 500 or 5000 sheets requires a same procedure. All I
need it the automated program. I need a final result in one excel
sheet, showing the list of Xmax, like (sheet 1, Xmax = 3.4), (Sheet 2,
X max = 4.56)....(Sheet 500, X max = 4.33) etc. I will try to upload
Excel file in somewhere so that you can see an example. I will add
more comments later to clarify the situation.
|
Request for Answer Clarification by
los111-ga
on
30 May 2006 13:13 PDT
Another problem is that your version ot Table Curve can be older than
mine. I use the newest version v5.01.02, you can get 30days trial
version (full functional) for free.
I pasted the screenshot of my table curve here.
http://img144.imageshack.us/img144/7885/tablecurve6so.gif
http://img330.imageshack.us/img330/7752/table24pz.gif
I can't find "Production facility" you mentioned. I think what you
meant was "Automated Processing"? I tried it, but it didn't work
either. OK button disappeared, and I can't click it.
|
Request for Answer Clarification by
los111-ga
on
30 May 2006 13:58 PDT
I think I figured out the "Production Facility" part. In a new version
of TableCurve, it's called "Automated Processing".
But there is a problem again. You told me that I find the Xmax value
in B5 cell in each sheet, but then I have to collect the number in B5
cells, by cut and paste from 5000 sheets, which takes probably 12
hours.
This problem cannot be solved unless you write a simple python program
(or any program) which can collect value in B5 cells from all sheets,
and arrange only Xmax value in a sheet.
By the way, I filed the complain to Google Answer yesterday before I
saw your clarification, because your first answer didn't solve the
problem at all. But as long as you can solve this simple problem by
two python programs (one combine all sheets into one Excel sheet,
another collects B5 from all sheets into one sheets), I will withdraw
it.
In addition, if you have a capability to write this type of program, I
am willing to do more business with you.
I am not going to be unreasonalbe, if 5000 sheets are too many, I can
do ten times for 500 sheets.
|
Request for Answer Clarification by
los111-ga
on
30 May 2006 17:12 PDT
Again, just wanted to clarify this. Auto-processing function of Table
Curve itself doesn't solve the problem, because I have already known
the "Batch Process Excel" function and "Regression Wizard" function in
Sigma Plot, which allowed me to open all Excel files. But batch
process function still creates 5000 excel files, and I have to go
through all sheets, which takes probably 15 hours. I need all Xmax
value in one sheet. That's what I need, and I think you can solve it
easily by Python program. Thank you.
|
Clarification of Answer by
leapinglizard-ga
on
30 May 2006 18:06 PDT
Let me get this straight. Are you saying that you can already run a
batch job on all 5000 data sets? So the only remaining problem is to
extract the Xmax value from each result file? And Xmax is always in
cell B5 in each of the 5000 result files?
If the answer to all three of these questions is yes, then I can
indeed write a Python script to assemble the Xmax values into a single
file. I presume a CSV (comma-separated value) file would be fine?
Excel can open CSV files without any trouble. I imagine it would be
best to have the Xmax values in column A and the corresponding file
names in column B.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
31 May 2006 08:25 PDT
Dear leapinglizard-ga,
First of all, I think i should apologize to 1 star rating thing.
Some other explained the whole situation, and I put this comment to
answer his question.
When I saw that my order was "CLOSED" while I didn't get my real
answer, and I was charged $200 on 05/05/2006 while I never received
any notification in my gmail, and Google didn't have any phone line,
and Google Answer customer service never replied to my complain, I
felt that it was like a fraud, and that's why I gave a 1 big star, as
a way to show my dissatisfaction. But After reading Clarification from
this expert, I started to think that this expert himself is doing his
best and the poor service is due to Google Answer itself.
|
Request for Answer Clarification by
los111-ga
on
31 May 2006 08:35 PDT
Let me get this straight. Are you saying that you can already run a
batch job on all 5000 data sets? So the only remaining problem is to
extract the Xmax value from each result file? And Xmax is always in
cell B5 in each of the 5000 result files?
>>>>This Batch function of Sigma Plot produces a whole report in the
word file, not excel. So, I can't say it's in "B5 cell".
CSV is absolutely fine.
I imagine it would be
best to have the Xmax values in column A and the corresponding file
names in column B.
>> that is correct.
And I need both original code and compiled program. I do not
distribute it, you can keep the copy right, but I need the code in
case I have to modify something. (I cannot write a program from
scratch, but I do have some very basic knowledge of C, UNIX, MS-DOS
and I may be able to change something small.)
|
Clarification of Answer by
leapinglizard-ga
on
31 May 2006 09:26 PDT
It can be tricky to extract data from Word files, but I'll be able to
write a script to do it if they are formatted in a consistent fashion.
Could you take, say, five of these files from a batch job and upload
them to a file hosting service such as streamload.com or badongo.com?
This would ensure that I am working with exactly the same file format
that you're producing.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
31 May 2006 14:47 PDT
>>>Clarification of Answer by leapinglizard-ga on 31 May 2006 09:26 PDT
It can be tricky to extract data from Word files, but I'll be able to
write a script to do it if they are formatted in a consistent fashion.
Could you take, say, five of these files from a batch job and upload
them to a file hosting service such as streamload.com or badongo.com?
This would ensure that I am working with exactly the same file format
that you're producing.
------------------------------------------------------
I will upload thing in this evening. But if you can convert all excel
files (all in the same directory) into one file by Python (all sheets
have X number in column 1, Y number in colum 2), Table Curve can
produce the excel file too. I don't care about the order of X max, as
long as I know the sheet's file name. Either way is fine to me. I will
upload the file and more files and will post it tonight.
|
Clarification of Answer by
leapinglizard-ga
on
31 May 2006 15:21 PDT
I am willing to implement the other method if necessary, but I want to
try this one first because it calls for less code and therefore less
debugging, greater maintainability, and so forth. Either way, I'll
have some code for you tomorrow.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
31 May 2006 16:35 PDT
Dear lizard:
I just found that the problem with Sigma Plot is that you can
"batch-process" to open 5000 excel files and do regression all data
from 5000 Excel sheets with a single click, but you cannot
batch-process to save all these 5000 files (what a stupid program!)
with a single click. It opens up 5000 REPORTs, but you have to save
them manually. I asked around in my lab, but it seems that there is no
way to get around this.
It seems that would be best if you can convert 2 columns in 5000
excels sheet in one excel sheets. I tried Visual Basic Editor for
Excel to do that task, but coudn't figure out. So it would be best if
you can write a Python for.
1. your program converts 5000 Excels sheets to one Excel sheet.
2. Table Curve calculates Xmax (Gaussian) for all of them and export
into 5000 sheets.
3. Collect all B5 cells from 5000 sheets into one excel file.
Total sheet numbers may be 5000 or 3499 or 342. If your program can
figure it out automatically, it would be great. But if it can't, I can
change some parameter as long as there is an instruction.
Thank you.
|
Clarification of Answer by
leapinglizard-ga
on
31 May 2006 17:03 PDT
I understand. I'll proceed to work on scripts to do the Excel file
consolidation and data extraction. You'll hear from me tomorrow
evening.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
31 May 2006 17:26 PDT
Thanks.
Just to explain the situation, the reason why i want to do this very
fast is that I have to do about 1000 calculations of 5000 Excel sheets
for over next 2 years. My one experiment produces about 5000 Excel
sheets and I have to do it again and again. That's why I have to
establish the fast method. Even cutting 1 minute in each process will
save a lot of work over years.
|
Clarification of Answer by
leapinglizard-ga
on
31 May 2006 18:06 PDT
It is perfectly natural to want to automate a procedure like yours.
Trust me, the problem will be solved within the next day or two. I
have plenty of experience with automated data extraction and
conversion, and, furthermore, I aim to restore your confidence in this
service and to earn your repeat business.
leapinglizard
|
Clarification of Answer by
leapinglizard-ga
on
01 Jun 2006 23:26 PDT
I apologize for the delay, but I am very tired and I ask you to wait
one more day before I give you the solution. I have worked it out over
the past few hours, and it remains only to write instructions for you.
Instead of writing a Python script to do the file merging, which after
some study I concluded would be too hard, I found an Excel macro that
will do this work. Once the files are merged into one, with one data
set per sheet, you can run the TableCurve batch process and output the
results in an RTF document. My script extracts the Xmax values and
produces a CSV file. So it's all ready to go. I just need to rest and
run some urgent errands tomorrow before I can sit down to do the
write-up.
leapinglizard
|
Request for Answer Clarification by
los111-ga
on
02 Jun 2006 07:34 PDT
That's OK. Please take your time.
I will ask Google Answer to try to change the star rating to better one.
I do not understand why Google doesn't email-notify the user. I myself
run a small web service, but that's simply unacceptable. Had they done
it, this whole thing went much better for both of us.
|
Request for Answer Clarification by
los111-ga
on
05 Jun 2006 14:06 PDT
Hi, 4 days passed, but no reply. Are you still working on it?
|
Request for Answer Clarification by
los111-ga
on
05 Jun 2006 18:36 PDT
I noticed that this question expired without any answer. I have to
file for a refund.
|
Clarification of Answer by
leapinglizard-ga
on
06 Jun 2006 14:17 PDT
I apologize for the delay in getting back to you. I had a very stressful
weekend dealing with personal matters, but I am now back in action and
ready to devote my attention to customers.
At the end of this message, I enclose the Visual Basic code for an Excel
macro that will merge all your separate data spreadsheets into a single
workbook, with one data set per sheet. Follow the instructions below to
use this macro.
1. Start a new workbook in Excel.
2. Type Alt-F11 to open the macro explorer.
3. Right-click on VBAProject (Book1) in the upper-left pane.
4. Select Insert -> Module and paste in the code.
5. In the code, change the value of dataFolderName to the absolute
pathname of your data directory. Now close the macro explorer.
6. Type Alt-F8 to open Macros, select "merge", and click on Run.
7. Save the resulting workbook before making any changes.
8. Make sure the front sheet, named Index, is active. Now select
File -> Save As... and, in the "Save as type:" menu at bottom, choose
CSV (Comma delimited). Choose an appropriate name for the file.
9. When you click Save, a warning will appear. Click OK to save the
active sheet. Another warning will appear about incompatible features.
Click Yes to leave out these features.
10. After saving the index as a CSV file, close this file and choose
not to save changes.
11. Now open the workbook that resulted from running the macro.
12. Delete the index sheet by selecting Edit -> Delete Sheet. Save the
workbook.
You can now run a batch job in TableCurve, as outlined in my original answer.
You should choose to output the results as an RTF (Rich Text Format) file. In
my next message, I will give you Python code to extract the X_max values from
this RTF file and merge them with the CSV index.
leapinglizard
Option Explicit
Sub merge()
Application.ScreenUpdating = False
Dim xlsFileNames() As String
Dim i As Integer
Dim fileName As String
Dim dataFolderName As String
Dim newSheet As Worksheet
Dim rowIndex As Long
Dim tempBook As Workbook
dataFolderName = "C:\Documents and Settings\x\My Documents\data"
If Right(dataFolderName, 1) <> "\" Then
dataFolderName = dataFolderName & "\"
End If
fileName = Dir(dataFolderName & "*.xls")
If fileName = "" Then
MsgBox "no excel files found"
Exit Sub
End If
Do While fileName <> ""
i = i + 1
ReDim Preserve xlsFileNames(1 To i)
xlsFileNames(i) = dataFolderName & fileName
fileName = Dir()
Loop
Set newSheet = Workbooks.Add(1).Worksheets(1)
newSheet.Name = "Index"
rowIndex = 0
For i = LBound(xlsFileNames) To UBound(xlsFileNames)
rowIndex = rowIndex + 1
newSheet.Cells(rowIndex, 1).Value = "'" & xlsFileNames(i)
Application.StatusBar = "Processing: " & i & " : " & xlsFileNames(i)
Application.DisplayAlerts = False
Application.EnableEvents = False
Set tempBook = Workbooks.Open( _
Filename := xlsFileNames(i), _
ReadOnly := True)
tempBook.Worksheets(1).Copy after := _
newSheet.Parent.Worksheets(newSheet.Parent.Worksheets.Count)
newSheet.Cells(rowIndex, 2).Value = "'" & ActiveSheet.Name
tempBook.Close savechanges := False
Application.EnableEvents = True
Application.DisplayAlerts = True
Next i
newSheet.UsedRange.Columns.AutoFit
newSheet.Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
|
Clarification of Answer by
leapinglizard-ga
on
06 Jun 2006 14:24 PDT
Have you submitted the refund request already? If so, I am sure it
will be approved. You will get your money back, and the one-star
rating will be expunged from my record. If you wish to continue our
exchange, please post a new question to my attention.
If you have not requested a refund, I will complete the present answer
through Clarifications.
leapinglizard
|
Clarification of Answer by
leapinglizard-ga
on
06 Jun 2006 19:11 PDT
It seems a message has gone missing. Did you post a Clarification
Request recently? I am anxious to resolve this matter to your
satisfaction.
leapinglizard
|
,
5 Comments
)
