Chapter 1 - Loading Data

1.01 Batch Importing Local Files - Part 1 (11 min.) Sample Lesson
1.02 Batch Importing Local Files - Part 2 (16 min.)

Chapter 2 - Wellbore Diagrams & Well Spacing

2.01 Distance Calculations (13 min.)
2.02 Distance Calculations Exercise - Part 1 (19 min.)
2.03 Distance Calculations Exercise - Part 2 (17 min.)
2.04 Distance Calculations Exercise - Part 3 (14 min.)
2.05 Distance Calculations Exercise - Part 4 (31 min.)

Chapter 3 - Plots, Charts & Maps

3.01 Gunbarrel Plots (17 min.)
3.02 Copy & Paste Example (6 min.)
3.03 Box Plots (28 min.)
3.04 Pie Charts - Part 1 (22 min.)
3.05 Pie Charts - Part 2 (14 min.)
3.06 Map Charts - Part 1 (13 min.)
3.07 Map Charts - Part 2 (22 min.)
3.08 Pie Chart Markers on a Scatter Plot (18 min.)

Chapter 4 - Data Limiting

4.01 Simple Type Curve Generator (12 min.)
4.02 Merging Tables (12 min.)
4.03 Limit Data Using Markings - Part 1 (12 min.)
4.04 Limit Data Using Markings - Part 2 (17 min.)
4.05 Python Functionality - Part 1 (14 min.)
4.06 Python Functionality - Part 2 (27 min.)

Chapter 5 - Data Functions

5.01 TIBCO Data Functions (27 min.)
5.02 Contour Lines - Part 1 (6 min.)
5.03 Contour Lines - Part 2 (8 min.)
5.04 Type Curve Generator using Python (21 min.)
5.05 Scatter Plot (16 min.)
5.06 Fitting Type Curve to Raw Data (16 min.)

TIBCO Spotfire® for O&G Professionals – Intermediate / Chapter 1 - Loading Data

Lesson 1.01 Batch Importing Local Files - Part 1

Access All SAGA Wisdom Training content Subscribe

Already a member? Sign in

Access All SAGA Wisdom Training content Subscribe

Already a member? Sign in

Transcript

01. Lesson 1.01: Batch Importing Local Files - Part 1 02. Batch Importing Local Files 03. Import Files 04. Add as rows to 05. Origin of Data

01. Lesson 1.01: Batch Importing Local Files - Part 1

OK, so the next exercise we're going to go through is Batch Importing Local Files into Spotfire.

02. Batch Importing Local Files

Now, Spotfire doesn't have an official process or an official workflow in the software to actually batch import stuff. What we're actually doing is we're telling Spotfire to bring in multiple files and based off of a similar table structure, Spotfire is going to make a recommendation to combine them in a single table. Now, in order to do this, the tables do have to have the same or a very similar number of columns. They also have to have the same or similar data type for each one of those columns too. So this is really great for production data or frac van data. So production data, a lot of times you can get this data from different operators or from a productions team on a weekly or monthly or quarterly basis or a per well basis or something like that. In the frac van data, this is really common to get one CSV file for each stage, OK. Now if you're trying to combine all this stuff up, say on a pretty big sized drilling pad, you could have maybe 2, 3, 4 or 500 different frac stages on that pad. If you can, you want to avoid going through and adding these files or joining these files one by one, right. So what we're going to show you here is a way that you can effectively kind of drag and drop this stuff over as one method or kind of trick it through the "browse to local file" as well to let Spotfire give us some recommendations on how to pull the stuff together in one batch.
Now the problem with this is that we still have to go through the import settings window for each one of these files. So what does that mean? Spotfire, remember when you bring in a local file, you have to go through and you have to tell the data type for each one of the columns. Think about this, if you're bringing in 500 different CSV files and you also have 10 columns in there, you will have to pick out the data types 5,000 times. Now, one way to avoid this is that you can include a data type row on this, which we've done in this example for you. We're also going to have some other tips and trick modules in here too, that shows you different ways that you can add the data type row either through regular expressions or some really simple Excel macros.

03. Import Files

So to do this, let's go ahead and get started. So what I'm going to do is we're going to flip over to Spotfire. So we've got a fresh example here. And we're just going to import a bunch of files from the get go. Now, the easiest way to just batch import stuff is we can go over to Spotfire. We're going to go out here to this folder where we've given you directional surveys for 10 different wells. So these are the same 10 wells that we went through in the beginners course. We can just highlight all of these guys. Drag and drop them into the Spotfire project. Let Spotfire load it up. Right now it's trying to locate the data types for each one of these and here we go. Now for each one of these Excel files, there's actually 2 different sheets in it. There's one called "Raw data" and one called "Scrubbed data". So what we want is for each one of these, we have to change this from the raw to the scrub data table. And actually, just to show you, if I open up one of these files too, just a second. OK, on the first tab, the raw data, this is raw data came directly from the COGCC, which is the regulatory body in Colorado. On the second tab here, I have the scrubbed data which has all these guys in here. Now you'll notice on these, I didn't put a data type row for these Excel sheets. And the reason for that is because honestly, it's all pretty much the same, it's really not going to matter too much here. But again, every time we go to the import settings window, you need to be able to change this. So again, change the worksheet from "Raw data" to "Scrubbed data". We're going to assume that all the data types here are correct and hit "OK". And we're going to do this 9 more times. This is the problem with this is because there is no batch import, we have to go through this every single time. So again, raw data to scrubbed, hit "OK". I'm going to have to keep going through this.

04. Add as rows to

So now that we've gone through the import settings for all those, you'll notice on mine, Spotfire didn't actually give me a good recommendation at all. What it's actually trying to do is it's trying to take each one of those Excel files, even though they're all very, very similar, it's trying to add them all as individual data tables. So what we're going to do here is for each one is a little dropdown menus, we're going to actually expand this guy. And does everybody see right here, where it says "Add a new data table"? If we actually select this and change it to "Add as rows to", you notice as soon as I select that, it asks me for a target. I'm just going to choose the first table here, which I believe is the one above me. And look at this, you see the little icon for it went from this, what's supposed to be the new data type table icon, to this one down here, which has like this little green bar at the bottom with the plus. This is the symbol for an add rows. So if I do this for each one of these individually, I can get all 10 of these guys lined up. You can see for each one of these, it's now adding it to the existing list. Now, this is not ideal. However, I'm going to show you here in a second another way that we can trick it to where if it does do this... This is fine for 10 different tables. You know, it's not a huge effort, but if you're trying to bring in a couple hundred, obviously this is not a fun time. So we'll change these last couple of ones here and we're good to go.
All right, so now what I should be seeing is this one symbol up here, which says we're going to import a data table here. If I expand that out, it says add as a new data table. And then everything else here where I have this little green with a plus on it, this is going to add rows to that existing table. So once we get this, let's go and select "OK". This is going to compile all these suckers together, create that new data table. And now I have data in the project. So you can see here, the background for Spotfire went from blue to white, that means I have data available. And I can create a new table visualization here and there we go. So you notice I got 1,273 rows, which is what I expected, OK.

05. Origin of Data

Now, you'll notice on the right-hand side, there's a new column here called "Origin of Data". This is a column that Spotfire creates when you actually do an "Add rows" between 2 different tables. The reason for that is because if you have 2 different tables or these directional surveys for these 2 different wells or 10 different wells and stack them on top of each other, a lot of times it's really hard to figure out where that actual data came from, which one of those Excel files. And this origin of data is essentially, by default, gives you the file name or the table name that it came from. So, if we ever wanted to edit that or if we had one of these joins mess up, we can go down here to our 3 little dots in a string. This is our data canvas. So let's open this guy up. In here, we can see where we have the initial Excel sheet coming in here and then we have an add rows for each one of these files coming in. For each one of these, I have this little gear here for the settings. So let's go ahead and select this guy. So here, you can see my from original data and from new data, so this is where I'm matching the columns between the 2. This dropdown here is showing me if there's any additional columns that I can add. Now, all of these Excel files I've cleaned up for you so there is no extra columns. They all have the exact same number of columns with all the same names. And at the bottom here, you have the identified origin of rows, which is showing me this is where the original data came from, which is this Excel file. So remember, Spotfire is going to name tables by default as the Excel file name, followed by a hyphen and then the sheet name. And then the value for the new rows, this is the Excel file for the second source. And again, we can always see the preview for these on the right-hand side with the light blue up top and the dark blue down at the bottom. And then here is that origin of data column.
Now, you can change this column name, however, I don't recommend it. The reason for that is that if you move this project or one of these files from one location to another, for whatever reason, a lot of times on these add rows unions that origin of data column, if you change the name, will get corrupted more often than not. So I recommend leaving that name as is. Now this is the easiest way where I showed you all this kind of drag-and-drop method to pull these guys together. So I'm going to close out the "My data table" here and I'm going to go up to data in data table properties. And I'm going to rename this the drag-and-drop method. So that's one way to do this. That's going to be fine when you're working with a couple of files like this. Although even when we did this example, you'll notice that it didn't automatically recommend to put all these guys together.
So another way to do this is, let's hit this little plus symbol, is we can browse up to a local file, we can join to together, 2 different Excel files together and kind of train Spotfire to actually join those. All right, so what we just did here is the drag-and-drop method. One way to do it, not always the most efficient. The reason for that is because a lot of times Spotfire will not recommend what we want it to recommend, which is the example we saw here. So what we're going to do is we're going to save this project out and we're going to create a new project and I want to show you how I typically bring this stuff in, which is essentially bring in one data table, add rows from another data table, and then we batch import the rest. The reason we're doing that is we're going to train Spotfire effectively beforehand to recognize what we want it to do. So see you in the next lesson.