CODEWITHSUNDEEP
jsonrcsv
USAL
USAL

Reputation: 53

CSV to customized JSON format conversion in R

I want to convert the following data file in to JSON format

House   space   type        ID  less than 18    18 to 23    Greather than 23
1   Livingroom  Temperature 1   0             29.44004742   70.55995258
1   Hallway     temperature 1   14.59211237   61.59805511   23.80983252
1   Bedroom     temperature 1   1.683093749   60.63394348   37.68296277
2   Livingroom  Temperature 2   17.16494111   49.53457447   33.30048442
2   Hallway     temperature 2   36.3833926    49.56992189   14.04668551
2   Bedroom     temperature 2   39.74861892   53.78744108   6.463939993

JSON format should be like this

'[
    {
        "id":"house_1", 
        "condition":
            [
                {
                    "type":"temperature",
                    "segment":{"Less than 18":20, "18-25":30 , "Greater than 25":10},
                    "unit":"day",
                    "space":"livingroom"
                },
                {
                    "type":"temperature",
                    "segment":{"Less Lan 18":20, "18-25":30 , "Greater than 25":10},
                    "unit":"day",
                    "space":"hallway"
                },
                {
                    "type":"temperature",
                    "segment":{"Less Lan 18":20, "18-25":30 , "Greater than 25":10},
                    "unit":"day",
                    "space":"bedroom"
                },

]
    },

{
        "id":"house_2", 
        "condition":
            [
                {
                    "type":"temperature",
                    "segment":{"Less Lan 18":20, "18-25":30 , "Greater than 25":10},
                    "unit":"day"
                },

.... and so on

I tried different options whith reshape, jsonlite,RJSONIO but could not get the required results. e.g. 

library(rjson)
library(reshape2)

mm <- melt(newdata)

y <- melt(data, id.vars=c("ID", "type", "space"), measure.vars=c("less.than.18", "X18.to.23", "Greather.than.23"), variable.name="Segment",
          value.name="percentage")

ss <- split(y, y$ID)
exportJson <- toJSON(ss)
exportJson

other option:

library(RJSONIO)

modified <- list(
  traits = colnames(data),
  Conditions = unname(apply(data, 1, function(x) as.data.frame(t(x))))
)
cat(toJSON(modified))

Any suggestion please?

Upvotes: 0

Views: 2940

Answers (1)

hrbrmstr
hrbrmstr

Reputation: 78832

Don't treat it like a data tidying/munging operation, treat it like a "templating" problem. This iterates over each house and fills in a JSON template string.

NOTE: I deliberately left off the outer array brackets since that should be encouragement to read/grok the code vs just cut/paste this answer.

dat <- read.table(text="House   space   type        ID  less.than.18    18.to.23    Greather.than.23
1   Livingroom  Temperature 1   0             29.44004742   70.55995258
1   Hallway     temperature 1   14.59211237   61.59805511   23.80983252
1   Bedroom     temperature 1   1.683093749   60.63394348   37.68296277
2   Livingroom  Temperature 2   17.16494111   49.53457447   33.30048442
2   Hallway     temperature 2   36.3833926    49.56992189   14.04668551
2   Bedroom     temperature 2   39.74861892   53.78744108   6.463939993",
                  header=TRUE)

by(dat, list(dat$House), function(x) {

  outer_template <- '  { 
    "id":"House_%s", 
    "condition":
      [ 
%s 
      ] 
  }'

  inner_template <- '        { "type":"%s", 
          "segment":{"Less than 18":%s, "18-25":%s , "Greater than 25":%s},
          "unit":"day",
          "space":"%s"
        }'

  condition <- paste0(apply(x, 1, function(y) {
    sprintf(inner_template, 
            tolower(y["type"]), y["less.than.18"], y["X18.to.23"], 
            y["Greather.than.23"], tolower(y["space"]))
  }), collapse=",\n")

  sprintf(outer_template, x$House[1], condition)

}) -> houses

house_json <- paste0(houses, collapse=",\n")
cat(house_json)

  { 
    "id":"House_1", 
    "condition":
      [ 
        { "type":"temperature", 
          "segment":{"Less than 18": 0.000000, "18-25":29.44005 , "Greater than 25":70.55995},
          "unit":"day",
          "space":"livingroom"
        },
        { "type":"temperature", 
          "segment":{"Less than 18":14.592112, "18-25":61.59806 , "Greater than 25":23.80983},
          "unit":"day",
          "space":"hallway"
        },
        { "type":"temperature", 
          "segment":{"Less than 18": 1.683094, "18-25":60.63394 , "Greater than 25":37.68296},
          "unit":"day",
          "space":"bedroom"
        } 
      ] 
  },
  { 
    "id":"House_2", 
    "condition":
      [ 
        { "type":"temperature", 
          "segment":{"Less than 18":17.16494, "18-25":49.53457 , "Greater than 25":33.30048},
          "unit":"day",
          "space":"livingroom"
        },
        { "type":"temperature", 
          "segment":{"Less than 18":36.38339, "18-25":49.56992 , "Greater than 25":14.04669},
          "unit":"day",
          "space":"hallway"
        },
        { "type":"temperature", 
          "segment":{"Less than 18":39.74862, "18-25":53.78744 , "Greater than 25": 6.46394},
          "unit":"day",
          "space":"bedroom"
        } 
      ] 
  }

Upvotes: 1

Related Questions