# union several arrays of same flag(date for example) efficiently

Suppose I have several arrays from data files which look like:

arrayi = {{datei1, xi11, xi21, ...}, {datei2, xi12, xi22, ...}, ..., {datein, xi1n, xi2n ,...}}


The arrayis are from different data sources so Length@arrayi and Length@arrayj is generally different. For certain arrayi, the flags (datei1, for example) might not be continuous (on some days the observation were missed).

I want to combine all the arrayis data for comparison, so data with the same flag (dates in this example) should be selected and put together.

Consider a simple example, in which there are only two test data arrays:

len1 = 1000;
array1 =
RandomSample[
Transpose@{
DateRange[{2001, 1, 1}, DatePlus[{2001, 1, 1}, len1 - 1]],
Range@len1 + RandomReal[{0, 1}, len1]
},
Round@(0.95*len1)
]


and

len2 = 1500;
array2 =
RandomSample[
Transpose@{
DateRange[{2001, 1, 7}, DatePlus[{2001, 1, 7}, len2 - 1]],
Range@len2,
(Range@len2)^2 + RandomReal[{0, 1}, len2]
},
Round@(0.95*len2)
]


where Dimensions@array1 is {95, 2} and Dimensions@array2 is {142, 3}.

I can combine data of same date in array1 and array2 by

combinearray1array2 =
DeleteCases[
(i = 0; {i++; date = #[[1]]; #, Select[array2, #[[1]] == date &]} & /@ array1),
x_ /; x[[2]] == {}
];


In fact I don't care the exact form of combinearray1array2, I only want that every line of combinearray1array2 contains information from both array1 and array2 of same flag (which is date here).

However, this code is very slow and will be slower for 3 or more arrays. Are there any other ways for the same job?

Another way which I find is fractionally faster than @march's and I think scales better when combining information from more than 2 arrays (as you say you are interested in) is simply:

    GroupBy[Join[array1, array2, array3], First -> Rest, Join];


This produces a well formatted output straight away (imo), for the shorter length 10 and 15 arrays:

For timings I find with the len 1000 and 1500 arrays:

• @Harry's method - 1.24 s
• @march's method - 0.0083 s
• This method - 0.0057 s

When also incorporating a 3rd array of length 2000:

• @march's method - 0.01 s
• This method - 0.0089 s

EDIT:

As @eldo pointed out, this gives the combination of the two data sets, not just those entries for comparison. For that we can simply take:

    assoc = GroupBy[Join[array1, array2, array3], First -> Rest, Join];
Select[assoc, Length[#] == 2 &]


which gives:

Timing wise it's now essentially the same as @march, maybe slightly more compact when considering more than 2 arrays.

• This doesn't give at all what the OP asked for – eldo Nov 28 '15 at 11:58
• How so? Have I misunderstood something? Fundamentally if does what is asked - combine the data from separate arrays under the same flag. For example, for {2001,1,7} we have both the record from array 1 {7.69853} and those from array 2 {1, 1.0447}. Yes they are separate, but maybe Harry still wants that, if not, flattening it is trivial. Yes the solution is still as associations, but again converting to a list is trivial. Have I missed something? – Quantum_Oli Nov 28 '15 at 12:27
• Ah, I think I do see how my answer is not quite what was requested, standby, I'll see if I can sort it. – Quantum_Oli Nov 28 '15 at 12:33
• You should only keep items like key -> {_List, _List}, i.e. overlapping dates – eldo Nov 28 '15 at 12:37
• Indeed, I've updated my answer correspondingly. Thanks for pointing it out! – Quantum_Oli Nov 28 '15 at 12:47

We will use Associations. There are many ways to form these Associations from your data. I will choose one and re-format it at the end. Using your provided data, we form Associations that use the date as the Key:

assoc1 = Association[Rule @@@ array1];
assoc2 = Association[#1 -> {##2} & @@@ array2];


We then "intersect" these intersections, taking only the Keys that are the same:

assoc = KeyIntersection[{assoc1, assoc2}];


When I ran AbsoluteTiming on your code and the Association code, it took 1.84 seconds and 0.012 seconds, respectively, which is a pretty nice speed-up.

To get back the data in the form that you have it, we can do

Apply[{#1, Sequence @@ #2} &, Transpose@Normal@assoc, {2}]


For the purposes of illustration, let's consider the following, much smaller example:

len1 = 10;
array1 =
RandomSample[
Transpose@{
DateRange[{2001, 1, 1}, DatePlus[{2001, 1, 1}, len1 - 1]],
Range@len1 + RandomReal[{0, 1}, len1]
},
Round@(0.95*len1)
]
len2 = 15;
array2 =
RandomSample[
Transpose@{
DateRange[{2001, 1, 7}, DatePlus[{2001, 1, 7}, len2 - 1]],
Range@len2,
(Range@len2)^2 + RandomReal[{0, 1}, len2]
},
Round@(0.95*len2)
]


combinearray1array2 =
DeleteCases[
(i = 0; {i++; date = #[[1]]; #, Select[array2, #[[1]] == date &]} & /@ array1),
x_ /; x[[2]] == {}
]


My code is

assoc1 = Association[Rule @@@ array1]
assoc2 = Association[#1 -> {##2} & @@@ array2]
assoc = KeyIntersection[{assoc1, assoc2}]


To get back the data in the form that you have it, we can do

Apply[{#1, Sequence @@ #2} &, Transpose@Normal@assoc, {2}]


yielding