Merge duplicate rows in a text file using python based on a key column

Question

I have a csv file and I need to merge records of those rows based on a key column name

a.csv

Name|Acc#|ID|Age
Suresh|2345|a-b2|24
Mahesh|234|a-vf|34
Mahesh|4554|a-bg|45
Keren|344|s-bg|45
yankie|999|z-bg|34
yankie|3453|g-bgbbg|45

Expected output: Merging records based on name like values from both the rows for name Mahesh and yankie are merged

Name|Acc#|ID|Age
Suresh|2345|a-b2|24
Mahesh|[234,4555]|[a-vf,a-bg]|[34,45]
Keren|344|s-bg|45
yankie|[999,3453]|[z-bg,g-bgbbg]|[34,45]

can someone help me with this in python?

I've never seen a csv file containing arrays, so multiple values in one column. Just out of curiosity, what type of application is able to read that output file? — BdR
– BdR, Commented Jul 29, 2020 at 13:01
this is related to ETL flow where one app consumes data from a file coming from another app — Abhinandan Mehandiratta
– Abhinandan Mehandiratta, Commented Sep 17, 2020 at 8:13

bigbounty · Accepted Answer · 2020-07-29 07:34:26Z

2

import pandas as pd

df = pd.read_csv("a.csv", sep="|", dtype=str)
new_df = df.groupby('Name',as_index=False).aggregate(lambda tdf: tdf.unique().tolist() if tdf.shape[0] > 1 else tdf)
new_df.to_csv("data.csv", index=False, sep="|")

Output:

Name|Acc#|ID|Age
Keren|344|s-bg|45
Mahesh|['234', '4554']|['a-vf', 'a-bg']|['34', '45']
Suresh|2345|a-b2|24
yankie|['999', '3453']|['z-bg', 'g-bgbbg']|['34', '45']

edited Jul 29, 2020 at 7:34

answered Jul 29, 2020 at 7:28

bigbounty

17.5k7 gold badges45 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge duplicate rows in a text file using python based on a key column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related