Random date based on certain range in pandas

Question

My main_csv.csv file looks like

Client_ID     Frequency
123AASD45         10
2345OPU78         9
763LKJ90          2

Here my frequency is the number of dates like if the frequency is 10 that client has to be met 10 times within my 1st quarter working days(Jan 2018-Mar 2018) my desired output should be like

Client_ID    Dates_Reached
123AASD45      01/05/2018 /* random dates */
123AASD45      01/08/2018
 ...............

should I use loop or any other better way this can be done? I tried like below

df=read_csv('main_csv.csv',delimiter='|')

for rows in df:
    i=0
    #generate random date
    i=i+1
    if (i==df['Frequency']):
       break

Possible duplicate of [Generating random dates within a given range in pandas](https://stackoverflow.com/questions/50559078/generating-random-dates-within-a-given-range-in-pandas) — n1tk, Oct 27 '18 at 14:14

score 1 · Accepted Answer · answered Oct 27 '18 at 17:09

First you define a function date_range that takes the start date and end dates and the size of the sample and returns a sample.

import pandas as pd
df = pd.DataFrame({'client':['123AASD45', '2345OPU78', '763LKJ90'], 'frequency':[10,9,2]})

def date_range(n, start='1/1/2011', end='4/1/2011'):
    date_range = pd.date_range(start, end)
    return list(pd.Series(date_range).sample(n))

Then for each client you assign the sample of dates and do some data reshape to so you can join with the original table.

df['dates'] = df['frequency'].apply(lambda x: date_range(x))
df_dates = df['dates'].apply(pd.Series).reset_index()
df_dates = df_dates.melt(id_vars='index').dropna().drop(['variable'], axis=1).set_index('index')

Finally you join on the original dataset assuming there is one row per client.

df.join(df_dates)

When I join this I am getting data like 0001ABASD ['2018-09-07','2018-03-05'] but I wanted in Client_ID and dates row wise. How can I do that? — banpd, Oct 29 '18 at 09:45
the field 'value' contains the date, you can drop the dates field — Franco Piccolo, Oct 29 '18 at 10:05

Random date based on certain range in pandas

1 Answers1