Create a new Date column

Hi Alexé,

It is still a bit confusing what you are trying to achieve. Let's assume you have a dataset that has a Date column in 'yyyy-MM' format and you don't have all Date values from the start date until today in your dataset. Example below.

Value	Date	Value 2
1	2005-01	1000
2	2006-03	1001
3	2007-03	1002
4	2020-01	1003

If you want to modify the dataset to include all Dates from the start Date until today (insert missing Dates into dataset), then one of the ways to achieve this is to use the Python Code recipe.

Below is the code I wrote to complete this task.

# -*- coding: utf-8 -*-
import dataiku
import pandas as pd
from datetime import datetime

# Read recipe inputs
dates = dataiku.Dataset("input_dataset") #input dataset
dates_df = dates.get_dataframe()

#sort by date
sorted_df = dates_df.sort_values(by='Date', ascending=True)
#get start date
start_date = sorted_df.iloc[0]['Date']

#generate date range
year_month_range = pd.date_range(start_date, datetime.today().strftime("%Y-%m"), freq='MS')
# create DataFrame
df_year_month_range = pd.DataFrame(year_month_range)
#rename column to merge two datasets
df_year_month_range.rename(columns = {0:'Date'}, inplace = True)
#format date to the same fromat for merging
df_year_month_range['Date'] = df_year_month_range['Date'].dt.strftime('%Y-%m')
#merge two DataFrames
df_merged = pd.merge(df_year_month_range, dates_df, how="left", on=["Date"])

# Write recipe outputs
dates_added = dataiku.Dataset("output_dataset")
dates_added.write_with_schema(df_merged)

The above code reads input dataset, then gets start Date from column Date, then creates date range DataFrame with all Dates from the start Date until today in 'yyyy-MM' format, then merge it with DataFrame created from input dataset, and then saves merged DataFrame into output dataset.

As the result, you will get a dataset that will include all dates. To better understand what has been done, you can find input and output datasets attached in form of CSV files.

I hope this will be useful,

Vitaliy