1
$\begingroup$

I have a simple dataset for which I am attempting to modify so that values for two columns, entitled “LATITUDE” and “LONGITUDE” can be converted from a Deg-Min-Sec format, expressed as a String (ie “9°44'N”), into a decimal format suitable for geocoding (ie "9.7333").

A subsample is given below:

{<|"SPECIES" -> "Pontinus moseri", "COUNTRY" -> "COSTA RICA", "CATALOG NUMBER" -> "CAS 60423", "NUMBER OF SPECIMENS" -> 1, "SIZE RANGE in mm" -> 156.1, "LOT REMARKS" -> "", "LATITUDE-LONGITUDE" -> "", "LATITUDE" -> "", "LONGITUDE" -> "", "LOCALITY" -> "no precise locality", "BOTTOM" -> "", "DEPTH" -> "", "GEAR" -> "", "FIELD NUMBER" -> "FHB 11-XII-73A", "EXPEDITION" -> "", "VESSEL" -> "", "CRUISE" -> "", "STATION" -> "", "COLLECTOR" -> "F. H. Berry", "TIME" -> "", "DATE" -> "11 Dec 1973", "ACCESSION NUMBER" -> "Acc. 1974-V:31"|>, <|"SPECIES" -> "Pontinus moseri", "COUNTRY" -> "COSTA RICA", "CATALOG NUMBER" -> "CAS 60632", "NUMBER OF SPECIMENS" -> 2, "SIZE RANGE in mm" -> "91.9-94.1", "LOT REMARKS" -> "", "LATITUDE-LONGITUDE" -> "9[Degree]34'N, 84[Degree]48'43\"W", "LATITUDE" -> "9[Degree]34'N", "LONGITUDE" -> "84[Degree]48'43\"W", "LOCALITY" -> "Gulf of Nicoya, off Quepos, Zone 21", "BOTTOM" -> "", "DEPTH" -> "150 fa [274 m]", "GEAR" -> "", "FIELD NUMBER" -> "FBH 13-XII-73C", "EXPEDITION" -> "", "VESSEL" -> "", "CRUISE" -> "", "STATION" -> "", "COLLECTOR" -> "F. H. Berry", "TIME" -> "0620 hrs", "DATE" -> "13 Dec 1973", "ACCESSION NUMBER" -> ""|>, <|"SPECIES" -> "Pontinus moseri", "COUNTRY" -> "COSTA RICA", "CATALOG NUMBER" -> "CAS 204797", "NUMBER OF SPECIMENS" -> 1, "SIZE RANGE in mm" -> 84.2, "LOT REMARKS" -> "[ex CAS 60419]", "LATITUDE-LONGITUDE" -> "9[Degree]34'N, 84[Degree]53'W", "LATITUDE" -> "9[Degree]34'N", "LONGITUDE" -> "84[Degree]53'W", "LOCALITY" -> "Peninsula de Osa, Zone 27", "BOTTOM" -> "", "DEPTH" -> "120-127 fa [219-232 m]", "GEAR" -> "bottom trawl", "FIELD NUMBER" -> "FHB 9-XII-73", "EXPEDITION" -> "", "VESSEL" -> "", "CRUISE" -> "", "STATION" -> "", "COLLECTOR" -> "F. H. Berry", "TIME" -> "", "DATE" -> "9 Dec 1973", "ACCESSION NUMBER" -> "Acc. 1979-IV:23"|>, <|"SPECIES" -> "Pontinus moseri", "COUNTRY" -> "COSTA RICA", "CATALOG NUMBER" -> "LACM 30720-4", "NUMBER OF SPECIMENS" -> 3, "SIZE RANGE in mm" -> "115.4-148.2", "LOT REMARKS" -> "", "LATITUDE-LONGITUDE" -> "[ca. 9[Degree]23'30\"N, 84[Degree]15'00\"W]", "LATITUDE" -> "9[Degree]23'30\"N", "LONGITUDE" -> "84[Degree]15'00\"W", "LOCALITY" -> "Puntarenas, Punta Quepos", "BOTTOM" -> "", "DEPTH" -> "", "GEAR" -> "", "FIELD NUMBER" -> "", "EXPEDITION" -> "", "VESSEL" -> "", "CRUISE" -> "", "STATION" -> "", "COLLECTOR" -> "R. T. Nishimoto", "TIME" -> "", "DATE" -> "16 Jul 1969", "ACCESSION NUMBER" -> ""|>}

with the above imported as 

dataset = Import["Pontinus_moseri_locality_data.csv","Dataset",HeaderLines-> 1]

and as converted by

assoc = Normal[dataset];

and

Take[assoc, {9, 15, 2}]

to get the subsample given above.

Interestingly, if instead I import from an *.XLSX file using the following command:

dataset = Import["Pontinus_moseri_locality_data.xlsx", "Dataset",  
HeaderLines -> 1]

Normal does not return the association but rather the displayed dataset table, which looks identical to that using the .csv import given above, but instead has a Head[dataset] as List. I am unsure why this happens as the tables seem identical with the same displayed scrollable MMA format give for Dataset objects.

In any event, from within this dataset object (via csv) I attempt to make the conversion using an overloaded function to handle the cases for value = Null or value = "", as follows:

latlongConvert[x_ /; x != Null] := 
 Module[{a}, a = ToString[N[FromDMS[ToString[x]], 5]]; Return [a]]

latlongConvert[x_ /; SameQ[x, ""]] := Module[{a}, a = ""; Return [a]]

latlongConvert[x_ /; SameQ[x, Null]] := Module[{a}, a = Null; Return [a]]

moseriLocalityDataMod1 = dataset[All, {"LATITUDE" -> latlongConvert, 
 "LONGITUDE" -> latlongConvert}]

Although the code executes with no errors, the conversions do not take place, with values within the Dataset object instead being replaced by the called object (ie latlongConvert["9°44'N"] etc.) but not evaluating to "9.7333"

Nonetheless, executing the expression 

ToString[N[FromDMS[ToString[9°44'N]],5]]

returns “9.7333" as expected.

What do I have to change to get the conversion to execute correctly, with the correct decimal format being substituted for the original deg min sec format within the dataset?

Improve this question
$\endgroup$
6
  • $\begingroup$ The format of data does not make much sense. For instance, your quotes are "curly quotes" whereas MMA would only recognize "regular" quotes to delimit strings. Could you please paste exactly the content of dataset instead? $\endgroup$
    – MarcoB
    Apr 10, 2019 at 21:07
  • $\begingroup$ Let's start with syntax: Try Clear[latitudeConvert]; latitudeConvert[x_String] := ToString[N[FromDMS[ToString[x]], 5]] for starters. Notice that you cannot write Return a; you could use Return[a], but you actually almost never need Return in this context. $\endgroup$
    – MarcoB
    Apr 10, 2019 at 21:11
  • $\begingroup$ Furthermore, the two convert functions are the same, are they not? So just write a single convert function that works for both. $\endgroup$
    – MarcoB
    Apr 10, 2019 at 21:12
  • $\begingroup$ Marco, I have made the suggested changes, with cut/paste using output directly from MMA rather than the data I previously gave using a wordprocessor, which used the "curly" double quotes (but same key). Full Dataset too big to pass here, hence the subsample. $\endgroup$
    – Stuart Poss
    Apr 11, 2019 at 1:58
  • $\begingroup$ FOUND THE ANSWER! by just replacing the first function with latlongConvert[x_ /; StringLength[x] > 1] := Module[{a}, a = ToString[N[FromDMS[ToString[x]], 5]]; Return [a]]. Following up using your suggestions was all that was needed. Thanks Marco. $\endgroup$
    – Stuart Poss
    Apr 11, 2019 at 2:12

1 Answer 1

Reset to default
1
$\begingroup$

Following MarcoB's suggestions to their logical conclusion the First Overloaded function works. The problem arises from the different structure of the output from the two lines

dataset = Import["Pontinus_moseri_locality_data.csv","Dataset",HeaderLines-> 1]

which generates has a Head[dataset] = Dataset and 

dataset = Import["Pontinus_moseri_locality_data.xlsx", "Dataset",  
HeaderLines -> 1] (this does not work and Head[dataset] = List)

and making two additional changes to the original code:
1) overloading the function to account for Null and "" in the data as shown above, and 2) changing the original conditional on the function to /; StringLength[x]>1 rather than /; x!=Null.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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