Merge rows of csv file with same category in python

Question

I am familiar with basic concepts of reading and writing a csv file in python. But I am stuck to make a logic for this problem. I think GROUP BY can solve my problem but how one can do in python

Category         Data
A                Once upon a time.
A                There was a king.
A                who ruled a great and glorious nation.
B                He loved each of them dearly. 
B                One day, when the young ladies were of age to be married. 
B                terrible, three-headed dragon laid. 
C                It is so difficult to deny 
C                the reality

I want to make logic for such an output that data with category A merges to one row and same for category B and C like this.

Category         Data
    A                Once upon a time. There was a king. who ruled a great and glorious nation.
    B                He loved each of them dearly. One day, when the young ladies were of age to be married. terrible, three-headed dragon laid. 
    C                It is so difficult to deny the reality

Please if anyone of you can help me out with this logic I would appreciate his effort.

Given the initial list of category-data, you can use Python's groupby: docs.python.org/2/library/itertools.html#itertools.groupby, to group on the category. Use that to concatenate the data, and create a new csv. — Kiran Subbaraman
– Kiran Subbaraman, Commented Feb 7, 2018 at 12:41

Orenshi · Accepted Answer · 2018-02-07 12:45:14Z

3

With pandas library you can use groupby and make a custom aggregate function that just concatenates each category's Data

>>> import pandas as pd
>>> data = [['A', 'Once upon a time.'], ['A', 'There was a king.'], ['A', 'who ruled a great and glorious nation.'], ['B', 'He loved each of them dearly. '], ['B', 'One day, when the young ladies were of age to be married. '], ['B', 'terrible, three-headed dragon laid. '], ['C', 'It is so difficult to deny '], ['C', 'the reality']]
>>> df = pd.DataFrame(data=data, columns=['Category','Data'])
>>> df
  Category                                               Data
0        A                                  Once upon a time.
1        A                                  There was a king.
2        A             who ruled a great and glorious nation.
3        B                     He loved each of them dearly.
4        B  One day, when the young ladies were of age to ...
5        B               terrible, three-headed dragon laid.
6        C                        It is so difficult to deny
7        C                                        the reality
>>> df.groupby('Category').agg({'Data': lambda x : ' '.join(x)})
                                                       Data
Category
A         Once upon a time. There was a king. who ruled ...
B         He loved each of them dearly.  One day, when t...
C                   It is so difficult to deny  the reality

answered Feb 7, 2018 at 12:45

Orenshi

1,87314 silver badges13 bronze badges

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

1 Comment

Orenshi Over a year ago

Are the other columns in the csv also distinct? Meaning if a second column Data2 were in your csv are the records for each Category the same or different on every line?

hiro protagonist · Accepted Answer · 2018-02-07 13:01:43Z

itertools.groupby can help (assuming the letters in your first row are ordered):

from itertools import groupby
from io import StringIO

text = '''Category         Data
A                Once upon a time.
A                There was a king.
A                who ruled a great and glorious nation.
B                He loved each of them dearly.
B                One day, when the young ladies were of age to be married.
B                terrible, three-headed dragon laid.
C                It is so difficult to deny
C                the reality
'''

with StringIO(text) as file:
    next(file)  # skip header
    rows = (row.split('                ') for row in file)
    for key, items in groupby(rows, key=lambda x: x[0]):
        phrases = (item[1].strip() for item in items)
        print(key, ' '.join(phrases))

which gives:

A Once upon a time. There was a king. who ruled a great and glorious nation.
B He loved each of them dearly. One day, when the young ladies were of age to be married. terrible, three-headed dragon laid.
C It is so difficult to deny the reality

if your data is in a file, you have to replace the with StringIO(text) as file: above with:

with('textfile.txt') as file:
    # do stuff as above with file

Collectives™ on Stack Overflow

Merge rows of csv file with same category in python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related