Python Forum
How to get first and last row index of each unique names in pandas dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to get first and last row index of each unique names in pandas dataframe
#1
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.00
in this case, the unique element first index are:
1
4
6
in this case, the unique element last index are:
3
5
8
Reply
#2
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]}
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  pandas: Compute the % of the unique values in a column JaneTan 1 311 Oct-25-2021, 07:55 PM
Last Post: jefsummers
  update values in one dataframe based on another dataframe - Pandas iliasb 2 1,473 Aug-14-2021, 12:38 PM
Last Post: jefsummers
  empty row in pandas dataframe rwahdan 3 782 Jun-22-2021, 07:57 PM
Last Post: snippsat
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 1,096 Jan-11-2021, 09:53 PM
Last Post: Azureaus
  [split] Getting Index Error - list index out of range krishna 2 1,054 Jan-09-2021, 08:29 AM
Last Post: buran
  Comparing results within a list and appending to pandas dataframe Aryagm 1 881 Dec-17-2020, 01:08 PM
Last Post: palladium
  How to search for specific string in Pandas dataframe Coding_Jam 1 1,131 Nov-02-2020, 09:35 AM
Last Post: PsyPy
  Interpolating DataFrame method=‘index’ help tlewick1 1 740 Oct-22-2020, 12:48 AM
Last Post: scidam
  PANDAS: DataFrame | White Spaces & Special Character Removal traibr 1 1,903 Sep-10-2020, 07:02 PM
Last Post: eddywinch82
  No Output In Pandas DataFrame Query eddywinch82 1 916 Aug-17-2020, 09:25 PM
Last Post: eddywinch82

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020