About sai_ch

sai_ch · ‎10-07-2016

Thanks for the solution ballardw

sai_ch · ‎10-07-2016

Good observation ballardw, the need table should have only 4 observations. The distribution of Numer does not need to be "random". In the example given, the Numer for first two observations can be populated as 1 and the remaining 2 will have a value of 0.

sai_ch · ‎10-07-2016

Hello, I am trying to expand the number of observations in a dataset based on certain conditions and am little stuck. I think it will be easier to explain through an example: Have Need Date Denom Numer Date Denom Numer 1/1/2008 4 2 1/1/2008 1 1 4/1/2008 3 1 1/1/2008 1 0 7/1/2008 2 2 1/1/2008 1 1 10/1/2008 4 1 1/1/2008 1 0 1/1/2008 1 0 4/1/2008 1 0 4/1/2008 1 1 4/1/2008 1 0 . . . So, I am expanding the first observation based on the value of Denom, i.e. 4, and randomly distributing the value of Numer, i.e. 2, across the four observations. Any help to solve this is greatly appreciated. Thanks

sai_ch · ‎08-22-2016

@RobPratt @Ksharp Is there a way to accept the both approaches as solutions. Thanks again for all the help and the patience.

sai_ch · ‎08-19-2016

@RobPratt My bad, should have stated that the Def column has changed from 0 and 1s to total count. I made the change in the next line too. With further rolling up the data and using dynamic programming, I have been able to execute the code. Thanks again for that. Is there any available resource that will help me conceptually understand as to how the shortest path algorithm fits my problem. Thanks

sai_ch · ‎08-19-2016

@RobPratt In reference to the code pasted below, do we have to multiply the sum of defaults by total customer count. The idea is to calculate simple average, not weighted. When I roll up the data, I am counting the total number defaults as wells customers. num avg_def {<i,j> in OBS_PAIRS} = ( if <i,j-1> not in OBS_PAIRS then (sum {k in i..j-1} def[k] * cust_cnt[k]) / num_cust_ij[i,j] else (avg_def[i,j-1] * num_cust_ij[i,j-1] + def[j-1] * cust_cnt[j-1]) / num_cust_ij[i,j] ); I am changing the part highlighted in red with the below code, please let me know what you think. num avg_def {<i,j> in OBS_PAIRS} = ( if <i,j-1> not in OBS_PAIRS then (sum {k in i..j-1} def[k]) / num_cust_ij[i,j] else (avg_def[i,j-1] * num_cust_ij[i,j-1] + def[j-1] * cust_cnt[j-1]) / num_cust_ij[i,j] );

sai_ch · ‎08-19-2016

@Ksharp The customer ID is unique within a group. But each customer ID in the rolled up dataset could have multiple underlying customers. For example, the first customer_ID of 1, which is unique, could have 100 corresponding customers. At the customer ID level the bounds are satisfied but once we disaggregate the bounds are violated.

sai_ch · ‎08-18-2016

@RobPratt Thanks for posting another solution to the problem. Appreciate it.

sai_ch · ‎08-18-2016

@Ksharp Is it possible to put bounds on the count of customers in a group instead of the number of observations in the group?

sai_ch · ‎08-18-2016

@RobPratt You are right, the code is imposing restriction on the number of observations on each bucket. The number of customers per grade is violating the constraints. I put the new dataset with count of customers after @Ksharp had posted the solution with constraints. If possible, if there is a way to put a constraint on the number of customer per bucket, that would be ideal. Thanks

sai_ch · ‎08-18-2016

@Ksharp When I originally posted the question, I wasn't rolling up the input dataset. But then ran into memory issues. To circumvent the memory issue, I rolled up the dataset and that necessitated the introduction of total count of customers. @RobPratt I am thinking through the calculation of Def rate suggested by you.

sai_ch · ‎08-17-2016

@RobPratt @Ksharp Maybe I didn't explain the objective function to well. Hopefully, the modified code will explain it better, assuming its correct. I changed the input dataset slightly to account for roll up (~16k as opposed to ~200k) and to calculate the def rate accurately. pd def cust_cnt 0.001% 0 1 0.002% 0 3 0.003% 0 2 n=nrow(start_end); n1=nrow(xx); temp=j(n1,3,.); temp1=j(n,1,.); do i=1 to n; idx=start_end[i,1]:start_end[i,2]; do j=start_end[i,1] to start_end[i,2]; temp[j,1]=have[j,1]; temp[j,2]=sum(have[idx,2])/sum(have[idx,3]); temp[j,3]=abs(temp[j,1]-temp[j,2]); end; temp1[i,1]=sum(temp[idx,3]); end; obj=sum(temp1); return (obj); thanks

sai_ch · ‎08-17-2016

@RobPratt @Ksharp I really appreciate all the help in solving this problem. I am still getting out of memory error with ~16k observations with OPTMODEL. Since this is my first exposure to OPTMODEL, its taking longer to understand and make small tweaks. I have used IML previously. I fully intend to get both solutions working. I am going to randomly select 1000 observations and run the OPTMODEL code

sai_ch · ‎08-16-2016

@RobPratt If it was upto me, I would have happily supplied the data. Since this data contains sensitive information, its difficult for me to share it. Sorry about that. @Ksharp I was able to modify the objective function to first minimize the error for each observation in the bucket and then for all the buckets together. Is it possible to appy couple of constraints to the optimization. No bucket should have more than 25% of observations or less than 1%. Have been trying to use the repair strategy but with no success. Thanks

sai_ch · ‎08-16-2016

@Ksharp By spending a considerable part of yesterday, I think, I understand what the code is doing. Thank you for initiating me into the world of optimization, it is fascinating. One thought, is it possible to tweak the objective function a little bit. Instead of minmizing the difference between the average PD and average Def for a bucket, is it possible to first minimize the distance between each customer and the average Def for a bucket and then to minimize the sum of errors for all the buckets. Essentially, it becomes a double minimization problem. @RobPratt I was able to reduce the dataset from ~200k to ~16k and will try to implement the Network solution. Thanks for the suggestion.

Online Status	Offline
Date Last Visited	‎10-17-2016 01:55 PM

Re: Expanding the number of observations

Re: Expanding the number of observations

Expanding the number of observations

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: How do I create monthly observations from Yearly

Re: How do I create monthly observations from Yearly

Re: Conditional Merge of 2 Datasets

Re: Expanding the number of observations

Re: Expanding the number of observations

Expanding the number of observations

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket

Re: Finding the optimal cut-off for each bucket