Split dates into time ranges in pandas

Question

14  [2018-03-14, 2018-03-13, 2017-03-06, 2017-02-13]
15  [2017-07-26, 2017-06-09, 2017-02-24]
16  [2018-09-06, 2018-07-06, 2018-07-04, 2017-10-20]
17  [2018-10-03, 2018-09-13, 2018-09-12, 2018-08-3]
18  [2017-02-08]

this is my data, every ID has it's own dates that range between 2017-02-05 and 2018-06-30. I need to split dates into 5 time ranges of 4 months each, so that for the first 4 months every ID should have dates only in that time range (from 2017-02-05 to 2017-06-05), like this

14  [2017-03-06, 2017-02-13]
15  [2017-02-24]
16  [null] # or delete empty rows, it doesn't matter
17  [null]
18  [2017-02-08]

then for 2017-06-05 to 2017-10-05 and so on for every 4 month ranges. Also I can't use nested for loops because the data is too big. This is what I tried so far

months_4 = individual_dates.copy()

for _ in months_4['Date']:
    _ = np.where(pd.to_datetime(_) <= pd.to_datetime('2017-9-02'), _, np.datetime64('NaT'))

and

months_8 = individual_dates.copy()
range_8 = pd.date_range(start='2017-9-02', end='2017-11-02')

for _ in months_8['Date']:
     _ = _[np.isin(_, range_8)]

achieved absolutely no result, data stays the same no matter what

update: I did what you said

individual_dates['Date'] = individual_dates['Date'].str.strip('[]').str.split(', ')


df = pd.DataFrame({

'Date' : list(chain.from_iterable(individual_dates['Date'].tolist())), 
'ID' : individual_dates['ClientId'].repeat(individual_dates['Date'].str.len())

})

df

and here is the result

Date    ID
0   '2018-06-30T00:00:00.000000000' '2018-06-29T00...   14
1   '2017-03-28T00:00:00.000000000' '2017-03-27T00...   15
2   '2018-03-14T00:00:00.000000000' '2018-03-13T00...   16
3   '2017-12-14T00:00:00.000000000' '2017-03-28T00...   17
4   '2017-05-30T00:00:00.000000000' '2017-05-22T00...   18
5   '2017-03-28T00:00:00.000000000' '2017-03-27T00...   19
6   '2017-03-27T00:00:00.000000000' '2017-03-26T00...   20
7   '2017-12-15T00:00:00.000000000' '2017-11-20T00...   21
8   '2017-07-05T00:00:00.000000000' '2017-07-04T00...   22
9   '2017-12-12T00:00:00.000000000' '2017-04-06T00...   23
10  '2017-05-21T00:00:00.000000000' '2017-05-07T00...   24

user_2901002 · Answer

For better performance I suggest convert list to column - flatten it and then filtering by isin with boolean indexing:

from itertools import chain

df = pd.DataFrame({
    'Date' : list(chain.from_iterable(individual_dates['Date'].tolist())), 
    'ID' : individual_dates['ID'].repeat(individual_dates['Date'].str.len())
})

range_8 = pd.date_range(start='2017-02-05', end='2017-06-05')

df['Date'] = pd.to_datetime(df['Date'])

df = df[df['Date'].isin(range_8)]
print (df)
        Date  ID
0 2017-03-06  14
0 2017-02-13  14
1 2017-02-24  15
4 2017-02-08  18

Split dates into time ranges in pandas

1 Answers