Missing["KeyAbsent", somekey] (How to extract somekey)?

Questions
,
1

Hello, I am using the KeyUnion function to combine a couple of lists of lists. In some cases, a key exists in one list of sublists, but not in the other.

Here is an example:

assoc1 = <|1 -> {1, a, aa},3 -> {3, c, cc}|>

assoc2 = <|1 -> {1, 10, 100, 1000}, 5 -> {5, 50, 500, 5000}|>

I do a KeyUnion…

assocList = KeyUnion[{assoc1, assoc2}]

…to get something like this…

assocList = { <|1 -> {1, a, aa},3 -> {3, c, cc}, 5 -> Missing["KeyAbsent", 5]|>, <|1 -> {1, 10, 100, 1000},  
     3 -> Missing["KeyAbsent", 3], 5 -> {5, 50, 500, 5000}|>}

Now I want to merge the two lists, based on the key, replacing the missing values by some Key, Value pair that includes “dummy” value such as 5->{-9999}. The following piece of code, for example, works nicely for an individual replacement (eg. Key “3”):

Merge[{assocList[[1]], assocList[[2]]}, Identity]
merged = Values[%] /. _Missing -> {3, -9999};

To get…

{{{1, a, aa}, {1, 10, 100, 1000}}, {{3, c, cc}, {3, -9999}},...}

But you also get…

 {...,{{3,-9999}, {5, 50, 500, 5000}},...}

Which is not what we want. (The ultimate goal is to make a Union between the two lists associated with each key, where the first item in the list is also the same as the key.)

What I am looking for is a way to make a {Key, Value} pair replacement for each missing key. Is there a way to Is there a way to pick out the “index” part of the Missing[KeyAbsent, index] so that I can write a replacement rule that iterates over the keys?

2

This

    In[37]:= Clear [assoc1, assoc2]
    assoc1 = <|1 -> {1, a, aa}, 3 -> {3, c, cc}|>;
    assoc2 = <|1 -> {1, 10, 100, 1000}, 5 -> {5, 50, 500, 5000}|>;
        
    In[41]:= assocList = KeyUnion[{assoc1, assoc2}, <|1 -> {1}, 3 -> {3}, 5 -> {5}|>]
    Out[41]= {<|1 -> {1, a, aa}, 3 -> {3, c, cc}, 5 -> {5}|>,
              <|1 -> {1, 10, 100, 1000}, 3 -> {3}, 5 -> {5, 50, 500, 5000}|>}
        
    In[44]:= Merge[assocList, Identity]
    Out[44]= <|1 -> {{1, a, aa}, {1, 10, 100, 1000}}, 3 -> {{3, c, cc}, {3}}, 5 -> {{5}, {5, 50, 500, 5000}}|>

does not help? From the manual for KeyUnion:

The missing function can be an association:

the only thing yet to be done is creating the missing function association automatically from the all the present keys which seems possible but now I have to get the gifts out of the wrap … Merry Christmas everyone!

3

the only thing yet to be done is creating the missing function
association automatically

In[19]:= Clear [assoc1, assoc2, assoc3, assoK]
assoc1 = <|1 -> {1, a, aa}, 3 -> {3, c, cc}|>;
assoc2 = <|1 -> {1, 10, 100, 1000}, 5 -> {5, 50, 500, 5000}|>;
assoc3 = <|2 -> {4, 8, 16}, 4 -> {16, 64}, 7 -> {"The", "whole", "nine", "yards"}|>;
assoK = Association @@ (Rule[#, {#}] & /@ Union[Flatten[Keys /@ {assoc1, assoc2, assoc3}]])
Out[23]= <|1 -> {1}, 2 -> {2}, 3 -> {3}, 4 -> {4}, 5 -> {5}, 7 -> {7}|>

In[24]:= Clear[assocList]
assocList = KeyUnion[{assoc1, assoc2, assoc3}, assoK]
Out[25]= {<|1 -> {1, a, aa}, 3 -> {3, c, cc}, 5 -> {5}, 2 -> {2}, 4 -> {4}, 7 -> {7}|>, 
          <|1 -> {1, 10, 100, 1000}, 3 -> {3}, 5 -> {5, 50, 500, 5000}, 2 -> {2}, 4 -> {4}, 7 -> {7}|>, 
          <|1 -> {1}, 3 -> {3}, 5 -> {5}, 2 -> {4, 8, 16}, 4 -> {16, 64}, 7 -> {"The", "whole", "nine", "yards"}|>}

In[26]:= Merge[assocList, Identity]
Out[26]= <|1 -> {{1, a, aa}, {1, 10, 100, 1000}, {1}}, 
           3 -> {{3, c, cc}, {3}, {3}}, 
           5 -> {{5}, {5, 50, 500, 5000}, {5}}, 
           2 -> {{2}, {2}, {4, 8, 16}}, 
           4 -> {{4}, {4}, {16, 64}}, 
           7 -> {{7}, {7}, {"The", "whole", "nine", "yards"}}|>

of course, in defining assoK one could give a value more reminiscent about a missing value like the NULL of SQL …

4

I’ve mulled this one over, and unless I am missing something, you are handling the “missing” values by not generating “missing” in the first place. Perhaps that is my fault, for constructing a “toy example” that is not well suited to illustration. Usually, I would be starting with a dataset from an outside source, “from the wild,” where I do not have any control over the code or system that actually generates the sample data.

Here, I will present a typical example I would encounter importing real-world data:

In my sample data, I have two kinds of Missing …

Missing["Unrecognized", "21.799999"]

Missing["Empty"]

To give a little context, these are being imported as a Dataset (in the Mathematica 10 sense) using Semantic Import[]. I can extract values from a column called “Days to Start a Business” that would typically contain a numeric value:

in := $CurrentData[All, "Days to Start a Business"] // Normal
out := {5, 4, Missing["Unrecognized", 
  "21.799999"], 18, 32, 13, 5, 10, 101, 15, 22, 60, 15, 101, 8, 17, 8,
  Missing["Unrecognized", "16.5"], 84, 15, 27, 2, 14, 
 Missing["Unrecognized", "19.5"], 16, 9, 19, 97, 14, 
 Missing["Unrecognized", "26.1"], 
 Missing["Unrecognized", "75.5"], 32, 31, 8, 92, 29, 
 Missing["Unrecognized", 
  "4.5"], 8, 40, 11, 19, 11, 11, 13, 17, 36, 17, 
 Missing["Unrecognized", 
  "30.799999"], 19, 53, 35, 36, 7, 9, 5, 6, 12, 9, 
 Missing["Empty"], 11, 36, 38, 13, 33, 19, 32, 21, 
 Missing["Unrecognized", "7.5"], 34, 40, 
 Missing["Unrecognized", "7.5"], 90}

For those that are Missing[“Unrecognized”, “7.5”], one might choose to handle them by mapping some function onto the “unrecognized” values (eg. applying a rounding strategy to convert to the nearest integer) and replacing. I should note, I haven’t yet explored an alternative possibility: specifying the type explicitly as a decimal in the semantic import statement. This function and its options are new to me.

For the values that are simply Missing[“Empty”] a different approach would be required. In practice, the empty or “missing” values might be replaced by either the Min or Max of the non-empty members of the set.

But in explaining this in more detail, I am prompted to look into the documentation on “Missing Values” under SemanticImport, and will follow up if/when I find a solution.

5

you are handling the “missing” values by not generating “missing” in
the first place

Right.

a typical example I would encounter importing real-world data

If the source is not confidential, can you please attach it as it is for on-going experimentation on it?

6

Yes, please do. The source is not confidential. I am attaching a text file.

7

Not much to do with this. I loaded the file down to the local machine and could open it with MS Excel but

In[15]:= dS = SemanticImport[FileNameJoin[{NotebookDirectory[], "others", "ramsey", "timeseries_fy14.csv"}]]
Out[15]= $Failed

In[16]:= FileNameJoin[{NotebookDirectory[], "others", "ramsey", "timeseries_fy14.csv"}]
Out[16]= "N:\\Udo\\Abt_N\\others\\ramsey\\timeseries_fy14.csv"

In[22]:= FileNames[FileNameJoin[{NotebookDirectory[], "others", "ramsey", "*"}]]
Out[22]= {"N:\\Udo\\Abt_N\\others\\ramsey\\Example_Missing.txt", \
          "N:\\Udo\\Abt_N\\others\\ramsey\\timeseries_fy14.csv"}

In[18]:= sales = SemanticImport["ExampleData/RetailSales.tsv"]
Out[18]= $Failed

SemanticImport has a denial of service, even with the example from the help (RetailSales.tsv). Is the user supposed the preprocess the file before SemanticImport?

In[25]:= Clear[dS]
dS = SemanticImport[FileNameJoin[{NotebookDirectory[], "others", "ramsey", "timeseries_fy14.csv"}],
  Automatic, "Rows", "CharacterEncoding" -> "Unicode", HeaderLines -> 1]
Out[26]= $Failed