• Raster files are continuous grid data with a defined resolution usually stored as .tiff, .jpeg, .bmp, or .png. For example, a satellite image georeferenced to a coordinate system is a raster file.
• Vector files are either point, line or polygon features. Most commonly these are stored as shapefiles, but can also be GeoJSON, KML, GML, TIGER or nested inside a geodatabase (GDB)

GIS data is inherently complex due to the file format structures and the hundreds reference coordinate systems used. For example, a single polygon shapefile is actually a collection of at minimum (3) files (.shp, .shx and .dbf) with up to (13) other associated filetypes storing various metadata.

The complexity of these data types requires special consideration when storing the data in a database management system (DBMS). One of the most popular open-source DBMS is PostgreSQL which can store GIS data by installing the PostGIS extension.

Getting Started

First you must have PostgreSQL downloaded locally or have access to an instance on a server. This article assumes some experience with Postgres and the associated tools pgAdmin and psql (Shell).

If you need/want a crash course with Postgres then follow this link: https://www.postgresql.org/docs/9.4/tutorial-install.html. ←Walks you through the download and creation of your first database.

Installing PostGIS Extension

Open Application Stack Builder from Windows Start menu.

Choose the version of PostgreSQL you want to install PostGIS onto.

Click Next and expand the Spatial Extensions tree and choose the latest PostGIS version.

You’ll be prompted about various installation parameters during the install, just go with the defaults.

Creating a GeoDatabase

Once installation is complete open psql (Shell) and login. Make sure you are in the version of Postgres the PostGIS extension was added to.

Create a new database, here I call it gis. You’ll see CREATE DATABASE as a callback confirming one has been created.

postgres=# CREATE DATABASE gis;
CREATE DATABASE

Create the PostGIS extension for the new database.

postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION

At this point you have a new database named gis which is populated with the PostGIS extension attributes. You can open the database in pgAdmin and visually inspect the properties.

There are many functions that are added which allow for specialized GIS queries and, most importantly, there is a table added called spatial_ref_sys. This table contains all of the SRIDs (spatial reference identifier) which you’ll need as a reference for importing GIS data (this will be shown later).

Source Some Spatial Data

With the database created and configured for GIS we can upload a shapefile to it. I found a shapefile in the public domain which is the 100-year floodplain for Colorado from FEMA (https://geo.colorado.edu/catalog/47540-5ca228ffd43267000b8c7448). Download and extract the files to a separate folder.

shp2pgsql Utility

When PostGIS was installed it came along with some utilities. One of those utilities is shp2pgsql. This tool is capable of converting a shapefile into a SQL format digestible by Postgres. It will have been placed at C:/Program Files/PostgreSQL/14/bin/shp2pgsql.exe.

Let’s use this tool to convert our shapefile. The first step is to figure out the reference coordinate system for the shapefile. To do this open the .prj file, included in the floodplain download, with a text editor.

PROJCS[“NAD_1983_UTM_Zone_13N”,GEOGCS[“GCS_North_American_1983”,DATUM[“D_North_American_1983”,SPHEROID[“GRS_1980”,6378137.0,298.257222101]],PRIMEM[“Greenwich”,0.0],UNIT[“Degree”,0.0174532925199433]],PROJECTION[“Transverse_Mercator”],PARAMETER[“False_Easting”,500000.0],PARAMETER[“False_Northing”,0.0],PARAMETER[“Central_Meridian”,-105.0],PARAMETER[“Scale_Factor”,0.9996],PARAMETER[“Latitude_Of_Origin”,0.0],UNIT[“Meter”,1.0]]

Unfortunately this doesn’t include SRID; the information shown is stored in the srtext which is a column of the spatial_ref_sys table in our new database. So, the best approach is to query this table using SQL to find the closest matching SRID.

Open psql and connect to the gis database. Use the following command format to search the spatial_ref_sys table:

SELECT srid, srtext FROM spatial_ref_sys WHERE srtext ILIKE ‘%NAD83 / UTM zone 13n%;’;

Doing so shows us:

26913 | PROJCS[“NAD83 / UTM zone 13N”,GEOGCS[“NAD83”,DATUM[“North_American_Datum_1983”,SPHEROID[“GRS 1980”,6378137,298.257222101,AUTHORITY[“EPSG”,”7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[“EPSG”,”6269"]],PRIMEM[“Greenwich”,0,AUTHORITY[“EPSG”,”8901"]],UNIT[“degree”,0.0174532925199433,AUTHORITY[“EPSG”,”9122"]],AUTHORITY[“EPSG”,”4269"]],PROJECTION[“Transverse_Mercator”],PARAMETER[“latitude_of_origin”,0],PARAMETER[“central_meridian”,-105],PARAMETER[“scale_factor”,0.9996],PARAMETER[“false_easting”,500000],PARAMETER[“false_northing”,0],UNIT[“metre”,1,AUTHORITY[“EPSG”,”9001"]],AXIS[“Easting”,EAST],AXIS[“Northing”,NORTH],AUTHORITY[“EPSG”,”26913"]]

This tells us that the SRID is 26913. Now we can import the shapefile into our database.

Open command prompt and cd to the folder containing the shapefile library.

cd custompath/Floodplain_Colorado_100yr_Effective_1_28_2016_X2

Now you’ll need to point to shp2pgsql, specify the SRID, the file to convert and the name of the resultant sql file. In the code below, the -D specifies ‘dump format’ which speeds up the process, -I creates a geospatial index and after -s is where you define the SRID we found.

C:/”Program Files”/PostgreSQL/14/bin/shp2pgsql -D -I -s 26913 Floodplain_Colorado_100yr_Effective_1_28_2016.shp > Floodplain_Colorado_100yr_Effective_1_28_2016.sql

After execution of this code you’ll see a new file added to the shapefile directory:

Importing the Transformed Shapefile into PostgreSQL

Use the command line to import the file to postgres. You’ll be prompted to input your postgres admin password.

psql -p 5433 -U postgres -d gis -f Floodplain_Colorado_100yr_Effective_1_28_2016.sql

Using /dt we can confirm that the shapefile was added as a new table to the gis database.

Connecting to QGIS — Adding Layer

This tutorial also assumes that you have experience with QGIS which is a freeware GIS solution with a lot of really advanced analytical tools built in.

Open QGIS and in the browser panel you’ll see PostGIS with the PostgreSQL icon next to it. Right-click on it to add a new connection.

Now add in the appropriate credentials and name the connection, here I chose GIS.

Once connected you see the database and shapefile added previously.

You can add the shapefile to the project by right-clicking. You’ll be prompted to choose a conversion method from a list of options. It estimates the error margin from each method which is convenient for determining which to choose.

After conversion add the layer onto a basemap to view it.

Adding a Raster File to PostGIS

Raster files can be loaded into PostGIS similarly, but using a different tool. The utility needed is in the same bin folder previously described, called, you guessed it, raster2pgsql.

First download a raster file. I got one from the public domain at: https://www.naturalearthdata.com/downloads/50m-raster-data/50m-natural-earth-1/. At this link I downloaded the Shaded Relief and Water raster.

The raster file is a .tif file and it comes with a read me that says its coordinate reference system is WGS84 a very commonly used coordinate reference. I happen to know the SRID for this 4326, but you could query the spatial_ref_sys table as before if needed.

Open the command prompt and cd to the location of the .tif file. Similar to before, enter the path to raster2pgsql followed by cod ein the format below:

C:/”Program Files”/PostgreSQL/14/bin/raster2pgsql -I -C -F -s 4326 NE1_50M_SR_W.tif public.shaded_relief_ocean_basemap > shaded_relief_ocean_basemap.sql

Here’s a code breakdown:

• -I : creates a spatial index in the raster table
• -F : creates a column containing the filename (good for housekeeping)
• -C : opens constraints for ref coordinate system declaration (-s)
• -s : followed by an SRID applies the coordinate system to the raster
• public.shaded_relief_ocean_basemap : This is the <schema>.<table> you want to create when importing to postgres. So you’re seeing me want the table in public schema with an intuitive name
• shaded_relief_ocean_basemap.sql : this is the SQL dump file created which will be imported to postgres

After the file is created, upload it to the gis database as before.

psql -p 5433 -U postgres -d gis -f shaded_relief_ocean_basemap.sql

**Note that you will run into an issue if postgis_raster is not added as an extension to your database. To check if it is run \dx in psql. You can easily add it via pgAdmin by right-clicking on extensions in the database tree**

Congrats, now you have a geodatabase with one shapefile and a raster file which can be connected to QGIS or ArcGIS.

Creating a GeoDatabase with PostgreSQL/PostGIS and Connecting to QGIS was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

Prelude

A data engineer is mainly tasked with extracting data from multiple sources, transforming the data, checking the validity of the data, transforming it for specific uses before finally loading it into a data warehouse for end users to access (a process often described by the acronym ETL). More often than not the data is streaming (updating frequently); so these tasks must be built as scripts and compiled into a ‘pipeline’ which can run automatically at specific intervals.

This tutorial shows a simple example of how to extract data from an API, transform it with Python, store it in a local PostgreSQL database and then compile the actions as a pipeline which automatically recurs on a schedule and logs each occurrence.

This workflow has many applications from small scale personal use up to large scale commercial applications. The actual example I present was chosen to simplify the tutorial and likely doesn’t have much actual utility. Enjoy!

Description

Case

You want to collect intraday stock trade data for stocks in your portfolio to store in your local Postgres database. Maybe you’re interested in developing a machine learning model which attempts to predict the market and you want to build your own data pool rather than buying it from a vendor, or something like that.

**Now, there are a million billion trillion market analysis applications already developed and available for free even so actually doing this would be silly, but that’s our case here. Actually useful cases I can think of are too domain-specific and add too much complexity which would make things a bit thick for most readers.**

Prerequisite Skills & Software

• Development was performed in a Jupyter Notebook (https://www.anaconda.com/products/distribution)
• PostgreSQL (local) is the database (https://www.postgresql.org/download/)

You should at least be a seasoned beginner with Python and know Pandas to hang here. SQL… you can’t be a data person without SQL, so leave and go do intro tutorials for that, at minimum, before attempting this. This could be your first foray into PostgreSQL, but you should understand relational database theory or else you’ll be quite lost.

Getting the Data

Alpha Vantage (https://www.alphavantage.co/) offers a free API for intraday stock data; just sign up and receive a free API key. The intraday API offers 1–2 months of historical price and volume data in intervals as low as 1 minute. This will be our data source.

The API documentation for building queries is found here: https://www.alphavantage.co/documentation/

Data can be accessed as either CSV or JSON, we’ll do JSON to add some complexity (everyone knows how to use CSV data, it’s boring).

Examining the query string for the URL and referencing the API documentation you’ll see that I am requesting intraday data for Tesla (TSLA) at a 1-minute interval.

Doing this provides JSON data which structurally is a dictionary of dictionaries; one containing metadata and the other containing the time series data with open, close, high and low prices for each minute interval as well as the volume.

Converting the JSON data to a Pandas data frame lets us examine it visually.

The meta data offers the description of the data (time interval etc.), the refresh date and the ticker symbol. The time series data clearly needs transforming to be useful. We’ll want the rows switched into columns and the column names changed mainly.

Much better. Since we’ll also we collecting data for multiple stocks (composing a portfolio) an additional column for the ticker symbol should be added as well.

Before we move on from the data set we need to check and transform the data types if needed.

All object (strings) data types, not good. Obviously time should be datetime, volume should be integer and all others (besides ticker) should be floats. Although time should be a datatime data type, we’ll leave it as a string for easier import into Postgres which we’ll get into next.

PostgreSQL

If you haven’t already you’ll need to download and configure PostgreSQL which is a free open-source database management solution. There are two main ways to interact with Postgres: pgadmin and psql (shell). Pgadmin is a GUI for connecting to and managing a Postgres database; psql is a command line prompt for achieving the same goals. You really should be able to use both of these in my opinion to be proficient in Postgres.

Once you download PostgreSQL you’ll setup a password for your local server. We’ll mainly use the psql shell to setup a new database for our project.

Open psql from your start menu. To connect to the server just hit enter to accept the defaults unless you’ve specified otherwise previously. Then you’ll be prompted to enter the password you set up earlier.

Next, create a data base with:

CREATE DATABASE stocks;

If you’re learning PostgreSQL, it’s useful to open pgadmin as well and check that the commands you are performing are actually working. If you do, you’ll see a new database called stocks has been created.

Now we’ll create a tables, also called stocks, where our data will be housed. This is where your SQL skills come into play. The table should be equivalent to the table you’ve transformed from the JSON data collected via the API.

CREATE TABLE IF NOT EXISTS stocks( 
     ticker VARCHAR(10) NOT NULL, 
     time TIMESTAMP NOT NULL,
     open FLOAT(2),
     high FLOAT(2),
     low FLOAT(2),
     close FLOAT(2),
     volume INTEGER
);

You’ll notice the time column in SQL is defined as TIMESTAMP, Postgres will convert the string timestamp as long as the format is yyyy-mm-dd hh:mm:ss.

Now there is an empty table called stocks inside of a database called stocks. We will also want an index. Below is the code for establishing a multi-level index which will ensure efficient queries as the database grows in size (a more advanced topic; see: https://www.timescale.com/blog/select-the-most-recent-record-of-many-items-with-postgresql/).

CREATE INDEX ix_ticker_time ON stocks (ticker, time DESC);

Connecting to PostgreSQL from Python

The next steps are more complex for a Python beginner, but the methodology can be copied easily without knowing exactly what’s going on here (maybe).

First there are two Python packages I use which are not standard and need installed; pip install if you don’t have them already.

• psycopg2 — a popular tool for connecting to PostgreSQL from Python
• pytz — a package for getting time for specific time zones

There are (3) main processes needed to achieve our goal at this stage:

1. Connect to the PostgreSQL database
2. Inject the API data into the stocks table
3. Log the successful transaction or capture any errors generated

We want to achieve these goals programmatically using Python so that we can have a script that runs on a schedule.

Code below shows how to connect to the database with psycopg2; a tuple of connection parameters is entered into the connect module.

A cursor is needed to allow submission of SQL queries to the connection, see below.

The extras module of psycopg2 will allow for the UPSERT of the data into the empty table (UPSERT is the method of updating a table while avoiding duplication). This module takes (3) arguments which are:

• a cursor for the Postgres connection (created already above)
• a SQL query
• a list of tuples with the collected data

After you execute the values you must commit the connection to execute the action on the data base.

Awesome! Use pgadmin if you want to see that you’ve added the data successfully to your stocks database. This proves the concept and sets up the framework for establishing a script which contains all of these steps which performs all of these steps sequentially in (1) .py file.

In setting up a singular script we must not forget about an important step which is we want to log our transactions with the database. I’ve wrapped the entire database transaction into a single function. Also included is a try — except block in the advent of an error. A log file is written and saved with a numeric file name representing the NYC time zone of the update.

As stated in the case description we want to cycle through a portfolio of stocks. A for loop is used to cycle through the API queries and concatenate a compiled data frame. All of this is compiled into a single python script which you can view as gist at the end of this article.

Scheduling the Job to Run Daily

We will set this job to run daily using Windows Task Scheduler. You could have it run in smaller intervals by setting up a cron scheduler alternatively.

First step is to create a batch file. To do so, open notepad and save a file with .bat extension. Inside of this file write (3) lines as shown below:

"C:\Program Files\Python37\python.exe"
"C:\your_custom_path\python_file_name.py"
pause

Obviously change the paths to fit your specific scenario and then follow the next steps outlined below:

1. Open Windows Task Scheduler
2. in the Action tab choose Create Basic Task
3. Click Next and choose the time you want the script to run each day
4. Click Next and in the program/script box enter the path to your python.exe
5. In the Add Arguments box enter the name of the python file you’re scheduling
6. In the Start In box enter the path the to python file

Great! Now, every day, you PostgreSQL database will be updated with the minute-by-minute trade data for all stocks in your portfolio.

I hope that someone somewhere out there in the ether has found this useful.

Cheers.

Data Engineering: Automatically Update PostgreSQL Database from API was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

Self Introduction

My name is Elii Skeans and I am a reservoir engineer at Surtek. I hold a B.S. Petroleum Engineering from Colorado School of Mines, B.B.A. International Business from the University of Akron and a M.S. Data Analytics from CSU.

I specialize in compositional numerical modeling for enhanced oil recovery in conventional and unconventional reservoirs, oilfield data analytics and economic modeling for oilfield valuation.

I have had the luxury in my position so far to perform reservoir simulation on world class assets in South America, Libya, Kuwait, Canada and the U.S.

Since 1978 Surtek has been a global leader in enhanced oil recovery

• Experience includes all forms of EOR but specialty is in chemical EOR (liquids)
• Laboratory services specialized in EOR, world recognition for EOR coreflooding expertise. (250+ laboratory evaluations)
• Client list includes nation oil companies, major and independent oil companies, and chemical manufacturers. (375+ clients in 25+ countries)
• Most successful Chemical EOR hotspots initiated/assisted by Surtek (China, Wyoming, Canada, Kuwait, etc.)
• 30+ years in modeling of EOR technologies. Compositional modeling specialists.

Problem Statement

Production data is one of the most common datasets that a reservoir engineer will encounter. Every asset I have worked on has had production data in different formats.

Depending on the type of analysis planned, different formats are needed:

• FHF data for simulation software
• Month normalized data for DCA
• Rates vs. Cums
• Etc.

Python is the perfect tool to complete the require transformations and code is often reusable.

Wyoming Oil & Gas Commission Field Data (WOGC)

The example I have built for this exercise involves the extraction of field production data (at the individual well level) for a field in Wyoming called Hartzog Draw.

The field was chosen for no specific reason other than that it is still active and has many oil-bearing wells.

Field data is available at http://pipeline.wyo.gov/FieldMenu2.cfm on the WOGC website.

Unfortunately this data is painful to collect. The screenshot below shows that for each of the ~250 wells in the field I would have to check a box next to the well name. That is a mind numbing task that anyone would hope to avoid.

There is a better option but it is reserved to only those who know Python or an equivalent tool

There is an option to download all of the Wyoming’s historic production data at the well level from the following link: http://pipeline.wyo.gov/urecordsMenu.cfm?Skip=%27Y%27&oops=ID69217

The two datasets of interest are the well headers and the well production data. Download both to follow along.

The first thing you will notice is that the production data comes in (4) CSV files. The next thing you will notice is that Excel crashes when you try to open it because each spreadsheet alone busts the row limit in Excel.

This is the PERFECT Python example in that you cannot even use Excel if you wanted to.

Loading Data

For those who are new to Python, Pandas is an absolutely essential package which utilizes data frames which I will abbreviate to df . A great tool for aggregating data is the glob package. Using glob I tell Python to cycle through each file that starts with ‘Counties’.

• As a warning I will tell you that this bit of code takes upwards of 30 minutes to complete. I provide a slice of the data frame that allows for quicker runtimes and less RAM.

We can see from the data frame’s shape that there are 1,490,485 rows in the data set. Printing the head of the df shows us that it is not listed as continuous time series data. You will notice that there are separate columns for each month and each production fluid. This means that you cannot plot this data in the current format.

For this data to be useful it must be transformed. Doing so will explode the number of rows in the dataset. Each row currently represents a year, so doing a transform will increase the number of rows by 12X.

The next dataset we need is the well headers. This is a much more manageable data set with only 142k rows.

This data table contains the field name for each well which is missing form the production data set.

We will find the API numbers associated with the field and select only those wells from the total production data set.

Now our production data set includes only wells in the Hartzog Draw field. The shape of the data frame is shown below and we can see it is now only 7,768 rows long and there are 359 wells included.

Now for the Transform

The main tool used here is _melt_ which is included in the Pandas package.

id_vars defines the columns that will remain columns and value_vars are the column you want converted into rows. var_name sets the name of the new row data and value_name sets the name of the newly created column.

This tool is used to create columns for a new data frame called dfnew which is then reloaded into df_HD_prd.

Now that is the format we are all used to and is easy to work with.

This completes the ETL protion of the webinar. The idea here was to show how Python can save you time and effort in loading, manipulating and transforming data into formats that are useable in your work flow. Each data transformation is unique requiring the development of unique code. This example should show some of the basic functionalities of Pandas in Python.

Decline Curve Analysis (DCA)

If you are watching this webinar then surely you are familiar with Arp’s equation for decline curve analysis. This is the most basic tool used in estimating the future performance of a well.

Arp’s Equation for Hyperbolic Decline

Typically DCA is done using commercial software. That software is expensive and a lot of smaller companies limit their access to these tools due to cost especially with the market today. This workflow is more to showcase the capabilities of Python and how it can boost your workflow to the next level.

In this section I’ll introduce two new packages that we’ll combine together in this analysis; Plotly and SciPy.

Plotly is a package for making interactive plots. We are going to be using an extension called plotly.express which still blows my mind at how easy it is to use. In the next section I make an interactive scatter plot in one line using 46 characters.

SciPy is an essential package in Python that acts as an ecosystem for hundreds of pre-defined mathematical operations typical in science and engineering. Optimization is listed as one of the main competencies of SciPy along with numerical integration, interpolation, linear algebra, and statistics.

We want to create a function that fits a decline curve using Arp’s equation. We also want to be able to view the decline fit in real time and manipulate the variables as needed. So Plotly will show us the results and let us interact with them and SciPy will run code to generate a best fit to our data.

Plotting

Let’s make a simple plot of the production of some random well.

Great. Now it looks like there has been some recompletions or well workover done around 2017 so lets grab a decline off of that.

To do so we’ll have to subset the data. I’ll just manually enter a date as a variable in prewritten code.

Fitting the Data

Now we will create a function that contains the various permutations of Arp’s equation. I want my function to be able to fit an exponential, hyperbolic, or harmonic decline.

With the function built we can load the production data into the algorithm.

Below I sort the df by date, then reset the index which creates a new index from 0 to len(df). Now my x values are the df.index numbers and my y data is the oil rate. You must provide a starting point for the optimization variables to the algorithm which is what I am doing with p0

The optimize function outputs (2) arrays. One contains the fitted values for variables and the other is a covariance array. I’ll print the popt list to see the values for b, Qi, and Di.

Neat. Now I want to plot all of this. First lets create a new data frame for the fit data. We’ll pass the fitted variables into the dca() function to create a decline that goes out 20 years into the future. We will also set an economic limit, below this number the well will be shut in.

We might as well output the expected remaining production for the well to assign a PDP value as well.

The code below is plotting the decline data onto the total production history.

Now we have reached the conclusion. DCA was applied to a well in an automated way that was then tweaked manually for a better fit. The data was then analyzed to determine the PDP (proved-developed-producing) from the remaining life of the well.

Find the GitHub repo @: https://github.com/es42289/HackerVillage_1

Feel free to reach out to me for any clarifications or requests regarding this post at es42289@gmail.com.

Cheers,

Elii

BONUS

How cool would and interactive tool with a dropdown menu to cycle through each well be? The answer is very cool. I won’t add any discourse here to support this but below is code to make the same charts with a dropdown for each well. Also included in the code is an automated DCA applied to each well (which it does unsuccessfully).

Oilfield Data Analytics: Extract, Transform, Load (ETL) and Decline Curve Analysis (DCA) was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

I was in no hurry to buy a car and wanted to make sure that when I bought it I was getting a good deal. It can be pretty hard to compare value across Jeeps with all kinds of different features and modifications; there is no clear market value like new cars have.

To do this I needed to think of some way to automate a daily search which identifies features I value and presents them with easily digestible graphs and charts. This way I would save countless hours of scrolling through ads and prevent myself from making a brash decision because I was fed up with the search.

Automating the Search

All of my future potential Jeeps are listed on the internet and the posts are changing daily due to the high volume of sales. I needed to automate a search, extract data from the search results, store the data, run analytics, and then output useful graphs.

Identifying Features

First, I needed to know what information I wanted to search for and collect. The following parameters are what I chose:

· Miles driven

· Model Year

· Make/Model

· Location

· Date Posted

· Price (<$20,000)

Scraping

I like to buy cars from Craigslist.org, so this was the website I needed my code to search. I knew that there were multiple web scraping tools available in the wonderful and labyrinth-like world of Python packages. I decided to use Scrapy which is self-described as “An open source and collaborative framework for extracting the data you need from websites. In a fast, simple, yet extensible way.”

When you search on Craiglist for something you can examine how the url changes for each search page and notice that it is a regular expression (meaning it follows a pattern). Recognizing this was the key to building the web-crawling ‘spider’. The basic tasks my spider accomplished were:

· Search ‘Jeep’ on craigslist at a 100 mile radius around Denver

· Extract the title, price, mileage, odometer, year, and make from the listing

· Output the results to a file

On a Craigslist search page there are tiles for each posting. The spider had to ‘crawl’ across each one of them to get the data I wanted. Luckily each add is labeled with regular html tags which can be searched. For example, the price is located at xpath: ‘///span[@class=”result-price”]/text()’. Each of my variables has its own common tag which I can search for and output sequentially to a text document. Not being a web developer can make this part of the code pretty tricky, but if you are interested in seeing what I am talking about, go to any website and right-click and choose ‘Inspect Element’. If you hover around in the html code you can find the part of the webpage you are interested in, then you can right-click again to copy the xpath.

Scrapy Code

Data Analysis

Now we have this text file built as a table with the output of the days search for Jeeps. Next, I have a separate code which I run in Jupyter Notebook to analyze the data. The main libraries used for this task are pandas and matplotlib.Pandas is an absolutely essential data science library for python and matplotlib is a robust visualization package for making graphs.

The most basic analysis I ran was price by year shown in this figure:

This chart tells me a number of things even though it is pretty basic. (1) There are more newer Jeeps than older ones for sale. This is expected since cars tend to die after 20–30 years or so of use. (2) The oldest Jeeps are the most expensive. This is also logical given that a 1960 Jeep must have had extensive renovations and may be a collectors item as well.

There are several Jeep models to choose from; Wrangler, Cherokee, Commander, Liberty, etc. I was mostly interested in finding either a Cherokee or a Wrangler so I split the dataset into three categories:

· Wrangler

· Cherokee

· Other

Plotting the same graph for each category gives further insight:

The next figure provides additional useful information for each type. The conclusions are that Wranglers are more expensive than Cherokees on average and that both are more expensive than other types of Jeeps.

Another important parameter to consider was the odometer reading for each car. To plot this I decided to segment the ads into three odometer ranges:

· 0–50k miles

· 50–100k miles

·100–150k miles

I wasn’t interested in cars with more than 150k miles on the engine so this worked well. Plotting Wrangler and Cherokee separately for price by year and coloring by odometer range outputs the next figure:

From this we see that:

· Jeeps with less miles are newer

· Most have over 100k miles

· There are few Wranglers under $20k with less than 100k miles

· Cherokees with less than 100k miles tend to be newer than Wranglers with the same mileage

It is easier to analyze the data all on one graph, so I decided to plot a 3D scatter chart for each odometer range by price by year and colored by model. In Figure 4 we see the output for this idea. From this we see that the quantity of Jeeps decreases with less mileage and that there is an inverse relationship between price and mileage. My ideal purchase would be a Wrangler with less than 100k miles and less than $15,000. With this in mind, my target is the middle of the far right cluster for potential purchases.

Running my web-crawler and analyzing the graphs led me to my final decision after about 3 months. I’ve been more than pleased with my final purchase and had a lot of fun building my python skills while solving a real life problem.

Data Analytics for Life Optimization: Buying a Car was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

Volve is an oil field located on the Norwegian continental shelf. The field was discovered in 1993, but production did not begin until 2008. Equinor (formally known as Statoil) was the majority stake operator of the field. Approximately 63 million barrels of oil were produced from the field with peak rates reaching 56Mbpd before being shutdown in 2016.

In 2018, Equinor took the unprecented step of releasing all associated files used in the management and development of the field. Files include seismic, drilling, production, dynamic model, PVT, and many other types of data. The purpose of the release was to add a comprehensive and rich data set for use in academia to bolster the next generation of engineers. The data can be found here: https://www.equinor.com/en/how-and-why/digitalisation-in-our-dna.html

Historically this type of reservoir data has been read and analyzed within commercial software packages. Programs like Petra, Landmark, or Petrel are very powerful and very, very expensive. Licenses for these softwares can run thousands of dollars a month. This expense is justifiable for large corporations, but out of reach for individuals outside of these companies.

The purpose of this article is to show open source methods for visualizing the Volve reservoir data using Plotly and Python.

A plethora of data is available but we will focus on surface data for the Ty, Hugin, and Shetland formations along with the well data. The formation top data is found in the Geophysical Interpretations data set.

First let’s explore the data.

The well data has latitudes, longitudes, depths, and perforation information. The horizon files all contain just three columns: latitude, longitude, and depth.

Let’s plot the well locations on a simple scatter plot just to get the orientation of the wells.

This is slightly useful in that it shows the general location of the wells compared to each other.

To make things a little more fancy we can create a meshgrid that interpolates values between known depths of the wells to create a contour plot.

This provides some extra insight to the reservoir structure. We can see one outlier well off to the west which could be a disposal well or a dry hole at a much deeper depth and we see some wells in what might be the crestal region of the field.

Plotting things in 3D gets us closer towards having something useful to visualize. Let’s set the well data aside and incorporate the formation tops to get some structure.

We want to make 3D surface plots here so we need to make a smooth and continuous surface from the simple formation top data. The data is robust enough to give us a pretty picture. We are using the surface function from Plotly graph_objects which gives us some cool features out of the box including contour and spike lines.

Now we really have something useful and pretty cool to play with also. The graph is interactive showing contour lines, spike lines, and a hover text with location and layer data.

Now let’s get the well bores plotted showing the perforation intervals in red so we can see where the producing formation is.

Now we can see the wellbores in the main crestal region of the Hugin formation. If we remove the other layers we can get a cleaner image of the in the main target formation.

A fully interactive version of this plot can be found at https://es42289.github.io/Volve_Hugin

Visualizing Reservoir Data with Plotly by Elii Skeans was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.