Aggregating rows in dataframe with another aggregated rows

user_6713342 0
Viewed 61

Following is my code:

import pandas as pd
data = {"uid": ['1','2','3','','3','','4','5'], "pid":['a','b','a','d','c','e','','']}

data = pd.DataFrame(data)
data

enter image description here

Now when i group my dataframe based on UID and aggregate the data of grouped rows. So that I get the following output.

enter image description here

Notice here i don't want the rows with uid = "" to be grouped together.

Now i want to group by above dataframe let's say temp dataframe on PID so that i get the following output:

enter image description here

Notice how UID of 1 and 3 is grouped because they had 'a' as common in UID, inspite of PID = 3 having one more element in its UID column. And while grouping rows with pid = "" are also not grouped.

pandas-groupby pandas dataframe python
1 Answers

It seems networkx graph problem for find groups by uid and pid by common values:

data = {"uid": [1,2,3,'',3], "pid":['a','b','a','d','c']}

data = pd.DataFrame(data)

import networkx as nx

# Create the graph from the dataframe
g = nx.Graph()
g = nx.from_pandas_edgelist(data,'uid','pid')

connected_components = nx.connected_components(g)

# Find the component id of the nodes
node2id = {}
for cid, component in enumerate(connected_components):
    for node in component:
        node2id[node] = cid + 1

data['g'] = data['uid'].map(node2id)
print (data)
  uid pid  g
0   1   a  1
1   2   b  2
2   3   a  1
3       d  3
4   3   c  1

df = data.astype(str).groupby("g").agg(lambda x: '|'.join(x.unique()))
print (df)
   uid  pid
g          
1  1|3  a|c
2    2    b
3         d

EDIT:

data = {"uid": ['1','2','3','','3','','4','5'], "pid":['a','b','a','d','c','e','','']}
data = data.replace('', np.nan)

data = pd.DataFrame(data)

#filter only non missing values in rows
mask = data.notna().all(axis=1)

data1 = data[mask].copy()
print (data1)
  uid pid
0   1   a
1   2   b
2   3   a
4   3   c

import networkx as nx

# Create the graph from the dataframe
g = nx.Graph()
g = nx.from_pandas_edgelist(data,'uid','pid')

connected_components = nx.connected_components(g)

# Find the component id of the nodes
node2id = {}
for cid, component in enumerate(connected_components):
    for node in component:
        node2id[node] = cid + 1

data1['g'] = data1['uid'].map(node2id)
print (data1)
  uid pid  g
0   1   a  1
1   2   b  2
2   3   a  1
4   3   c  1

data1 = data1.astype(str).groupby("g").agg(lambda x: '|'.join(x.unique()))
print (data1)
   uid  pid
g          
1  1|3  a|c
2    2    b

#add original rows with missing values
df = pd.concat([data1, data[~mask]])
print (df)
   uid  pid
1  1|3  a|c
2    2    b
3  NaN    d
5  NaN    e
6    4  NaN
7    5  NaN
user_29010020
Related