Duplicate rows with multiple entries

Question

I have a huge dataset, which looks similar to this:

data = Dataset[{
<|"a" -> 3, 
"b" -> 5, 
"c" -> Missing["NoInput"], 
"d" -> 2600, 
"e" -> 80, 
"f" -> 300, 
"g" -> {4114}, 
"h" -> {47.4718}, 
"i" -> {7.5088}|>,
<|"a" -> 4, 
"b" -> 4, 
"c" -> Missing["NoInput"], 
"d" -> 1800, 
"e" -> Missing["NoInput"], 
"f" -> Missing["NoInput"], 
"g" -> {3001, 3014, 3029}, 
"h" -> {46.9805, "XXX", 46.9339}, 
"i" -> {7.452, "XXX", 7.3891}|>}]

Is there a way to split the entries from “g”, “h” and “i” (they have always the same amount of entries) in new rows if they consist of more than one value. The values at the same position belong always together.

The newly added rows should take the entries from “a” – “f” and the spitted values from “g”, “h” and “i”.

My description is a little confusing. But the solution should look like this.

datafinal = Dataset[{
<|"a" -> 3, 
"b" -> 5, 
"c" -> Missing["NoInput"], 
"d" -> 2600, 
"e" -> 80, 
"f" -> 300, 
"g" -> 4114, 
"h" -> 47.4718, 
"i" -> 7.5088|>,
<|"a" -> 4, 
"b" -> 4, 
"c" -> Missing["NoInput"], 
"d" -> 1800, 
"e" -> Missing["NoInput"], 
"f" -> Missing["NoInput"], 
"g" -> 3001, 
"h" -> 46.9805, 
"i" -> 7.452,|>,
<|"a" -> 4, 
"b" -> 4, 
"c" -> Missing["NoInput"], 
"d" -> 1800, 
"e" -> Missing["NoInput"], 
"f" -> Missing["NoInput"], 
"g" -> 3029, 
"h" -> 46.9339, 
"i" -> 7.3891|>}]

“XXX” entries should left ignored.

I tried to create nested lists in “g”, “h” and “i” but never came to a solution.

Answer 1

This looks like a threading problem. The idea seems to be to thread the rows of the dataset over any lists in their columns (essentially to reverse a Merge-type operation). I don't know of any nice high-level built-in ways to do this, but it is easy enough to construct such a function by hand.

First, we can thread the associations:

threadAssociation[a_] := AssociationThread[Keys@a -> #] & /@ Thread[Values@a]

threadAssociation@<|"a" -> 5, "b" -> {"x", "y", "z"}, "c" -> {5, 6, 7}|>

{<|"a" -> 5, "b" -> "x", "c" -> 5|>,
 <|"a" -> 5, "b" -> "y", "c" -> 6|>, 
 <|"a" -> 5, "b" -> "z", "c" -> 7|>}

Then, we can create a function to apply it to a dataset by mapping and flattening.

threadDataset[d_] := Flatten[threadAssociation /@ d]

datafinal = threadDataset@data

The OP also stated that "XXX"-type entries should be ignored, which I take to mean that they should not be present in the final product. With this method, the simplest way one can accomplish this is to delete any rows containing the unwanted elements from the final dataset

datafinal // DeleteCases[KeyValuePattern[_ -> "XXX"]]

but one could equally well perform this deletion during or after each threadAssociation call.

Notes

1. The approach above threads over all lists. If one wants to thread over some lists and not others (for instance, the OP indicated that the threading is really only needed for the "g", "h", and "i" columns of the dataset -- perhaps, these are the only columns that contain lists, but perhaps there are lists in the other columns that shouldn't be threaded), Thread has an optional third argument that can be used to specify which positions are threaded over. To use it we can construct threadAssociation and threadDataset to take another argument:

   threadAssociation[a_, n_:All] := Map[
     AssociationThread[Keys@a -> #] &,
     Thread[Values@a, List, n]]
   
   threadDataset[d_, n_:All] := Flatten[threadAssociation[#, n] & /@ d]
   

   As an example, consider

   threadDataset[data, {8, 9}]