4
$\begingroup$

The start of my question is about one specific situation - I have some erroneous lat/long values in a dataset which I need to clean up. But I'd like it to cover the overall topic of cleaning up messy data for analysis work.

The task at hand. I'm importing the NGA anti-shipping activity message database to do some analysis work with it. The problem comes when I try to extract lat/long value - there are a few invalid values in my dataset.

Sometimes Mathematica can make it difficult to tell what sort of data something is. Is it a Null, or "Null" (string)?

What are some ways to identify bad data, and correct it (or delete it)?

(* First import all the piracy events between 1990 and aug 1st, 2014 *)
(* This will take a minute or so *)
asamjson = 
 Import["http://msi.nga.mil/MSI_JWS/ASAM_JSON/getJSON?typename=\
DateRange_AllRefNumbers&fromDate=19900101&toDate=20140801", "JSON"];
Needs["GeneralUtilities`"]
asamdataset = Dataset[ToAssociations@asamjson];

(* Then we define some helper functions *)
getLong[ds_] := fixstr[ds["Longitude"]];
getLat[ds_] := fixstr[ds["Latitude"] ];
getPos[ds_] := {getLat[ds], getLong[ds]};
fixLong[ds_] := StringReplace[ds["Longitude"], "°" -> "\[Degree]"];
fixstr[str_] := StringReplace[str, "°" -> "\[Degree]"];

(* However, if we try to use our dataset we have some erroneous lat/long*) 
(* values.   How can we correct these values (Zero the particular values, 
(* make something up, ?), without resorting to deleting the entire record. *)
piracyLocations = 
 Map[GeoPosition[ {fixstr[#["Latitude"]], 
  fixstr[#["Longitude"]] }]   &, asamdataset]

EDIT: PatoCriollo's solution below is elegant. But sooner or later we'll come across data that doesn't have a nicely cleaned duplicate of itself waiting to be used. In fact even the ASAM dataset has bogus values in the lat/lng data.

dat = Map[ ToExpression@{#["lat"], #["lng"] }   &, 
Take[asamdataset, 1000]];
Show[SmoothDensityHistogram[dat, .3, ColorFunction -> "SunsetColors", 
  PlotPoints -> 150, Mesh -> 20, MeshStyle -> Opacity[.1], 
  PlotRange -> All], 
  Graphics[{FaceForm[], EdgeForm[Directive[White, Opacity[.7]]], 
  CountryData["World", "Polygon"]}], AspectRatio -> Automatic]

ASAM data heatmap

$\endgroup$
  • $\begingroup$ Could you perhaps post some of the problematic entries and the intended result? $\endgroup$ – Yves Klett Aug 2 '14 at 19:25
7
$\begingroup$

Steven, looking at the data you can see that you already have the latitude and longitude converted into de proper format under fields lng and lat.

asamdataset[1] // Normal
(*<|"Subregion" -> "57", "Reference" -> "2014-175", 
 "Description" -> 
  "GHANA:On 25 July, the 3,232 gross ton Kiribati-flagged product \
tanker MT HAI SOON 6 was boarded and hijacked by a group of 10 \
heavily armed pirates about 46 nautical miles south of Anloga, \
Ghana.", "Aggressor" -> "Pirates", "Longitude" -> "0&deg; 54.0' E", 
 "Victim" -> "Product tanker", "Date" -> "07/25/2014", "lng" -> "0.9",
  "lat" -> "5.0", "Latitude" -> "5&deg; 0.0' N"|>*)

So we can go ahead and extract the data, the only caveat is that we need to convert the fields from strings to values.

data = Normal@asamdataset[All, {"lat", "lng"}]
pairs = Normal[data[[#]]][[All, 2]] & /@ Range@Length@data
markers = GeoMarker[ToExpression@#] & /@ pairs

Lets plot the first hundred markers

GeoGraphics[Take[markers, 100]]

Mathematica graphics

$\endgroup$
  • $\begingroup$ Very nice and succinct! +1. $\endgroup$ – seismatica Aug 2 '14 at 19:58
  • $\begingroup$ Well, this is a succinct solution to the problem at hand. Perhaps I should have asked a question which would have forced us to follow the data scrubbing/shovel work route. $\endgroup$ – user4860 Aug 2 '14 at 21:26
  • $\begingroup$ On my first attempt using lat/long, I tried this ti simply map out the lat/lng values into a geoposition, but that failed. Map[GeoPosition[ {#["lat"], #["lng"] }] &, asamdataset] $\endgroup$ – user4860 Aug 2 '14 at 21:34
  • 1
    $\begingroup$ Responding to myself, but I think I have a more elegant solution. Map[GeoPosition[ ToExpression@{#["lat"], #["lng"] }] &, asamdataset] $\endgroup$ – user4860 Aug 2 '14 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy