Importing Data to Matrix that includes Dates and Charachters

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Thu Dec 08, 2016 4:20 pm

Hi,

I am having difficulty importing data from a CSV that has dates, numbers, and charachters in cells. Non-numeric data is being listed as NA. However, I need the non-numeric data so that I can write a program that references cell contents so that instructions can be computed properly.

How do I get the import to allow for columns that are of different data/charachter types?

Thanks,

Jim

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Data to Matrix that includes Dates and Charachters

Postby EViews Gareth » Thu Dec 08, 2016 4:58 pm

Force it to be character data, using the "Data Type" field on page 3 of the data import dialog.
Follow us on Twitter @IHSEViews

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Thu Dec 08, 2016 7:30 pm

I tried that just now, specifying every column as charachter, and the result was all NA in every field. This is what the capture field output:

{%matrix}.sheet
importmat "C:\Users\Jim\Dropbox\30-Year Bond Auctions.csv" ftype=ascii rectype=crlf skip=0 fieldtype=delimited na="na." delim=comma types=(,A,A,A,A,A,A,A,A,A,A,A,A,A) colhead=1 eoltype=pad badfield=NA

Are mixed data types not allowed in a matrix? is the (,A,A,A...) above where I give the data type for each column? Seems like it should work.

page 396 indicates the following: “types=("arg1","arg2",…)”, user specified data types of the series. If types are provided
they will override the types automatically detected by EViews. You may use any
Matrix::import—393
of the following format keywords: “a” (character data), “f” (numeric data), “d”
(dates), or “w”(EViews automatic detection). This option is rarely used.

When I run it again and let eviews choose types, the wizard seems to do so correctly, but only the numberical fields don't have NAs. the capture screen outputs the following: ftype=ascii rectype=crlf skip=0 fieldtype=delimited na="na." delim=comma colhead=1 eoltype=pad badfield=NA

note that the types= command is not in the capture above.

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Thu Dec 08, 2016 8:31 pm

I tried moving the source file out of my dropbox, to see if that was the problem. I tried this, and named the column types individually in the command:

Code: Select all

{%matrix}.sheet
importmat  "C:\Users\Jim\Desktop\30-Year Bond Auctions.csv" ftype=ascii rectype=crlf skip=0 fieldtype=delimited na="na." delim=comma types=(,a,f,a,a,d,d,d,f,f,f,f,f) colhead=1 eoltype=pad badfield=NA


Here is a snip of the output:
NA NA NA NA NA NA NA NA 11.01000 56.00000 NA NA 1.770000 655.0000
NA NA NA NA NA NA NA NA 10.29000 76.00000 NA NA 2.040000 800.0000
NA NA NA NA NA NA NA NA 12.08000 94.00000 NA NA 2.150000 984.0000
NA NA NA NA NA NA NA NA 11.80000 37.00000 NA NA 2.200000 528.0000
NA NA NA NA NA NA NA NA 11.88000 87.00000 NA NA 2.480000 335.0000
NA NA NA NA NA NA NA NA 13.32000 68.00000 NA NA 2.150000 371.0000
NA NA NA NA NA NA NA NA 12.52000 57.00000 NA NA 3.470000 291.0000

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Sat Dec 10, 2016 6:55 pm

Still having issues with this. Nothing I do, including changing the csv files to xlsx files, is able to import multiple formatted columns into a matrix that holds each format without putting NAs in non-numerical columns where dates and strings should be.

I can, however, import them to a table from a csv or xls file and it does it perfectly.

Can a table be converted to a matrix that would preserve formatting???

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Data to Matrix that includes Dates and Charachters

Postby EViews Gareth » Sat Dec 10, 2016 7:02 pm

Can you provide the file?
Follow us on Twitter @IHSEViews

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Sat Dec 10, 2016 8:15 pm

I sent via email. Ty

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Data to Matrix that includes Dates and Charachters

Postby EViews Gareth » Mon Dec 12, 2016 10:06 am

I just opened that file just fine.

The only non-default thing is that the delimiter is a space, not a comma.
Follow us on Twitter @IHSEViews

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Mon Dec 12, 2016 10:22 am

You opened it and got columns with charachters, dates, and numbers - no NAs?

I have tried this many times. In the import matrix from file function, it autoselects the delimiter.

How exactly did you open it?

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Data to Matrix that includes Dates and Charachters

Postby EViews Gareth » Mon Dec 12, 2016 10:24 am

Ah, I missed you were importing into matrix. Matrices can't contain character data, so, yeah, they'll be NA.
Follow us on Twitter @IHSEViews

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Mon Dec 12, 2016 11:20 am

Not sure then how I would store character information in an array for a program to read sequentially in a loop. Any advice?? I need to do this for charachter and date data. Can it be done from a table??? I am able to import the data to a table object. Seems like a viable alternative - see my post in the 'Big Bond Table" thread, if you can. I have a program I started to outline there that shows what I am trying to do.

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13305
Joined: Tue Sep 16, 2008 5:38 pm

Re: Importing Data to Matrix that includes Dates and Charachters

Postby EViews Gareth » Mon Dec 12, 2016 11:32 am

Yes, a table can hold character and numerical data.
Follow us on Twitter @IHSEViews

JimForest
Posts: 83
Joined: Thu Oct 16, 2008 7:53 pm
Location: MA

Re: Importing Data to Matrix that includes Dates and Charachters

Postby JimForest » Mon Dec 12, 2016 2:10 pm

Thank you Gareth. I am aware that a table can hold various forms of data. But the question was regarding reading the cell contents so that the information in the cell can be passed to a smpl function. This is the problem I am having. More detail is in the other thread, originally about filtering a large bond data file - I can't pass 861 cusips to one command, need to do it in a loop - can't hold dates in a matrix, need to do in a table. How do I get it to read table cells?

My posts here have some better detail: viewtopic.php?f=3&t=17662


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 10 guests