+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: How to get first and last row index of each unique names in pandas dataframe (/thread-13385.html)
How to get first and last row index of each unique names in pandas dataframe - SriRajesh - Oct-12-2018
I am very new to python, I want to get the first and last row index of each unique elements based on the 2nd column.
MUY09KT00 TW00.00 MUY09KT00 TW00.00 MUY09KT00 TW00.00 MHJ09KT00 PW00.00 MHJ09KT00 PW00.00 LHJ09KT00 NPW00.00 LHJ09KT00 NPW00.00 LHJ09KT00 NPW00.00in this case, the unique element first index are:
1
4
6
in this case, the unique element last index are:
3
5
8
RE: How to get first and last row index of each unique names in pandas dataframe - perfringo - Oct-13-2018
NB! I didn't notice that it was about pandas dataframe. Therefore following is not applicable solution.
One way of doing it is below. However, it's unclear what 'unique' means. Following code assumes that rows are ordered and only change in items must be recorded. There was no requirements in which data structure result must be stored so I used dictionary (if items are unordered then only last occurrence is preserved). It works only on Python 3.6+ because it relies on fact that dictionaries are insertion ordered.
I added one row to example data to show how one ocurrance is recorded
lst = ['MUY09KT00 TW00.00',
'MUY09KT00 TW00.00',
'MUY09KT00 TW00.00',
'MHJ09KT00 PW00.00',
'MHJ09KT00 PW00.00',
'LHJ09KT00 NPW00.00',
'LHJ09KT00 NPW00.00',
'LHJ09KT00 NPW00.00',
'PHJ09KT00 PPW00.00',
]
for i, row in enumerate(lst, start=1):
item = row.split()[1]
if i == 1:
d = {item: [i]} # create dictionary and first item with start index
else:
if item != prev_item: # if item changes
d[list(d.keys())[-1]].append(i-1) # add ending index to item
d[item] = [i] # add new item with start index
if i == len(lst): # last row
d[item].append(i)
prev_item = item
print(d)
{'TW00.00': [1, 3], 'PW00.00': [4, 5], 'NPW00.00': [6, 8], 'PPW00.00': [9, 9]}