CODEWITHSUNDEEP
pythonpandasnetwork-programmingnetworkx
ccopp
ccopp

Reputation: 43

Python Pandas - Create DataFrame that contains node pairs and edge strength

I am working on creating a simple network graph and I'm having some issues getting my data into the right shape.

I have a Pandas DataFrame with two columns that contains information on collaboration between different entities. The column Project_ID lists the ID of the project and Participating_entity lists one entity that participated in the project. A project with 3 entities would take up 3 rows. Here is a simple sample DF listing collaborations between 3 entities on 3 projects:

df =  pd.DataFrame([[1,'a'],[1,'b'],[2,'a'],[2,'c'],[3,'a'],[3,'b'],[3,'c']],  columns = ['Project_ID','Participating_entity']) 

#|---------------------|-------------------------|
#|       Project_ID    | Participating_entity    |
#|---------------------|-------------------------|
#|          1          |            A            |
#|          1          |            B            |
#|          2          |            A            |
#|          2          |            C            |
#|          3          |            A            |
#|          3          |            B            |
#|          3          |            C            |
#|---------------------|-------------------------|

I would like to create a new DF that displays the number of collaborations between Participating_entity pairs. For the simple data above that would be.

#|-------------|-----------|--------------------|
#|  Entity_1   | Entity_2  | Num_collaborations |
#|-------------|-----------|--------------------|
#|     A       |      B    |        2           |
#|     A       |      C    |        2           |
#|     B       |      C    |        1           |
#|-------------|-----------|--------------------|

A collaborated twice with each of B and C. B and C collaborated once. Collaborations should only be listed once. The connection between A and B for instance should only be listed under A-B and no row should exist for B-A.

Thanks in advance!

Upvotes: 4

Views: 557

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210842

you can do it directly in NetworkX:

In [210]: G = nx.from_pandas_edgelist(df, 'Project_ID', 'Participating_entity')

In [211]: from networkx.algorithms import bipartite

In [212]: W = bipartite.weighted_projected_graph(G, df['Participating_entity'].unique())

In [213]: W.edges(data=True)
Out[213]: EdgeDataView([('a', 'c', {'weight': 2}), ('a', 'b', {'weight': 2}), ('b', 'c', {'weight': 1})])

Upvotes: 2

jpp
jpp

Reputation: 164673

One way is to use collections.defaultdict combined with itertools.combinations. There may be a pandas-specific way but this, by nature, will be library-specific.

from collections import defaultdict
from itertools import combinations

df_grouped = df.groupby('Project_ID')['Participating_entity'].apply(list).reset_index()

d = defaultdict(int)

for idx, row in df_grouped.iterrows():
    for comb in combinations(row['Participating_entity'], 2):
        d[frozenset(comb)] += 1

# defaultdict(int,
#             {frozenset({'a', 'b'}): 2,
#              frozenset({'a', 'c'}): 2,
#              frozenset({'b', 'c'}): 1})

d = {tuple(sorted(k)): v for k, v in d.items()}

df_out = pd.DataFrame(list(d.items()))\
           .rename(columns={0: 'Entities', 1: 'Num_collaborations'})

df_out = df_out.join(df_out['Entities'].apply(pd.Series))\
               .drop('Entities', 1).rename(columns={0: 'Entity 1', 1: 'Entity 2'})

#    Num_collaborations Entity 1 Entity 2
# 0                   2        a        b
# 1                   2        a        c
# 2                   1        b        c

Upvotes: 0

Related Questions