Table Joining with Addition Functions

I have two tables. They both have two columns. A category column and then a count for that category.

Example:
Table #1

Column 1 Column 2
Abuse 45

Table #2

Column 1 Column 2
Abuse 3

I want to join these two tables so that the category is summed.
In this example Abuse would be summed at 48.

Any non-matching rows would simply get their own row and count.

So in this example there is clearly two rows between the two tables with a category of Abuse.
However, maybe there’s a row category of ‘Antagonism’ that doesn’t have any other match. It gets its own row.

Jimbo

Use a Concatenate node, then a Groupby node. In the latter, add Column 1 to the grouping columns, and then in the aggregation tab add Column 2, and select ‘Sum’ as the aggregation method, and at the bottom of the dialog, select the ‘Keep Incoming Column Names’ option

Steve

Roger that -
Will look those up.

Thanks sir~

That was close, but the abuse is still at 45 when it should be at 48…

Jimbo

  1. What is shown exactly as result?
  • Just “Abuse | 45” => Please check output of concatenate node.
  • Both “Abuse | 45” and “Abuse | 3” => Check if both fields are really spelled the same (including leading / trailing spaces, special characters etc.).
  1. What kind of aggregation did you specify in the group by node?

How about to use the concatenate node and just put the two tables together and then use the group by node? And as @Patrick1974 mentioned: check for blanks or spellings. There should be a string manipulation node for that.

Here’s what the table looks like after concatenate

I’m thinking I’m probably going to have to manually input the data after I separate the initial tags column…

Your problem looks like you have 2 different column names in the two tables, Either rename the second table to match the first (Column Rename node), or use two Column Merger nodes, one for your categories columns and one for your counts columns,
Steve

1 Like

I didn’t even think to rename the columns. I’ll give that a try.

Jimbo

Vern - looks like the conc is working now?
However I can’t seem to get it to sum right…

Oh i think I see it…looks like there’s a space in the category column somehow added from the second table…

holy crap - got it to work…it was the white space that was stopping the group from working right. ha ~

Thanks for the help folks!

Jimbo

2 Likes

Now I just have to create this huge meta node to get it all into a single table. will take some time, but doable.

Jimbo