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.