0
$\begingroup$

I'm new in python and coding. I'm doing a university project exercise. Last question is :

" For each year, compute the total amount of loans. Each loan that has planned expiration time and disburse time in different years must have its amount distributed proportionally to the number of days in each year.

For example, a loan with:

  1. disburse time = 2016/12/01
  2. planned expiration time = 2018/01/30
  3. amount = 5000USD

has an amount of :

  1. 5000 * 31 / (31+365+30) = 363.85 for 2016,
  2. 5000 * 365 / (31+365+30) = 4284.04 for 2017,
  3. 5000 * 30 / (31+365+30) = 352.11 for 2018. "

I splitted original dataset in two dataset:

  1. one where "planned expiration time" and "disburse time" have the same year;
  2. one where "planned expiration time" and "disburse time" have different year.

I computed the total amount of loans for each years for the first split dataset, but i don't understand how to compute total amount of loans for each years for the second split dataset.

I'm using Jupyter notebook. My code:

loans_cleaned = loans_cleaned.dropna(subset= ['planned_expiration_time', 'disburse_time']) 

loans_cleaned 
loans_cleaned['planned_expiration_time'] = loans_cleaned['planned_expiration_time'].dt.tz_localize(None)

loans_cleaned['disburse_time'] = loans_cleaned['disburse_time'].dt.tz_localize(None)

loans_cleaned['planned_expiration_time'] = loans_cleaned['planned_expiration_time'].dt.normalize()

loans_cleaned['disburse_time'] = loans_cleaned['disburse_time'].dt.normalize()
loans_same_year = pd.DataFrame(loans_cleaned[loans_cleaned['planned_expiration_time'].dt.year == loans_cleaned['disburse_time'].dt.year][["loan_id" , "disburse_time", "planned_expiration_time","loan_amount"]])

loans_same_year.reset_index(drop=True, inplace=True)
loans_same_year

    loan_id  disburse_time  planned_expiration_time     loan_amount
0   658010   2014-01-09     2014-02-15                  400.0
1   659347   2014-01-17     2014-02-21                  625.0
2   659605   2014-01-15     2014-02-20                  350.0
3   660240   2014-01-20     2014-02-21                  125.0
4   661601   2014-01-10     2014-02-25                  1600.0
... ... ... ... ...

loans_same_year['year'] = loans_same_year['disburse_time'].dt.year
loans_amount_year = pd.DataFrame(loans_same_year.groupby('year')['loan_amount'].sum().reset_index())

loans_amount_year

    year    loan_amount
0   2012    103911725.0
1   2013    98427750.0
2   2014    120644250.0
3   2015    131208475.0
4   2016    133271575.0
5   2017    144870625.0
6   2018    85300.0
loans_different_year = pd.DataFrame(loans_cleaned[loans_cleaned['planned_expiration_time'].dt.year != loans_cleaned['disburse_time'].dt.year][["loan_id" , "disburse_time", "planned_expiration_time","loan_amount"]])

loans_different_year.reset_index(drop=True, inplace=True)

loans_different_year

How can I compute the total days for each years of each loans in the loans_different_year and compute the total amount of loans for each years? Thanks for the attention.

i tried to:

def func(disburse_time, planned_time):
    cost=loans_different_year['loan_amount']
    for year in range(disburse_time.year, planned_time.year+1):
        if year==disburse_time.year:
            dict_map[year] = (datetime.date(year, 12, 31) - disburse_time).days
        elif year==planned_time.year:
            dict_map[year] = (planned_time - datetime.date(year-1, 12, 31)).days
        else:
            if year%4==0:
                dict_map[year]=366
            else:
                dict_map[year]=365
    dict_year_share = {year:cost*days/sum(dict_map.values()) for year,days in dict_map.items()}
    return dict_year_share
a = loans_different_year.apply(lambda x: func(x['disburse_time'], x['planned_expiration_time']), axis=1)
a
TypeError: unsupported type for timedelta days component: Timestamp

in the follow line code:
dict_map[year] = (datetime.date(year, 12, 31) - disburse_time).days

i setted:

loans_different_year['planned_expiration_time'] = pd.to_datetime(loans_different_year['planned_expiration_time'])

loans_different_year['disburse_time'] = pd.to_datetime(loans_different_year['disburse_time'])
```
$\endgroup$
0
$\begingroup$

Not sure if a 1-2 liner is possible. This can be a working function

import pandas as pd, datetime

def func(disburse_time, planned_time, loan_amount):
    total_cost=loan_amount
    dict_map = {}
    for year in range(disburse_time.year, planned_time.year+1):
        if year==disburse_time.year:
            dict_map[year] = (pd.to_datetime(datetime.date(year, 12, 31)) - disburse_time).days
        elif year==planned_time.year:
            dict_map[year] = (planned_time - pd.to_datetime(datetime.date(year-1, 12, 31))).days
        else:
            if year%4==0:
                dict_map[year]=366
            else:
                dict_map[year]=365

    dict_year_share = {year:total_cost*days/sum(dict_map.values()) for year,days in dict_map.items()}
    return dict_year_share

Calling on the df rows

dataset = pd.read_csv('/content/loan.csv')
dataset.disburse_time = pd.to_datetime(dataset.disburse_time,format="%Y-%d-%m")
dataset.planned_expiration_time = pd.to_datetime(dataset.planned_expiration_time,format="%Y-%d-%m")

result = dataset.apply(lambda x: pd.Series(func(x['disburse_time'], x['planned_expiration_time'],x['loan_amount'])), axis=1)
result

This is my dataset

"disburse_time","planned_expiration_time","loan_amount"
"1959-01-01","1960-30-11",35000
"1959-01-02","1962-31-08",32000
"1959-01-03","1965-31-05",30000
"1959-01-11","1959-30-11",31000
"1959-01-10","1961-31-03",44000

Also, need a check for boundary scenarios.

$\endgroup$
6
  • $\begingroup$ Thanks so much for the answer, but with my little experience in python, I have not yet understood how to pass to the function all the lines of the 4 columns (loan_id, loan_amount, disburse-time, planned_time) of the loan_different_year dataframe. I tried to do something like, disburse_time = loans_different_year ['disburse_time'], planned_time = loans_different_year ['planned_expiration_time'], without success. $\endgroup$ – Dario Sep 6 '20 at 9:21
  • $\begingroup$ I updated my question with what I tried to do. Your function is exactly what I would like for each observation of the different_year_loans dataset. I don't know if I did it right, I don't think so. thank you so much $\endgroup$ – Dario Sep 6 '20 at 10:03
  • $\begingroup$ Updated. Converted the datetime.time to pandas Object. Also, a slight change in your lambda passed the Cost too from the DataFrame. $\endgroup$ – 10xAI Sep 6 '20 at 11:21
  • $\begingroup$ Everything works perfectly now. Thank you very much especially for your patience. I hope, over time, I can learn python as well as you do. $\endgroup$ – Dario Sep 6 '20 at 11:53
  • $\begingroup$ From result, can i split each years and costs in two different column, So i can compute tot_amount for year? $\endgroup$ – Dario Sep 6 '20 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.