randomize date and month but preserve year and time interval

Question

I am dealing with big data in multiple files. This is part of a larger problem but for simplicity purposes, I am breaking it into parts.

file 1 is stored in df1 and file 2 is stored in df2. I have around 12 files with 3 million records in each..

Both df1 and df2 are related but stored as separate files.

df1 = pd.DataFrame({'person_id': [1, 2, 3, 4, 5],
                        'date_birth': ['12/30/1961', '05/29/1967', '02/03/1957', '7/27/1959', '01/13/1971'],
                        'date_death': ['07/23/2017','05/29/2017','02/03/2015',np.nan,np.nan]})
df1['date_birth'] = pd.to_datetime(df1['date_birth'])
df1['date_death'] = pd.to_datetime(df1['date_death'])
df1['diff_birth_death'] = df1['date_death'] - df1['date_birth']
df1['diff_birth_death']=df1['diff_birth_death']/np.timedelta64(1,'D')


df2 = pd.DataFrame({'person_id': [1,1,1,2,3],
                    'visit_id':['A1','A2','A3','B1','B2'],
                    'diag_start': ['01/01/2012', '02/25/2017', '02/03/2015', '07/27/2016', '01/13/2011'],
                    'diag_end': ['05/03/2012','05/29/2017','03/03/2015','08/15/2016','02/13/2011']})
df2['diag_start'] = pd.to_datetime(df2['diag_start'])
df2['diag_end'] = pd.to_datetime(df2['diag_end'])
df2['diff_birth_diag_start'] = df2['diag_start'] - df1['date_birth']
df2['diff_birth_diag_end'] = df2['diag_end'] - df1['date_birth']
df2['diff_birth_diag_start']=df2['diff_birth_diag_start']/np.timedelta64(1,'D')
df2['diff_birth_diag_end']=df2['diff_birth_diag_end']/np.timedelta64(1,'D')

What I would like to do is

1) randomize/shift the date and month values but retain the year component and time difference between events (between birth and death, between birth and diag_start, between birth and diag_end)

2) How to find the date offset value for each subject (no of days to be added/subtracted/randomized) for which condition above is satisfied

In the example below, I have manually added below offsets.

person_id 1 = -10 days (incorrect value. you will see below as to why it's incorrect)
person_id 2 = 10 days
person_id 3 = 100 days
person_id 4 = 20 days
person_id 5 = 125 days

I expect my output to be something like below

df1 - all correct - date and months shifted (year and interval is retained)

df2 - offset chosen was incorrect leading to change in year. Though interval was maintained year value changed.

Answer 1

As stated in the comments, what you want is to randomize two datetime objects given some restrictions:

1. The start date must be lower than the end date
2. The time interval between start and end dates must remain the same after randomization
3. The start and end years must remain the same (e.g. 2000-01-01 cannot become 1999-12-31)

To solve this problem, what I thought was to find the range of change that is possible for the start data without changing the year, then find the range of change that is possible for the end date, also without changing the year, and finally intersect them to get the range of change that applies to both dates. After that, any random value inside the final range will not change the year of any of the limiting dates and will keep the interval intact.

I have created a function that implements this functionality. You pass it the start and end datetime objects, and it will return a tuple with those dates randomized according to the restrictions.

import datetime as dt
from random import random

def rand_date_diff_keep_year_and_interval(dt1, dt2):
    if dt1 > dt2:
        raise Exception("dt1 must be lesser than dt2")
    range1 = {
        "min": dt1.replace(month=1, day=1) - dt1,
        "max": dt1.replace(month=12, day=31) - dt1,
    }
    range2 = {
        "min": dt2.replace(month=1, day=1) - dt2,
        "max": dt2.replace(month=12, day=31) - dt2,
    }
    intersection = {
        "min": max(range1["min"], range2["min"]),
        "max": min(range1["max"], range2["max"]),
    }
    rand_change = random()*(intersection["max"] - intersection["min"]) + intersection["min"]
    return (dt1 + rand_change, dt2 + rand_change)

print(rand_date_diff_keep_year_and_interval(dt.datetime(2000, 1, 1), dt.datetime(2000, 12, 31)))
print(rand_date_diff_keep_year_and_interval(dt.datetime(2000, 5, 18), dt.datetime(2001, 8, 20)))

Pandas Solution

To work with Pandas DataFrame we need to adapt the previous code to work with series instead of single datetime objects. The logic stays almost the same, but now we are doing everything "series-wise" so to speak. Also, I used numpy.random to generate a series of random number, instead of creating just one random number and repeat it for all rows... that would be a lot less random.

import datetime as dt
import pandas as pd
import numpy.random as rnd

def series_rand_date_diff_keep_year_and_interval(sdt1, sdt2):
    if any(sdt1 > sdt2):
        raise Exception("dt1 must be lesser than dt2")
    range1 = {
        "min": sdt1.apply(lambda dt1: dt1.replace(month=1, day=1) - dt1),
        "max": sdt1.apply(lambda dt1: dt1.replace(month=12, day=31) - dt1),
    }
    range2 = {
        "min": sdt2.apply(lambda dt2: dt2.replace(month=1, day=1) - dt2),
        "max": sdt2.apply(lambda dt2: dt2.replace(month=12, day=31) - dt2),
    }
    intersection = {
        "min": pd.concat([range1["min"], range2["min"]], axis=1).max(axis=1),
        "max": pd.concat([range1["max"], range2["max"]], axis=1).min(axis=1),
    }
    rand_change = pd.Series(rnd.uniform(size=len(sdt1)))*(intersection["max"] - intersection["min"]) + intersection["min"]
    return (sdt1 + rand_change, sdt2 + rand_change)

df = pd.DataFrame([
        {"start": dt.datetime(2000, 1, 1), "end": dt.datetime(2000, 12, 31)},
        {"start": dt.datetime(2000, 5, 18), "end": dt.datetime(2001, 8, 20)},
    ])

df2 = pd.DataFrame(df)
df2["start"], df2["end"] = series_rand_date_diff_keep_year_and_interval(df["start"], df["end"])
print(df2.head())

Multicolumn Pandas Solution

Looking again at the question, there are many columns in the sequence of events, all of them representing dates, and some of them of NaT values (null dates). If we want the same restrictions to apply, and keep the relative distance between all events in the series of events, without changing the year of any of the values, and also accepting NaT columns, we have to change a few things. Instead of listing the changes, lets go straight into the code:

import datetime as dt
import pandas as pd
import numpy.random as rnd
import numpy as np
from functools import reduce

def manyseries_rand_date_diff_keep_year_and_interval(*sdts):
    ranges = list(map(
        lambda sdt:
            {
                "min": sdt.apply(lambda dt: dt.replace(month=1,  day=1 ) - dt),
                "max": sdt.apply(lambda dt: dt.replace(month=12, day=31) - dt),
            },
        sdts
        ))
    intersection = reduce(
        lambda range1, range2:
            {
                "min": pd.concat([range1["min"], range2["min"]], axis=1).max(axis=1),
                "max": pd.concat([range1["max"], range2["max"]], axis=1).min(axis=1),
            },
        ranges
        )
    rand_change = pd.Series(rnd.uniform(size=len(intersection["max"])))*(intersection["max"] - intersection["min"]) + intersection["min"]
    return list(map(lambda sdt: sdt + rand_change, sdts))

def setup_diffs(df1, df2):
    df1['diff_birth_death'] = df1['date_death'] - df1['date_birth']
    df1['diff_birth_death'] = df1['diff_birth_death']/np.timedelta64(1,'D')

    df2['diff_birth_diag_start'] = df2['diag_start'] - df1['date_birth']
    df2['diff_birth_diag_end'] = df2['diag_end'] - df1['date_birth']
    df2['diff_birth_diag_start'] = df2['diff_birth_diag_start']/np.timedelta64(1,'D')
    df2['diff_birth_diag_end'] = df2['diff_birth_diag_end']/np.timedelta64(1,'D')

df1 = pd.DataFrame({'person_id': [1, 2, 3, 4, 5],
                        'date_birth': ['12/30/1961', '05/29/1967', '02/03/1957', '7/27/1959', '01/13/1971'],
                        'date_death': ['07/23/2017', '05/29/2017', '02/03/2015', np.nan,      np.nan]})
df1['date_birth'] = pd.to_datetime(df1['date_birth'])
df1['date_death'] = pd.to_datetime(df1['date_death'])

df2 = pd.DataFrame({'person_id': [1,1,1,2,3],
                    'visit_id':['A1','A2','A3','B1','B2'],
                    'diag_start': ['01/01/2012', '02/25/2017', '02/03/2015', '07/27/2016', '01/13/2011'],
                    'diag_end': ['05/03/2012','05/29/2017','03/03/2015','08/15/2016','02/13/2011']})
df2['diag_start'] = pd.to_datetime(df2['diag_start'])
df2['diag_end'] = pd.to_datetime(df2['diag_end'])
setup_diffs(df1, df2)

display(df1)
display(df2)

series_list = manyseries_rand_date_diff_keep_year_and_interval(
    df1['date_birth'], df1['date_death'], df2['diag_start'], df2['diag_end'])
df1['date_birth'], df1['date_death'], df2['diag_start'], df2['diag_end'] = series_list
setup_diffs(df1, df2)

display(df1)
display(df2)

This time, I used Jupyter Notebook to better visualize the DataFrames:

Hope this helps! Any comments and suggestion are welcome.