0

I have two data sets:

data1 =
{
  {{2004, 1, 1}, 28.0774}, {{2004, 1, 2}, 33.0775}, {{2004, 1, 3}, 33.6817}, 
  {{2004, 1, 4}, 31.41}, {{2004, 1, 5}, 31.6763}, {{2004, 1, 6}, 28.255}, 
  {{2004, 1, 7}, 29.4663}, {{2004, 1, 8}, 28.8583}, {{2004, 1, 9}, 27.3979}, 
  {{2004, 1, 10}, 26.2367}, {{2004, 1, 11}, 25.9692}, {{2004, 1, 12}, 28.7213}, 
  {{2004, 1, 13}, 27.405}, {{2004, 1, 14}, 27.6808}, {{2004, 1, 15}, 29.0129}, 
  {{2004, 1, 16}, 28.65}, {{2004, 1, 17}, 27.6096}, {{2004, 1, 18}, 27.1733}, 
  {{2004, 1, 19}, 30.3533}, {{2004, 1, 20}, 32.5442}, {{2004, 1, 21}, 35.2171}, 
  {{2004, 1, 22}, 35.7233}, {{2004, 1, 23}, 31.0108}, {{2004, 1, 24}, 27.9142}, 
  {{2004, 1, 25}, 26.8617}, {{2004, 1, 26}, 30.0146}, {{2004, 1, 27}, 30.4133}, 
  {{2004, 1, 28}, 29.8754}, {{2004, 1, 29}, 29.8067}, {{2004, 1, 30}, 29.7713}, 
  {{2004, 1, 31}, 27.4904}, {{2004, 2, 1}, 28.6221}, {{2004, 2, 2}, 30.9058}, 
  {{2004, 2, 3}, 28.1475}, {{2004, 2, 4}, 27.0221}, {{2004, 2, 5}, 26.9771}, 
  {{2004, 2, 6}, 26.5542}, {{2004, 2, 7}, 25.5142}, {{2004, 2, 8}, 25.7975}, 
  {{2004, 2, 9}, 28.3658}, {{2004, 2, 10}, 30.0617}
}
data2 =
{
  {{2004, 1, 2}, ""}, {{2004, 1, 5}, 33.71}, {{2004, 1, 6}, 33.54}, 
  {{2004, 1, 7}, 33.57}, {{2004, 1, 8}, 34.27}, {{2004, 1, 9}, 34.38}, 
  {{2004, 1, 12}, 34.92}, {{2004, 1, 13}, 34.26}, {{2004, 1, 14}, 34.62}, 
  {{2004, 1, 15}, 33.61}, {{2004, 1, 16}, 35.16}, {{2004, 1, 19}, ""}, 
  {{2004, 1, 20}, 36.21}, {{2004, 1, 21}, 35.53}, {{2004, 1, 22}, 35.12}, 
  {{2004, 1, 23}, 34.94}, {{2004, 1, 26}, 34.41}, {{2004, 1, 27}, 33.99}, 
  {{2004, 1, 28}, 33.63}, {{2004, 1, 29}, 32.86}, {{2004, 1, 30}, 33.16}, 
  {{2004, 2, 2}, 34.02}, {{2004, 2, 3}, 34.2}, {{2004, 2, 4}, 33.06}, 
  {{2004, 2, 5}, 33.26}, {{2004, 2, 6}, 32.49}, {{2004, 2, 9}, 32.91}, 
  {{2004, 2, 10}, 34.03}
}

Sometimes values are missing and sometime both values and dates are missing. I need just the records that are complete and common to both data sets. 

data = Transpose[{data1value, data2value}];
mixeddata = {{Date}, data1value, data2value};

How could I write the code for this?

m_goldberg
  • 107,779
  • 16
  • 103
  • 257
Alex
  • 1,235
  • 8
  • 23
  • 1
    Possible duplicate of this post. – Rod Jun 30 '13 at 17:04
  • @RodLm on that problem they want to add the missing data as well which I dont want that.The questions are not the same. – Alex Jun 30 '13 at 17:06
  • I don't see how this question differs materially from your previously asked and answered question – m_goldberg Jun 30 '13 at 18:14
  • @m_goldberg it differs.For an starter who has a lot of things to do and at the same time debug the previous problem to solve new cases it differs.I am learning and solving my problems at the same time and you can see here commands are totally different.I know they are boring to you as professionals; but thanks for understanding. – Alex Jun 30 '13 at 18:21
  • "you as professionals" -- what a joke! Read my profile to see how much of a professional I am. – m_goldberg Jun 30 '13 at 20:28

2 Answers2

2

This is some code I have found on stackoverflow and used frequently:

MergeTables[data1_, data2_, pos1_, pos2_] := Cases[data1, x:{{__},_Real} :> Block[
    {y = Cases[data2, z:{{__},_Real} /; z[[pos2]] == x[[pos1]]]},
    Sequence @@ (Join[x, Delete[#, Thread@{pos2}]] & /@ y)
    ]
]

EDIT: now skipping empty entries by using x:{{__},Real} as the pattern, not just x.

For your example, usage would be MergeTables[data1,data2,1,1].

This will yield one list with entries {{date}, value1, value2}. If you need to separate your values into two lists again, you can use Transpose@list[[All,2;;]].

Community
  • 1
Theo Tiger
  • 1,273
  • 8
  • 10
1

I am afraid my solution may not taste much like Mathematica, but it will do the job. Here the trick is done by using NumberQ. NumberQ first ensures if there is a valid data value in first data file for a date; and then the Do loop search for the same date in second file. If the same date is present in the second file then NumberQ again checks if there is a data value and finally store the result in data. ndata is the number of final data. 

ndata1 = Length[data1];
ndata2 = Length[data2];
(*To find the # of data in each file*)

k = 1;
Table[
 If[
  NumberQ[data1[[i]][[2]]] == True , (*NumberQ checks if there is a data*)
  Do[
   If[NumberQ[data2[[j]][[2]]] == True  && data2[[j]][[1]] == data1[[i]][[1]], 
    data[k] = {data2[[j]][[1]], data1[[i]][[2]], data2[[j]][[2]]}; k++; Break[]
   ], {j, 1, ndata2}
  ]
 ], {i, 1, ndata1}
]; ndata = k - 1;

newdata = Table[data[i], {i, 1, ndata}]

You can separate the data values as well

datavalue=Table[{data[i][[2]],data[i][[3]]}, {i, 1, ndata}]
Sumit
  • 15,912
  • 2
  • 31
  • 73
  • Indeed, in Mathematica you would only iterate through lists on rare occasions. Try to use Map (/@) if you want to operate on each element of a list, and make use of patterns. You can use Length to get the length of the first dimension of a list. – Theo Tiger Jun 30 '13 at 19:13
  • @TheoTiger the codes wont work in my com by the way – Alex Jun 30 '13 at 19:21
  • Thanks @TheoTiger. Although I am not sure if Map can do any significant reduction. Because it is more like comparing than operating. It would be great if you can give some reference on the use of Map for comparing two data sets. – Sumit Jun 30 '13 at 19:37
  • 1
    @Alex, can you check once more. I missed one } in the last table (datavalue). Otherwise the code works fine. And you have execute data1 and data2 before executing the code. – Sumit Jun 30 '13 at 19:42
  • Well, for instance you could Map through data1. For each element in data1, you look for a corresponding element in data2: – Theo Tiger Jun 30 '13 at 20:47
  • 1
    {#[[1]], #[[2]], Sequence @@ Cases[data2, {#[[1]], x_Real} :> x]} & /@ data1 Note how Sequence is used to splice the list returned by Cases directly into the {} list. As a hint, Sequence is generally Apply'ed (Sequence@@ in short). Is the rest of this code clear? By the way, this example code will not remove elements which are only in data1 but not in data2. – Theo Tiger Jun 30 '13 at 20:54
  • Thanks @TheoTiger. It is very helpful. And if for a date there is a data in first set and no data in second one, the NumberQ within the Do will return False and there will be no output. – Sumit Jul 01 '13 at 19:49