[R] Excel User interface for R

[Barry Rowlingson]

On Mon, Mar 4, 2013 at 1:39 PM, Tammy Ma <metal_licaling at live.com> wrote:
>
> HI,
>
> Assume I have the data frame generated from R as the following:
>
> Product     Price     market_share
>   A             100        10%
>   B             110        9%
>  C              120        20%
>  D              90          61%
>
> What I want to do is to have this kind of excel user interface by changing price of certain products, to get the impact of market share generated from R:
> if I change A price from 100 to 90, and change B price from 110 to 100, based on the calculation in R, I want to get the result from excel, for example,
>
>
> Product     Price     market_share
>   A             90        20%
>   B             100       12%
>  C              120       10%
>  D              90         58%
>
>
> I want to have the excel interface which make me be able to change the value in excel cell, then based on the calculation in R, then exported market share impact in excel.
>
> DO you have recommendation for such user interface or sth which make me achieve this task?

 This is just not how you *think* in R. Formulas in Excel are all
hidden, and stuff happens automatically, and if columns are all
dependent on one another then you are redundantly storing information.

 If I was doing a table like this I would only store the original
information, and then write some functions to produce the spreadsheet
with all the derived columns. I would only change the original
information, and then re-run the function that generates the report.

 Now, I really don't understand in your example how changing the price
of A from 100 to 90 results in a change of market_share from 10% to
20%, so I guess there must be some complex formula going on. I'll
simplify....

 Suppose your data is Product, Price, and Unit Sales:

 > d = data.frame(Product=c("A","B","C","D"),Price=c(100,110,120,90),Sales=c(3,5,3,7))
 > d
  Product Price Sales
1       A   100     3
2       B   110     5
3       C   120     3
4       D    90     7


 And in Excel you would normally compute market share by value as
100*Price * Sales / SUM(Price*Sales). Then in R:

 > d$ShareByValue = 100 * d$Price * d$Sales / sum(d$Price*d$Sales)
 > d
  Product Price Sales ShareByValue
1       A   100     3     16.30435
2       B   110     5     29.89130
3       C   120     3     19.56522
4       D    90     7     34.23913

But if the price of Product A was wrong, and you have to change it to
90, then yes, you have to re-run the function:
 > d$Price=90
 > d$ShareByValue = 100 * d$Price * d$Sales / sum(d$Price*d$Sales)
 > d
  Product Price Sales ShareByValue
1       A    90     3     16.66667
2       B    90     5     27.77778
3       C    90     3     16.66667
4       D    90     7     38.88889

But now we can get a bit smarter. Let's write a function that computes
the share by value:

 computeShare = function(d){100*d$Price*d$Sales/sum(d$Price*d$Sales)}
 > computeShare(d)
[1] 16.66667 27.77778 16.66667 38.88889


 So we just have to do:

 > d$Share = computeShare(d)

and we can re-use that function on other data (with the same named
columns). If there are any bugs in that function they only need fixing
in one place. Yes, if your data changes then all your columns may need
adjusting, but you just write a script or a function that does it. I
think something like this:

 updateSales = function(d){
  d$Share = computeShare(d)
  d$TotalItemCost = totalItemCost(d)
  d$Tax = taxTate*d$TotalItemCost
  totalTax = sum(d$Tax)
 list(data=d,totalTax=totalTax)
}

is a lot easier to understand than hunting round spreadsheet cells to
find that one formula you typed in wrong...

Barry