Page 1 of 1

Conference Board, TED: Labels - importing data

Posted: Sat Mar 17, 2018 12:07 am
by tvonbrasch
Hi

I have imported data from the Conference Board, the Total Economy Database, see
https://www.conference-board.org/data/economydatabase/index.cfm?id=27762

The data and my code to import the data can be found below. When importing, there is information about each series in the object called "Measure" which is available in the page "Untitled", see picture below. I would like to attach this information as description of each series in the page "tedl" using the command

Code: Select all

series.setattr(description) ..measure...


How can this be done?

Thomas

Code: Select all

wfopen(wf=tedimport) "..\TED\TED_2_NOV20171.XLSX" colhead=1 range="tcb_original"

'organisere workpagen
pagestack num? @ *?  *

   'lager en indicator variabel som EViews forstÃ¥r
   alpha indicator_=@replace(indicator," - ","_")  'retter opp "Labor Input - Quality"
   alpha indicator_=@replace(indicator,"-","_")   'retter opp "Non-ICT Capital contribution"
   alpha indicator_=@replace(indicator_," ","_")  'tar bort mellomrom

rename var01 year
pagestruct(bal=se)  indicator_ iso @date(year)

alpha superid = "_" +indicator_+ "_" + iso
pageunstack(page=tedl) superid @date @ num

rename num_* * 'tar bort num_ fra navnet til alle seriene
rename *___* *_* 'tar bort dobbelt understrek

rename total_factor_productivity* tfp*

Re: Conference Board, TED: Labels - importing data

Posted: Sat Mar 17, 2018 5:31 am
by EViews Steve
Our IMPORT command actually supports reading in your data transposed so I don't think you need to do PAGESTACK.

Also, our IMPORT command supports reading in multiple header rows and marking each as either part of the name, or part of the description, or as an attribute with the same name. You can define all of this using our import wizard.

Note, when the wizard started for me, after selecting the correct cell range/page and then checking the "Read series by row" checkbox, I then had to manually re-define the cell range from A5 to BU1989. Sometimes our wizard can get a little confused in determining the proper range.

Finishing the import wizard generated the following command (actually the generated command included a bunch of extra commas after "DATE", one for each column (or row post transpose), but they were optional as I only needed to rename the first column to "DATE") so I removed them):

Code: Select all

import TED_2_NOV20171.xlsx range=TCB_ADJUSTED!$A$5:$BU$1989 byrow colhead=6 namepos=custom colheadnames=("Region","Iso","Country","Nr","Name","Description") na="#N/A" names=("DATE") format=(D,1984W) @freq A @id @date(date) @smpl @all

Where I treat the "INDICATOR" column as the name of the series object, the "MEASURE" column as the description, and all the other header rows (once transposed of course) as attributes.

I then got a workfile that looked like this:
Screen Shot 2018-03-17 at 5.16.36 AM.png
Screen Shot 2018-03-17 at 5.16.36 AM.png (452.25 KiB) Viewed 533 times

If I open one of the series objects and look at the Label view, you can see the attributes and descriptions:
Screen Shot 2018-03-17 at 5.23.08 AM.png
Screen Shot 2018-03-17 at 5.23.08 AM.png (171.17 KiB) Viewed 533 times


If you'd rather have the MEASURE value be it's own attribute instead of being stored as Description, just change the last colheadnames element to say "Measure" instead of "Description".

Re: Conference Board, TED: Labels - importing data

Posted: Sat Mar 17, 2018 10:16 am
by tvonbrasch
This is great, thanks for your elaborate answer. Your way is much more efficient than mine!

I have a follow up question: How can I change the import command so that the last part of the series name is the iso code for the country, e.g. "capital_input_ict_usa"? (see picture below)
Thanks again!
Thomas

Re: Conference Board, TED: Labels - importing data

Posted: Sun Mar 18, 2018 9:03 am
by EViews Steve
Just change "Iso" to "Name" in the colheadnames argument:

Code: Select all

import TED_2_NOV20171.xlsx range=TCB_ADJUSTED!$A$5:$BU$1989 byrow colhead=6 namepos=custom colheadnames=("Region","Name","Country","Nr","Name","Description") na="#N/A" names=("DATE") format=(D,1984W) @freq A @id @date(date) @smpl @all

When you define multiple column headers as Name, EViews simply concatenates each value together to be the final name. But because the ISO header appears before Indicator, you'll see the Iso value appear before the Indicator value in the new name.

If you want the Iso value to appear 2nd, you'll have to re-order the header rows (columns in your case) in your Excel spreadsheet so that Indicator appears first (to the left of Iso), then adjust your colheadnames argument appropriately to reflect the re-ordering.

Oh, and by doing it this way, you'll lose the ISO attribute on the object since it's now part of the name.

Re: Conference Board, TED: Labels - importing data

Posted: Mon Mar 19, 2018 1:32 am
by tvonbrasch
great, thanks again for your help!
Thomas