Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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

share|improve this question
    
Could you perhaps post some of the problematic entries and the intended result? –  Yves Klett Aug 2 at 19:25

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
    
Very nice and succinct! +1. –  seismatica Aug 2 at 19:58
    
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. –  Steven Aug 2 at 21:26
    
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] –  Steven Aug 2 at 21:34
1  
Responding to myself, but I think I have a more elegant solution. Map[GeoPosition[ ToExpression@{#["lat"], #["lng"] }] &, asamdataset] –  Steven Aug 2 at 22:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.