How to export data from R to postgres pgadmin 4

General
1

so, i have 2 API'S. I read them and store them in a data frame and when i try to export it to pgadmin4 to a table , i get errors. i couldn't find a solution. Here is my code. My aim is to merge 2 api into a data frame and then store it i a table in pgadmin4. Also, these 2 apis don't have same number of rows, because of which cbind fill is needed, but that doesn't help. I get the errors as "Error in (function (..., row.names = NULL, check.rows = FALSE, check.names = TRUE, :
arguments imply differing number of rows: 1, 0" above the line of code "json_data1 <- data.frame(json_data1)" and error "Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"PostgreSQLConnection", "character", "list"’" above the line of code "dbWriteTable(con,"newdatatable", json_data1 )". Any help would be very appreciated, i am struck here

 library("rjson")
 library("RCurl")
 require("RPostgreSQL")
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, 
             dbname="demo",
             host="***",
             port=5432,
             user="abc",
             password="abc" )
print("connected")
dbExistsTable(con, "product_product")
json_file1<- "https.njjj.com"
 json_data1 <- fromJSON(file=json_file1)
 json_data1 <-data.frame(json_data1)
dbWriteTable(con, "newTable", json_data1, row.names=FALSE, append=TRUE)

json_file2<- "https.njjj.com"
json_data2 <- fromJSON(file=json_file2)

 json_data2 <- data.frame(json_data2)

combineddata = rbind(json_data1,json_data2)
# dbWriteTable(con, newdatatable,"combineddata",overwrite = T ) 
 # dbWriteTable(con,"newdatatable", x)
2

Some of the entries in the json file seem to have "missing" data in the "brand name" tag. This seem to crash the import with "fromJSON":

image
image513×789 11.8 KB

You will probably need to "sanitize" somehow the data before you can use it.

1 Like
3

How do i do that? is it data cleaning in R or cbind fill to just fill some values in missing spaces?

4

You'd need to go over the imported json and fill-in the missing data with NA.

Something like this could work, in this case:

library("rjson")
library("dplyr")

json_file1<- "https://nrrne.salesdiary.in:4001/api/sale_reports/get_sales_data?start_date=2017-09-01&end_date=2017-09-02"
json_data1 = fromJSON(file = json_file1)

fix_missing <- function(x){
  x["brand_name"][[1]] <- ifelse(!is.null(x["brand_name"][[1]]),  
                            x["brand_name"][[1]], 
                            NA)
  x
}
json_data1 = lapply(json_data1[["result"]], FUN = function(x) fix_missing(x)) %>% 
  dplyr::bind_rows()
json_data1
#> # A tibble: 79 x 26
#>    state emp_code salesman mobile role_name order_date area_name zone_name
#>    <chr> <chr>    <chr>    <chr>  <chr>     <chr>      <chr>     <chr>    
#>  1 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Ram Nagar West Tri…
#>  2 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Krishna … West Tri…
#>  3 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Ram Nagar West Tri…
#>  4 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Krishna … West Tri…
#>  5 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Krishna … West Tri…
#>  6 Trip… 3272     rajan.d… 96120… Front Li… 2017-09-02 Reshamba… West Tri…
#>  7 Trip… 3272     rajan.d… 96120… Front Li… 2017-09-02 Dhaleswar West Tri…
#>  8 Trip… 3272     rajan.d… 96120… Front Li… 2017-09-02 Dhaleswar West Tri…
#>  9 Trip… 3272     rajan.d… 96120… Front Li… 2017-09-02 Reshamba… West Tri…
#> 10 Trip… 2107     abhi.ba… 89746… Front Li… 2017-09-02 Krishna … West Tri…
#> # ... with 69 more rows, and 18 more variables: territory_name <chr>,
#> #   beat_name <chr>, beat_type <chr>, distributor_name <chr>,
#> #   distributor_code <chr>, outlet_name <chr>, outletid <dbl>,
#> #   prod_name <chr>, prod_code <chr>, outlet_type <chr>, categ_name <chr>,
#> #   brand_name <chr>, qty_sales <chr>, line_type <chr>, is_mss <chr>,
#> #   is_focus <chr>, is_unscheduled <chr>, total_sales <chr>

Created on 2018-02-19 by the reprex package (v0.2.0).

1 Like
5

Thank you. it works great. i have some more doubts. is it possible to auto refresh the API's? in the sense, no need to read in a new api everytime, but automatically refresh it?

6

Hi. I do not know if I follow, sorry. What do you mean by "refreshing the API"?

7

in my code, i had named something like json file1 and json file2. those are links that contains data in the form of json. is it possible that these links can be auto refreshed and the updated data can be viewed when administrator adds new data into the json file of the link?

8

You could wrap your script in a function that when run interrogates the website and retrieves the results so that you can do:

mydata <- get_mydata()

, but you can not avoid accessing the "website" if you want to update the results.

However, if the name of the json file to be accessed changes, you should account for that by modifying
the "address" to be fetched. For example by changing the dates in

"https://nrrne.salesdiary.in:4001/api/sale_reports/get_sales_data?start_date=2017-09-01&end_date=2017-09-02"

You could use string replacement functions available in package stringr, for that.

1 Like
9

Alright. Thank you so much. your explanation is greatly helping me. i have one more doubt. i have a shiny app that gives bar graphs based on input selected from user thru drop down menu. many such drop downs are there. they seems to be clumsy, seen on a single page. i want to have radio buttons and select whichever drop down type user wants to see and based on that , the graph, only for that particular drop down is shown.

10

Glad it helped.

concerning your last question, I do not know much about shiny. I think you'd be better off by starting a new thread. Consider however that if you do not provide some additional details/examples I do not think that you will get many replies.

11

so, this is my code, and "mydata1" is a csv file with 2 columns. one column is distributor_name and it has the following data
distributor_name<-data.frame("Chhutu Enterprise" , "Durga Agency", "DeyS Agency House", "S.P Traders") . Next column is outlet_type and it has outlet_type <-data.frame("Grocer","Grocer", "Puja Outlet", "Self Service")
. i am able to show drop downs for specific option chosen by user, but graphs are not being plotted.

 #install.packages("magrittr")
library(magrittr)
library(shiny)                                                                                                           
library(gdata)                                                                                             
library(plyr)
library(ggplot2)                                                                                                                                                                                                                  
 library(shiny)                                                                                                    

mydata1 = read.csv("C:\\Users\\BPO18\\Documents\\Book1.csv")                                                                                                                                                                                                                                                                      

ui <- fluidPage(

titlePanel ("Hello"),
fluidRow(
  
  column(4, wellPanel(
    
    radioButtons("rb", "Please select an option",
                 choices = c("Distributor_wise", "Outlet_type_wise"))),
    
    wellPanel(
      
      uiOutput("radiobuttonChoiceOutput")
      
    )
  ),

column(3,
       
       plotOutput("distributoroutput"),
       
       plotOutput("outlettypeoutput")
  )    
)
) 


shinyServer <- function(input, output) {

 output$radiobuttonChoiceOutput <- renderUI({
switch(input$rb,
       Distributor_wise = checkboxGroupInput("distributorInput", 
                                             h3("Distributor-wise"), 
                                             choices = unique(mydata1$distributor_name),
                                             selected = mydata1$distributor_name[1]),
       Outlet_type_wise =  checkboxGroupInput("outlettypeInput", 
                                              h3("Outlet type wise"), 
                                              choices = unique(mydata1$outlet_type),
                                              selected = mydata1$outlet_type[1])
       
       
)
})

output$distributorOutput <-  renderPlot({
if(input$rb == "Distributor_wise"){
  return()
}
filtered <-
  mydata1 %>%
  dplyr :: filter(distributor_name == input$distributorInput)

ggplot(filtered, aes(total_sales)) +
  geom_histogram(fill=I("red"),
                 col=I("green")) 
}, height = 400, width = 800)


output$outlettypeOutput <- renderPlot({
if(input$rb == "Outlet_type_wise"){
  return()
}
filtered <-
  mydata1 %>%
  dplyr :: filter(outlet_type == input$outlettypeInput
  )
ggplot(filtered, aes(total_sales)) +
  geom_histogram(fill=I("blue"),
                 col=I("yellow"))
}, height = 400, width = 800)
}


shinyApp(ui=ui, server = shinyServer)
12

alright. sure. thank you so much.