CODEWITHSUNDEEP
pythonpandas
Snorrlaxxx
Snorrlaxxx

Reputation: 168

Modifying the date column calculation in pandas dataframe

I have a dataframe that looks like this

enter image description here

I need to adjust the time_in_weeks column for the 34 number entry. When there is a duplicate uniqueid with a different rma_created_date that means there was some failure that occurred. The 34 needs to be changed to calculate the number of weeks between the new most recent rma_created_date (2020-10-15 in this case) and subtract the rma_processed_date of the above row 2020-06-28.

I hope that makes sense in terms of what I am trying to do.

So far I did this

def clean_df(df):
    '''
    This function will fix the time_in_weeks column to calculate the correct number of weeks
    when there is multiple failured for an item.
    '''
    
    # Sort by rma_created_date
    df = df.sort_values(by=['rma_created_date'])

Now I need to perform what I described above but I am a little confused on how to do this. Especially considering we could have multiple failures and not just 2.

I should get something like this returned as output

enter image description here

As you can see what happened to the 34 was it got changed to take the number of weeks between 2020-10-15 and 2020-06-26

Here is another example with more rows

enter image description here

Using the expression suggested

df['time_in_weeks']=np.where(df.uniqueid.duplicated(keep='first'),df.rma_processed_date.dt.isocalendar().week.sub(df.rma_processed_date.dt.isocalendar().week.shift(1)),df.time_in_weeks)

I get this

Final note: if there is a date of 1/1/1900 then don't perform any calculation.

enter image description here

Upvotes: 0

Views: 34

Answers (1)

wwnde
wwnde

Reputation: 26676

Question not very clear. Happy to correct if I interpreted it wrongly.

Try use np.where(condition, choiceif condition, choice ifnotcondition)

#Coerce dates into datetime
df['rma_processed_date']=pd.to_datetime(df['rma_processed_date'])
df['rma_created_date']=pd.to_datetime(df['rma_created_date'])

#Solution    

df['time_in_weeks']=np.where(df.uniqueid.duplicated(keep='first'),df.rma_created_date.sub(df.rma_processed_date),df.time_in_weeks)

Upvotes: 1

Related Questions