How to eliminate empty columns

Specific Domains Data
1

Here is roughly the code which exports data from the table to CSV String

using LibPQ, DataFrames, CSV

executeResult = execute(conn, "SELECT c1, c2, c3, c4, c5 FROM some_table WHERE some_id = '$someId'")
if (LibPQ.libpq_c.PGRES_TUPLES_OK != LibPQ.status(executeResult))
    throw("Data not found.")
end
bEmptyData = isempty(executeResult)
if !bEmptyData
    frame = DataFrame(executeResult)
    sResult = join(CSV.RowWriter(frame))
end

Code works pretty well, but it needs one adjustment.
In the resulting data set, some of the columns can be completely empty (e.g. result contains 5 rows and in each of them column c4 is NULL/missing). In such case, I am supposed to remove c4 from sResult altogether (not in the header nor comma for it in any row).

What would be the most efficient way to do that?

Thank you.

2

I’d probably do something like:

using TableOperations
io = IOBuffer()
executeResult |> 
  TableOperations.select(:c1, :c2, :c3, :c5) |>
  CSV.write(io)
sResult = String(take!(io))
3 Likes
3

Yes, thank you. But I need also the logic to find out which of these 5 columns have all result NULL/missing. c4 I mentioned above was just an example.

4 
using LibPQ, DataFrames, CSV

executeResult = execute(conn, "SELECT c1, c2, c3, c4, c5 FROM some_table WHERE some_id = '$someId'")
if (LibPQ.libpq_c.PGRES_TUPLES_OK != LibPQ.status(executeResult))
    throw("Data not found.")
end
bEmptyData = isempty(executeResult)
if !bEmptyData
    frame = DataFrame(executeResult)
    to_remove = String[]
    for name in names(frame)
        coltype = eltype(frame[!, name])
        if coltype != nonmissingtype(coltype) && all(ismissing.(frame[!, name]))
            push!(to_remove,name)
        end
    end
    !isempty(to_remove) && select!(frame, Not(to_remove))
    sResult = join(CSV.RowWriter(frame))
end

Here is one option. I believe that LibPQ returns types based on the table schema, so verifying that missing is part of the eltype will only tell you that it’s nullable and thus might not be worth doing in your case, which is why I didn’t filter by that.

Edit: added it because why not

1 Like
5

This is it, Peter. Thank you!

1 Like