CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
daiyue
daiyue

Reputation: 7448

Grouping by column of lists in pandas GroupBy

I have the following df,

pri_key          doc_no    c_code
[9001, 7620]     767       0090
[9001, 7620]     767       0090
[9002, 7530]     768       4100
[9002, 7530]     769       3000
[9003, 7730]     777       4000
[9003, 7730]     777       4000
[9003, 7730]     779       4912

I need to hash pri_key then groupby hashed pri_key, and excludes groups whose rows have the same doc_no and c_code combination from df;

 df["doc_group"] = df['pri_key'].apply(lambda ls: hash(tuple(sorted(ls))))

 grouped = df.groupby("doc_group")

 m = grouped[['doc_no', 'c_code']].apply(lambda x: len(np.unique(x.values)) > 1)

 df = df.loc[m]

but it did not work,

pandas.core.indexing.IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match

I am wondering how to solve this. So the result will look like,

pri_key          doc_no    c_code
[9002, 7530]     768       4100
[9002, 7530]     769       3000
[9003, 7730]     777       4000
[9003, 7730]     777       4000
[9003, 7730]     779       4912

Upvotes: 1

Views: 389

Answers (1)

cs95
cs95

Reputation: 402263

You can tupleize and hash pri_key, then use it to group on df:

grouper = [hash(tuple(x)) for x in df['pri_key']]
df[df.groupby(grouper)[['doc_no', 'c_code']].transform('nunique').gt(1).all(1)]

        pri_key  doc_no  c_code
2  [9002, 7530]     768    4100
3  [9002, 7530]     769    3000
4  [9003, 7730]     777    4000
5  [9003, 7730]     777    4000
6  [9003, 7730]     779    4912

Upvotes: 1

Related Questions