Suppose I have imported two tables with a few hundred rows each, here are some snippets.
Table 1:
raceFriends = {{"6/5/2014", "Bob", 0, 1, 1}, {"6/25/2014", "Jill", 0,
1, -1}, {"6/25/2014", "Hannah", 0, 1, -1}, {"6/25/2014",
"Robert,Peter", 1, 2, -2}, {"7/11/2014", "Nancy,Fred", 1,
2, -2}, {"8/16/2014", "John", 0, 1, 1}, {"11/2/2014", "Bob,Jill",
1, 2, 2}, {"11/2/2014", "Hannah,Jack,Peter", 2,
3, -3}, {"11/5/2014", "Robert", 0, 1, -1}, {"11/5/2014", "Greg", 0,
1, 1}}
Table 2:
raceTimes = {{"6/5/2014",
1.04}, {"6/10/2014", -0.05}, {"6/15/2014", -1.21}, {"6/20/2014",
1.51}, {"6/25/2014", -0.42}, {"6/30/2014", 0.67}, {"7/10/2014",
0.09}, {"7/11/2014", -0.82}, {"8/16/2014", -1.44}, {"10/4/2014",
1.05}, {"11/2/2014", 1.98}, {"11/1/2014", 2.12}, {"11/5/2014",
0.04}}
and I want to do the following:
- Combine the duplicate dates in table 1 so that there is only one occurrence of each date in the data. The number values can simply be added and the names aren't a big deal if they are lost.
- Create a table that will append the corresponding value (from table 2) to each date (in table 1) as a 6th column.
Ideally, the end result should look like this:
raceTable = {{"6/5/2014", "Bob", 0, 1, 1, 1.04}, {"6/25/2014",
"Jill,Hannah,Robert,Peter", 1, 4, -4, -0.42}, {"7/11/2014",
"Nancy,Fred", 1, 2, -2, -0.82}, {"8/16/2014", "John", 0, 1,
1, -1.44}, {"11/2/2014", "Bob,Jill,Hannah,Jack,Peter", 3, 5, -1,
1.98}, {"11/5/2014", "Robert,Greg", 0, 2, 0, 0.04}}
Ultimately, the most important values here are the dates and the fifth and sixth columns. I am clueless as to how to combine the rows in the first table or lookup values in the second table to append to the first.
Any help would be greatly appreciated.