Calculate the year difference between columns when multiple values ​​are present in one cell

Question

I ask you to participate in this. I have the following data framework:

df
    dob         date                                         
1   8/11/1966   3/1/1990, 5/1/2000, 8/1/2010
2   6/13/1970   4/1/2014, 3/1/2016, 4/1/2017
3   10/10/2010  4/13/2017

My goal is to create a column showing the difference in years between the "dob" and "date" columns as follows:

df
    dob         date                            difference                  
1   8/11/1966   3/1/1990, 5/1/2000, 8/1/2010    23.570, 23.740, 23.992
2   6/13/1970   4/1/2014, 3/1/2016, 4/1/2017    43.833, 45.751, 46.836
3   10/10/2010  4/13/2017                       6.512

Using the following code,

diff = (df['date'].sub(df['dob']))/365
diff = (diff / np.timedelta64(1, 'D')).astype(float)
df['difference'] = diff.round(3)

I was able to calculate the difference when a single date was presented, but it does not work when there are several values ​​separated by a comma in one cell. How can I achieve my goal? Thanks in advance.

Answer 1

Consider a data block df

df = pd.DataFrame(dict(
        dob=['8/11/1996', '6/13/1970'],
        date=[['3/1/1990', '5/1/2000', '8/1/2010'],
              ['4/1/2014', '3/1/2016', '4/1/2017']]
    )).reindex_axis(['dob', 'date'], 1)

l = df.date.str.len()
ilvl0 = df.index.repeat(l)
ilvl1 = np.concatenate(l.apply(np.arange))
date = pd.Series(
    pd.to_datetime(np.concatenate(df.date.values)),
    [ilvl0, ilvl1]
)

difference = date.sub(
    dob, level=0).dt.days.div(365.25).groupby(level=0).apply(list)
df.assign(difference=difference)

         dob                            date            difference
0  8/11/1996  [3/1/1990, 5/1/2000, 8/1/2010]  [-6.45, 3.72, 13.97]
1  6/13/1970  [4/1/2014, 3/1/2016, 4/1/2017]   [43.8, 45.72, 46.8]

OLD ANSWER

date_df = pd.to_datetime(
    pd.DataFrame(df.date.values.tolist(), df.index).stack()
 ).unstack()

Little magic and ...

df.assign(
    difference=date_df.sub(
        pd.to_datetime(df.dob), 0
    ).stack().dt.days.groupby(level=0).apply(list)
)

         dob                            date             difference
0 1996-08-11  [3/1/1990, 5/1/2000, 8/1/2010]    [-2355, 1359, 5103]
1 1970-06-13  [4/1/2014, 3/1/2016, 4/1/2017]  [15998, 16698, 17094]

If you want it in years, not days

df.assign(
    difference=date_df.sub(
        pd.to_datetime(df.dob), 0
    ).stack().apply(lambda x: x.days / 365.25).round(2).groupby(level=0).apply(list)
)

         dob                            date            difference
0  8/11/1996  [3/1/1990, 5/1/2000, 8/1/2010]  [-6.45, 3.72, 13.97]
1  6/13/1970  [4/1/2014, 3/1/2016, 4/1/2017]   [43.8, 45.72, 46.8]