Utilize Python Input() to perform DateShift on a dataset

Question

I have a dataset where, whenever a date value in the Update column is entered into the input() prompt, Date1, Date2 and Date3 values will be updated according to these rules:

   Date1 is 5 months from the date entered in the input() prompt
   Date2 is 2 months from the date entered in the input() prompt
   Date3 is 1 month from the date entered in the input() prompt

The only data that is changing are the dates, which are essentially getting shifted based upon the user input.

Data

ID   Stat   Date1     Date2     Date3       Update
aa   500    1/1/2021  4/1/2021  5/1/2021    6/1/2021
bb   800    1/1/2021  4/1/2021  5/1/2021    6/1/2021

Desired

Input prompt will ask user which date value they wish to input. User inputs the date '8/1/2021', which updates the remaining column date values. based on rules above.

ID   Stat   Date1     Date2     Date3       Update
aa   500    3/1/2021   6/1/2021 7/1/2021    8/1/2021
bb   800    3/1/2021   6/1/2021 7/1/2021    8/1/2021

Doing

I believe I can use a combination of a function as well as user prompt to approach this problem.

Type input

datevalue = pd.to_datetime(input("Enter date value: "))
print(datevalue)

Use input variable in function or script to create date update for Date1, Date2 and Date3

df[0] = df[1].apply(lambda x: datevalue - pd.DateOffset(months=x))

s = df['Update'].str.replace(r'(\S+) (\S+)', r'\2\1')
df['Update'] = (pd.PeriodIndex(s, freq='D') + 3).strftime('D%q %Y')

I am looking for some starting point suggestion or a good foundation/documentation on how to best approach this problem. I am still researching. Any suggestion is appreciated.

So, for e.g., since the user input is 2 months greater than Update, add 2 months to all dates? — user17242583
– user17242583, Commented Dec 14, 2021 at 2:19
Hi @user17242583 all dates are updated from the value in the 'Update' column Date 1 is 5 months from the Update column value Date 2 is 2 months from the Update column value Date 3 is 1 month from the Update column value — Lynn
– Lynn, Commented Dec 14, 2021 at 2:28
When you say "Date 1 is 5 months from the Update column value" do you mean "Date 1 should be 5 months from the Update column value", i.e., that's what you want it to be? — user17242583
– user17242583, Commented Dec 14, 2021 at 2:30
yes it is what I am wanting it to be. Lets say user enters a new date of 10/1/2021 in the update column. I now want Date1, Date2 and Date3 to be updated based on that input. — Lynn
– Lynn, Commented Dec 14, 2021 at 2:32
And by "updated" you mean you want the difference between old update and new update to be added to all the dates, right? If so, see my answer below. — user17242583
– user17242583, Commented Dec 14, 2021 at 2:33

Shubham Sharma · Accepted Answer · 2021-12-14 03:23:57Z

2

We can define a mapping dictionary which maps the column names to corresponding dateoffset, then create a series from this dictionary and subtract the datevalue from offsets, finally assign the updated date values back to dataframe

d = {
    'Date1': pd.DateOffset(months=5),
    'Date2': pd.DateOffset(months=2),
    'Date3': pd.DateOffset(months=1),
}

s = pd.Series(d).rsub(datevalue)
df.assign(**{**s, 'Update': datevalue})

   ID  Stat      Date1      Date2      Date3     Update
0  aa   500 2021-03-01 2021-06-01 2021-07-01 2021-08-01
1  bb   800 2021-03-01 2021-06-01 2021-07-01 2021-08-01

answered Dec 14, 2021 at 3:23

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

6 Comments

Lynn Over a year ago

Thank you - what does the ‘**’ symbolize? @shubham

user17242583 Over a year ago

It expands a dict object into something else, @Lynn. For the first **, it expands the keys/values of the dict as keyword args.

Lynn Over a year ago

@shubham, I wonder if you could respond to my latest post? thank you

Shubham Sharma Over a year ago

@Lynn Sure, I'll try to answer.

Lynn Over a year ago

Ok yes I am trying to incorporate the conditional -still researching thank you kindly

|

user17242583 · Accepted Answer · 2021-12-14 02:31:40Z

1

I think you're looking for something like this:

cols = df.filter(like='Date').columns.tolist() + ['Update']
df[cols] = df[cols].apply(lambda col: pd.to_datetime(col) + (datevalue - df['Update']))

Output:

>>> df
   ID  Stat      Date1      Date2      Date3     Update
0  aa   500 2021-03-03 2021-06-01 2021-07-01 2021-08-01
1  bb   800 2021-03-03 2021-06-01 2021-07-01 2021-08-01

answered Dec 14, 2021 at 2:31

user17242583

Collectives™ on Stack Overflow

Utilize Python Input() to perform DateShift on a dataset

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related