Math time series merge effectively

Question

what I'm trying to do seems common enough to be an effective solution.

I use mathematica and I have several different timeseries like {{date1, value1}, {date1, value1} ...} - the sort you can pass to DateListPlot.

However, the problem is that these datasets partially overlap (some may have data from 95-2004, some from 1999 to 2011, etc.)

Now what I would like to do is combine them into one big list with a common timeline, which is the Union [] of all available dates. Then for the values ​​there will be arrays, but with zeros where there is no data.

Is there an effective way to do this? I have hundreds of these timers and creating something that loops all this, probably not very efficient (and even quite tedious)

any help is greatly appreciated!

Answer 1

For instance,

ClearAll[l1, l2]; l1 = {{date1, value1}, {date1, value2}, {date2, value3}, {date4, value4}} l2 = {{date3, value5}, {date4, value5}, {date1, value6}} 

then

 DeleteDuplicates[Union[l1, l2], #1[[1]] \[Equal] #2[[1]] &] 

gives {{date1, value1}, {date2, value3}, {date3, value5}, {date4, value4}} . This means that if you have two data points for the same date, and they are different, they will be lost. It is not clear (to me) if this is what you need or not, so perhaps you could add more details.

On the other hand, it is

 Transpose[{DeleteDuplicates[ Last@Last @ Reap@Scan [Sow[#[[1]]] &, Union[l1, l2]]], Last@Reap [Scan[Sow[#[[2]], #[[1]]] &, Union[l1, l2]]]}] 

eliminates duplicate headings and collects values ​​under each heading, thus:

 {{date1, {value1, value2, value6}}, {date2, {value3}}, {date3, {value5}}, {date4, {value4, value5}}} 

(i.e. collects all values ​​for each date).

Some examples of what you want will be enjoyable.

Answer 2

If I understand your question correctly, you want

 l1 = {{date1, value1}, {date1, value2}, {date2, value3}, {date4, value4}} l2 = {{date3, value5}, {date4, value5}, {date5, value6}} 

To become

 l1 = {{date1, value1}, {date1, value2}, {date2, value3}, {date3, 0}, {date4, value4}, {date5,0}} l2 = {{date1, 0}, {date2, 0}, {date3, value5}, {date4, value5}, {date5, value6}} 

If so, maybe something like this:

 If[MemberQ[l1[[All,1]],#],Cases[l1,{#,_}],{#,0}]& /@ Union[l1[[All,1]],l2[[All,2]] ] 

Depending on how you need to process multiple data points on the same date in a given series, you may need to precede the Cases [] function with the Sequence @@ or First @ command, for example

 If[MemberQ[l1[[All,1]],#],Sequence @@ Cases[l1,{#,_}],{#,0}]& /@ Union[l1[[All,1]],l2[[All,1]] ] 

Now I'm at home, so this one was checked for syntax errors :-)

Answer 3

Thanks guys. I ended up making a decision that I took upon myself in all time frames. Keeping this in let say daterange, I then used Mapthread as follows.

 daterange= Union[DatesOfFirstTimeseries,DatesOfSecondTimeseries]; NewVersionOfFirstTimeSeries = (daterange /. MapThread[Rule, {DatesOfFirstTimeseries, ValuesOfFirstTimeseries}] /. MapThread[ Rule, {daterange, Table[Indeterminate, {Length[daterange]}]}]); NewVersionOfSecondTimeSeries = (daterange /. MapThread[Rule, {DatesOfSecondTimeseries, ValuesOfSecondTimeseries}] /. MapThread[ Rule, {daterange, Table[Indeterminate, {Length[daterange]}]}]); 

tjis did what I needed, but it really hurts my aesthetic outlook.