Message Boards Message Boards

0
|
3515 Views
|
3 Replies
|
2 Total Likes
View groups...
Share
Share this post:

Combining Rows with Strings & Look-up Values in a Second Table

Christian Rossanda
Posted 10 years ago

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.
POSTED BY: Christian Rossanda
3 Replies
Sort By:

Hi Christian,

here's a clumsy solution to get you started. It's not optimized for speed, so if your lists are long and you have to do this multiple times, you may have to play around with other approaches.

First I'll change raceFriends to gather up all rows that have the same first column:

resorted = Transpose /@ GatherBy[raceFriends, #[[1]] &]

From each of the sublists, I'll use the date, join all the names together, and take the total for each of the other original columns:

newRaceFriends = {#[[1, 1]], StringJoin[Riffle[#[[2]], ","]],Total[#[[3]]], Total[#[[4]]], Total[#[[5]]]} & /@ resorted

This isn't the only way to do this, but the result now looks like this:

{{"6/5/2014", "Bob", 0, 1, 1}, {"6/25/2014","Jill,Hannah,Robert,Peter", 1, 4, -4}, {"7/11/2014", "Nancy,Fred",1, 2, -2}, {"8/16/2014", "John", 0, 1, 1}, {"11/2/2014","Bob,Jill,Hannah,Jack,Peter", 3, 5, -1}, {"11/5/2014","Robert,Greg", 0, 2, 0}}

Now that the first list is in the desired format, I can join it with the second one. If the first column of newRaceFriends and raceTimes are identical, you can just do this:

raceTable=Join[newRaceFriends[[#]], {raceTimes[[#, 2]]}] & /@Range[Length[newRaceFriends]]

However, if you need to look up the dates explicitly, you could do something like this:

raceTable =Join[#, {raceTimes[[Position[raceTimes, #[[1]]][[1, 1]], 2]]}] & /@newRaceFriends

As I said above, this is certainly not the only way to do this and not too elegant either, so play around with the functions I used a bit more.
POSTED BY: Bianca Eifert

To familiarize myself with Associations, I tried to translate your data as associations, so that I could use GroupBy, JoinAcross and Dataset. Here is what I came up with:

toDate[dt_String] := DateObject[{dt, {"Month", "Day", "Year"}}];
raceFriendsAssociations0 = 
  Map[AssociationThread[{"date", "name", "score1", "score2", 
       "score3"} -> #] &, raceFriends];
raceFriendsAssociations = 
  Normal@GroupBy[raceFriendsAssociations0, First -> Rest, Total] /. 
    HoldPattern[Plus[strgs__]] :> 
     StringRiffle[{strgs}, ","] /. (dt_String -> assoc_Association) :>
     Association["date" -> toDate[dt], Normal[assoc]];
raceTimesAssociations = 
  Map[AssociationThread[{"date", "time"} -> #] &, 
   raceTimes /. (dt_String) :> toDate[dt]];
Dataset@JoinAcross[raceFriendsAssociations, raceTimesAssociations, 
  "date"]
POSTED BY: Gianluca Gorni

Thank you, Bianca and Gianluca! Both solutions work wonderfully.
POSTED BY: Christian Rossanda
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract