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)])))
ototal_sum_of_tens = findsum(10, years, quarters)
new = 
for x in sumlist:
sum_of_tens = adjust(total_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
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_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.
Click here to view the jupyter notebook file in a new tab
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
Quarter Q1 for each years.
@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…