Group_By and Sum() giving rather unextpected results

Question

I have imported from excel a file with 1071 rows and 16 columns, representing all renewable energy projects that won energy auctions in Brazil since 2005.

> HIST <- read_excel("D:/Paulo/Desktop/2018 Historico Leiloes Total para R.xls", 
+     col_types = c("numeric", "text", "text", 
+         "text", "text", "text", "date", "numeric", 
+         "numeric", "numeric", "numeric", 
+         "numeric", "numeric", "numeric", 
+         "numeric", "numeric"))

> HIST
# A tibble: 1,071 x 16
     Ano        Leilao Fonte    UF                Vend
   <dbl>         <chr> <chr> <chr>               <chr>
 1  2011      2011 LER   Bio    SP      Louis Dreyffus
 2  2008 2008 Leilao 1   Bio    MG                CMAA
 3  2008 2008 Leilao 1   Bio    SP              RAIZEN
 4  2017      2017 A-4    PV    PI                Enel
 5  2013    2013 A-5 2  PPEE    BA               CHESF
 6  2008 2008 Leilao 1   Bio    SP             Abengoa
 7  2008 2008 Leilao 1   Bio    GO                 N/A
 8  2009      2009 A-3  PPEE    RS           Eletrosul
 9  2017      2017 A-4    PV    BA SOLATIO SINDUSTRIAL
10  2009      2009 A-3  PPEE    RS           Eletrosul
# ... with 1,061 more rows, and 11 more variables: Projeto <chr>, CODPPA <dttm>,
#   CAPEX <dbl>, MW <dbl>, GF <dbl>, FC <dbl>, PPA <dbl>, RMW <dbl>, WACC <dbl>,
#   TIR <dbl>, VPL <dbl>

Then, I loaded the dplyr:

> library(dplyr)

Finally, when I group_by by YEAR (ANO in portuguese) and summarise the sum of MW (Megawatts) - I would like to know how many megawatts per year were auctioned - I get the following rather disapointing results :

HIST %>%
+     group_by(HIST$Ano)%>%
+     summarise(sum(HIST$MW))

# A tibble: 13 x 2
   HIST$Ano sum(HIST$MW)
    <dbl>          <dbl>
 1       2005       72677.67
 2       2006       72677.67
 3       2007       72677.67
 4       2008       72677.67
 5       2009       72677.67
 6       2010       72677.67
 7       2011       72677.67
 8       2012       72677.67
 9       2013       72677.67
10       2014       72677.67
11       2015       72677.67
12       2016       72677.67
13       2017       72677.67`

Shouldnt it sum the MW per Year? it is showing the grand total of MW, and repeating the value every year. What am I doing wrong?

Thank you, Paulo

Hi, I dont think you need the Hist$Ano and Hist$MW. What I think is happening is that it it is doing the sum of MW from the original dataframe rather than having the dataframe passing to the group_by then to the sum you are calling the entire sum. Try just getting rid of the hist$ — Bill Perry
Yeah, tidyverse functions generally use Non-Standard Evaluation, so you can directly refer to bare column names, rather than repeatedly referring to the original data frame. This is a bit different to (most of) base R! — jimjamslam

elpidio filho elpidio filho · Accepted Answer · 2018-01-08T03:11:32

try

HIST %>%
    group_by(Ano) %>%
    summarise(soma = sum(MW))

Group_By and Sum() giving rather unextpected results

1 Answers