Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

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?

share|improve this question
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
    
What have you tried so far Alex ? –  image_doctor Jun 30 '13 at 17:50
    
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
show 1 more comment

2 Answers

up vote 2 down vote accepted

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;;]].

share|improve this answer
    
Nice start! However he doesn't want to select dates with empty values in one the tables. –  Rod Jun 30 '13 at 17:51
    
Well... let's just adjust the Cases pattern then. –  Theo Tiger Jun 30 '13 at 17:55
    
@TheoTiger many thanks nice codes! –  Alex Jun 30 '13 at 19:14
add comment

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}] 
share|improve this answer
    
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
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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.