Orphan Well Association Data Preparation for Wells Under Reclamation

Reading Reclaim wells from the Orphan Well Association (OWA) of Alberta

Lists of wells are published by the OWA almost quarterly in an excel format. This list represents what is currently under reclamation and under the management of the OWA. The files come from this page and are Open data and can be found here – http://www.orphanwell.ca/pg_orphan_well_list.html .

This workflow starts after the file has been downloaded.

The code below also adds dates to the original columns in the file. The date it was published by the OWA appears as a header in the original file and the date that it was read is added as a column for reference.

#the originl file can be found here #http://www.orphanwell.ca/pg_orphan_well_list.html
#read OWA to reclaim
library(readxl)
OWA_to_Reclaim <- read_excel("D:/Input/List of Orphan Sites Under Reclamation.xlsx", 
                             skip = 1)
OWA_to_Reclaim$PublishDate = as.POSIXct("2017-9-14", tz = "MST")
OWA_to_Reclaim$RetrieveDate = Sys.Date()
head(OWA_to_Reclaim)
## # A tibble: 6 x 6
##       `Unique Well ID`          `Licensee Name` `Licence No` `OWA Job #`
##                  <chr>                    <chr>        <chr>       <dbl>
## 1 00/10-09-001-07W4/02 HAZELWOOD ENERGY LIMITED     W0170330      602096
## 2 02/10-09-001-07W4/00 HAZELWOOD ENERGY LIMITED     W0175619      602097
## 3 00/13-03-001-11W4/00    RICHBAR RESOURCES LTD     W0150868      602065
## 4 00/04-04-001-11W4/00    RICHBAR RESOURCES LTD     W0146550      602067
## 5 02/07-04-001-11W4/02    RICHBAR RESOURCES LTD     W0130523      602068
## 6 03/07-04-001-11W4/03      SAGUARO ENERGY CORP     W0353316      602084
## # ... with 2 more variables: PublishDate <dttm>, RetrieveDate <date>

Reading All Alberta wells from shapefile

To get more detail, like latitude and longitude, we need to retrieve the ST37 shapefile from the Alberta Energy Regulator (AER).

## Loading required package: sp
## Checking rgeos availability: TRUE
## rgdal: version: 1.2-7, (SVN revision 660)
##  Geospatial Data Abstraction Library extensions to R successfully loaded
##  Loaded GDAL runtime: GDAL 2.0.1, released 2015/09/15
##  Path to GDAL shared files: C:/Users/jenni/Documents/R/win-library/3.3/rgdal/gdal
##  Loaded PROJ.4 runtime: Rel. 4.9.2, 08 September 2015, [PJ_VERSION: 492]
##  Path to PROJ.4 shared files: C:/Users/jenni/Documents/R/win-library/3.3/rgdal/proj
##  Linking to sp version: 1.2-4
## OGR data source with driver: ESRI Shapefile 
## Source: "D:/Input", layer: "ST37_Well_GCS_NAD83"
## with 594409 features
## It has 18 fields
##             coordinates                 UWI       KeyList Update
## 1 (-110.1338, 49.00462) 00/06-06-001-01W4/0 0014010606000   <NA>
## 2 (-110.1338, 49.00462) F1/06-06-001-01W4/0 0014010606F10   <NA>
## 3  (-110.0694, 49.0193) F1/05-10-001-01W4/0 0014011005F10   <NA>
## 4 (-110.0619, 49.02066) 00/06-10-001-01W4/0 0014011006000   <NA>
## 5 (-110.0433, 49.03841) 00/11-14-001-01W4/0 0014011411000   <NA>
## 6 (-110.0493, 49.04294) 00/13-14-001-01W4/0 0014011413000   <NA>
##                                   Name Field    Pool OSArea   OSDep
## 1          CMG PCGAS WILDHORSE 6-6-1-1  0998 0000000   <NA> 0000000
## 2        HEYLAUFF WILDHORSE DW 6-6-1-1  0961 0158098   <NA> 0000000
## 3            BORDER FELDON DW 5-10-1-1  0998 0000000   <NA> 0000000
## 4     HERTZ MINERALS CRESSDAY 6-10-1-1  0998 0000000   <NA> 0000000
## 5        CANDEL ET AL FELDON 11-14-1-1  0998 0000000   <NA> 0000000
## 6 SOCONY 15 CYPRESS HILLS TH 13-14-1-1  0998 0000000   <NA> 0000000
##      LicStatus  License  LicDate Licensee Agent Operator   FDDate TotalDep
## 1 RecCertified  0065081 19770803    A5D40  <NA>     <NA> 19770820  1204.00
## 2       Issued  0078503 19791009    0FF30  <NA>     <NA> 19770820  1205.00
## 3       Issued  0107524 19840607    A2450  <NA>     <NA> 19840624  0391.40
## 4 RecCertified  0050356 19740705    0T690  <NA>     <NA> 19740710  1216.20
## 5 RecCertified  0083713 19800625    0K210  <NA>     <NA> 19800730  0847.00
## 6    RecExempt 0004123L 19520125    00570  <NA>     <NA> 19520125  0244.00
##     WellStat StatDate
## 1 0002000000 19770824
## 2 0600080000 19770822
## 3 0600080000 19840626
## 4 0002000000 19740712
## 5 0002000000 19800731
## 6 0002000000 19520126

Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.

OWA_to_Reclaim$UWI <- substr(OWA_to_Reclaim$"Unique Well ID",1, 19)
OWA_to_Reclaim$Reclaim <- 1
Reclaim <- merge(OWA_to_Reclaim, ST37_shape, x.all = TRUE)
head(Reclaim)
##                   UWI       Unique Well ID                   Licensee Name
## 1 00/01-01-008-13W4/0 00/01-01-008-13W4/00              HANSAR ENERGY CORP
## 2 00/01-02-033-10W4/0 00/01-02-033-10W4/00     FAIRWEST ENERGY CORPORATION
## 3 00/01-04-033-25W4/0 00/01-04-033-25W4/00 WATER SOLUTIONZ ENTERPRISES INC
## 4 00/01-08-069-22W5/0 00/01-08-069-22W5/00             BRL ENTERPRISES INC
## 5 00/01-11-026-14W4/0 00/01-11-026-14W4/00                GP RESOURCES LTD
## 6 00/01-12-049-05W4/0 00/01-12-049-05W4/00            STEALTH VENTURES INC
##   Licence No OWA Job # PublishDate RetrieveDate Reclaim       KeyList
## 1   W0443004    601525  2017-09-14   2017-09-30       1 0084130101000
## 2   W0234231    601407  2017-09-14   2017-09-30       1 0334100201000
## 3   W0063401    601183  2017-09-14   2017-09-30       1 0334250401000
## 4   W0071818    602744  2017-09-14   2017-09-30       1 0695220801000
## 5   W0288147    601514  2017-09-14   2017-09-30       1 0264141101000
## 6   W0381211    602149  2017-09-14   2017-09-30       1 0494051201000
##   Update                             Name Field    Pool OSArea   OSDep
## 1   <NA> HANSAR ENERGY DD CHINCO 1-1-8-13  0998 0000000   <NA> 0000000
## 2   <NA>        RICHBAR PROVOST 1-2-33-10  0750 0126342   <NA> 0000000
## 3   <NA>              CREW TWIN 1-4-33-25  0913 0800160   <NA> 0000000
## 4   <NA>  BRALORNE ET AL STURLS 1-8-69-22  0876 0000000   <NA> 0000000
## 5   <NA>          GP RES  CESS 1-11-26-14  0206 0220427   <NA> 0000000
## 6   <NA>           SLV WILDMERE 1-12-49-5  0963 0160098   <NA> 0000000
##   LicStatus License  LicDate Licensee Agent Operator   FDDate TotalDep
## 1 Abandoned 0443004 20120118    A6450  <NA>     <NA> 20120305  1122.00
## 2 Abandoned 0234231 20000225    A1KF0  <NA>     <NA> 20000307  0368.00
## 3 Abandoned 0063401 19770404    A2WL0  <NA>     <NA> 19770412  1779.40
## 4 Abandoned 0071818 19780908    0P630  <NA>     <NA> 19781106  2643.00
## 5 Abandoned 0288147 20030609    A2TL0  <NA>     <NA> 20030621  1146.00
## 6 Abandoned 0381211 20070830    A1PT0  <NA>     <NA> 20070922  0528.00
##     WellStat StatDate coords.x1 coords.x2
## 1 0002000000 20160726 -111.6380  49.61493
## 2 0202000000 20151023 -111.3089  51.79730
## 3 0103000000 19780519 -113.4810  51.79679
## 4 0002000000 19781113 -117.3102  54.95446
## 5 0202000000 20170120 -111.8414  51.19905
## 6 2502000000 20170305 -110.5913  53.20825

How many rows are in the result?

Print the number of rows that match a well in the AER shape master list.

nrow(Reclaim)
## [1] 782

Manupulate the Towship Range

Parse the township rage for easy sorting by surveyed area. Concatenate to represent groups acording to surveyed Townships in Alberta.

Reclaim$TWP <- substr(Reclaim$KeyList,1,3)
Reclaim$MER <- substr(Reclaim$KeyList,4,4)
Reclaim$RNG <- substr(Reclaim$KeyList,5,6)
Reclaim$TWPRNG <- paste(Reclaim$TWP,"-",Reclaim$RNG,"-W",Reclaim$MER, sep="")
head(Reclaim)
##                   UWI       Unique Well ID                   Licensee Name
## 1 00/01-01-008-13W4/0 00/01-01-008-13W4/00              HANSAR ENERGY CORP
## 2 00/01-02-033-10W4/0 00/01-02-033-10W4/00     FAIRWEST ENERGY CORPORATION
## 3 00/01-04-033-25W4/0 00/01-04-033-25W4/00 WATER SOLUTIONZ ENTERPRISES INC
## 4 00/01-08-069-22W5/0 00/01-08-069-22W5/00             BRL ENTERPRISES INC
## 5 00/01-11-026-14W4/0 00/01-11-026-14W4/00                GP RESOURCES LTD
## 6 00/01-12-049-05W4/0 00/01-12-049-05W4/00            STEALTH VENTURES INC
##   Licence No OWA Job # PublishDate RetrieveDate Reclaim       KeyList
## 1   W0443004    601525  2017-09-14   2017-09-30       1 0084130101000
## 2   W0234231    601407  2017-09-14   2017-09-30       1 0334100201000
## 3   W0063401    601183  2017-09-14   2017-09-30       1 0334250401000
## 4   W0071818    602744  2017-09-14   2017-09-30       1 0695220801000
## 5   W0288147    601514  2017-09-14   2017-09-30       1 0264141101000
## 6   W0381211    602149  2017-09-14   2017-09-30       1 0494051201000
##   Update                             Name Field    Pool OSArea   OSDep
## 1   <NA> HANSAR ENERGY DD CHINCO 1-1-8-13  0998 0000000   <NA> 0000000
## 2   <NA>        RICHBAR PROVOST 1-2-33-10  0750 0126342   <NA> 0000000
## 3   <NA>              CREW TWIN 1-4-33-25  0913 0800160   <NA> 0000000
## 4   <NA>  BRALORNE ET AL STURLS 1-8-69-22  0876 0000000   <NA> 0000000
## 5   <NA>          GP RES  CESS 1-11-26-14  0206 0220427   <NA> 0000000
## 6   <NA>           SLV WILDMERE 1-12-49-5  0963 0160098   <NA> 0000000
##   LicStatus License  LicDate Licensee Agent Operator   FDDate TotalDep
## 1 Abandoned 0443004 20120118    A6450  <NA>     <NA> 20120305  1122.00
## 2 Abandoned 0234231 20000225    A1KF0  <NA>     <NA> 20000307  0368.00
## 3 Abandoned 0063401 19770404    A2WL0  <NA>     <NA> 19770412  1779.40
## 4 Abandoned 0071818 19780908    0P630  <NA>     <NA> 19781106  2643.00
## 5 Abandoned 0288147 20030609    A2TL0  <NA>     <NA> 20030621  1146.00
## 6 Abandoned 0381211 20070830    A1PT0  <NA>     <NA> 20070922  0528.00
##     WellStat StatDate coords.x1 coords.x2 TWP MER RNG    TWPRNG
## 1 0002000000 20160726 -111.6380  49.61493 008   4  13 008-13-W4
## 2 0202000000 20151023 -111.3089  51.79730 033   4  10 033-10-W4
## 3 0103000000 19780519 -113.4810  51.79679 033   4  25 033-25-W4
## 4 0002000000 19781113 -117.3102  54.95446 069   5  22 069-22-W5
## 5 0202000000 20170120 -111.8414  51.19905 026   4  14 026-14-W4
## 6 2502000000 20170305 -110.5913  53.20825 049   4  05 049-05-W4

Write to csv for analysis with Power BI or Tableau or tool of your choice

write.csv(Reclaim, file = "D:/Output/Reclaim.csv")

Advertisements
Posted in Uncategorized | Leave a comment

Why a Unit Chart?

This is about representing data in a way to make people understand data scale and perspectives. I want others to understand why I care about a certain detail and it is because of my perspective and interest in the data. I would measure the success of any visualization by having people’s ability to readily understand the message that you are trying to convey – without them having to ask many basic questions. Questions and refinement are actually welcome, when they build on your basic visual. Questions like “What am I looking at?” can be valid but a sign that perhaps you did not choose and design the visual well enough and you have missed your point!

So what about today’s title and Unit charts? I had these two concepts I wanted to show about data:

  1. A portion of a whole based on three populations and each with some categorization. In case I wanted to emphasize division of data on a category and not what the categorization was about. There were only 2 categories and should be easy to read.
  2. I wanted to communicate a sense of scale. The main thing here is the size of the unit chart would be proportional to set or population size.

I was mostly concerned with visual impact and not in exploring the data itself. The intent was to show what steps I would take next, depending on perspective and intent, to design visuals that would promote data investigation and insight later.

Having showed the first document to someone, I had I pretty much  gave up on it. But then as coincidence would have it –  I saw a unit chart in the “Show Your Work“, pages 49-50 under the chapter suggesting you “SEND OUT A DAILY DISPATCH.” By the way the person I had check the document gave me the book! So unit charts are appealing to me and perhaps I should dispatch that thought. I also stumbled upon Unit Charts are for Kids on the internet. His point is perhaps they are too simplistic but for my purpose I am after simplistic. This is another internet read on unit charts for adults with another name for this type being  Square Pie Charts.

So this is what I tried and I am not sure how effective it is. I would have liked to change the colours but I cannot adjust them without reprogramming the Power BI custom visual “BrickChart”.

The intent here is to show how each group is categorized independently. Making each population appear as if it is equally weighted. Which is more interesting to you without looking at the numbers and why?

2017-02-04_9-12-41-all-equal-size

The second example scales the same squares according to population. Each smaller square fits in the “black” area of the bigger square. This is where colour options would help show the story better. But the sizes really can tell a story on their own even without the colour differentiation.Which square gets your attention most?

2017-02-04_9-13-47-sized-by-population

I welcome comments and suggestions. If anyone is out there!

 

Posted in data, data visualization | Tagged , , , , , | Leave a comment

The Problem of Sharing Pictures

For some reason I am faced with the problem of trying to get a picture available to the general public. My intent is to have that copy  under my control as the person publishing it. There seems to be many issues with possible copyright but I do not really know the detail which in the end inhibit my creativity and knowledge sharing. I find that there are the  problems based on my ignorance, with using pictures from the internet as follows:

  1. It is not necessarily clear to me who owns rights to any image on the internet and if I can use it.
  2. Creative Commons exist but many images are not under Creative Commons so the selection is very limited.
  3. The are applications that many of us upload photos to as in Instagram, Facebook and Pinterest. These platforms enable sharing  to your own specifications within these applications but they do not make it easy to share outside of that application.
  4. Copyright seems complex. It is not clear to me  if the Internet is beyond what any country specifies and there seems to be so much legal ease or unease in my case to wade through and understand to fully be cognizant of all or any related implications.
  5. If you are researching topics and want to share conclusions by altering or reusing images then where are the guidelines, best practices and who defines these and follows these?

I am now going to upload my own picture and one that I do not care if it circulates. I am going to see if it is then accessible by WordPress with an associated Image URL.

sunrise_photo_Jan_Calgary

I seriously do not think anyone reads this but if by chance you do and you are knowledgeable about this – I would appreciate some tips and information.

 

Posted in Uncategorized | 1 Comment

Why even think of blogging?

So why do this? Well it seems like these things are important to me right now (Q1 2016):

  •  being able to voice my opinions
  • thinking about and exploring my own creativity
  • expressing thoughts about my creativity
  • creativity in general
  • themes that relate to contradiction
  • enabling a way to exchange and share thoughts with others

I work with and create using computers – it is a medium that enables me. This mode of communication seems to be a contradiction in the following ways:

  •  This blog is very transparent to anyone with a network and modern device YET I feel it is private.
  • I do not write for a living YET I am committing to write things here.

The benefit to me is:

  • I get to write my ideas down in some structured way.
  • I can share what I write to anyone and at any time if we are using a device that is connected to the internet.
  • There seems to be no cost
    • only a potential cost in what I intend or accidentally share revealing my private and temporal thoughts

I will include information on data and data science because that is one of the creative endeavors I am currently exploring. And I do see science and data science as being creative.

 

Posted in Uncategorized | Leave a comment