Economics should be open

December 16, 2008

Data cleaning, excel

Filed under: coding, Excel, Open Source, Uncategorized — Tags: , , , — howardchong @ 10:17 pm

Ever have data with commas between the thousand and million marks? spaces at the end of numbers? text footnotes appended to numbers, spaces in numbers?

Cleaning this by hand is a complete pain, so I wrote an Excel Macro.

To install, record a new macro and then stick this in the module.

There’s even a “careful” mode. (Change the line of careful=false to careful=true) which prompts you for each change that I felt might be wrong.

Please write a comment if you find it helpful.

 

(more…)

December 9, 2008

Polish Heat and Power

Filed under: Energy — Tags: , — howardchong @ 1:01 am

I was looking at International Energy Agency (IEA) stats on Electricity Generation. For most countries, an average of 90% of electricity is produced by electricity-only plants. But in Poland, 91% is produced by combined heat and power (CHP).

I thought this must be a mistake, but this excerpt from a googled site shows otherwise:

“Heat and hot water
The district heating sector in Poland comprises almost 400 individual networks, including the world’s largest district heating network in Warsaw. Of Poland’s urban population, 70% receive space-heat and 50% receive hot water from district heating systems. The sector is powered by a total of over 8,000 boilers, and delivers about 488 PJ (petajoules) of heat each year, at a peak rate of 45 GW. Out of this total heat production, approximately 170 PJ is produced within, and used by industrial enterprises.”
(source: http://www.ecee.org/pubs/poland.htm, accessed 08 DEC, 2008 )

Wow! What does this mean? It means that instead of every house having it’s own water heater, there is a big chunk of Poland that gets it’s hot water from a central plant. Instead of everyone having their own heater, some people also get heat piped in from a central heating plant.

December 2, 2008

MS Access Tables to Stata, for Residential Energy Consumption Survey

Problem: To convert MS Access tables (of the EIA Residential Energy Consumption Survey 1997 data) to CSV files for STATA import

This is also a general script for converting MS Tables to CSVs

Solution: Wrote a VBA script

 

  1. Open MS Access
  2. Tools | Macros | Visual Basic Editor
  3. Create a new module on your database by doing: Right Clickon database | Insert | Module
  4. Copy the following text (without the line numbers)
    1. Sub ExportAllTablesCsv()
    2. Dim dbMyDB As Database
    3. Set dbMyDB = OpenDatabase(“recs97_converted.mdb”)
    4.     For Each tdfCurrent In dbMyDB.TableDefs
    5.         fileoutname = “C:\” & tdfCurrent.Name & “.csv”
    6.         If Left(tdfCurrent.Name, 2) <> “MS” Then
    7.             DoCmd.TransferText acExportDelim, , tdfCurrent.Name, fileoutname, TRUE
    8.         End If
    9.             
    10.     Next
    11. End Sub
  5. For your customization:
    1. change your mdb file to your mdb file
    2. The If statement (line 10) is there to deal with the fact that there are certain system tables that I needed to skip. None of my tables started with “MS”, so this was a simple non-general fix.
    3. If you look up help for TransferText it has a SpecificationName. I left that argument blank. You can change to tabs or other things using that.
    4. I had trouble with getting column headings, but jwhite at UtterAccess.com showed me the light. This code now gives column headings with the TRUE argument.  For more details, check out http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1752140

If you can think of a better solution, I’d love your thoughts!

Other possible/failed solutions:

  • I tried running a query that joined all the tables with the EIA ID Num as the key for each table. Though this was easy, I got the “too many fields” error.
  • I think there is a way to use SQL (or something???) to select all the tables and write them and to import those into another program. STATA, to my limited knowledge, doesn’t do SQL. 
  • STATA supposedly does XML. One can use TransferText to write XML too. I don’t know XML well enough to try it.

Any comment appreciated.

 

 

—-

(more…)

Create a free website or blog at WordPress.com.