Search code examples
pythonpandasdataframecalculated-columns

calculate value basis the column value post dataframe grouping in pandas

I am trying to find difference in time between sailing and arrival for each IMO number.

IMO       Name          State      Datetime
8300327 SILVER FJORD    Arrival 13/08/2021 04:51
8300327 SILVER FJORD    Sailing 13/08/2021 22:59
8300327 SILVER FJORD    Arrival 20/08/2021 10:52
8300327 SILVER FJORD    Sailing 20/08/2021 20:24
9340738 FRAMFJORD       Arrival 19/08/2021 11:05
9340738 FRAMFJORD       Sailing 20/08/2021 17:32

for above dataframe the output should be

IMO     Name            State     Datetime           Time_int
8300327 SILVER FJORD    Arrival 13/08/2021 04:51    
8300327 SILVER FJORD    Sailing 13/08/2021 22:59    18:08:00
8300327 SILVER FJORD    Arrival 20/08/2021 10:52    
8300327 SILVER FJORD    Sailing 20/08/2021 20:24    09:32:00
9340738 FRAMFJORD       Arrival 19/08/2021 11:05    
9340738 FRAMFJORD       Sailing 20/08/2021 17:32    06:27:00

I have written below code for the calculation

def dwell_calc(df):
    if (df['State'] == "Sailing"):
        val = df['Datetime'].diff().dt.seconds.div(3600).fillna(0).reset_index()

        return val


# data.sort_values(['IMO', 'Datetime'], inplace=True)


cond2=(data['State']=='Sailing')
data.loc[cond2, 'time_int'] = dwell_calc(data)

print(data['time_int'])

I am getting error:

 if (df['State'] == "Sailing"):
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Please help with solution to find time interval using python

Solution

  • Below is the final correct code

    Code:

    df['time_diff'] = df.sort_values(["IMO","Datetime"]).groupby(["IMO"],as_index="False")['Datetime'].diff().dt.seconds.div(3600)
cond1=df['State']=="Arrival"
df.loc[cond1,"time_diff"]=0

    Output:

    IMO      State  Datetime            time_diff
8300327 Arrival 2021-08-13 04:51:00 0
8300327 Sailing 2021-08-13 22:59:00 18.1333333
8300327 Arrival 2021-08-20 10:52:00 0
8300327 Sailing 2021-08-21 02:24:00 15.5333333
8516263 Arrival 2021-08-22 20:10:00 0
8516263 Sailing 2021-08-23 17:25:00 21.25
8802882 Arrival 2021-08-18 07:25:00 0
8802882 Sailing 2021-08-18 22:01:00 14.6