'How can I group panel data ranges into predefined fixed data points
I am struggling with grouping panel data ranges into predefined fixed data points. The data I am working with is various mortgage lenders and the interest rate for each loan-to-value range (the higher the loan compared to the value of the property, the higher the interest). Sample dataset:
| Lender | Loan-to-value | Mortgage Rate |
|---|---|---|
| A | =<60% |
3.0% |
| A | =<80% |
3.5% |
| A | =<100% |
3.9% |
| B | =<45% |
2.9% |
| B | =<75% |
3.4% |
| B | >75% |
3.8% |
| C | =<60% |
2.9% |
| C | =<70% |
3.1% |
| C | =<80% |
3.3% |
| C | >80% |
3.8% |
I want to create a table where I can pre-define the Loan-to-value by steps of 10% and find the corresponding interest rate for each lender. For example:
| LTV |
|---|
| 50% |
| 60% |
| 70% |
| 80% |
| 90% |
| 100% |
The result would then be:
| Lender | Loan-to-value | Rate |
|---|---|---|
| A | 50% | 3.0% |
| A | 60% | 3.0% |
| A | 70% | 3.5% |
| A | 80% | 3.5% |
| A | 90% | 3.9% |
| A | 100% | 3.9% |
| B | 50% | 3.4% |
| B | 60% | 3.4% |
Unfortunately I have no idea where to begin. My idea was to define the upper and lower boundary but it is dependent on the classifications used by a lender (some lenders call is >75% while others call it =<100%). Any help or suggestions would be much appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|