0

Need Help, I'm trying to get a Date column based on user input it will decrease the current date with one month interval with increase in Total Age column Number.

CurrentDf = {
  "VIN": ['v1','v1','v1','v2','v2','v2','v2','v3','v3','v3'],
  "Total Age": [10,10,10, 08,08,08,08,11,11,11],
  "Months": [05,07,09,01,06,07,08,02,05,07],
  "Monthly Revenue": [1108,4330,7121,1998,1783,9628, 2082,8763,5683, 5780]
}

Total_Age = [int(x) for x in input('Please enter Total Age?= ').split(',')]

CurrentDf:

VIN Total Age Months Monthly Revenue
v1 10 05 1108
v1 10 07 4330
v1 10 09 7121
v2 08 01 1998
v2 08 06 1783
v2 08 07 9628
v2 08 08 2082
v3 11 02 8763
v3 11 05 5683
v3 11 07 5780

UserInput: [8]

outputDF:

VIN Total Age Months Monthly Revenue Date
v1 10 05 1108 2022-04-01
v1 10 07 4330 2022-04-01
v1 10 09 7121 2022-04-01
v2 08 01 1998 2022-05-01
v2 08 06 1783 2022-05-01
v2 08 07 9628 2022-05-01
v2 08 08 2082 2022-05-01
v3 11 02 8763 2022-03-01
v3 11 05 5683 2022-03-01
v3 11 07 5780 2022-03-01
Ninja
  • 79
  • 1
  • 2
  • 6
  • What do you mean by "decrease the current date with one month interval with increase in Total Age column Number"? The "Total Age" column in your expected output is the same as your input – not_speshal May 01 '22 at 16:27
  • yess @not_speshal – Ninja May 01 '22 at 16:32
  • Please explain your "Date" column in your expected output. Your question is not clear. – not_speshal May 01 '22 at 16:34
  • If you are having an issue with the datetime module, then you may want to look at this answer: https://stackoverflow.com/questions/4130922/how-to-increment-datetime-by-custom-months-in-python-without-using-library – Florian Fasmeyer May 01 '22 at 16:35
  • If the userInput is 8 it should add current date in all the row where Total age is "8" & have one month interval with current date where Total Age age is 9 & so on @not_speshal – Ninja May 01 '22 at 16:44
  • You have one month interval where Total Age is 10 and not 9 (there is no 9 in your dataframe). – not_speshal May 01 '22 at 16:46
  • @not_speshal whatever the next number is after 8 – Ninja May 01 '22 at 16:48
  • Provide a better example. What if user enters 9? What if the ages in your dataframe are 5, 9, 24? Explain the *general* logic and not for a specific case. – not_speshal May 01 '22 at 16:50
  • Could you please fix the [minial, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) please? When I use your code, your values (08,02, ...) produce a SyntaxError: invalid token. Also, add the imports for pandas. – Florian Fasmeyer May 01 '22 at 16:53
  • @not_speshal Total Age is alway going to b in increasing order, In other words we can say userinput is going to b the smallest number – Ninja May 01 '22 at 16:55

1 Answers1

0

If I understand correctly, try ranking the ages to produce the desired month offset:

input_age = int(input('Please enter Total Age?= '))

df["Date"] = df["Total Age"].astype(int).sub(input_age).rank(method='dense').apply(lambda x: pd.Timestamp.today().date()-pd.DateOffset(months=x-1))
Output with input_age = 8:
>>> df
  VIN Total Age Months  Monthly Revenue       Date
0  v1        10     05             1108 2022-04-01
1  v1        10     07             4330 2022-04-01
2  v1        10     09             7121 2022-04-01
3  v2        08     01             1998 2022-05-01
4  v2        08     06             1783 2022-05-01
5  v2        08     07             9628 2022-05-01
6  v2        08     08             2082 2022-05-01
7  v3        11     02             8763 2022-03-01
8  v3        11     05             5683 2022-03-01
9  v3        11     07             5780 2022-03-01
Input df:
df = pd.DataFrame({"VIN": ['v1','v1','v1','v2','v2','v2','v2','v3','v3','v3'],
                   "Total Age": ['10','10','10','08','08','08','08','11','11','11'],
                   "Months": ['05','07','09','01','06','07','08','02','05','07'],
                   "Monthly Revenue": [1108,4330,7121,1998,1783,9628, 2082,8763,5683, 5780]
                   })
not_speshal
  • 22,093
  • 2
  • 15
  • 30