Using PowerShell Filter functions to convert weather comma separated datasets from the US to the metric system

Klaus Graefensteiner — Tue, 07 Jul 2009 05:07:47 -0700

This is another interesting application of PowerShell filter functions. The input for this filter is a comma separated line of text representing US system floating point values like Temperature, Pressure, Wind Speed. The output of this filter is a comma separated string of floating point values converted to the metric system.

Figure 1: Weather Station on top of mount Zugspitze

The Problem
The Solution
- PowerShell script
  - Combi-Filter with explicit expressions
  - Combi-Filter with array of FilterInfo objects
Download
Ausblick

The Problem

I wanted to convert weather data in a CSV file that are stored in the US system into a file that stores the equivalent values in the metric system. Here is how the file looks in Excel before the conversion:

Figure 2: Weather data in the US system

And here is how the file should look like after the conversion:

Figure 3: Weather data in the metric system

Temperatures needed to be converted from degrees Fahrenheit to Celsius, the pressure expressed in HPa (Hecto Pascal) instead of Inch Mercury, speeds converted from MPH to Km/h and finally mm (Millimeters) used instead of Inches. The formulae for these conversions can be found here for example: http://www.srh.noaa.gov/elp/wxcalc/wxcalc.shtml.

The Solution

The solution is actually fairly simple. I was using Get-Content and Add-Content to read and write the weather records as lines of text. Then I was piping the strings through a Combi-Filter. A Combi-Filter is a PowerShell Filter function that uses several specialized Filter function itself in its implementation.

PowerShell script

Here is the PowerShell script that shows the basic approach.

Combi-Filter with explicit expressions

   1: Filter FormatTime()

   2: {

   3:     [System.Globalization.CultureInfo] $provider = [System.Globalization.CultureInfo]::InvariantCulture;

   4:     [DateTime] $dt = [DateTime]::ParseExact($_, "yyyy-MM-dd HH:mm:ss", $provider)

   5:     $DateTimeString = $dt.ToString()

   6:     return $DateTimeString

   7: }

8:

   9: Filter FToC()

  10: {

  11:     return [Double](($_ - 32) * 5/9)

  12: }

13:

  14: Filter InHgToHPa()

  15: {

  16:     return [Double](33.8639 * $_)

  17: }

18:

  19: Filter NOP()

  20: {

  21:     return $_

  22: }

23:

  24: Filter MPHToKmPH()

  25: {

  26:     return [Double](1.609344 * $_)

  27: }

28:

  29: Filter InToMm()

  30: {

  31:     return [Double](25.4 * $_)

  32: }

33:

  34: $Records = "Time,TemperatureC,DewpointC,PressureHPa,WindDirectionDegrees,WindSpeedKmH,WindSpeedGustKmH,Humidity,HourlyPrecipMm",

  35:             "2008-01-01 00:03:00,49.6,29.7,30.10,5,1,7,46,0.00",

  36:             "2008-01-01 00:08:00,49.5,29.6,30.10,68,3,3,46,0.00",

  37:             "2008-01-01 00:12:00,49.5,29.6,30.10,68,3,3,46,0.00",

  38:             "2008-01-01 00:17:00,49.5,29.6,30.10,68,3,3,46,0.00",

  39:             "2008-01-01 00:22:00,49.5,29.6,30.10,68,3,3,46,0.00",

  40:             "2008-01-01 00:27:00,49.5,29.6,30.10,68,3,3,46,0.00",

  41:             "2008-01-01 00:32:00,49.2,30.4,30.10,300,3,4,48,0.00",

  42:             "2008-01-01 00:37:00,49.0,30.7,30.10,328,3,12,49,0.00"

43:

44:

  45: Filter Filter-Header

  46: {

  47:     if($_ -match "^Time")

  48:     {

  49:         $_ | out-null

  50:     }

  51:     else

  52:     {

  53:         $_

  54:     }

  55: }

56:

  57: # Combi-Filter with explicit expressions

  58: Filter Filter-USToMetric($d=",")

  59: {

  60:     $Fields = $_.Split($d)

61:

  62:     $Fields[0] = $Fields[0] | FormatTime

  63:     $Fields[1] = $Fields[1] | FToC

  64:     $Fields[2] = $Fields[2] | FToC

  65:     $Fields[3] = $Fields[3] | InHgToHPa

  66:     $Fields[4] = $Fields[4] | NOP

  67:     $Fields[5] = $Fields[5] | MPHToKmPH

  68:     $Fields[6] = $Fields[6] | MPHToKmPH

  69:     $Fields[7] = $Fields[7] | NOP

  70:     $Fields[8] = $Fields[8] | InToMm

71:

  72:     $Record = [String]::Join($d, $Fields)

  73:     return $Record

  74: }

75:

  76: $Results = $Records | Filter-Header | Filter-USToMetric

  77: $Results

78:

79:

View Plain | Copy To Clipboard

Combi-Filter with array of FilterInfo objects

After my initial implementation I was not quite happy with the way the different conversion Filter functions needed to be applied for the different fields. With the help from Oisin Grehan I was able to find a more elegant way to call the appropriate filter function.

   1: Filter FormatTime()

   2: {

   3:     [System.Globalization.CultureInfo] $provider = [System.Globalization.CultureInfo]::InvariantCulture;

   4:     [DateTime] $dt = [DateTime]::ParseExact($_, "yyyy-MM-dd HH:mm:ss", $provider)

   5:     $DateTimeString = $dt.ToString()

   6:     return $DateTimeString

   7: }

8:

   9: Filter FToC()

  10: {

  11:     return [Double](($_ - 32) * 5/9)

  12: }

13:

  14: Filter InHgToHPa()

  15: {

  16:     return [Double](33.8639 * $_)

  17: }

18:

  19: Filter NOP()

  20: {

  21:     return $_

  22: }

23:

  24: Filter MPHToKmPH()

  25: {

  26:     return [Double](1.609344 * $_)

  27: }

28:

  29: Filter InToMm()

  30: {

  31:     return [Double](25.4 * $_)

  32: }

33:

34:

  35: #For Loop doesn't work

  36: $ConversionTable = @(

  37:                         (get-item function:FormatTime),

  38:                         (get-item function:FToC),

  39:                         (get-item function:FToC),

  40:                         (get-item function:InHgToHPa),

  41:                         (get-item function:NOP),

  42:                         (get-item function:MPHToKmPH),

  43:                         (get-item function:MPHToKmPH),

  44:                         (get-item function:NOP),

  45:                         (get-item function:InToMm)

  46:                     )

47:

  48: $ConversionTable

  49: $ConversionTable[0].GetType().FullName

50:

51:

52:

  53: $Records = "Time,TemperatureC,DewpointC,PressureHPa,WindDirectionDegrees,WindSpeedKmH,WindSpeedGustKmH,Humidity,HourlyPrecipMm",

  54:             "2008-01-01 00:03:00,49.6,29.7,30.10,5,1,7,46,0.00",

  55:             "2008-01-01 00:08:00,49.5,29.6,30.10,68,3,3,46,0.00",

  56:             "2008-01-01 00:12:00,49.5,29.6,30.10,68,3,3,46,0.00",

  57:             "2008-01-01 00:17:00,49.5,29.6,30.10,68,3,3,46,0.00",

  58:             "2008-01-01 00:22:00,49.5,29.6,30.10,68,3,3,46,0.00",

  59:             "2008-01-01 00:27:00,49.5,29.6,30.10,68,3,3,46,0.00",

  60:             "2008-01-01 00:32:00,49.2,30.4,30.10,300,3,4,48,0.00",

  61:             "2008-01-01 00:37:00,49.0,30.7,30.10,328,3,12,49,0.00"

62:

63:

  64: Filter Filter-Header

  65: {

  66:     if($_ -match "^Time")

  67:     {

  68:         $_ | out-null

  69:     }

  70:     else

  71:     {

  72:         $_

  73:     }

  74: }

75:

76:

  77: #Combi-Filter using array of FilterInfo objects

  78: Filter Filter-USToMetricA($d=",")

  79: {

  80:     $Fields = $_.Split($d)

81:

  82:     for ($i = 0; $i -lt 9; $i++)

  83:     {

  84:         $Fields[$i] = $Fields[$i] | &$ConversionTable[$i]

  85:     }

86:

  87:     $Record = [String]::Join($d, $Fields)

  88:     return $Record

  89: }

90:

  91: $Results = $Records | Filter-Header | Filter-USToMetricA

  92: $Results

93:

View Plain | Copy To Clipboard

Download

The complete PowerShell script can be downloaded here: Convert-USToMetric.zip

Ausblick

In one of my next blog posts I am going to write about how to use Filter functions to extend CSV files with new fields.

Tellingmachine