How to "connect/link" 2 .csv files together and obtain bar graph?

758
7
08-17-2021 08:52 AM
JonathanGray2
New Contributor II

Hello all, 

New guy here asking some very basic questions, thank you for any help :).

I want to know how to "connect/link" a .csv file of only gas well locations to a separate .csv file containing data about the gas wells.  

The gas well location .csv file has a single row for each gas well and its location. This is an example of that data. https://imgur.com/a/5vXYbrt

The second .csv file contains information about each gas well's production by year. In this .csv file each year has its own row. This is an example of that data; https://imgur.com/a/eJ3h0o0

If you notice the "record_number" column appears on both .csv files. 

I can currently display in my dashboard webmap the location of each gas well. My question is how can I now "connect/link" the data in the second .csv file to the points on the map? 

My end goal is when I select a single gas well I obtain bar graph widget showing that single gas wells production by year, but when I select multiple gas wells I obtain a bar graph widget showing the average of all gas wells selected production by year. 

Like I said I am new here and I honestly just don't know the steps involved in doing this at all, but I hope some kind stranger would be willing to help me out.  If anybody would be willing to make a short .gif or youtube video, even without audio, showing me how to do this I would forever be thankful. 

I am attaching a very small amount of data from my total number of gas wells which is about 600 wells in total if anybody would like to experiment with this data. 

0 Kudos
7 Replies
RPGIS
by
Regular Contributor

Depending on what you are trying to accomplish, either a relate or join may work for your given situation.

A join will match both tables based on a common attribute so you will be able to combine each table based on that common attribute. However, the shortcoming with joins is that they are only 1:1, meaning that each record in one table will only join to a single record by the common attribute.

The other option is to do a relate. A relate (or relationship class) is similar to a join with the advantage of relating multiple records that share a common attribute. These relationships can be 1:1, 1:Many, or Many:Many.

So based on the sample data, I would recommend utilizing a relate and setting up a widget (such as Web App Builder) to utilize the related table as additional information to use in your app. This will allow for you to view the related information as a graph.

I can provide a small sample using your data if needed.

0 Kudos
JonathanGray2
New Contributor II

Hello there!  What I have done so far is convert both .csv file to geodatabase tables and then I converted my gas well location to actual points in the drawing using "make xy event layer geoprocessing tool". 

 

Now I am up to the point where that I have locations on a map and my gas well data table and I don't know how to utilize this tool properly linked here.  https://imgur.com/a/KJtuNUx Do you have any suggestions? 


0 Kudos
RPGIS
by
Regular Contributor

Yes. So here is what you would put for the information.

  • Origin Table - Gas Wells Point Feature Class
  • Destination Table - Geodatabase Table with All The Additional Well Information
  • Relationship - Simple is fine for what you are needing
  • Forward Path Label - This is simply a label that labels the to and from information
  • Backward Path Label - This is similar to the forward path label but for the reverse
  • Message Direction - Origin to Destination (Best Option)
  • Cardinality - 1: Many
  • Origin Primary Key - Record Number Field
  • Destination Primary Key - Record Number Field

This should give you what you need in order for you to create your relationship.

JonathanGray2
New Contributor II

Thank you so much for your help!

Today I have published my data and created a webmap and added a serial chart widget, but I am getting stuck and was hoping you could give me a little guidance.

If you look here https://imgur.com/a/24WTGDG you will see how my serial widget looks. It is displaying all well production data by year for each well in my map at a single time(I only have have 2 wells in this map).

What I want it to do is display production data for only a single well I select it and I get a chart that looks like something you see from excel, which is also shown in that image. With years as X-axis and production numbers on the y-axis.

Would you please consider helping me more with this?

0 Kudos
RPGIS
by
Regular Contributor

I will have to do some research to see how this can be done. I know in WAB that this can be done but I haven't tried anything in terms of Dashboards. I will let you know what I find.

0 Kudos
JonathanGray2
New Contributor II

Hey there! Would you know of a way so my dashboard chart only displays the production for each well at a time when I select a single well? Is there a way to filter by selection?   
I made a video of my current selection prefences if this helps you.  The data i am currently pointing to is the gas well data table, not the gas well location layer. Maybe I need to change this... maybe..  Any help at all is appreciated.

https://youtu.be/CCaykEm_pag

0 Kudos
RPGIS
by
Regular Contributor

Yes, but are you looking for an interactive method or just a general filter. In the configuration settings, there is the option to filter the data but that is for the data as is. If you are looking for an interactive filter, then there might be another way. Have you tried setting the filter action using the Layer Action in the map? This may allow for you to filter the data based on the matching attribute.

filter option for map.JPG

0 Kudos