Count of Year in Python-CodePudding

How can I find count of year in python from certain date and a date since people opened an account (CRDACCT_DTE_OPEN)? The certain date (MIS_DATE) is 2021-03-01 with format= '%Y%m%d'.

The dataset given below:

import pandas as pd
df = pd.DataFrame(
     { "MIS_DATE": ["2018-03-02", "2020-03-26", "2019-08-17", "2019-08-17", "2019-08-19"],
       "CRDACCT_DTE_OPEN": ["2021-03-31", "2021-03-31", "2021-03-31", "2021-03-31",  "2021-03-31"]})

Format date:

df['CRDACCT_DTE_OPEN'] = pd.to_datetime(df['CRDACCT_DTE_OPEN'], format='%Y%m%d')
df['MIS_DATE'] = pd.to_datetime(df['MIS_DATE'], format='%d%m%Y')

I've tried to do this operation. Let's say MOB is subtract of MIS_DATE - CRDACCT_DTE_OPEN, but the result is not what I expected. I want the output in form of year format, for example if someone opened an account in 2018-03-31, hence the MOB is 3. Meaning that 3 years since that person open an account.

MOB = df['MIS_DATE'] - df['CRDACCT_DTE_OPEN']
MOB

Output:

1        370 days
2        592 days
3        592 days
4        590 days
 ...   

Name: MOB, Length: 5, dtype: timedelta64[ns]

CodePudding user response：

this is what you need.

`df['col_of_datetime'].dt.year`

here is the example

import pandas as pd
df = pd.DataFrame(
    {"MIS_DATE": ["2018-03-02", "2020-03-26", "2019-08-17", "2019-08-17", "2019-08-19"],
     "CRDACCT_DTE_OPEN": ["2021-03-31", "2021-03-31", "2021-03-31", "2021-03-31",  "2021-03-31"]})

df['CRDACCT_DTE_OPEN'] = pd.to_datetime(df['CRDACCT_DTE_OPEN'], format='%Y-%m-%d')
df['MIS_DATE'] = pd.to_datetime(df['MIS_DATE'], format='%Y-%m-%d')

target_year = 2021
result = target_year - df['MIS_DATE'].dt.year
print(result)

Output:

0    3
1    1
2    2
3    2
4    2
Name: MIS_DATE, dtype: int64

CodePudding user response：

Please try this: Here we are using the date_range function from Pandas

for x in range(len(df)):
print(len(pd.date_range(start=pd.to_datetime(df.iloc[x][0]),end=pd.to_datetime(df.iloc[x][1]),freq='Y')))

3
1
2
2
2

You can change freq ='M' for months

CodePudding user response：

You can use numpy's timedelta()

import numpy
(MOB/numpy.timedelta64(1, 'Y')).round()
0    3.0
1    1.0
2    2.0
3    2.0
4    2.0
dtype: float64