2
$\begingroup$

I have the following well-formed JSON string

jsonResult="{\"result\":[{\"@type\":\"d\",\"@rid\":\"#12:0\",\"@version\":1,\"@class\":\"PERSON\",\"perName\":\"Jay\",\"perSurname\":\"Miner\"},{\"@type\":\"d\",\"@rid\":\"#12:1\",\"@version\":4,\"@class\":\"PERSON\",\"perName\":\"Luca\",\"perPhones\":{\"office\":\" 3906222222\",\"mobile\":\" 393333888\"},\"perAge\":34,\"perCity\":\"Rome\"}]}"

that I convert to a list of rules

jsonRules = "result" /. ImportString[jsonResult, "JSON"]

I would like to apply two generic type of functions/operators. The first takes an ordered list of keys for the desired rearrangement and the second a list of ordering functions for the type of sorting each column will have. You can combine them of course but for clarity and learning purposes I would prefer to see them separately.

Something like....

rulesRearrange[
    {"perSurname","perName","perAge","perPhones","perCity","@rid","@version","@type","@class"},
    jsonRules]

and

rulesSortOrder[.....,jsonRules]

that will produce the following output

jsonRulesSorted = {{"perSurname" -> "Miner", "perName" -> "Jay", 
"perAge" -> Missing[], "perPhones" -> Missing[], 
"perCity" -> Missing[], "@rid" -> "#12:0", "@version" -> 1, 
"@type" -> "d", "@class" -> "PERSON"},{"perSurname" -> Missing[], "perName" -> "Luca", "perAge" -> 34,"perPhones" -> {"office" -> " 3906222222", 
  "mobile" -> " 393333888"}, "perCity" -> "Rome", 
"@rid" -> "#12:1", "@version" -> 4, "@type" -> "d", 
"@class" -> "PERSON"}};

that can be nicely presented and formatted with associations and Dataset

Dataset[ToAssociations[jsonRulesSorted]]

enter image description here

I am adding the following dataset for the sorting part of my question. I would like to rearrange it with the order of columns as {"a", "@x", "@y", "@z", "b", "c", "d", "e"}

lr = {{"a" -> 2, "@y" -> "PERSON", "@x" -> "#12:1", "c" -> 8, 
"@z" -> 4}, {"d" -> 5, "@x" -> "#12:5", "b" -> 4, "e" -> 9, "@y" -> "COMPANY","@z" -> 10},{"@x" -> "#12:8", "@y" -> "PERSON", "@z" -> 12, "a" -> 1, "b" -> 2,"c" -> 3, "e" -> 4},{"@x" -> "#12:8", "@y" -> "PERSON", "@z" -> 10, "a" -> 2, "b" -> 7,"e" -> 4}};

Then I would like to see this sorted by specifying the sorting order in the first two columns as Ascending and Descending respectively. This is similar to an excel column sorting feature.

$\endgroup$

1 Answer 1

4
$\begingroup$

Let me start by generating a Dataset from your JSON string. Note that I adjusted your expressions a little.

jsonRules = "result" /. ImportString[jsonResult, "JSON"];
originalds = Dataset[Association @@@ jsonRules]

Mathematica graphics

Rearranging the columns can be accomplished without a helper function. Instead, we pick columns in originalds to generate a new data set with the columns rearranged:

rearrangedcolumnds = originalds[All, {"perSurname", "perName", "perAge", "perPhones", 
                                      "perCity", "@rid", "@version", "@type", "@class"}]

Mathematica graphics


The second part of your question did not make a lot of sense to me as it is posed right now: if I sort any one column of a dataset, I would like all other columns to "follow suit" without breaking the connections within the data. For instance, imagine imposing a sort that swaps the entries in the first column; I would want entries in all other columns to swap with them, or otherwise the data would become jumbled!

For instance, suppose that you want to sort the dataset by age:

rearrangedcolumnds[SortBy["perAge"]]

Mathematica graphics

The result correctly swaps the whole second row with the first one, so that data pertaining to Luca are still kept together.

Perhaps I am misunderstanding your question though, in which case you might want to clarify it or otherwise comment.

$\endgroup$
4
  • $\begingroup$ Thanks for your reply, the first part (rearrangement) is answered fully. About the second part I would like to specify the sorting order (ascending/descending) in multiple columns. I am adding another dataset to test this. $\endgroup$ Jun 13, 2015 at 0:43
  • $\begingroup$ Note to self: look at 2934, 69396, 77806. $\endgroup$
    – MarcoB
    Jun 15, 2015 at 5:06
  • $\begingroup$ Thank you MarcoB, it seems there is a lot behind Sorting and Ordering mechanism in Mathematica and I have to find time to revisit and study all these relevant posts I found (26974, 19300, 9840, 2729, 7679, 2934) including yours 69396, 77806 to understand or come up with my own answer on this topic. I am still a youngster in Mathematica, but a fervent one ;-) $\endgroup$ Jun 16, 2015 at 9:01
  • $\begingroup$ @Athanassios The topic of sorting with multiple cascading criteria is actually surprisingly complicated in Mathematica, unfortunately. I think this complication has surprised quite a few newcomers, since it is a pretty basic functionality, and yet it is somewhat complicated to achieve! $\endgroup$
    – MarcoB
    Jun 16, 2015 at 11:40

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.