PowerShell Archive

How to Measuring Data with PowerShell

Working together with PowerShell, it’s possible to pretty much conduct anything and among these matters occurs to function as investigating a great number of information to quantify a variety of matters. Maybe you wish to discover the entire count of each the information which you’re looking at or wish to work out the typical price of other data and also do not really wish to load up the data in an excel spreadsheet to entertaining several formulas. PowerShell and perform the job for you breaking a sweat

Employing a CSV file featuring Iowa Crash Data, we could examine the information and offer some statistical info only by using PowerShell’s present commands.

The CSV file is near 34MB in proportion, therefore it could have a brief time to download it. This will simply default a count that represents the amount of rows which have information in it.

Import-Csv .\Crash_Data.csv | Measure-Object

We can see that there are close to 165,000 rows of data in this CSV file. This should make for a great look at data and determining how often weather might have been a factor among other things. Let’s grab a look at the first row of data to better understand what we are looking at.

$CrashData = Import-Csv .\Crash_Data.csv
  $CrashData | Select-Object -First 1 

That’s a great deal of information for only a single row. Also, a few of the pillars might not be that understandable and lots of the merits have numbers from them. Normally this implies that somewhere, there’s a hash table any key to appreciate collection that may translate every one of the amounts to a human readable format. This is average when storing information in a database to utilize a number that maps rear elsewhere into a human readable price.

After a small bit of hunting, I discovered the missing piece to the puzzle that distinguishes each column in addition to the anticipated lookups to every one the numerical outcomes. The connection which hosts this information luckily returns the info in JSON meaning I can utilize Invoke-RestMethod to pull down the data and browse it again simpler than if I had been only seeing the net page itself.

$Data = Invoke-RestMethod -Uri  'https://gis.iowadot.gov/public/rest/services/Traffic_Safety/Crash_Data/MapServer/0?f=pjson'
  $Data.fields|Select-Object -Property Name, alias, Domain

Now we know what the column names are by looking at the alias. When looking at the domain, we can step into each of those hash tables to find out the numeric lookup values for items such as the weather.

($Data.fields | Where-Object {$_.name -eq 'weather'}).Domain.CodedValues

name        code
  ----                          ----
  Clear                           1
  Cloudy                          2
  Fog, smoke, smog                3
  Freezing rain/drizzle           4
  Rain                            5
  Sleet, hail                     6
  Snow                            7
  Blowing Snow                    8
  Severe Winds                    9
  Blowing sand, soil, dirt        10
  Other (explain in narrative)    98
  Unknown                         99
Not Reported                    77

With this knowledge, we now know exactly the weather conditions that were reported at the time of the crash. Before we do that, let’s run some measurements using Measure-Object to get an idea some averages of things like property damage and anything else that stands out to me.

#Property Damage Average and Max 
  $CrashData | Measure-Object -Property PROPDMG  -Average -Maximum

We see that the average amount per crash was $5155.02 while there was one crash where the property damage was $4,851,387! That is quite a bit of damage being done. That was pretty interesting, but I wonder how the weather played a factor in some of these crashes.

#Weather Related 
  $WeatherHash = @{}
  ($Data.fields  | Where-Object  {$_.name  -eq 'weather'}).Domain.CodedValues  | ForEach {
  $CrashData | Group-Object -Property WEATHER  | 
  Select-Object -Property  Count,  @{L='Name';E={$WeatherHash[([int]$_.Name)]}} | 
Sort-Object -Property  Count -Descending

Here I made use of the JSON data to put the weather code and display name into a hash table for an easier lookup. I also used Group-Object to group everything together and then sorted the count to show the most common reasons for a crash and then the least common reasons. Weather is one thing, but I want to know if there are other environmental conditions which were reported at the time of the crash.

#Environmental Conditions 
  $ECNTCRC =  @{}
  ($Data.fields  | Where-Object  {$_.name  -eq 'ECNTCRC'}).Domain.CodedValues  | ForEach {
  $CrashData | Group-Object -Property ECNTCRC  | 
  Select-Object -Property  Count,  @{L='Name';E={$ECNTCRC[([int]$_.Name)]}} | 
Sort-Object -Property  Count -Descending

It appears that the majority of accidents didn’t appear to have any environmental conditions with weather being second as a reason followed by nothing being reported and animals being on the road as the fourth most reported reason.

There is a wealth of information here that we can dig into and using PowerShell, are able to better visualize the data using Group-Object and Measure-Object. I’ve only scratched the surface with this data and we could continue to dig deeper by combining the grouped data by picking the largest weather condition (clear skies in this case) and determining other factors such as major cause of the crash and taking the top hit from that and determining which city had the most of the top major cause.

#Top Weather/City/Reason 
  $WeatherHash = @{}
  ($Data.fields  | Where-Object  {$_.name  -eq 'weather'}).Domain.CodedValues  | ForEach {
  #Major Cause 
  $MAJCSEHash = @{}

So based on our query, we can determine that the top weather condition is clear conditions and the top city which had reported the most clear conditions at the time of the wreck is in Des Moines with the number one reason for the crash was due to following too close to the car in front of them. Now you can take this data (or other data that is available) and conduct your own data analysis using PowerShell.

Credit : mcpmag.com