How to create a group | sub-group (pre-defined) cyclic order by considering the identical consecutive groupings (in Pandas DataFrame) columns?

Viewed 122

Task 1: I am looking for a solution to create a group by considering the identical consecutive groupings in one of the columns (of my Panda's DataFrame, ..considering this as values of a list):

from itertools import groupby

test_list = ['AA', 'AA', 'BB', 'CC', 'DD', 'DD', 'DD', 'AA', 'BB', 'EE', 'CC']
data = pd.DataFrame(test_list)
data['batches'] = ['1','1','2','3','4','4','4','5','6','7','8'] # this is the goal to reach
print(data)

result = [list(y) for x, y in groupby(test_list)]
print(result)

[['AA', 'AA'], ['BB'], ['CC'], ['DD', 'DD', 'DD'], ['AA'], ['BB'], ['EE'], ['CC']]

So, I have a DataFrame with two columns: the first is a list of elements that must be kept in order + grouped into batches: identical consecutive grouping. The batch column where the result should be assigned.

I couldn't find a solution or a workaround. As you can see, I've created a list using the itertools groupby function by grouping the same cons. items, but this isn't the final result I'd like to see. I know that itertools groupby allows me to utilize a lambda function with the 'key=' parameter to perhapsĀ get to my solution.

I was thinking of merging the above and looping it into a dictionary, with the key being the batch numbers obtained by iterating the list using enumerate and the values being the list elements:

{1:['AA', 'AA'], 2:['BB'], 3:['CC'], 4: ['DD', 'DD', 'DD']...}

After that, I'll convert the dictionary (or any other solution/workaround) to a Data Series and add it to my batch column:

In this exercise, I just want to return the key(s) of my 'dictionary' (the number of unique batches) to the batches column.

| list     | batches |
| -------- | ------- |
| AA       | 1       |
| AA       | 1       |
| BB       | 2       |
| CC       | 3       |
| DD       | 4       |
| DD       | 4       |
| DD       | 4       |
| AA       | 5       |
| BB       | 6       |
| EE       | 7       |
| CC       | 8       |

EDITED:

Task 2: The added query for a similar task:

In this scenario, my initial list has a (pre-defined) cyclic order to follow such as AA -- AB -- AC belongs to one main group, DA -- DB -- belongs to another group.

The question is how to calculate the column sub-group so that I can have sub-groups listings under my main group...so to say, capturing repeated groups within the main group.

list sub main gr
AA 1 1
AB 1 1
AC 1 1
AA 2 1
AB 2 1
AC 2 1
DA 1 2
DB 1 2

I found a solution whose logic was based on @Shubham's comment. My solution to use the .cumcount() function as the following: df['sub'] = df.groupby(['main gr', 'list'].cumcount()+1 .cumcount()+1 if we want that the sub-order count/index starts at 1 instead 0.

(I'm not looking for the best solution, I am looking for a solution. Nevertheless, I would like to use this code for large datasets containing millions of entries).

I will highly appreciate any comment or supporting feedback.

0 Answers
Related