Coalescing the Rows based on occurence and while maintaining unique values in a DataFrame in Python

user_12054441 0
Viewed 47

DataFrame:

Name         Category        Port          Class
A                Code         443        string1
A                Code          80        string2
B                Math         443        string3
A                Code         443        string1
B                Math          80        string4

Code that I have tried:

df1 = df.name.ne(df.name.shift())
count = df.name.groupby(df1.cumsum()).size()

Output that I got:

Name     Category      Port       Class     Count
A            Code       443     string1         2
B            Math       443     string3         1
A            Code       443     string1         1
B            Math        80     string4         1

The output that I want:

Name    Category         Port               Class   Count
A           Code      443, 80    string1, string2       2
B           Math          443             string3       1
A           Code          443             string1       1
B           Math           80             string4       1

Any help is appreciated.

pandas-groupby pandas dataframe python
2 Answers

Using groupby and agg with the appropriate functions should get you what you want. The below uses list which will result in all values being kept, if you only want the unique ones you can change this to set.

df['cumsum'] = df.Name.ne(df.Name.shift()).cumsum()
df2 = df.groupby(df["cumsum"]).agg({'Name': 'first', 'Category': 'first', 'Port': list, 'Class': [list, 'count']})
df2.columns = df2.columns.droplevel(1).values[:-1].tolist() + ['Count']
df2 = df2.reset_index(drop=True)

This will give you:

  Name  Category       Port               Class  Count
0    A      Code  [443, 80]  [string1, string2]      2
1    B      Math      [443]           [string3]      1
2    A      Code      [443]           [string1]      1
3    B      Math       [80]           [string4]      1
user_75795470

The key here is to idendify consecutive lines with common name and category. A simple trick is to use shift and cumsum:

x = pd.Series(np.where((df['Name'] == df1['Name'])&(df['Category'] == df1['Category']), 0, 1),
              index = df.index).cumsum()

Once this is done, a groupby and agg does the job:

df.assign(Count=1, Port=df['Port'].astype(str)).groupby(['Name', 'Category',x]).agg(
    {'Port': ', '.join, 'Class': ', '.join, 'Count': 'sum'}).reset_index().drop(columns='level_2')

It gives:

  Name Category     Port             Class  Count
0    A     Code  443, 80  string1, string2      2
1    A     Code      443           string1      1
2    B     Math      443           string3      1
3    B     Math       80           string4      1
user_35452730
Related