Search code examples
rpivot-tableaggregate

Two way table with mean of a third variable R

Here's my problem. I have a table, of which I show a sample here. I would like to have the Country as row, Stars as column and the mean of the price for each combination. I used aggregate which gave me the info that i want but not how I want it.

The table looks like that :

    Country Stars Price
1   Canada     4   567
2    China     2   435
3   Russia     3   456
4   Canada     5   687
5   Canada     4   432
6   Russia     3   567
7    China     4  1200
8   Russia     3   985
9   Canada     2   453
10  Russia     3   234
11  Russia     4   546
12  Canada     3   786
13   China     2   456
14   China     3   234
15  Russia     4   800
16   China     5   987

I used this code :

aggregate(Stars[,3],list(Country=Stars$Country, Stars = Stars$Stars), mean)

output :

   Country Stars      x
1   Canada     2  453.0
2    China     2  445.5
3   Canada     3  786.0
4    China     3  234.0
5   Russia     3  560.5
6   Canada     4  499.5
7    China     4 1200.0
8   Russia     4  673.0
9   Canada     5  687.0
10   China     5  987.0

Where x stands for the mean, I would like to change x for "price mean" to... So the goal would be to have one country per row and the number of stars as column with the mean of the price for each pair.

Thank you very much.

Solution

  • It seems you want Excel like pivot table. Here package pivottabler helps much. See, it generates nice html tables too (apart from displaying results)

    library(pivottabler)
qpvt(df, "Country", "Stars", "mean(Price)")

        2      3                 4      5    Total     
Canada    453               786  499.5  687       585  
China   445.5               234   1200  987     662.4  
Russia                    560.5    673            598  
Total     448  543.666666666667    709  837  614.0625

    for formatting use format argument

    qpvt(df, "Country", "Stars", "mean(Price)", format = "%.2f")
        2       3       4        5       Total   
Canada  453.00  786.00   499.50  687.00  585.00  
China   445.50  234.00  1200.00  987.00  662.40  
Russia          560.50   673.00          598.00  
Total   448.00  543.67   709.00  837.00  614.06

    for html output use qhpvt instead.

    
qhpvt(df, "Country", "Stars", "mean(Price)")

    Output enter image description here

    Note: tidyverse and baseR methods are also possible and are easy too