3
$\begingroup$
{<|"date" -> "2016-04-12", "key" -> 1, 
 "1st" -> 0.9652777777777778`, "2nd" -> 0.8867924528301887`, "3rd" -> 0.49074074074074076`|>,
 <|"date" -> "2016-04-12", "key" -> 2, 
 "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>,
 <|"date" -> "2016-04-13" , "key" -> 1, 
 "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>,
 <|"date" -> "2016-04-13" , "key" -> 2, 
 "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>
} // Dataset

Consider we are constructing a feature dataset for an id[product,user]. Feature is time related, like action_behavior, and we need some feature for a user in a period.

Should I do this in a Association structure? since we have JoinAcross? not a Dataset structure?

The example shows that "key" 1 has feature in 04-12 and feature in 04-13. What I want to do is, when I get data from hive, to construct this kind of tables

select a.key,a.1_st,a.2_st,b.1_st,b.2_st from select a.key a.1_st a.2_st from table_1 where date='date' a left outer join b.key b.1_st b.2_st where date='date-1' b on a.key1=b.key2

I mean the result table is one dataset only, and there are many dates rows beside 2016-04-12, 2016-04-13:

enter image description here

$\endgroup$
  • $\begingroup$ But what is a query input, a date or a period of time? $\endgroup$ – Kuba Apr 13 '16 at 9:11
  • $\begingroup$ @Kuba [a date+a period time], if just last day, then a date is enough $\endgroup$ – HyperGroups Apr 13 '16 at 13:56
3
$\begingroup$

Dataset excels at representing hierarchical data. If you look at your problem as creating a hierarchy of records with levels "key" and "date" then a Dataset solution quickly presents itself.

To make querying easier I first ensure all records contain all keys with KeyUnion. Then create the Dataset.

dat = Dataset@KeyUnion@associations

Mathematica graphics

Now with GroupBy a single hierarchical Dataset can be created.

gdat = dat[GroupBy[#"key" &], GroupBy[#"date" &]]

Mathematica graphics

It is kind of difficult to see the structure so we can implicitly use Dataset to show it. Note this is only needed to show the structure in a visually friendly way. You don't need to do this on your larger dataset.

gdat[All, Dataset, Dataset]

Mathematica graphics

Now you can query the hierarchical dataset for your values.

gdat[1, All, All, "1st"]

Mathematica graphics

gdat[All, "2016-04-12", All, "3rd"]

Mathematica graphics

gdat[2, "2016-04-12"]

Mathematica graphics

And so on.

Hope this helps.

$\endgroup$
0
$\begingroup$

Not sure if this can be done using JoinAcross.

associations = {
   <|"date" -> "2016-04-12", "key" -> 1, "1st" -> 0.9652777777777778`, 
    "2nd" -> 0.8867924528301887`, "3rd" -> 0.49074074074074076`|>,
   <|"date" -> "2016-04-12", "key" -> 2,
    "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>,
   <|"date" -> "2016-04-13", "key" -> 1,
    "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>,
   <|"date" -> "2016-04-13", "key" -> 2,
    "2nd" -> 0.14619883040935672`, "3rd" -> 0.15212981744421908`|>};

Dataset[associations]

enter image description here

dates = Flatten@Values[Union@KeyTake[associations, "date"]];

Dataset /@ Map[Function[date, Select[#["date"] == date &]@associations], dates]

enter image description here

$\endgroup$
  • 1
    $\begingroup$ An alternative: Dataset /@ GatherBy[associations, #date &]. $\endgroup$ – user31159 Apr 13 '16 at 12:21
  • $\begingroup$ There is a problem to join two dataset into one, I've update my pic. $\endgroup$ – HyperGroups Apr 13 '16 at 13:49
  • $\begingroup$ @Xavier hi, GatherBy/GroupBy just split is a thread, and then should filter by dates, and to join the results. See my updated picture. consider the situation, there are 10 dates, and for a specific date, I just want the right part is the last date's data in the right part. $\endgroup$ – HyperGroups Apr 13 '16 at 13:51
  • $\begingroup$ @HyperGroups Will the dates given in the list of associations always be ordered? Are you dealing always with 2 keys "keys" for each date, or can you have any number of it? $\endgroup$ – user31159 Apr 13 '16 at 14:47

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.