I have a table that looks like below
Loc | ID | filter | P1 |
---|---|---|---|
A | ABC1 | GHY | 55.6 |
A | DFT1 | FGH | 67.8 |
B | HJH5 | GHY | 67 |
C | HKL | BHY | 78 |
B | GTY | FGH | 60 |
I want the output as below. Basically, I want the records with the same Filter to be one row
Filter | ID | Loc | P1 | m_ID | m_Loc | m_p1 | total |
---|---|---|---|---|---|---|---|
GHY | ABC1 | A | 55.6 | HJH5 | B | 67 | 122.6 |
FGH | DFT1 | A | 67.8 | GTY | B | 60 | 127.8 |
Is this achievable using itertools i python. If yes can someone please suggest how can we do it?
Here's a solution using
lead
androw_number
that I think is a little nicer.Fiddle
There should be a better solution to this question, but here is a solution that's based on what you did. I used
left join
to not lose filters that only appear once and then I usedgroup by
to consolidate the results.Fiddle