How to convert Australian Bureau of Stats data cube to useful layout

[atlasB]

I often get data from the ABS (Australian Bureau of Statistics). For example, this data on nutrient intake percentiles. It comes in Excel format, in a very cumbersome layout.

I spend a lot of time reformatting and combining ABS outputs to make them into the long data format. I arrange the data manually, then use Excel's pivot table wizard to turn it into a useable format. Doing it manually takes age.

Different ABS outputs have different, but similar, layouts. Here is another output on household expenditure.

What can I do to make using this data easier? Is there a macro or something that could save me from copying and arranging the data bit by bit? My current process is tedious and error prone, and so boring...

Thanks

 

[Hui]

atlasB

Firstly, Welcome to the Chandoo.org Forums

Have you contacted the ABS directly?
explain the issues

They may have other download/online systems to simplify this process

I regularly use ABS data as well, but apart from them changing data locations, I don't have many issues, but i only use a small set of common data

 

[NARAYANK991]

Hi ,

Have you checked this out ?

http://www.abs.gov.au/websitedbs/D3310114.nsf/home/About+TableBuilder

Narayan

 

[atlasB]

atlasB

Firstly, Welcome to the Chandoo.org Forums

Have you contacted the ABS directly?
explain the issues

They may have other download/online systems to simplify this process

I regularly use ABS data as well, but apart from them changing data locations, I don't have many issues, but i only use a small set of common data

I have, and I have also checked out TableBuilder. TableBuilder is great if you want census data, otherwise the data access seems to cost about $1000 per data set (which is out of my budget for what is essentially a hobby). And the ABS output layout is a relic from the days when they would publish everything in pdf, so the excel sheets they produce are designed to look like a printed page.

 

[NARAYANK991]

Hi ,

The ABS.Stat website here :

http://stat.data.abs.gov.au/

mentions the SDMX standard here :

http://www.abs.gov.au/websitedbs/d3...62bb00121564/9ab27aa30c513c41ca257b20000f7923

and has an SDMX converter here :

https://sdmx.org/?page_id=4649

Have you checked these out ?

Narayan