Chapter 5 Working Excel data

5.1 Prep

Steps;

5.1.1 Install packages

#install.packages(c("readxl", "magrittr", "knitr", "kableExtra", "DT", "plotly", "dplyr", "ggplot2", "treemapify"))

5.1.2 Call libraries for the installed packages

library(readxl)
library(magrittr)
library(knitr)
library(kableExtra)
library(DT)
library(plotly)
library(dplyr)
library(ggplot2)
library(treemapify)

5.1.3 Import excel table from local drive

To load and use excel data from local storage such as your pc, follow the following steps

  1. Create an object by typing in a name for the data you want to upload followed by the greater than sign and a hyphen/dash (<-).My object below is called ‘gcf’
  2. Call the library function read_excel or read_csv in case your data is stored in csv format
  3. Inside the parenthesis, open single or double quotation marks " " or '' , enter the path to your excel file (you may copy and paste this form your file explorer address bar). Be sure to include the full name of your file including the format extension (.xls, .xlsx, .csv, etc)
  4. The backslash in markdown is a special character, therefore to avoid associated errors, add another backslash to each existing backslash in your path.
  5. Specify the sheet name or number in case your excel data has multiple sheets, otherwise skip this step
    see screenshot below for all the steps above.

exceldata

gcf<-read_excel("C:\\Users\\Makabe\\NAP_Progress_Aggregate\\Open_NAPs_Database.xlsm", sheet = "GCF")
  1. Run code
  2. Your data object appears in the environment window
  3. To view your data attributes and structure, click on the ‘play’ icon

view attr

  1. To view your data, click on the data object or table sign on the far right of your data set/object name

view excel data

5.2 Build Tables

5.2.1 Static table

  1. Open a new code chunk
  2. Call the kable function by typing in ‘kable()’ # auto fill is your friend
  3. Fill in details as below ( since we are only plotting the first 10 rows of our table, we will add another argument called `head’ and specify we want to only plot columns 2,3,5,and 8 and only the first 10 rows)

kable table

Hover over the function to see parameters required or go to the ‘Help’ tab and search ‘datatable’ to see further details on its application.

kable(head(gcf[,c(2,3,5,8)],5),caption = 'GCF Funding')%>%
kable_styling(bootstrap_options = c('condensed', 'bordered'),
              fixed_thead = TRUE, font_size=10, repeat_header_continued = TRUE, full_width = F)%>%
  column_spec(1:4, width = "10em")
Table 5.1: GCF Funding
countryname Region Project Name Total GCF Funding
Antigua and Barbuda Latin America and the Caribbean Resilience to hurricanes in the building sector in Antigua and Barbuda 32706595
Antigua and BarbudaDominicaGrenada Latin America and the Caribbean Integrated physical adaptation and community resilience through an enhanced direct access pilot in the public, private, and civil society sectors of three Eastern Caribbean small island developing states 20000000
Bahrain Asia-Pacific Enhancing climate resilience of the water sector in Bahrain 2320388
Bangladesh Asia-Pacific Climate Resilient Infrastructure Mainstreaming (CRIM) 40000000
Bangladesh Asia-Pacific Enhancing adaptive capacities of coastal communities, especially women, to cope with climate change induced salinity 24980000

5.2.2 Interactive table

  1. Open a new code chunk
  2. Install packages DT and magrittr
  3. Load their libraries
  4. Call datatable function by typing in ‘datatable()’ # auto fill is your friend
  5. Hover over the function to see parameters required or go to the ‘Help’ tab and search ‘datatable’ to see further details on its application.
  6. Fill in details as below
gcf_columns<-gcf[,c(2,3,5,8)] # selects only the columns we want to see in our table
datatable(gcf_columns,filter = 'top',rownames = F, editable = F, style = 'jqueryui', class = 'display responsive', width = '100%', caption = "GCF Project Funding", extensions = 'Buttons', options=list(pageLength= 5, dom='lfrtipB', buttons = c('copy', 'csv', 'excel', 'pdf')))%>%
  DT::formatStyle(columns = colnames(gcf_columns),fontSize= '10px')

5.3 Generate Other Graphics

5.3.1 A bar chart

  1. Open a new code chunk
  2. Install packages plotly & dplyr
  3. Call their respective libraries
  4. To plot the no of projects per region, first group your data by region, then count the no of projects (use a unique identifier, in our case the project ID)
  5. Call the function ‘plot_ly’ and enter parameters as in below
gcf %>% group_by(Region)%>% count(gcf$`Project Name`)%>%
  summarise("Projects"=sum(n))%>%   plot_ly(type = "bar", 
          y = ~Projects,  x = ~Region
          
               ) %>%
plotly::layout(yaxis=list(title='No. of Projects',tickfont=list(size=12)),
                 xaxis=list(title='Region', tickfont=list(size=12)))

5.3.2 A donut chart

To plot the same data as above but on a pie chart, the process the same, only the type of chart changes

gcf%>%group_by(Region)%>%summarise('Total'=sum(`Total GCF Funding`))%>%plot_ly(labels=~Region, values=~Total,sep = '\n')%>% add_pie(hole=0.5)%>%
    layout(title="GCF Funding by Region")

5.4 Filter and plot select data

5.4.1 Pie chart

To plot projects for a select region (s), use the filter function to select only values that match your selection, then group the data by country and create count by unique identifier as above. Repeat the plotting steps as above. you may use any type of charts as may be preferred.

gcf%>%filter(Region=="Asia-Pacific")%>%
  group_by(countryname)%>%count(`Project Name`)%>%
        plot_ly(labels=~countryname, values=~n)%>%
    add_pie()%>%
    layout(title=" GCF Projects in Asia-Pacific",
           legend=list(orientation='h'))

5.4.2 Build Treemap

5.4.2.1 Static

  1. Open a new code chunk
  2. Install packages ggplot2 & treemapify
  3. Call the respective libraries
  4. To plot amount of grant in Region x, filter values for only that region, then summarise the data by country using the functions ‘group-by’ and ‘sum’
  5. Call the function ‘ggplot’ and enter parameters as shown in screenshot above
  6. Run code
tm<-ggplot(gcf, aes(fill=countryname, 
       area=`Total GCF Funding`, 
       label = paste(countryname,"\n",prettyNum(`Total GCF Funding`,
                                                big.mark = ","))))+
  geom_treemap()+
  geom_treemap_text(colour='black', place='centre')+
  labs(subtitle = 'GCF Funding in Latin America & Caribbean')+
  theme(legend.position = 'none')
tm

5.4.2.2 Interactive

gcf_sum<-gcf%>%filter(Region=="Africa")%>%
  group_by(countryname, Region)%>%summarise("Total"=sum(`Total GCF Funding`))

plot_ly(
  data = gcf_sum,
  type= "treemap",
  values = ~Total,
  labels= ~countryname,
  parents= ~Region,
  name = "GCF Funding",
  textinfo="label+value+percent parent")