Creating id in a DataFrame for a range of dates

Question

I want to create a dataframe with each of the below id to be in a column on each date in df1.

id = ['A', 'B', 'C']
df1 = pd.DataFrame()
df1['date'] = pd.date_range(start= '2000-1-1', end = '2010-12-31', freq = 'B')

I would like the data to look like the following:

           date     id
0    2000-01-03      A
1    2000-01-03      B
2    2000-01-03      C
3    2000-01-04      A
4    2000-01-04      B
5    2000-01-04      C
6    2000-01-05      A
7    2000-01-05      B 
8    2000-01-05      C

Any help would be awesome!

Answer 1

You can concatenate dataframes constructed from each value in your input list:

L = ['A', 'B', 'C']
dates = pd.date_range(start='2000-1-1', end='2010-12-31', freq='B')

df = pd.concat((pd.DataFrame({'date': dates}).assign(id=i) for i in L), ignore_index=True)
df = df.iloc[np.lexsort((df.index, df['date']))].reset_index(drop=True)

print(df)

           date id
0    2000-01-03  A
1    2000-01-03  B
2    2000-01-03  C
3    2000-01-04  A
4    2000-01-04  B
5    2000-01-04  C
...         ... ..

8604 2010-12-30  A
8605 2010-12-30  B
8606 2010-12-30  C
8607 2010-12-31  A
8608 2010-12-31  B
8609 2010-12-31  C