I'm a little new to Python. I am working on movement data. I have ID, location, speed, time, and destination columns. My dataframe looks like this:

I want to loop or iterate through ID and destination to create a new trip ID column.

So I wrote as follows

for shipid in df.shipid.unique():
    tripid=1
    for destination in df.destination.unique():
        df['tripid'] = np.where(df['destination']==df['destination'].shift(-1),tripid, tripid 1)

The code creates a new column tripid where every row is assigned "1", except for the last row of each destination, where "2" is assigned.

I want to assign a unique ID to each ship with the same destination as 1, 2, 3.

I am not sure of what to use, either loop or iteration, etc. I tried to use it for a loop and the error shows the series is ambiguous.

CodePudding user response：

Does this solve your problem?

import pandas as pd

# Example dataframe:
df = pd.DataFrame(
    {'shipid': [1, 1, 1, 1, 1, 2, 2],
    'destination': ['Brunei', 'Brunei', 'Brunei', 'Busan', 'Busan', 'Tokyo', 'Tokyo']}
)

df['tripid'] = df.groupby(['destination']).cumcount()   1

You can also group by both shipid and destination:

df['tripid'] = df.groupby(['shipid', 'destination']).cumcount()   1

Or maybe you need this?

https://stackoverflow.com/a/51329888/14627505

df['tripid'] = df.groupby(['shipid', 'destination']).ngroup()   1