kodiak | 🔮 A bot to automatically update and merge GitHub PRs | Continous Integration library

I added test data to your example csv since it only had 1 county:

I am not sure what you mean by "closest to the south". I will interpret it as the minimum latitude.

If so, you can use row_number():

Try any of these options (base R and fill() from tidyr):

The simplest one would be to merge the 2 data frames for intersection.

You could set the NULL parameter to 'n', but then that would break other cases, where null fields are represented in the usual way (by the unquoted empty string), rather than by 'n'. COPY does not let you set NULL per column.

Your current method seems fine to me, it would be my first choice (other than fixing the generator of this file which may be out of your control). You could also write something with Perl or awk or sed which would edit the file as a stream to turn the ',n,' to ',,' and hook it up to the COPY with the PROGRAM feature, but that seems fiddly and error prone and I doubt the efficiency gains would be all that large.

This strikes as one of those premature optimizations which is the root of most evil. Is this really a meaningful problem?

You can match this with:

It looks like you have "Na" instead of "NA" for one of your values. You could try adding na = c("", "NA", "Na") to your read_csv function call.

Assuming your dataframe is called df, try this:

You may use simple web-scraping techniques, such as bs4 and requests.