kandi background
Explore Kits

pq | Pure Go Postgres driver for database/sql | SQL Database library

 by   lib Go Version: v1.10.4 License: Non-SPDX

 by   lib Go Version: v1.10.4 License: Non-SPDX

Download this library from

kandi X-RAY | pq Summary

pq is a Go library typically used in Database, SQL Database applications. pq has no bugs, it has no vulnerabilities and it has medium support. However pq has a Non-SPDX License. You can download it from GitHub.
Pure Go Postgres driver for database/sql
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • pq has a medium active ecosystem.
  • It has 7212 star(s) with 844 fork(s). There are 153 watchers for this library.
  • There were 2 major release(s) in the last 12 months.
  • There are 212 open issues and 398 have been closed. On average issues are closed in 234 days. There are 70 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of pq is v1.10.4
pq Support
Best in #SQL Database
Average in #SQL Database
pq Support
Best in #SQL Database
Average in #SQL Database

quality kandi Quality

  • pq has 0 bugs and 0 code smells.
pq Quality
Best in #SQL Database
Average in #SQL Database
pq Quality
Best in #SQL Database
Average in #SQL Database

securitySecurity

  • pq has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
  • pq code analysis shows 0 unresolved vulnerabilities.
  • There are 0 security hotspots that need review.
pq Security
Best in #SQL Database
Average in #SQL Database
pq Security
Best in #SQL Database
Average in #SQL Database

license License

  • pq has a Non-SPDX License.
  • Non-SPDX licenses can be open source with a non SPDX compliant license, or non open source licenses, and you need to review them closely before use.
pq License
Best in #SQL Database
Average in #SQL Database
pq License
Best in #SQL Database
Average in #SQL Database

buildReuse

  • pq releases are available to install and integrate.
  • Installation instructions are not available. Examples and code snippets are available.
pq Reuse
Best in #SQL Database
Average in #SQL Database
pq Reuse
Best in #SQL Database
Average in #SQL Database
Top functions reviewed by kandi - BETA

Coming Soon for all Libraries!

Currently covering the most popular Java, JavaScript and Python libraries. See a SAMPLE HERE.
kandi's functional review helps you automatically verify the functionalities of the libraries and avoid rework.

pq Key Features

SSL

Handles bad connections for database/sql

Scan time.Time correctly (i.e. timestamp[tz], time[tz], date)

Scan binary blobs correctly (i.e. bytea)

Package for hstore support

COPY FROM support

pq.ParseURL for converting urls to connection strings for sql.Open.

Many libpq compatible environment variables

Unix socket support

Notifications: LISTEN/NOTIFY

pgpass support

GSS (Kerberos) auth

Install

copy iconCopydownload iconDownload
go get github.com/lib/pq

Conditional Columns and Delimited Fields

copy iconCopydownload iconDownload
let

//Read in the two tables
    Source = Excel.CurrentWorkbook(){[Name="tblStateNames"]}[Content],
    tblStateNames = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

    Source2 = Excel.CurrentWorkbook(){[Name="tblExport"]}[Content],
    tblExport = Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//split the States column by comma into Rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(tblExport, {
        {"States", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "States"),

//join with the States Names
    joined = Table.Join(#"Split Column by Delimiter",{"States","Company"},tblStateNames,{"Abbr","COMPANY"}, JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(joined,{"States", "COMPANY", "Abbr"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name","Company"}, {
        {"States", each Text.Combine([State Name],";"),type text}}),

//split with no column Count option
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "States", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))
in
    #"Split Column by Delimiter1"

Go postgres prepared statement with interval parameter not working

copy iconCopydownload iconDownload
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + interval '1 day' * $3)`
-----------------------
expires := "7 days"

stmt := `INSERT INTO snippets (title, content, created, expires)
    VALUES($1, $2, current_timestamp, current_timestamp + $3::interval)`

_, err := m.DB.Exec(stmt, title, content, expires)

Missing data fields in Wikidata Query Service results despite items having these data

copy iconCopydownload iconDownload
  OPTIONAL { ?mun wdt:P625 ?coords . }
  OPTIONAL { ?mun wdt:P131 ?parentPlace . }
  OPTIONAL { ?mun wdt:P2046 ?area . }
-----------------------
SELECT ?mun ?munLabel ?coords ?parentPlace ?area WHERE {
  ?mun p:P31 ?instanceOf # Get statement because we need this later
       .
  ?instanceOf ps:P31/wdt:279* wd:Q2039348.
  
  OPTIONAL {?mun wdt:P625 ?coords }
  OPTIONAL {?mun wdt:P131 ?parentPlace }
  OPTIONAL {?mun wdt:P2046 ?area }

  
  MINUS { ?instanceOf pq:P582 ?endTime. } # Don't show municipalities that have an end time
  
  service wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?munLabel

Read / Write Parquet files without reading into memory (using Python)

copy iconCopydownload iconDownload
import asyncio
from pyarrow.parquet import ParquetFile


class ParquetFileStreamer:
    """
    Attributes:
        ip_address: ip address of the distant server
        port: listening port of the distant server
        n_bytes: -1 means read whole batch
        file_source: pathlib.Path, pyarrow.NativeFile, or file-like object
        batch_size: default = 65536
        columns: list of the columns you wish to select (if None selects all)

    Example:
        >>> pfs = ParquetFileStreamer
        >>> class MyStreamer(ParquetFileStreamer)
                file_source = '/usr/fromage/camembert.parquet
                columns = ['name', 'price']
        >>> MyStreamer.start_stream()
    """
    ip_address = '192.168.1.1'
    port = 80
    n_bytes = -1

    file_source: str
    batch_size = 65536
    columns = []

    @classmethod
    def start_stream(cls):
        for batch in cls._open_parquet():
            asyncio.run(cls._stream_parquet(batch))

    @classmethod
    def _open_parquet(cls):
        return ParquetFile(cls.file_source).iter_batches(
            batch_size=cls.batch_size,
            columns=cls.columns
        )

    @classmethod
    async def _stream_parquet(cls, batch):
        reader, writer = await asyncio.open_connection(cls.ip_address, cls.port)
        writer.write(batch)
        await writer.drain()
        await reader.read()
        writer.close()
        await writer.wait_closed()


class ParquetFileReceiver:
    """
    Attributes: \n
        port: specify the port \n
        n_bytes: -1 reads all the batch
    Example:
        >>> pfr = ParquetFileReceiver
        >>> asyncio.run(pfr.server())
    """
    port = 80
    n_bytes = -1

    @classmethod
    async def handle_stream(cls, reader, writer):
        data = await reader.read(cls.n_bytes)
        batch = data.decode()
        print(batch)

    @classmethod
    async def server(cls):
        server = await asyncio.start_server(cls.handle_stream, port=cls.port)
        async with server:
            await server.serve_forever()
-----------------------
import pandas as pd
import numpy as np
from pyarrow.parquet import ParquetFile

# create a random df then save to parquet
df = pd.DataFrame({
    'A': np.arange(10000),
    'B': np.arange(10000),
    'C': np.arange(10000),
    'D': np.arange(10000),
})
df.to_parquet('./test/test')

# ****** below is @Micah Kornfield's answer ******
# 1. open parquet file
batch = ParquetFile('./test/test')
# 2. define generator of batches
record = batch.iter_batches(
    batch_size=10,
    columns=['B', 'C'],
)
# 3. yield pandas/numpy data
print(next(record).to_pandas()) # pandas
print(next(record).to_pydict()) # native python dict

How can I implement a numba jitted priority queue?

copy iconCopydownload iconDownload
from heapq import heappush, heappop
from typing import List, Tuple, Dict, Any

import numba as nb
import numpy as np
from numba.experimental import jitclass


class UpdatablePriorityQueueEntry:
    def __init__(self, p: float, i: Any):
        self.priority = p
        self.item = i

    def __lt__(self, other: "UpdatablePriorityQueueEntry"):
        return self.priority < other.priority


class UpdatablePriorityQueue:
    def __init__(self):
        self.pq = []
        self.entries_priority = {}

    def put(self, item: Any, priority: float = 0.0):
        entry = UpdatablePriorityQueueEntry(priority, item)
        self.entries_priority[item] = priority
        heappush(self.pq, entry)

    def pop(self) -> Any:
        while self.pq:
            entry = heappop(self.pq)
            if entry.priority == self.entries_priority[entry.item]:
                self.entries_priority[entry.item] = np.inf
                return entry.item
        raise KeyError("pop from an empty priority queue")

    def clear(self):
        self.pq.clear()
        self.entries_priority.clear()


@jitclass
class PriorityQueueEntry(UpdatablePriorityQueueEntry):
    priority: float
    item: Tuple[int, int]

    def __init__(self, p: float, i: Tuple[int, int]):
        self.priority = p
        self.item = i


@jitclass
class UpdatablePriorityQueue(UpdatablePriorityQueue):
    pq: List[PriorityQueueEntry2d]
    entries_priority: Dict[Tuple[int, int], float]

    def __init__(self):
        self.pq = nb.typed.List.empty_list(PriorityQueueEntry2d(0.0, (0, 0)))
        self.entries_priority = nb.typed.Dict.empty((0, 0), 0.0)

    def put(self, item: Tuple[int, int], priority: float = 0.0):
        entry = PriorityQueueEntry2d(priority, item)
        self.entries_priority[item] = priority
        heappush(self.pq, entry)

-----------------------
from typing import List, Dict, Tuple 
from heapq import heappush, heappop
import numba as nb
from numba.experimental import jitclass

# priority, counter, item, removed
entry_def = (0.0, 0, (0,0), nb.typed.List([False]))
entry_type = nb.typeof(entry_def)

@jitclass
class PriorityQueue:
    # The following helps numba infer type of variable
    pq: List[entry_type]
    entry_finder: Dict[Tuple[int, int], entry_type]
    counter: int
    entry: entry_type

    def __init__(self):
        # Must declare types here see https://numba.pydata.org/numba-doc/dev/reference/pysupported.html
        self.pq = nb.typed.List.empty_list((0.0, 0, (0,0), nb.typed.List([False])))
        self.entry_finder = nb.typed.Dict.empty( (0, 0), (0.0, 0, (0,0), nb.typed.List([False])))
        self.counter = 0

    def put(self, item: Tuple[int, int], priority: float = 0.0):
        """Add a new item or update the priority of an existing item"""
        if item in self.entry_finder:
            # Mark duplicate item for deletion
            self.remove_item(item)
    
        self.counter += 1
        entry = (priority, self.counter, item, nb.typed.List([False]))
        self.entry_finder[item] = entry
        heappush(self.pq, entry)

    def remove_item(self, item: Tuple[int, int]):
        """Mark an existing item as REMOVED via True.  Raise KeyError if not found."""
        self.entry = self.entry_finder.pop(item)
        self.entry[3][0] = True
    
    def pop(self):
        """Remove and return the lowest priority item. Raise KeyError if empty."""
        while self.pq:
            priority, count, item, removed = heappop(self.pq)
            if not removed[0]:
                del self.entry_finder[item]
                return priority, item
        raise KeyError("pop from an empty priority queue")
    q = PriorityQueue()
    q.put((1,1), 5.0)
    q.put((1,1), 4.0)
    q.put((1,1), 3.0)
    q.put((1,1), 6.0)
    print(q.pq)
    >>  [(3.0, 3, (1, 1), ListType[bool]([True])), (5.0, 1, (1, 1), ListType[bool]([True])), (4.0, 2, (1, 1), ListType[bool]([True])), (6.0, 4, (1, 1), ListType[bool]([False]))]
    print(q.pop())
    >> (6.0, (1, 1))
    print(len(q.entry_finder))
    >> 0
-----------------------
from typing import List, Dict, Tuple 
from heapq import heappush, heappop
import numba as nb
from numba.experimental import jitclass

# priority, counter, item, removed
entry_def = (0.0, 0, (0,0), nb.typed.List([False]))
entry_type = nb.typeof(entry_def)

@jitclass
class PriorityQueue:
    # The following helps numba infer type of variable
    pq: List[entry_type]
    entry_finder: Dict[Tuple[int, int], entry_type]
    counter: int
    entry: entry_type

    def __init__(self):
        # Must declare types here see https://numba.pydata.org/numba-doc/dev/reference/pysupported.html
        self.pq = nb.typed.List.empty_list((0.0, 0, (0,0), nb.typed.List([False])))
        self.entry_finder = nb.typed.Dict.empty( (0, 0), (0.0, 0, (0,0), nb.typed.List([False])))
        self.counter = 0

    def put(self, item: Tuple[int, int], priority: float = 0.0):
        """Add a new item or update the priority of an existing item"""
        if item in self.entry_finder:
            # Mark duplicate item for deletion
            self.remove_item(item)
    
        self.counter += 1
        entry = (priority, self.counter, item, nb.typed.List([False]))
        self.entry_finder[item] = entry
        heappush(self.pq, entry)

    def remove_item(self, item: Tuple[int, int]):
        """Mark an existing item as REMOVED via True.  Raise KeyError if not found."""
        self.entry = self.entry_finder.pop(item)
        self.entry[3][0] = True
    
    def pop(self):
        """Remove and return the lowest priority item. Raise KeyError if empty."""
        while self.pq:
            priority, count, item, removed = heappop(self.pq)
            if not removed[0]:
                del self.entry_finder[item]
                return priority, item
        raise KeyError("pop from an empty priority queue")
    q = PriorityQueue()
    q.put((1,1), 5.0)
    q.put((1,1), 4.0)
    q.put((1,1), 3.0)
    q.put((1,1), 6.0)
    print(q.pq)
    >>  [(3.0, 3, (1, 1), ListType[bool]([True])), (5.0, 1, (1, 1), ListType[bool]([True])), (4.0, 2, (1, 1), ListType[bool]([True])), (6.0, 4, (1, 1), ListType[bool]([False]))]
    print(q.pop())
    >> (6.0, (1, 1))
    print(len(q.entry_finder))
    >> 0

How to fix an error associating with a comparator?

copy iconCopydownload iconDownload
return a->get_distance() < b->get_distance() ? false : true;
return a->get_distance() >= b->get_distance();
return b->get_distance() < a->get_distance();
-----------------------
return a->get_distance() < b->get_distance() ? false : true;
return a->get_distance() >= b->get_distance();
return b->get_distance() < a->get_distance();
-----------------------
return a->get_distance() < b->get_distance() ? false : true;
return a->get_distance() >= b->get_distance();
return b->get_distance() < a->get_distance();

How to display an entire queue struct

copy iconCopydownload iconDownload
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;
-----------------------
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;
-----------------------
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;
-----------------------
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;
-----------------------
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;
-----------------------
rear = 4
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |          |          |          |          |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 4
queue:
 +----------+----------+----------+----------+----------+
 |   "c1"   |   "c2"   |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 3
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |          |          |   "c3"   |   "c4"   |          |
 +----------+----------+----------+----------+----------+
rear = 0
front = 1
queue:
 +----------+----------+----------+----------+----------+
 |   "c6"   |          |   "c3"   |   "c4"   |   "c5"   |
 +----------+----------+----------+----------+----------+
order=1;
for(int i=q.front+1;i<=q.rear;i++) {
  printf("[%d]-%s\n",order,q.items[i]);
  order++;
}
case 3:
  for (int i = q.front; i != q.rear; i = (i+1) % CAPACITY) {
    int order = (i+1-q.front+CAPACITY) % CAPACITY; // (... + CAPACITY) % CAPACITY ensures a positive number
    printf("[%d]-%s\n", order, q.items[(i+1) % CAPACITY]);
  }
  break;

GNU awk, FPAT and matching negative string regex with regex and special chars

copy iconCopydownload iconDownload
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/\\"/,"\n")              # Replace each\" with \n in the record
    $0 = $0                       # Re-split the record into fields
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)      # Replace each \n with \" in the field
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/@/,"@A")
    gsub(/\n/,"@B")
    gsub(/\\"/,"\n")
    $0 = $0
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)
        gsub("@B","\n",$i)
        gsub("@A","@",$i)
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
-----------------------
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/\\"/,"\n")              # Replace each\" with \n in the record
    $0 = $0                       # Re-split the record into fields
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)      # Replace each \n with \" in the field
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/@/,"@A")
    gsub(/\n/,"@B")
    gsub(/\\"/,"\n")
    $0 = $0
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)
        gsub("@B","\n",$i)
        gsub("@A","@",$i)
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
-----------------------
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/\\"/,"\n")              # Replace each\" with \n in the record
    $0 = $0                       # Re-split the record into fields
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)      # Replace each \n with \" in the field
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/@/,"@A")
    gsub(/\n/,"@B")
    gsub(/\\"/,"\n")
    $0 = $0
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)
        gsub("@B","\n",$i)
        gsub("@A","@",$i)
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
-----------------------
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/\\"/,"\n")              # Replace each\" with \n in the record
    $0 = $0                       # Re-split the record into fields
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)      # Replace each \n with \" in the field
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4
$ cat tst.awk
BEGIN { FPAT="([^|]*)|(\n[^\n]+\n)" }
{
    gsub(/@/,"@A")
    gsub(/\n/,"@B")
    gsub(/\\"/,"\n")
    $0 = $0
    for (i=1; i<=NF; i++) {
        gsub("\n","\\\"",$i)
        gsub("@B","\n",$i)
        gsub("@A","@",$i)
        print "$"i"=" $i
    }
}
$ awk -f tst.awk file
$1=1
$2=\"2\"
$3=\"3.1|3.2\"
$4=4

Schema for pyarrow.ParquetDataset &gt; partition columns

copy iconCopydownload iconDownload
partition_schema = pa.schema([pa.field('col3', pa.string())])
partitioning = pa.dataset.partitioning(schema=partition_schema)

partitionaldataset = pq.ParquetDataset(
    root_path, 
    partitioning=partitioning,
)

col1: int64
col2: double
col3: string
-----------------------
partition_schema = pa.schema([pa.field('col3', pa.string())])
partitioning = pa.dataset.partitioning(schema=partition_schema)

partitionaldataset = pq.ParquetDataset(
    root_path, 
    partitioning=partitioning,
)

col1: int64
col2: double
col3: string
-----------------------
table = pa.Table.from_pandas(df)
pq.write_to_dataset(
    table=table,
    root_path=path,
    filesystem=s3fs,
    partition_cols=partition_cols,
    partition_filename_cb=lambda _: "data.parquet",
)

# Write metadata-only Parquet file from schema
pq.write_metadata(
    schema=table.schema, where=path + "/_common_metadata", filesystem=s3fs
)

Gekko is not respecting the restrictions of the variables

copy iconCopydownload iconDownload
EXIT: Optimal Solution Found.

 The solution was found.

 The final value of the objective function is  0.005269679643169275
 
 ---------------------------------------------------
 Solver         :  IPOPT (v3.12)
 Solution time  :  0.011 sec
 Objective      :  0.005269679643169275
 Successful solution
 ---------------------------------------------------
[[0.00080192314638], [0.00066898310209], [0.00033248534634], [0.00031046466289], \
[0.00020163446647], [0.00025390495361], [0.004994273964], [0.0049445497412], \
[-0.00031911076974]]
Objective = 1053935.92864
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
from gekko import GEKKO
import numpy as np
import pandas as pd
obj = 0
obj2 = 0

ponderaciones = pd.Series({'ACC': 0.07645771,
 'UAA': 0.0,
 'EOAO': 0.000712,
 'CIA': 0.0055,
 'BJA': 0.01,
 'BOEA': 0.03,
 'UA': 0.110,
 'EOA': 0.0712,
 'CI': 0.00557,
 'BJ': 0.0161,
 'BOE': 0.0355,
 'U': 0.0553,
 'E': 0.00071231,
 'C': 0.005555,
 'B': 0.0157,
 'E': 0.0335}
)

rentabilidades = pd.Series({'ACC': 0.0035323168,
 'UAA': 0.033975,
 'EOAO': -0.0016047,
 'CIA': -0.00248652,
 'BJA': -0.0075425,
 'BOEA': 0.0016429,
 'UA': 0.550,
 'EOA': 0.0512,
 'CI': 0.00157,
 'BJ': 0.0861,
 'BOE': 0.0555,
 'U': 0.0593,
 'E': 0.00231,
 'C': 0.0555,
 'B': 0.07,
 'E': 0.05
})

un_pickle = [0.00119,  0.00107,  0.0013,  0.00105,  0.00182]

m = GEKKO(remote=False)
v0 = m.Var(0.002, -0.005, 0.005)
v1 = m.Var(0.004, -0.005, 0.005)
v2 = m.Var(0.002, -0.005, 0.005)
v3 = m.Var(0.002, -0.005, 0.005)
v4 = m.Var(0.002, -0.005, 0.005)
v5 = m.Var(0.001, -0.005, 0.005)
v6 = m.Var(0.003, -0.005, 0.005)
v7 = m.Var(-0.005, -0.005, 0.005)
v8 = m.Var(-0.002, -0.005, 0.005)
v9 = m.Var(-0.001, -0.005, 0.005)
y = []
y.append(v0)
y.append(v1)
y.append(v2)
y.append(v3)
y.append(v4)
y.append(v5)
y.append(v6)
y.append(v7)
y.append(v8)
objetivos = []
obj = 0
pq = []
p = ponderaciones[:9]
p2 = ponderaciones[9:]
r = rentabilidades[:9]
r2 = rentabilidades[9:]
for j in range(len(r2) + len(r)):
    if j < (len(r)):
        obj += p[j]/(1+y[j])*r[j]
    else:
        iterator = j - len(r)
        obj += p2[iterator]*r2[iterator]
objetivos.append(obj)
pq.append(obj)
a = np.array(objetivos)
b = un_pickle
print(b[0])
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
m.solve(disp=True)
print(y)
print('Objective = '+str(m.options.objfcnval*1000000000/5))
-----------------------
EXIT: Optimal Solution Found.

 The solution was found.

 The final value of the objective function is  0.005269679643169275
 
 ---------------------------------------------------
 Solver         :  IPOPT (v3.12)
 Solution time  :  0.011 sec
 Objective      :  0.005269679643169275
 Successful solution
 ---------------------------------------------------
[[0.00080192314638], [0.00066898310209], [0.00033248534634], [0.00031046466289], \
[0.00020163446647], [0.00025390495361], [0.004994273964], [0.0049445497412], \
[-0.00031911076974]]
Objective = 1053935.92864
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
from gekko import GEKKO
import numpy as np
import pandas as pd
obj = 0
obj2 = 0

ponderaciones = pd.Series({'ACC': 0.07645771,
 'UAA': 0.0,
 'EOAO': 0.000712,
 'CIA': 0.0055,
 'BJA': 0.01,
 'BOEA': 0.03,
 'UA': 0.110,
 'EOA': 0.0712,
 'CI': 0.00557,
 'BJ': 0.0161,
 'BOE': 0.0355,
 'U': 0.0553,
 'E': 0.00071231,
 'C': 0.005555,
 'B': 0.0157,
 'E': 0.0335}
)

rentabilidades = pd.Series({'ACC': 0.0035323168,
 'UAA': 0.033975,
 'EOAO': -0.0016047,
 'CIA': -0.00248652,
 'BJA': -0.0075425,
 'BOEA': 0.0016429,
 'UA': 0.550,
 'EOA': 0.0512,
 'CI': 0.00157,
 'BJ': 0.0861,
 'BOE': 0.0555,
 'U': 0.0593,
 'E': 0.00231,
 'C': 0.0555,
 'B': 0.07,
 'E': 0.05
})

un_pickle = [0.00119,  0.00107,  0.0013,  0.00105,  0.00182]

m = GEKKO(remote=False)
v0 = m.Var(0.002, -0.005, 0.005)
v1 = m.Var(0.004, -0.005, 0.005)
v2 = m.Var(0.002, -0.005, 0.005)
v3 = m.Var(0.002, -0.005, 0.005)
v4 = m.Var(0.002, -0.005, 0.005)
v5 = m.Var(0.001, -0.005, 0.005)
v6 = m.Var(0.003, -0.005, 0.005)
v7 = m.Var(-0.005, -0.005, 0.005)
v8 = m.Var(-0.002, -0.005, 0.005)
v9 = m.Var(-0.001, -0.005, 0.005)
y = []
y.append(v0)
y.append(v1)
y.append(v2)
y.append(v3)
y.append(v4)
y.append(v5)
y.append(v6)
y.append(v7)
y.append(v8)
objetivos = []
obj = 0
pq = []
p = ponderaciones[:9]
p2 = ponderaciones[9:]
r = rentabilidades[:9]
r2 = rentabilidades[9:]
for j in range(len(r2) + len(r)):
    if j < (len(r)):
        obj += p[j]/(1+y[j])*r[j]
    else:
        iterator = j - len(r)
        obj += p2[iterator]*r2[iterator]
objetivos.append(obj)
pq.append(obj)
a = np.array(objetivos)
b = un_pickle
print(b[0])
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
m.solve(disp=True)
print(y)
print('Objective = '+str(m.options.objfcnval*1000000000/5))
-----------------------
EXIT: Optimal Solution Found.

 The solution was found.

 The final value of the objective function is  0.005269679643169275
 
 ---------------------------------------------------
 Solver         :  IPOPT (v3.12)
 Solution time  :  0.011 sec
 Objective      :  0.005269679643169275
 Successful solution
 ---------------------------------------------------
[[0.00080192314638], [0.00066898310209], [0.00033248534634], [0.00031046466289], \
[0.00020163446647], [0.00025390495361], [0.004994273964], [0.0049445497412], \
[-0.00031911076974]]
Objective = 1053935.92864
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
from gekko import GEKKO
import numpy as np
import pandas as pd
obj = 0
obj2 = 0

ponderaciones = pd.Series({'ACC': 0.07645771,
 'UAA': 0.0,
 'EOAO': 0.000712,
 'CIA': 0.0055,
 'BJA': 0.01,
 'BOEA': 0.03,
 'UA': 0.110,
 'EOA': 0.0712,
 'CI': 0.00557,
 'BJ': 0.0161,
 'BOE': 0.0355,
 'U': 0.0553,
 'E': 0.00071231,
 'C': 0.005555,
 'B': 0.0157,
 'E': 0.0335}
)

rentabilidades = pd.Series({'ACC': 0.0035323168,
 'UAA': 0.033975,
 'EOAO': -0.0016047,
 'CIA': -0.00248652,
 'BJA': -0.0075425,
 'BOEA': 0.0016429,
 'UA': 0.550,
 'EOA': 0.0512,
 'CI': 0.00157,
 'BJ': 0.0861,
 'BOE': 0.0555,
 'U': 0.0593,
 'E': 0.00231,
 'C': 0.0555,
 'B': 0.07,
 'E': 0.05
})

un_pickle = [0.00119,  0.00107,  0.0013,  0.00105,  0.00182]

m = GEKKO(remote=False)
v0 = m.Var(0.002, -0.005, 0.005)
v1 = m.Var(0.004, -0.005, 0.005)
v2 = m.Var(0.002, -0.005, 0.005)
v3 = m.Var(0.002, -0.005, 0.005)
v4 = m.Var(0.002, -0.005, 0.005)
v5 = m.Var(0.001, -0.005, 0.005)
v6 = m.Var(0.003, -0.005, 0.005)
v7 = m.Var(-0.005, -0.005, 0.005)
v8 = m.Var(-0.002, -0.005, 0.005)
v9 = m.Var(-0.001, -0.005, 0.005)
y = []
y.append(v0)
y.append(v1)
y.append(v2)
y.append(v3)
y.append(v4)
y.append(v5)
y.append(v6)
y.append(v7)
y.append(v8)
objetivos = []
obj = 0
pq = []
p = ponderaciones[:9]
p2 = ponderaciones[9:]
r = rentabilidades[:9]
r2 = rentabilidades[9:]
for j in range(len(r2) + len(r)):
    if j < (len(r)):
        obj += p[j]/(1+y[j])*r[j]
    else:
        iterator = j - len(r)
        obj += p2[iterator]*r2[iterator]
objetivos.append(obj)
pq.append(obj)
a = np.array(objetivos)
b = un_pickle
print(b[0])
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
m.solve(disp=True)
print(y)
print('Objective = '+str(m.options.objfcnval*1000000000/5))
-----------------------
EXIT: Optimal Solution Found.

 The solution was found.

 The final value of the objective function is  0.005269679643169275
 
 ---------------------------------------------------
 Solver         :  IPOPT (v3.12)
 Solution time  :  0.011 sec
 Objective      :  0.005269679643169275
 Successful solution
 ---------------------------------------------------
[[0.00080192314638], [0.00066898310209], [0.00033248534634], [0.00031046466289], \
[0.00020163446647], [0.00025390495361], [0.004994273964], [0.0049445497412], \
[-0.00031911076974]]
Objective = 1053935.92864
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
from gekko import GEKKO
import numpy as np
import pandas as pd
obj = 0
obj2 = 0

ponderaciones = pd.Series({'ACC': 0.07645771,
 'UAA': 0.0,
 'EOAO': 0.000712,
 'CIA': 0.0055,
 'BJA': 0.01,
 'BOEA': 0.03,
 'UA': 0.110,
 'EOA': 0.0712,
 'CI': 0.00557,
 'BJ': 0.0161,
 'BOE': 0.0355,
 'U': 0.0553,
 'E': 0.00071231,
 'C': 0.005555,
 'B': 0.0157,
 'E': 0.0335}
)

rentabilidades = pd.Series({'ACC': 0.0035323168,
 'UAA': 0.033975,
 'EOAO': -0.0016047,
 'CIA': -0.00248652,
 'BJA': -0.0075425,
 'BOEA': 0.0016429,
 'UA': 0.550,
 'EOA': 0.0512,
 'CI': 0.00157,
 'BJ': 0.0861,
 'BOE': 0.0555,
 'U': 0.0593,
 'E': 0.00231,
 'C': 0.0555,
 'B': 0.07,
 'E': 0.05
})

un_pickle = [0.00119,  0.00107,  0.0013,  0.00105,  0.00182]

m = GEKKO(remote=False)
v0 = m.Var(0.002, -0.005, 0.005)
v1 = m.Var(0.004, -0.005, 0.005)
v2 = m.Var(0.002, -0.005, 0.005)
v3 = m.Var(0.002, -0.005, 0.005)
v4 = m.Var(0.002, -0.005, 0.005)
v5 = m.Var(0.001, -0.005, 0.005)
v6 = m.Var(0.003, -0.005, 0.005)
v7 = m.Var(-0.005, -0.005, 0.005)
v8 = m.Var(-0.002, -0.005, 0.005)
v9 = m.Var(-0.001, -0.005, 0.005)
y = []
y.append(v0)
y.append(v1)
y.append(v2)
y.append(v3)
y.append(v4)
y.append(v5)
y.append(v6)
y.append(v7)
y.append(v8)
objetivos = []
obj = 0
pq = []
p = ponderaciones[:9]
p2 = ponderaciones[9:]
r = rentabilidades[:9]
r2 = rentabilidades[9:]
for j in range(len(r2) + len(r)):
    if j < (len(r)):
        obj += p[j]/(1+y[j])*r[j]
    else:
        iterator = j - len(r)
        obj += p2[iterator]*r2[iterator]
objetivos.append(obj)
pq.append(obj)
a = np.array(objetivos)
b = un_pickle
print(b[0])
z = (np.sum(b[0]- a[0]))**2
m.Minimize(z)
m.solve(disp=True)
print(y)
print('Objective = '+str(m.options.objfcnval*1000000000/5))

Community Discussions

Trending Discussions on pq
  • Conditional Columns and Delimited Fields
  • Go postgres prepared statement with interval parameter not working
  • Missing data fields in Wikidata Query Service results despite items having these data
  • How to solve /bin/sh: 1: source: not found during making docker image in MacOS(Golang)?
  • Read / Write Parquet files without reading into memory (using Python)
  • How can I implement a numba jitted priority queue?
  • How to fix an error associating with a comparator?
  • How to display an entire queue struct
  • GNU awk, FPAT and matching negative string regex with regex and special chars
  • Schema for pyarrow.ParquetDataset &gt; partition columns
Trending Discussions on pq

QUESTION

Conditional Columns and Delimited Fields

Asked 2022-Mar-20 at 15:55

I receive a daily export of data every day I load into my excel sheet via Power Query. The table of data I can't control is:

tblExport

Name Company States
Jane Doe ABC AK,AL,GA,WA
John Smith ACME AK,GA,FL,WA

I need to replace those State Abbreviations with a technology string of information for this question I'll use "Full State Name" as a substitute. So basically it checks the COMPANY field against another table as the "technology Strings" will be different for each Company per State.

So far so good, or so I thought. Then I split delimiters of tblExport.States BY "," which then I get

Name Company States.1 States.2 States.3 States.4
Jane Doe ABC AK AL GA WA
John Smith ACME AK GA FL WA

Now we reference that table that contains the Company, State, FullStateNames

tblStateNames

COMPANY Abbr State Name
ABC AL AlabamaABC
ABC AK AlaskaABC
ACME AK AlaskaACME
ACME GA GeorgiaACME
ABC FL FloridaABC
ABC WA WashingtonABC
ACME WA WashingtonACME
        ST01 = Table.NestedJoin(#"Changed Type1", {"States.1", "Company"}, 
        tblStateNames, {"Abbr", "Company"}, "tblStateNames", 
        JoinKind.LeftOuter),
        ExpST01 = Table.ExpandTableColumn(ST01, "tblStateNames", {"State 
        Name"}, {"tblStateNames.State Name"}),

Which works great until I meet a condition such as Company ABC has GA in the TblExport.States, but they do not qualify for GA. So when it joins the query tblStateNames and ABC doesn't match for GA it returns a null value.

So my column output is

Name Company ST01 ST02 ST03 ST04
Jane Doe ABC AlaskaABC AlabamaABC null WashingtonABC
John Smith ACME AlaskaACME GeorgiaACME FloridaACME WashingtonACME

A couple things about this. The original TblExport is a daily intake and the people range for their states, some will have ZERO and the rest can be anywhere from 1 to 40 states. The challenge and why this is semi the issue is because I can't have any gaps in the columns. So while ST03 displays null as it should I rather have it fill ST04 into the ST03 column.

Name Company ST01 ST02 ST03 ST04
Jane Doe ABC AlaskaABC null null WashingtonABC
John Smith ACME AlaskaACME GeorgiaACME FloridaACME WashingtonACME

Now after the fact I could do a conditional IF ST02 is not equal <> null then ST02 else ST03. However in this example that null simply moves from ST03 to ST02. However, this only moves the next one down a column, so a double null will still lead to an issue.

In my very new to PQ head, I think I somehow need to validate the states in the original delimited field before doing the query lookup?

I know I've probably overly complicated things and masking the actual code for internal only reasons, takes a bit longer for me to try to explain. :)

I appreciate any input, when responding, try to keep in mind my experience level. Experience Level: I'm a Highly Functioning Idiot.

Patrick

ANSWER

Answered 2022-Mar-20 at 15:55

If I understand, here is one way to do it:

  • Read in the two tables
  • split the Export table state abbreviations into ROWS
  • Join with the StateName Table
  • Group by Name and Company
  • Extract a delimited list of the state names from each subtable
  • Expand that list

Please read the code comments and explore the Applied Steps to better understand what is going on

let

//Read in the two tables
    Source = Excel.CurrentWorkbook(){[Name="tblStateNames"]}[Content],
    tblStateNames = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),

    Source2 = Excel.CurrentWorkbook(){[Name="tblExport"]}[Content],
    tblExport = Table.TransformColumnTypes(Source2, List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//split the States column by comma into Rows
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(tblExport, {
        {"States", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
            let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "States"),

//join with the States Names
    joined = Table.Join(#"Split Column by Delimiter",{"States","Company"},tblStateNames,{"Abbr","COMPANY"}, JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(joined,{"States", "COMPANY", "Abbr"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Name","Company"}, {
        {"States", each Text.Combine([State Name],";"),type text}}),

//split with no column Count option
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Grouped Rows", "States", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv))
in
    #"Split Column by Delimiter1"

enter image description here

Source https://stackoverflow.com/questions/71547873

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install pq

You can download it from GitHub.

Support

For any new features, suggestions and bugs create an issue on GitHub. If you have any questions check and ask questions on community page Stack Overflow .

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Share this Page

share link
Reuse Pre-built Kits with pq
Consider Popular SQL Database Libraries
Compare SQL Database Libraries with Highest Support
Compare SQL Database Libraries with Highest Quality
Compare SQL Database Libraries with Highest Security
Compare SQL Database Libraries with Permissive License
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.