I have a dataset containing a column with lots of duplicated rows. My column looks something like the Index Column in the table below:
| Index | New_Column |
|---|---|
| 2020BAS | 2020BAS |
| 2022CAD | 2022CAD |
| 2012BET | 2012BET_1 |
| 2012BET | 2012BET_2 |
| 2012BET | 2012BET_3 |
| 2008TEQ | 2008TEQ |
| 2021JET | 2021JET_1 |
| 2021JET | 2021JET_2 |
| 2011ART | 2011ART_1 |
| 2011ART | 2011ART_2 |
| 2011ART | 2011ART_3 |
| 2019QAS | 2019QAS |
| 2010POS | 2010POS_1 |
| 2010POS | 2010POS_2 |
| 2010POS | 2010POS_3 |
| 2010POS | 2010POS_4 |
| 2016YIG | 2016YIG |
| 2017LIM | 2017LIM |
Using Pandas, I would like to create a new column that contains the rows in the Index Column and add numbered annotations to all duplicate occurrences of a specific row item. The result I have in mind can be seen in the New_Column series in the table above.
I have been searching here and elsewhere and still cannot find any examples of this issue or a proposed solution. Admittedly, I am new to Python and am still trying to figure out how to go about this issue. Any help would be a tremendous learning experience