0

Here is my sample table:

ID Code Col2 Col3
123 23 1 4
123 23 2 6
124 24 2 7
125 25 37 9
123 24 75 6
124 24 49 11
124 25 43 12

I want to get this. I need to sum up the values of Col2 and Col3 whenever Code for an ID repeats.

ID Code Col2_23 Col3_23 Col2_24 Col3_24 Col2_25 Col3_25
123 23 3 10 75 6 NaN NaN
124 24 NaN NaN 51 18 43 12
125 25 NaN NaN NaN NaN 37 9

Any help will be appreciated.

mittal_r
  • 1
  • 1
  • 1
    It looks something like `df.pivot_table(index=['ID', 'Code'], columns='Code', values=['Col2', 'Col3'])` but it's really unclear what `Col2` and `Col3` represent in the output dataframe. Why is the information duplicated in 25, but it's the first instance for 23 and 24? Is that just the first instance in each column regardless of whether or not it's covered in a later row? – Henry Ecker Feb 04 '22 at 20:45
  • Hi @HenryEcker, Col2 and Col3 are excessive. They should be removed, ideally. But even if they are present, it doesn't hurt my purpose. – mittal_r Feb 04 '22 at 22:50
  • @HenryEcker As for the duplicates, I have edited my table. I need to sum up the values of Col2 and Col3 whenever Code for an ID repeats. – mittal_r Feb 04 '22 at 22:59

0 Answers0