How to calculate time-difference in pandas with NaN-values

user_14614075 0
Viewed 490

I´m relatively new to Pandas and tried the search already, but I couldn´t find a solution. I have a dataframe with Transaction-No., customerId and the date of purchase which looks like this:

Transaction   12345    12346       12347     12348       12349
customerID
1             NaN    2019-09-01    NaN     2019-09-11      2019-09-22...
2           2019-10-01 NaN         NaN         NaN      2019-10-07...
3    ...

The dataframe has [6334 rows x 8557 columns]. Every row has NaN-values, as the Transaction-No. is unique.

I would like to calculate the date difference for each row so I get

customerID    Datedifference1    Datedifference2     etc.
1                10                    11
2                 6
3   ...

I´m struggling to get a list with the datedifferences for every customerId. Is there a way to ignore NaN in the dataframe and to only calculate on the values that are not NaN? I would like to have a list with customerId and the datediff between purchase 1 and 2, 2 and 3, etc. to estimate the days until the next purchase will occur.

Is there a solution for that?

pandas nan python
1 Answers

Idea is reshape data by DataFrame.stack, then get differences, remove first missing values per groups and reshape back:

df = df.apply(pd.to_datetime)

df1 = (df.stack()
         .groupby(level=0)
         .diff()
         .dropna()
         .dt.days
         .reset_index(level=1, drop=True)
         .to_frame())

df1 = (df1.set_index(df1.groupby(['customerID']).cumcount(), append=True)[0]
          .unstack()
          .add_prefix('Datedifference'))
print (df1)
             Datedifference0  Datedifference1
Transaction                                  
1                       10.0             11.0
2                        6.0              NaN

EDIT: If input data are different, solution is changed - convert column to datetimes, create new column by DataFrameGroupBy.diff for differencies, remove only NaN rows by DataFrame.dropna and last reshape with DataFrame.set_index and unstack with counter Series by GroupBy.cumcount:

print (df1)
   customerID Transaction       date
0           1       12346 2019-09-01
1           1       12348 2019-09-11
2           1       12349 2019-09-22
3           2       12345 2019-10-01
4           2       12349 2019-10-07

df1['date'] = pd.to_datetime(df1['date'])
df1['diff'] = df1.groupby('customerID')['date'].diff().dt.days
df1 = df1.dropna(subset=['diff'])

df2 = (df1.set_index(['customerID', df1.groupby('customerID').cumcount()])['diff']
          .unstack()
          .add_prefix('Datedifference'))
print (df2)
            Datedifference0  Datedifference1
customerID                                  
1                      10.0             11.0
2                       6.0              NaN
user_29010020
Related