4
$\begingroup$

I'm trying to export the distance matrix below to an excel file. I have no interested on the unit. I just care the value of a distance. A standard export command does not work properly. How should I proceed?

Thanks

SelectedCities = {Entity[
    "City", {"NewYork", "NewYork", "UnitedStates"}], 
   Entity["City", {"LosAngeles", "California", "UnitedStates"}], 
   Entity["City", {"Chicago", "Illinois", "UnitedStates"}], 
   Entity["City", {"Houston", "Texas", "UnitedStates"}], 
   Entity["City", {"Philadelphia", "Pennsylvania", "UnitedStates"}], 
   Entity["City", {"SanFrancisco", "California", "UnitedStates"}]};
CityNameList = #1 & @@@ Transpose[{CommonName@SelectedCities}];
MyDistanceMatrix = DistanceMatrix[GeoPosition[SelectedCities]];
Export["~/Desktop/InputFileChapter3.xlsx", MyDistanceMatrix, "XLSX"];
$\endgroup$
3
$\begingroup$

You can use QuantityMagnitude to extract the numerical value from a Quantity:

SelectedCities = {
   Entity["City", {"NewYork", "NewYork", "UnitedStates"}], 
   Entity["City", {"LosAngeles", "California", "UnitedStates"}], 
   Entity["City", {"Chicago", "Illinois", "UnitedStates"}], 
   Entity["City", {"Houston", "Texas", "UnitedStates"}], 
   Entity["City", {"Philadelphia", "Pennsylvania", "UnitedStates"}], 
   Entity["City", {"SanFrancisco", "California", "UnitedStates"}]};
CityNameList = #1 & @@@ Transpose[{CommonName@SelectedCities}];
MyDistanceMatrix = QuantityMagnitude[
   DistanceMatrix[GeoPosition[SelectedCities]], 
  "Kilometers"
 ]
(* {{0., 3966.05, 1157.99, 2286.3, 124.884, 4147.89},
    {3966.05, 0., 2820.05, 2223.06, 3869.84, 551.621},
    {1157.99, 2820.05, 0., 1506.58, 1075.28, 2990.05},
    {2286.3, 2223.06, 1506.58, 0., 2163.45, 2646.14},
    {124.884, 3869.84, 1075.28, 2163.45, 0., 4063.58},
    {4147.89, 551.621, 2990.05, 2646.14, 4063.58, 0.}} *)

Exporting should now work as expected.

$\endgroup$
3
  • $\begingroup$ It works great! thanks! $\endgroup$
    – Dan Cat
    Mar 25 '20 at 15:04
  • $\begingroup$ @Lukas Lang: How do you export data to several "Worksheets" in a given Excel file? $\endgroup$ Mar 25 '20 at 15:46
  • 1
    $\begingroup$ @TugrulTemel See this example from the documentation on the "XLSX" file format $\endgroup$
    – Lukas Lang
    Mar 25 '20 at 15:53

Your Answer

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

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