Count value changes

Guys i have this data here in csv sample.csv
i want to count all 10’s that moved to 20 from 1 Quarter to the other and total of all 10’s in each Quarter and have this pivot table below


so far i have tried with this code and it only gives me the counts but not movement

years = overdraft['Year'].unique().tolist()
quarters = overdraft['Quarter'].unique().tolist()
def findsum(value, yearlist=years, quarterlist=quarters):
    '''
    value: days past due to check. No default
    yearlist: number of years you want to search in the dataframe, default is 2013-2018.
    quarters: number of quarters in each year, default is 4, formatted as 'Q1', 'Q2', etc.
    '''
    past_due = []
    for year in yearlist:
        for quarter in quarterlist:
            past_due.append((len(overdraft[(overdraft['Year'] == year) & (overdraft['Quarter'] == quarter) & (overdraft['PAST DUE DAYS'] == value)])))
    return past_due   
ototal_sum_of_tens = findsum(10, years, quarters)
print(ototal_sum_of_tens)
print(len(ototal_sum_of_tens))

def adjust(sumlist):
    new = [0]
    for x in sumlist:
        new.append(x)
    del(new[-1])
    return new
sum_of_tens = adjust(total_sum_of_tens)
print(sum_of_tens)
print(len(sum_of_tens))

Also i have tried this but i still cant figure it out
(term_loans.shift(axis = 1).eq(10)&term_loans.eq(20)).sum().to_frame('Move to 20').join(term_loans.eq(10).sum().rename('eq 10')).T

Hi @ekkpodogui

Can you explain a bit more about what you want to calculate?

I want to calculate all the 10’s under each quarter and record the number of 1o’s that moved from Q1 2013 through to 2018 Q4 so if Q1 2013 had 20 10’s how many moved to 20’s in Q2 2013 if 3 moved then in a new dataframe under Q2 2013 i record 3 so on i hope this is more clearer you can use this image as a guide look at the first row its recording only the 10’s that changed to 20’s and above in other columns.

What I understand, According to that first we have to list all the ACCOUNT NAME from Quarter Q1 and Year 2013 having 10 PAST DUE DAYS. Then we start checking PAST DUE DAYS for that selected accounts from 2013 Q1 to 2019 Q4, if they have a value other than 10 then we will count that account.

Suppose, we have 5 unique accounts from Q1 2013 then for each selected accounts, check it’s PAST DUE DAYS in Q2 2013. Let’s from 5 accounts, 3 accounts having PAST DUE DAYS other than 10. So, under the 2013 Q2 column we will write 3, similarly, we count for other Quarters of each year and put under respective column.

You can see my work on sample.csv file.
Sample_data.ipynb (25.9 KB)

I am selecting ACCOUNT NAME and having 10 in PAST DUE DAYS then we are able to see the change from Q1 starting year to Q4 last year.

Correct me, if I am wrong in understanding your problem.

Thank You,
Prem :slightly_smiling_face: .

Click here to view the jupyter notebook file in a new tab

1 Like

You are so correct @Prem you understood the question so the last 2 loops what is the difference

And this one

In the above loop, you can see I am selecting rows which does not have Quarter Q1 that’s why I am not getting total number of 10’s in Quarter Q1. So, below loop calculating number_of_10 in Quarter Q1 for each years.

Ok i get it now thanks so much you are a saver :heart: :man_dancing: :man_dancing:

1 Like

@Prem sorry to disturb you again actually i gave you a wrong explanation
This is how i am supposed to do the checks for movement…
count 10’s that moved to 30 and above in Q1 2013 and compare with Q1 2014 and record the changes next count 10’s that moved to 30’s in Q2 2013 and compare with Q2 2014 then record the changes so on. Then i forget about 2013 and now compare 2014 and 2015 etc…