Changing dataframe number of weeks between dates calculation

Question

I have a dataframe that looks like this

from pandas import Timestamp
df = pd.DataFrame({'inventory_created_date': [Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00')],
                  'rma_processed_date': [Timestamp('2017-09-25 00:00:00'),
                                         Timestamp('2018-01-08 00:00:00'),
                                         Timestamp('2018-04-21 00:00:00'),
                                         Timestamp('2018-08-10 00:00:00'),
                                         Timestamp('2018-10-17 00:00:00'),
                                         Timestamp('2018-11-08 00:00:00'),
                                         Timestamp('2019-07-18 00:00:00'),
                                         Timestamp('2020-01-30 00:00:00'),
                                         Timestamp('2020-04-20 00:00:00'),
                                         Timestamp('2020-06-09 00:00:00')], 
                  'uniqueid':['9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959'],
                  'rma_created_date':[Timestamp('2017-07-31 00:00:00'),
                                     Timestamp('2017-12-12 00:00:00'),
                                     Timestamp('2018-04-03 00:00:00'),
                                     Timestamp('2018-07-23 00:00:00'),
                                     Timestamp('2018-09-28 00:00:00'),
                                     Timestamp('2018-10-24 00:00:00'),
                                     Timestamp('2019-06-21 00:00:00'),
                                     Timestamp('2019-12-03 00:00:00'),
                                     Timestamp('2020-04-03 00:00:00'),
                                     Timestamp('2020-05-18 00:00:00')],
                  'time_in_weeks':[50, 69, 85, 101, 110, 114, 148, 172, 189, 196],
                  'failure_status':[1, 1, 1, 1, 1, 1, 1, 1, 1, 1]})

I need to adjust the time_in_weeks numbers for every row after the first. What I need to do is for each row after the first I need to take the rma_created_date and the date rma_processed_date above that row and find the number of weeks between them.

For example, in the second row we have rma_created_date of 2017-12-12 and we have 'rma_processed_date' of 2017-09-25 in the first row. Thus the number of weeks in between these two dates is 11. There fore the 69 in the second row should become an 11.

Lets for another example. On the third row we have rma_created_date of 2018-04-03 and an rma_processed_date in the second row of 2018-01-08. Thus the number of weeks in between these two dates is 12. Therefore the 85 in the third row should become an 12.

This is what I have done so far

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'])
    
    # Convert date columns into datetime
    df['inventory_created_date'] = pd.to_datetime(df['inventory_created_date'], errors='coerce')
    df['rma_processed_date'] = pd.to_datetime(df['rma_processed_date'], errors='coerce')
    df['rma_created_date'] = pd.to_datetime(df['rma_created_date'], errors='coerce')
    
    # If we have rma_processed_dates that are of 1/1/1900 then just drop that row
    df = df[~(df['rma_processed_date'] == '1900-01-01')]
    
    # Correct the time_in_weeks column
    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)

    return df
df = clean_df(df)

When I apply this function to the example, this is what I get

df = pd.DataFrame({'inventory_created_date': [Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00'),
                                             Timestamp('2016-08-17 00:00:00')],
                  'rma_processed_date': [Timestamp('2017-09-25 00:00:00'),
                                         Timestamp('2018-01-08 00:00:00'),
                                         Timestamp('2018-04-21 00:00:00'),
                                         Timestamp('2018-08-10 00:00:00'),
                                         Timestamp('2018-10-17 00:00:00'),
                                         Timestamp('2018-11-08 00:00:00'),
                                         Timestamp('2019-07-18 00:00:00'),
                                         Timestamp('2020-01-30 00:00:00'),
                                         Timestamp('2020-04-20 00:00:00'),
                                         Timestamp('2020-06-09 00:00:00')], 
                  'uniqueid':['9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959',
                             '9907937959'],
                  'rma_created_date':[Timestamp('2017-07-31 00:00:00'),
                                     Timestamp('2017-12-12 00:00:00'),
                                     Timestamp('2018-04-03 00:00:00'),
                                     Timestamp('2018-07-23 00:00:00'),
                                     Timestamp('2018-09-28 00:00:00'),
                                     Timestamp('2018-10-24 00:00:00'),
                                     Timestamp('2019-06-21 00:00:00'),
                                     Timestamp('2019-12-03 00:00:00'),
                                     Timestamp('2020-04-03 00:00:00'),
                                     Timestamp('2020-05-18 00:00:00')],
                  'time_in_weeks':[50, 4294967259, 14, 16, 10, 3, 4294967280, 4294967272, 12, 7],
                  'failure_status':[1, 1, 1, 1, 1, 1, 1, 1, 1, 1]})

Obviously the calculation is incorrect, which leads me to believe there must be something wrong with this

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)

If anyone has any suggestions I would greatly appreciate it.

The time_in_weeks column is expected to be [50, 11, 12, 13, 7, 1, 32, 20, 9, 4]

Please don't post images. Images are discouraged on StackOverflow. Please take time to read [`how-to-make-good-reproducible-pandas-examples`](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Shubham Sharma, Dec 04 '20 at 16:54

score 1 · Accepted Answer · answered Dec 04 '20 at 17:40

Let's shift the rma_processed_date then subtract it from rma_created_date finally get the days using .dt.days and divide by 7 to get number of weeks, finnaly use update to update the time_in_weeks column:

weeks = df['rma_created_date'].sub(df['rma_processed_date'].shift()).dt.days.div(7).round()
df['time_in_weeks'].update(weeks)

Result:

  inventory_created_date rma_processed_date    uniqueid rma_created_date  time_in_weeks  failure_status
0             2016-08-17         2017-09-25  9907937959       2017-07-31             50               1
1             2016-08-17         2018-01-08  9907937959       2017-12-12             11               1
2             2016-08-17         2018-04-21  9907937959       2018-04-03             12               1
3             2016-08-17         2018-08-10  9907937959       2018-07-23             13               1
4             2016-08-17         2018-10-17  9907937959       2018-09-28              7               1
5             2016-08-17         2018-11-08  9907937959       2018-10-24              1               1
6             2016-08-17         2019-07-18  9907937959       2019-06-21             32               1
7             2016-08-17         2020-01-30  9907937959       2019-12-03             20               1
8             2016-08-17         2020-04-20  9907937959       2020-04-03              9               1
9             2016-08-17         2020-06-09  9907937959       2020-05-18              4               1

Is weeks now a list? How did you get the result then? Shouldn't we just update the existing df `time_in-weeks` column? — justanewb, Dec 04 '20 at 17:42
@justanewb `weeks` is a `pandas.Series`. we can use `update` method to update the existing column `time_in_weeks` with the new `weeks` series. — Shubham Sharma, Dec 04 '20 at 17:44

Changing dataframe number of weeks between dates calculation

1 Answers1