python pandas iterating rows of two different columns and returning the repeated one once and corresponding values of repeated values in single row

Question

for instance, I have a .csv file with 1000s of rows like below:

year,name
1992,Alex
1992,Anna
1993,Max
1993,Bob
1993,Tom

so on...

I want my output to be:

   year           name
   1992     Alex, Anna
   1993  Max, Bob, Tom

this looks simple but I'm not able to make the corresponding rows in a single row appended by a comma ','

Hi, welcome to SO. Can you please edit your example? It's open to misinterpretation. https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — talatccan, Feb 28 '20 at 22:57
Please provide what you're getting as a table and what you want. Because I'm not understanding what you want to do here. — Ukrainian-serge, Feb 28 '20 at 23:21

score 3 · Answer 1 · answered Feb 28 '20 at 23:18

3

You can achieve this by using groupby and aggregation. Try the below code:

df = df.groupby("year").agg({
    "year":"first",
    "name":", ".join
                          })

You can save the dataframe values to csv by ignoring index

df.to_csv("output.csv",index=False)

answered Feb 28 '20 at 23:18

Varsha

319
1
5

It is one of the agg function. Takes the first occurrence in the group. – Varsha Feb 28 '20 at 23:30

score 2 · Answer 2 · answered Feb 28 '20 at 23:25

2

This may help you

df = df.groupby('year')['name'].unique().reset_index()
df['name'] = df['name'].apply(lambda x: ', '.join(x))

Output:

   year           name
0  1992     Alex, Anna
1  1993  Max, Bob, Tom

answered Feb 28 '20 at 23:25

talatccan

743
5
19

score 1 · Accepted Answer · answered Mar 12 '20 at 17:27

How about this one?

import pandas as pd
x = pd.DataFrame.from_dict({'year':['1992', '1992', '1993', '1993', '1993'], 
                            'name':['ALEX', 'ANNA', 'MAX', 'BOB', 'TOM'],
                             'col':range(5)})
print (x)

a = x.groupby('year').agg({'name': lambda x: tuple(set(x)), 'col':'sum'})
print (a)

Result:

                 name  col
year                      
1992     (ALEX, ANNA)    1
1993  (BOB, TOM, MAX)    9

python pandas iterating rows of two different columns and returning the repeated one once and corresponding values of repeated values in single row

3 Answers3