So, I’ve a problem that I managed to get a solution but it doesn’t feel right nor it’s efficient.
There’s a list of assets assets = ['1', '2', '3', ..., 'n'], and for each asset, I’ve a unique input for each date in a date range as date_range = ['2023-01-01', '2023-01-02', ..., '202x-xx-xx'].
To build the calendar I’ve implemented as:
#list of assets
assets = ['A1','A2','A3','A4']
#Dataframe with data that will be used to build the calendar and be the reference for inputs
data = {'PRODUCT': ['A1', 'B1', 'C1', 'D1'], 'DATE': [2023-01-02, 2023-07-15, 2023-12-21]}
df_data = pd.DataFrame(data)
#Building the columns of the calendar
today = pd.Timestamp(2023, 1, 1)
today_str = str(today)
columns = list()
columns.append('ASSETS')
date_max = df_data['DATE'].max()
delta = (data_max - today).days
for i in range(0, delta+1):
columns.append(str((today+timedelta(days=i)).date()))
#Building the calendar and using the assets as index
df_calendar = pd.DataFrame(columns = columns)
df_calendar['ASSETS'] = assets
df_calendar.index = list(df_calendar['ASSETS'])
df_calendar= df_calendar.drop('ASSETS', axis=1)
df_calendar= df_calendar.fillna(0)
The final result is:
| index | 2023-01-01 | 2023-01-02 | ………. | 2023-12-21 |
|---|---|---|---|---|
| A1 | 0 | 0 | . | 0 |
| A2 | 0 | 0 | . | 0 |
| A3 | 0 | 0 | . | 0 |
| A4 | 0 | 0 | . | 0 |
Any help or ideas are welcomed, thank you all!
I started experimenting with pd.date_range and will try another solution.
>Solution :
IIUC, you can use:
import pandas as pd
assets = [f'A{i}' for i in range(1, 10)]
date_range = pd.date_range('2023-01-14', '2023-01-19', freq='D')
df = pd.DataFrame(0, index=assets, columns=date_range)
Output:
>>> df
2023-01-14 2023-01-15 2023-01-16 2023-01-17 2023-01-18 2023-01-19
A1 0 0 0 0 0 0
A2 0 0 0 0 0 0
A3 0 0 0 0 0 0
A4 0 0 0 0 0 0
A5 0 0 0 0 0 0
A6 0 0 0 0 0 0
A7 0 0 0 0 0 0
A8 0 0 0 0 0 0
A9 0 0 0 0 0 0