pv-current/joinTable/joinTable.hy

(import [pandas :as pd])

(defn extract-dev-id [inp]
  "Format of inp: pointID-StationID-roomNo-combinatorNo-stringNo
   For example: 1025744-SYGD-32-14-16"
  (setv syms (.split inp "-"))
  (+ (. syms [2]) "00" (. syms [3]) (. syms [4]))) ; here 00 is a dummy inverter No

(setv probes (as-> "probes.csv" it
               (.read_csv pd it
                :header None
                :names ["pointID" "fullDesc" "remark"]
                :dtype {"pointID" int "fullDesc" str "remark" int})
               (. it [(= 0 it.remark)])
               (.assign it :STATION_ID
                           (fn [x] (.map x.fullDesc (fn [y] (. (.split y "-") [1]) ))))
               (.assign it :DEVICE_ID
                           (fn [x] (.map x.fullDesc extract-dev-id)))))

(setv probe-data (.read_csv pd "final.csv"
           :header None
           :names ["PROBEID" "MONITOR_TIME" "CURRENT_VALUE" "VOLTAGE_VALUE"
                   "POWER_VALUE" "IRRADIANCE" "TEMPERATURE"]
           :dtype {"PROBEID" int "CURRENT_VALUE" float "VOLTAGE_VALUE" float
                   "POWER_VALUE" float "IRRADIANCE" float "TEMPERATURE" float}
           :parse_dates ["MONITOR_TIME"]))

(as-> probe-data it
  (.merge pd it probes :how "inner" :left-on "PROBEID" :right-on "pointID")
  (.sort_values it :by ["DEVICE_ID" "MONITOR_TIME"])
  (.to_csv it "resHy.csv"
         :index False
         :columns ["STATION_ID" "DEVICE_ID" "MONITOR_TIME" "VOLTAGE_VALUE" "CURRENT_VALUE"
                   "POWER_VALUE" "TEMPERATURE" "IRRADIANCE"]))

(as-> probe-data it
  (.merge pd it probes :how "inner" :left-on "PROBEID" :right-on "pointID")
  (.DataFrame pd it :columns ["DEVICE_ID"])
  (.sort_values it :by ["DEVICE_ID"])
  (.drop_duplicates it)
  (.to_csv it "devHy.csv"
         :index False))

(as-> probe-data it
  (.merge pd it probes :how "inner" :left-on "PROBEID" :right-on "pointID")
  (.DataFrame pd it :columns ["DEVICE_ID" "MONITOR_TIME"])
  (.sort_values it :by ["DEVICE_ID" "MONITOR_TIME"])
  (.drop_duplicates it)
  (.to_csv it "devtimeHy.csv"
         :index False))