How do I insert rows based on a column of start dates and end dates with python?

Question

I have a data frame of product numbers that are on sales promotions. The columns include the product number, start date, end date, promotion type and promotion description. The dates could span up to 4 months. I need to add rows to account for the months between the start and end dates.

Here is an example of the data currently:

import pandas as pd

sales_dict = {}
sales_dict['item'] = ['100179K', '100086K']
sales_dict['start_date'] = [201703, 201801]
sales_dict['end_date'] = [201707, 201802]
sales_dict['promotin_type'] = [1,0]
sales_dict['promotion_desc'] = [0,1]
df = pd.DataFrame.from_dict(sales_dict)

I tried to create a data frame of dates in year_month format from the beginning of the time frame through the end then join the two datasets. But some data seemed to fall out.

I also looked at Creating a single column of dates from a column of start dates and a column of end dates - python but not sure now to fill all the other columns correctly.

This is what I wanted to happen.

sales_dict = {}
sales_dict['item'] = ['100179K','100179K','100179K','100179K','100179K','100086K','100086K']
sales_dict['start_date'] = [201703, 201704, 201705, 201706, 201707, 201801, 201802]
sales_dict['promotin_type'] = [1,1,1,1,1, 0,0]
sales_dict['promotion_desc'] = [0,0,0,0,0,1,1]
df = pd.DataFrame.from_dict(sales_dict)

JordanBH · Accepted Answer · 2022-11-29 18:42:15Z

1

I figured this out although it may not be elegant:

df2 = pd.DataFrame()
for item in df['item'].unique():
    df_ = df[df['item'] == item]
    df_ = pd.concat([df_,df_.apply(lambda dt: pd.date_range(dt['start_date'], dt['end_date'], freq="MS"), axis = 'columns').explode(ignore_index=True)], axis=1)
    df_.drop(['start_date', 'end_date'], axis=1, inplace = True)
    # df_.iloc[:, -1:].rename({'0':'dates'},inplace=True)
    df_.ffill(inplace=True)
    df_.bfill(inplace=True)
    df2 = df2.append(df_)

df2.rename(columns={0:'dates'},inplace=True)

answered Nov 29, 2022 at 18:42

JordanBH

474 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I insert rows based on a column of start dates and end dates with python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related