Filtering detail table based on selection in summary table in Orange 3.11

Question

I'm rather new to Orange, so apologies in advance if I'm missing something obvious or am trying to fit a square peg in a round hole.

I have two files, Summary and Detail, that I'm importing into an Orange workflow. Summary has one row per Contact, and Detail has multiple rows per Contact, each row corresponding to a particular action they've taken. Contacts are identified in both files by Contact ID.

I've done a bunch of analysis to identify Contacts in Summary that I'm interested in using Orange's machine learning algorithms. I'd like to be able to step through each row in Summary (that is, each Contact) to see what actions in Detail that the Contact took.

I thought I'd be able to use the Merge Data widget to do this by hooking up Selected Data from Summary as the Data and Data from Detail as the Extra Data using a Find Matching Rows join type. However, since each unique Contact ID appears multiple times in Detail, I'm not able to select it in the Merge Data widget.

I know it's nonsensical, but I did try to wire Data from Detail as the Data and Selected Data from Summary as the Extra Data with the only Merge Data option that worked (Append columns from Extra Data). This showed me all the data from Detail, but appended with columns from Summary when the appropriate row was selected in Summary. Based on my understanding of Merge Data, this is predicted but (in my use case) unhelpful behavior.

In database terms, I'm sort of trying to set up an inner join with a one-to-many relationship between Summary and Detail and, by selecting a row in Summary, to be able to see the related rows in Detail.

So, the bottom line is, is this sort of behavior doable in Orange, or am I trying to paint a house with a chainsaw? Is there another widget that would be more appropriate for the task? (I also tried the Select Rows widget, but couldn't figure out how to populate the filters based on the row that I had selected in Summary.)

Answer 1

A workflow like this might do the trick, although it is static (does not adjust to varying actions).

• Load the two files, Summary and Details.
• I suppose "Details" has columns such as "Action" and some value. In my example, Details will contain "Subject" and "Grade" for 3 highschool subjects.
• "Select columns" is optional (slim down unused columns)
• You need a "Select rows" for each subject/action, filtering that specific one.
• Every "Edit Domain" is used to rename the generic feature name to a specific on (like "Grade" to "Grade_Math").
• Results are then merged step-wise to the Summary (which in my example contains students, your summary contains some other people).

If the number of actions is limited, the time needed is reasonable.

If this is a one-time job, you might as well save the output (CSV) and use that in a different project.

If it needs to be more dynamic and adaptive, you'll have to dig into Python and use the Python code widget to create a list of actions, create a list of columns based on that, and then populate those with matching values.