In pandas group all columns that are identical except for one column

Question

I would like to know if there is a succinct way of combining rows that have the identical column values in pandas using a custom function.

import pandas as pd
data = {'Val': ['10uF', '10uF', '10uF', '1uF', '100nF'], 
'Designator': ['C3', 'C4', 'C5', 'C6', 'C7'],
'Footprint': ['0805', '0805', '1206', '0805', '0805']}

data = pd.DataFrame.(data)

The resulting dataframe is:

     Val Designator Footprint
0   10uF         C3      0805
1   10uF         C4      0805
2   10uF         C5      1206
3    1uF         C6      0805
4  100nF         C7      0805

I want to combine rows such that there are only unique values in the Val column. In the Designator column of the combined rows there should be a custom value, the original designators separated by commas.

Expected output:

     Val Designator Footprint
0   10uF      C3,C4      0805
1   10uF         C5      1206
2    1uF         C6      0805
3  100nF         C7      0805

`df.groupby('Val')['Designator'].agg(','.join)` – Quang Hoang Aug 12 '20 at 18:41 — Quang Hoang, Aug 12 '20 at 18:41

score 0 · Answer 1 · answered Aug 12 '20 at 18:48

0

pd.DataFrame(data.groupby('Val').Designator.apply(lambda x : ",".join(x)))

answered Aug 12 '20 at 18:48

Ran Cohen

721
6
15

score 0 · Answer 2 · answered Aug 12 '20 at 18:52

data = """id     Val Designator
0   10uF         C3
1   10uF         C4
2   10uF         C5
3    1uF         C6
4  100nF         C7"""
a = [[t for t in l.split(" ") if t!=""]  for l in data.split("\n")]
df = pd.DataFrame(a[1:],columns=a[0]).set_index("id")
df.groupby("Val").agg({"Designator":lambda s: ",".join(list(s))})

output


Designator
Val 
100nF   C7
10uF    C3,C4,C5
1uF C6

In pandas group all columns that are identical except for one column

2 Answers2