Conference Board, TED: Labels - importing data

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

tvonbrasch
Posts: 540
Joined: Fri Apr 15, 2011 5:35 am

Conference Board, TED: Labels - importing data

Postby tvonbrasch » Sat Mar 17, 2018 12:07 am

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*
Attachments
measure.PNG
measure.PNG (99.2 KiB) Viewed 4544 times
TED_2_NOV20171.xlsx
(2.93 MiB) Downloaded 305 times

EViews Steve
EViews Developer
Posts: 788
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Conference Board, TED: Labels - importing data

Postby EViews Steve » Sat Mar 17, 2018 5:31 am

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 4535 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 4535 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".

tvonbrasch
Posts: 540
Joined: Fri Apr 15, 2011 5:35 am

Re: Conference Board, TED: Labels - importing data

Postby tvonbrasch » Sat Mar 17, 2018 10:16 am

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
Attachments
wf_iso_names.PNG
wf_iso_names.PNG (48.4 KiB) Viewed 4527 times

EViews Steve
EViews Developer
Posts: 788
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Conference Board, TED: Labels - importing data

Postby EViews Steve » Sun Mar 18, 2018 9:03 am

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.

tvonbrasch
Posts: 540
Joined: Fri Apr 15, 2011 5:35 am

Re: Conference Board, TED: Labels - importing data

Postby tvonbrasch » Mon Mar 19, 2018 1:32 am

great, thanks again for your help!
Thomas


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 12 guests