Reading in real world datasets for analysis

[AReader-disable]

I've been trying to find a way to do this in MathCAD (and I have version 14).

What I want to do is read in some real-world data into MathCAD so I can perform some analysis on it. Unfortunately MathCAD's abilities in this area are very limited.

I've attached a file showing the type of dataset I'm trying to analyze. The file shows a data set organized as a block of 31x12 daily measurements repeated n times.

Obviously for months that have less than 31 days then it puts a missing value in (in this case -999).

How in MathCAD would I program to read that data into a time series (a single column vector) so that I can plot the data?

[TomGutman]

You need to read the data as the full matrix (as it is in the file), and then use a program to loop through the data to create a single time series. That program would just ignore the missing data.
__________________
� � � � Tom Gutman

[ptc-1368288]

On 4/4/2010 7:53:49 PM, AReader wrote:
...
>What I want to do is read in
>some real-world data into
>MathCAD ... Unfortunately
>MathCAD's abilities in this
>area are very limited.
...
_______________________

jmG

[AlvaroDíaz]

On 4/4/2010 7:53:49 PM, AReader wrote:

>How in MathCAD would I program
>to read that data into a time
>series (a single column
>vector) so that I can plot the
>data?

See the attached. Can you tell us what values are?

Regards. Alvaro.

[AReader-disable]

On 4/4/2010 11:51:37 PM, adiaz wrote:
>On 4/4/2010 7:53:49 PM, AReader wrote:
>
>>How in MathCAD would I program
>>to read that data into a time
>>series (a single column
>>vector) so that I can plot the
>>data?
>
>See the attached. Can you tell us what
>values are?
>
>Regards. Alvaro.

Alvaro

Close but the data is read downwards in each column (31 values) and then across one column

The table would have a header of:

Year, Day of Month, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec

So for the year 1772, the first 35 days would produce 32,20,27,27,15,22,25,0,0,45,62,52,25,17,30,20,-18,-13,-18,-10,-6,15,12,5,12,15,0,15,-33,-10,-8,-15,7,15,-25 ...

So in blocks of 31 x 12, each year is read down a column and then across one. Dates that don't exist (eg February 29,30,31) get filled with -999

Does this make sense now?

[AlvaroDíaz]

On 4/5/2010 1:00:47 AM, AReader wrote:

>Does this make sense now?

Yes, I don't check in what order data ends (or I do that, but because I use dd/mm/yy sometimes I misread and get confusing with bad ordered data).

Regards. Alvaro.

[AReader-disable]

Alvaro

You have solved my problem and helped a friend of mine who asked about doing this in MathCAD and I couldn't work it out. So I'll pass the code onto hum

Thanks very much for your help.

The data set in this case is the Central England Temperature where the temperatures are in 1/10ths of a degree Celcius. It was given as an example of the kinds of datasets that my friend has been struggling with.

Perhaps he and I should get a good book on MathCAD programming.

But there are lots of datasets in the real world of statistical modeling which are organized like this and there wasn't a good way of extracting the data.

Do you know if the stats pack for M14 has extra facilities to extract data into a time series or would this programming have been the only way to do it?

Thanks once again.

[RichardJ]

On 4/5/2010 6:22:36 AM, AReader wrote:

>But there are lots of datasets
>in the real world of
>statistical modeling which are
>organized like this and there
>wasn't a good way of
>extracting the data.
>
>Do you know if the stats pack
>for M14 has extra facilities
>to extract data into a time
>series or would this
>programming have been the only
>way to do it?

The problem is that there is a lot of data, but it is organized in a lot of different formats. Some of those formats are very common, such as a simple 2 column format. Others are much more complex, such as yours. You will not find any general tool that will read such formats directly. If you are lucky you may find something that someone specifically wrote that brings that data into the software you are interested in using, but otherwise you will have to program it yourself.

At least it wasn't a binary format though 🙂

Richard

[AlvaroDíaz]

On 4/5/2010 9:18:07 AM, rijackson wrote:

>If you are lucky
>you may find something that someone
>specifically wrote that brings that data
>into the software you are interested in
>using, but otherwise you will have to
>program it yourself.
>
>At least it wasn't a binary format
>though 🙂

Richard have reason: you're lucky because your data is in a readable format (as text). This is my prefer format. Following are a brief list about avaible methods in mathcad to read external data:

1. ODBC input. It's the more robust, you can write your own odbc consult, but it is local in the sense that needs to configure the windows system to use it.

2. File read via "file read" component. Can read excel, dbase (foxpro dbf) or matlab files, but with precautions. For example excel files can't have not moving panels and matlab must to be in some earlier version. Also isn't easy to change the name of the reading file.

3. READPRN, the lowest level and flexible tool.

4. Excel component which reads external sources, but embed the data into the mathcad worksheet. Useful if data is not to much big. Can make dinamic questions into the data receiving parameters from mathcad. Preblem here is that this method starts excel, and it is not a good idea do that at any system.

5. Read data using vbscript components. Richard teachs a lot of those in this collab. Gutman publish one very interesting reading AISC tables from excel. Problem here is that probably you must to enable the components each time that you open the mathcad sheet.

6. Mathcad's own table input. Looks like a joke, but mathcad inputs tables are the default data storage system in mathcad.

You can found all of this in the help system, and the components searching in this forum.

Regards. Alvaro.

[AReader-disable]

Thanks Alvaro. You've been a very big help.

[ptc-1368288]

Below Marlett

Working ways added:
1. NaN
2. Block extract
3. Pipe size extractor

jmG

[ptc-1368288]

On 4/5/2010 1:00:47 AM, AReader wrote:
...
>Does this make sense now?
__________________________

NO ! if you don't abstract the project and don't put the data in a table.
READPRN as suggested, copy/paste in a Mathcad data table .
Then collabs can look at it.

>...the data is read downwards in each column (31 values), and then across one column<<br>
==> My interpretation is that you read serially all columns for a complete train of data plot,
then you select a specific column as highlight in the plot ???
If so: simple but you must start a work sheet.
Invalid positions might accept NaN, but you might have to NaN manually.

jmG

[ptc-1368288]

Here is an example of waterfall plot.

jmG