I'm trying to write a code to save in a matrix the common elements between some lists. Example:

Data frame with all the lists:

I start with an empty matrix having G1,G2,G3,G4 as columns and rows names and the cells filled with nan, the result I would like to obtain is the following:

This is my code:

import sys
import pandas as pd

def intersection(lst1, lst2):
    return [value for value in lst1 if value in lst2]

data = pd.read_csv(sys.argv[1], sep="\t")
p_mat = pd.read_csv(sys.argv[2], sep="\t", index_col=0)
c_mat = pd.read_csv(sys.argv[3], sep="\t", index_col=0)

#I need this since the elements of the second column once imported are seen as a single string instead of being lists
for i in range(0,len(data)):
    data['MP term list'][i] = data['MP term list'][i].split(",")


for i in p_mat:
    for j in p_mat.columns:
        r = intersection(data[data['MGI id'] == i]['MP term list'].values.tolist()[0],data[data['MGI id'] == j]['MP term list'].values.tolist()[0])
        if len(r)!=0:
            p_mat.at[i,j] = r
        else:
            p_mat.at[i, j] = None
        del(r) 

For now I'm able to fill only the first cell correctly, then at the first non-empty result that I try to store in a cell I get this error:

ValueError: Must have equal len keys and value when setting with an iterable

How can I fix it? Thank you for all the help

CodePudding user response：

Try with a cross merge, set intersection and pivot:

df["elements"] = df["elements of the ID"].str.split(",").map(set)

cross = df[["ID", "elements"]].merge(df[["ID", "elements"]], how="cross")
cross["intersection"] = (cross.apply(lambda row: row["elements_x"].intersection(row["elements_y"]), axis=1)
                              .map(",".join)
                              .replace("",None)
                        )

output = cross.pivot("ID_x", "ID_y", "intersection").rename_axis(None, axis=1).rename_axis(None)

>>> output
             G1     G2        G3    G4
G1  P2,P1,P3,P4     P3     P1,P3  None
G2           P3  P3,P5     P3,P5  None
G3        P1,P3  P3,P5  P1,P3,P5  None
G4         None   None      None    P6

Input df:

df = pd.DataFrame({"ID": [f"G{i 1}" for i in range(4)],
                   "elements of the ID": ["P1,P2,P3,P4", "P3,P5", "P1,P3,P5", "P6"]})

CodePudding user response：

import pandas as pd
ID = ["G1","G2","G3","G4"]
Elements = [["P1","P2","P3","P4"],
            ["P3","P5"],
            ["P1","P3","P5"],
            ["P6"]]

df = pd.DataFrame(zip(ID,Elements),columns = ["ID","Elements"])
df1 = pd.DataFrame(columns = ID)
df1["ID"] = ID
for i in ID:
    for j in ID:
        if i == j:
            df1.loc[df1.ID == i,j] = df.loc[df.ID == i,"Elements"]
        else:
            df1 = df1.astype("object")
            df1.loc[df1.ID == i,j] = df1.loc[df1.ID == i,j].apply(
                lambda x : list(set(list(df.loc[df.ID == i,"Elements"])[0]) & set(list(df.loc[df.ID == j,"Elements"])[0])))

Output :

df1
Out[38]: 
                 G1        G2            G3    G4  ID
0  [P1, P2, P3, P4]      [P3]      [P1, P3]    []  G1
1              [P3]  [P3, P5]      [P5, P3]    []  G2
2          [P1, P3]  [P5, P3]  [P1, P3, P5]    []  G3
3                []        []            []  [P6]  G4