I am given a data frame (Table 1) with the following format. It has only col1 and col2, and json_col.

id    col1   col2   json_col
 1     a      b        json1
 2     a      c        json2
 3     b      d        json3
 4     c      a        json4
 5     d      e        json5

I have a new table (Table 2) and I would like to join json files in my new table

col1   col2   col3  col4  union_json
a      b                 json1
a      b      d          json1 and json3 union
a      b      d     e    json1, json3, and json5 union 
c      a                 json4

Here is an example of Table 1

df = pd.DataFrame({'col1': ['a', 'a', 'b', 'c', 'd'],
                  'col2': ['b', 'c', 'd', 'a', 'e'],
                  'col3': [{"origin":"a","destination":"b", "arc":[{"Type":"763","Number":"20"}]},
                        {"origin":"a","destination":"c", "arc":[{"Type":"763","Number":"50"}]},
                        {"origin":"a","destination":"d", "arc":[{"Type":"723","Number":"40"}]},
                        {"origin":"c","destination":"a", "arc":[{"Type":"700","Number":"30"}]},
                        {"origin":"d","destination":"e", "arc":[{"Type":"700","Number":"40"}]}]})

And, here is an example of Table 2:

df = pd.DataFrame({'col1': ['a', 'a', 'a', 'c'],
                  'col2': ['b', 'b', 'b', 'a'],
                  'col3': ['', 'd', 'd', ''],
                  'col4': ['', '', 'e', '']})

The union of json1 and json2 should look like this:

 [[{"origin":"a","destination":"b", "arc":[{"Type":"763","Number":"20"}]}], 
 [{"origin":"a","destination":"d", "arc":[{"Type":"723","Number":"40"}]}]]

CodePudding user response：

I hope I've understood your question right:

from itertools import combinations


def fn(x):
    out, non_empty_vals = [], x[x != ""]

    for c in combinations(non_empty_vals, 2):
        out.extend(df1.loc[df1[["col1", "col2"]].eq(c).all(axis=1), "col3"])

    return out


df2["union_json"] = df2.apply(fn, axis=1)
print(df2.to_markdown(index=False))

Prints:

Dataframes used:

df1

  col1 col2                                                                           col3
0    a    b  {'origin': 'a', 'destination': 'b', 'arc': [{'Type': '763', 'Number': '20'}]}
1    a    c  {'origin': 'a', 'destination': 'c', 'arc': [{'Type': '763', 'Number': '50'}]}
2    b    d  {'origin': 'a', 'destination': 'd', 'arc': [{'Type': '723', 'Number': '40'}]}
3    c    a  {'origin': 'c', 'destination': 'a', 'arc': [{'Type': '700', 'Number': '30'}]}
4    d    e  {'origin': 'd', 'destination': 'e', 'arc': [{'Type': '700', 'Number': '40'}]}

df2

  col1 col2 col3 col4
0    a    b          
1    a    b    d     
2    a    b    d    e
3    c    a