> ## Content Index
> Fetch the complete content index at: https://www.bambooweekly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bamboo Weekly #33: Fracking (solutions)
- URL: https://www.bambooweekly.com/bw-33-fracking-solution/
- Published: 2023-09-28T15:00:33.000Z
- Updated: 2026-08-23T09:37:06.000Z
- Description: Get practice with CSV files, multiple files, comprehensions, dates and times, formatting, missing data, plotting, and grouping.
- Author: Reuven M. Lerner
- Tags: csv, multiple-files, comprehensions, datetime, formatting, missing-data, plotting, grouping

This week, we’re looking at fracking ([https://en.wikipedia.org/wiki/Fracking](https://en.wikipedia.org/wiki/Fracking?ref=bambooweekly.com)), a commonly used technique to extract oil and gas from the earth. Fracking has long been controversial, and is even illegal in some countries — but a New York Times story earlier this week discussed a new problem, namely the amount of water that is going to fracking. The fact that many areas of the United States are low on water makes it all the more controversial for fracking to use so much of this precious resource.

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fe084dfd3-ae3d-4796-91f6-18bdf6267a75_1024x1024.jpg)

Dall-E, in response to: A car (in its entirety) on the beach. A nozzle is filling the car not with gas, but with water from the ocean. .png

### Data and eleven questions

This week, we looked at data from FracFocus, an organization that collects information about fracking, including chemicals injected into the earth to improve the fracking process. This data set also describes how much water is used. The data files are all in CSV format, and can be downloaded via this link:

```
    https://fracfocusdata.org/digitaldownload/FracFocusCSV.zip
```

I gave you eleven tasks and questions this week. That’s a lot, but there was a lot to explore with this data set! (I thought about doing much more, but there’s a limit as to how much I can ask of you in a given week.) Here are the questions, along with my solutions and explanations:

### Import the "FracFocusRegistry" CSV files into a single data frame. Include only the columns: JobStartDate, TotalBaseWaterVolume, StateName, CountyName, and FederalWell. Don't bother trying to parse the \`JobStartDate\` column into datetimes just yet. Let them be strings.

First, let’s load Pandas:

```
import pandas as pd
```

Now that we’ve done that, we need to create a single data frame from a bunch of CSV files. Fortunately, the CSV files all fit the same pattern; if we were in the Unix shell, then we could describe them as

```
FracFocusRegistry_*.csv
```

(Of course, we could name each of the files individually. But we’re programmers, and we want to subscribe to the DRY (“don’t repeat yourself”) rule as often as possible. Fortunately, Python’s standard library comes with the “[glob](https://docs.python.org/3/library/glob.html?highlight=glob&ref=bambooweekly.com#module-glob)” module, whose “glob” function (i.e., “glob.glob”) returns a list of filenames matching a pattern. We can thus say:

```
import glob
glob.glob(f'FracFocusRegistry_*.csv')
```

But of course, we aren’t interested in the filenames. Rather, we want to take each of those CSV files, turn it into a data frame, and then combine all of those data frames into a single, large data frame. (You could, in theory, combine all of the files and then read them into Pandas with one call to [read\_csv](https://www.bambooweekly.com/pandas-read-csv/), but that would raise all sorts of other issue.)

What we’ll need to do is take each filename and run [read\_csv](https://www.bambooweekly.com/pandas-read-csv/) on it. The result from each call to read\_csv will be a data frame. If we put each of those data frames into a list, then we can call [pd.concat](https://www.bambooweekly.com/pandas-concat/) on the list, getting a single, large data frame as a result.

My favorite way to do this is with a list comprehension, which lets us invoke a Python expression repeatedly as we iterate over a bunch of values. Since we know what columns we want from the CSV files, we can even specify that with the “usecols” keyword argument:

```
import glob

all_dfs = [
    pd.read_csv(one_filename,
      usecols=['JobStartDate', 
        'TotalBaseWaterVolume', 'StateName', 
        'CountyName', 'FederalWell'])
    for one_filename in glob.glob(f'FracFocusRegistry_*.csv')
]

df = pd.concat(all_dfs)
```

The above code iterates over each of the filenames we get back from calling glob.glob. It runs pd.read\_csv on each filename, returning a data frame. all\_dfs is the list of data frames we get back from each of these calls. pd.concat takes a list of data frames and returns one new one from its inputs:

```
import glob

dirname = '/Users/reuven/Courses/Current/data/fracfocus'

all_dfs = [
    pd.read_csv(one_filename,
      usecols=['JobStartDate', 
        'TotalBaseWaterVolume', 'StateName', 
        'CountyName', 'FederalWell'])
    for one_filename in glob.glob(f'{dirname}/FracFocusRegistry_*.csv')
]

df = pd.concat(all_dfs)
```

We now have a single data frame with our five columns and 6,087,921 rows.

### Now turn \`JobStartDate\` into datetime dtypes. If the input date string cannot be parsed, then leave it as \`NaT\`.

Normally, when we read data from a CSV file, we can indicate that a particular column contains date and time information (rather than a string) by passing the “parse\_dates” keyword argument, along with a list of columns we want to be parsed as dates. However, if you were to try that with these files, it wouldn’t work. That’s because there are a number of lines that contain illegal datetime values. Using “parse\_dates” in “read\_csv” will result in a string column, rather than datetime64.

In such a case, it’s better to read the data in as a string, and then use “[pd.to\_datetime](https://www.bambooweekly.com/pandas-to-datetime/)” to perform the conversion. That’s because to\_datetime takes a variety of arguments that we can use to specify the conversion more clearly and explicitly.

The basic idea is that we call pd.to\_datetime on a series of strings, and get back a series of datetime64 objects. We can then assign that series to a new column in our data frame, or (more likely) assign it back to the same column that we’re converting, replacing strings with datetimes.

In the simplest case, we could say:

```
df['JobStartDate'] = pd.to_datetime(df['JobStartDate'])
```

But as I mentioned above, that’ll give us some warnings about the date format, and an indication that we would be better off specifying the date format explicitly, by passing the “format” keyword argument and a string describing the date format. (You have to use format codes to do this, and I never remember them. Fortunately, there’s [https://strftime.org](https://strftime.org/?ref=bambooweekly.com), which lists them.)

In this particular case, I decided to try my luck passing the “errors” keyword argument, with a value of “coerce”. When you do that, any failure to parse the date creates a “NaT” value — short for “Not a time,” the datetime equivalent of “NaN,” or “Not a number.”. This would guarantee that the resulting column would contain datetime64 values, with the only question being how many of them would be NaT:

```
df['JobStartDate'] = pd.to_datetime(df['JobStartDate'], errors='coerce')
```

Sure enough, this worked just fine, with only a handful of rows containing NaT values.

### Tell Pandas to display numbers with up to 3 digits after the decimal point, and with commas before every three digits.

The numbers in this data set, and that we’ll encounter with our analysis, are going to be pretty big. Some of them will be big and also contain values after the decimal point. I thus asked you to tell Pandas to format numbers with two rules:

- Put commas before every collection of three digits
- Limit floating-point numbers to display three digits after the decimal point

We can do this by setting a Pandas option, with the “[set\_option](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.set%5Foption.html?ref=bambooweekly.com)” function. The option that we want to change here is float\_format, which means we can say:

```
pd.set_option('display.float_format', FORMAT_WE_WANT)
```

Obviously, FORMAT\_WE\_WANT will need to be replaced with a value. But what value?

In this case, it will almost certainly be a string describing the format we want, followed by a reference to the “[str.format](https://docs.python.org/3/library/stdtypes.html?highlight=str%20format&ref=bambooweekly.com#str.format)” method. This method has become somewhat passe in the last few years, now that f-strings are available, but it still exists — and in this particular case, it’s quite useful.

By passing a reference to str.format, along with the string template we want to use, Pandas can apply it to every float that it displays. Wherever you have a {} (curly braces) inside of the string, Pandas will place the original value. For example, if you want all numbers in Pandas to be displayed with a dollar sign before them, you can say:

```
pd.set_option('display.float_format', '${}'.format)
```

By the way: Don’t really do this. However — and you didn’t hear this from me — it might be amusing to see what happens if you run the above code on a colleague’s computer when they step out to make a cup of coffee.

Inside of the curly braces, we’ll typically put a colon (:), followed by a format string describing how we want the value to be formatted. Don’t remember what’s available? It’s not precisely the same, but [https://fstring.help](https://fstring.help/?ref=bambooweekly.com) can give you a lot of insights.

Let’s take the two things that I wanted to do, for example:

- I want to have commas placed before every group of three digits. We can do this by putting a comma immediately after the colon, at the start of the format string.
- I want to show at most three digits after the decimal point. We can do this by stating .3f in the format string — in this case, right after the comma.

In the end, our format string will be:

```
pd.set_option('display.float_format', '{:,.3f}'.format)
```

And sure enough, now when we ask Pandas to display any calculations, we will get commas in our large numbers and a cutoff of three digits for our small numbers.

### Remove any rows that contain null values (\`NaN\` or \`NaT\`). How many rows did you remove?

Back in question 2, we imported the dates from our CSV files. However, because we said that any errors should be turned into NaT values, now we have to deal with them. The easiest way to deal with them is to remove them completely. It turns out that we can use [dropna](https://www.bambooweekly.com/pandas-dropna/) to remove any row containing not just NaN but also NaT.

But before we do that, let’s find out just how many we have. We can do that by invoking [isna](https://www.bambooweekly.com/pandas-isna/) on our data frame, getting a True or False value for each element:

```
df.isna()
```

That’s nice, but I want to know how many there are. Fortunately, we can take advantage of the fact that True is 1 and False is 0, and then just sum up the numbers:

df.isna().sum()

The result that I got is:

```
JobStartDate              699
TotalBaseWaterVolume    60468
StateName                   4
CountyName                 93
FederalWell                 0
dtype: int64
```

Given that we have more than 6 million rows, removing all of the rows with NaN or NaT values would remove about 1 percent of the rows in the entire system. We can even see the percentage of rows that contain NA values in each column:

```
(df.isna().sum() / len(df.index)) * 100
```

The result I get back is:

```
JobStartDate           0.011
TotalBaseWaterVolume   0.993
StateName              0.000
CountyName             0.002
FederalWell            0.000
dtype: float64
```

Sure enough, we see that TotalBaseWaterVolume — the very information that we’ll be using in many of our exercises — is NaN in nearly 1 percent of the rows. In the other cases, including JobStartDate, the number of NA values is so small that I’m willing to part with them. We can do that with dropna:

```
df = df.dropna()
```

Remember that dropna doesn’t actually modify the data frame on which you run it. Rather, it returns a new data frame, identical to the one on which you ran “dropna”, but without any NA (NaN, NaT, etc.) values.

If you only want to remove the rows containing NaT in JobStartDate, you can tell dropna to restrict its check to a subset of the columns:

```
df = df.dropna(subset=['JobStartDate'])
```

I didn’t do that, though, and my results will reflect removing all NaN values.

### How many entries are there for each state? Given that there are 50 states in the US, how do you explain the number of results we get? How could you fix that?

How many entries are there in our data frame for each state? The easiest way to answer this question is with a call to my favorite method, “[value\_counts](https://www.bambooweekly.com/pandas-value-counts/)”:

```
df['StateName'].value_counts()
```

The result, though, is a bit odd:

```
StateName
Texas           3032109
Oklahoma         576388
North Dakota     517132
Colorado         471379
New Mexico       318727
                 ...   
MS                    2
tx                    2
Ok                    2
Texasa                2
Pennsylvanya          2
Name: count, Length: 75, dtype: int64
```

value\_counts returns a new series, one in which the index contains the unique values in the column (“StateName”), and the values contain integers, the number of values.

We can see that the returned series contains 75 rows. And because we can see the top 5 and bottom 5 results, we also see that there are states like Texas, Oklahoma, and North Dakota… and other states such as MS, tx, Ok, Texasa, and Pennsylvanya — all with problems in spelling or capitalization.

Basically, the we’re getting these weird results because the state names haven’t been standardized. Somewhere along the line, a person entered the information, and it was just passed along, without any filtering or correction.

What can we do about this?

The data here is messy, but not impossible to fix. Many of the names are simply misspelled, some are abbreviations rather than names, and the like.

I started off by using the “str.title” method, which capitalizes the first letter in each word and keeps other letters lowercase:

```
df['StateName'] = df['StateName'].str.title()
```

I also decided that it would be useful to remove leading and trailing whitespace, just in case some values have them:

```
df['StateName'] = df['StateName'].str.strip()
```

With these in place, I ran the “[replace](https://www.bambooweekly.com/pandas-replace/)” method on my series. The first argument to replace can be a dict, with the keys indicating what we should look for, and the values indicating what they should be replaced with. I came up with the following dict:

```
replacements = {'Ms': 'Mississippi',
                'Tx': 'Texas',
                'Texasa': 'Texas',
                'Texs': 'Texas',
                'Texas ?': 'Texas',
                'Midland': 'Texas',
                'Noth Dakota': 'North Dakota',
                'Norht Dakota': 'North Dakota',
                'North Dakata': 'North Dakota',
                'North  Dakota': 'North Dakota',
                'North Dakota ?': 'North Dakota',
                'North Dakotta': 'North Dakota',
                'Norh Dakota': 'North Dakota',
                'Nd': 'North Dakota',              
                'Ok': 'Oklahoma',
                'Oklahoma ?': 'Oklahoma',
                'West Viginia': 'West Virginia',
                'Pennsylvanya': 'Pennsylvania',
                'Wy': 'Wyoming',
                'Pa': 'Pennsylvania',
                'Co': 'Colorado',
                'Nm': 'New Mexico',
                'Ca': 'California',
                'Wv': 'West Virginia',
                'Ks': 'Kansas',
                'Mt': 'Montana',
                'Oh': 'Ohio',
                'La': 'Louisiana',
                'Utah ?': 'Utah',
                'Ut': 'Utah',
                'Wyominng': 'Wyoming',
                'Roosevelt':'Montana',
                'Ward':'Texas'
               }

df['StateName'].replace(replacements).value_counts()
```

The final two entries in the dict (Roosevelt and Ward) are based checking the database and doing some online searching. I ran “replace”, and then called “value\_counts” on the result. And the results made a lot more sense, including only coming from about two dozen states:

```
StateName
Texas             3034299
Oklahoma           576718
North Dakota       517388
Colorado           471955
New Mexico         318749
Pennsylvania       238393
Utah               183361
Wyoming            157504
Ohio               111708
Louisiana          110779
West Virginia       95188
California          90547
Arkansas            33216
Virginia            22882
Kansas              21617
Montana             20866
Alaska               7627
Mississippi          6364
Alabama              4719
Kentucky             1194
Michigan              650
Nevada                389
Nebraska              299
New York              111
Illinois               70
North Carolina         51
Indiana                12
Idaho                   7
Name: count, dtype: int64
```

I hadn’t originally planned to clean up the data in this way, but given that I managed to get all of the states into a more refined format, I did make it a permanent change:

```
df['StateName'] = df['StateName'].replace(replacements)
```

### Create a line graph showing how many (total) gallons of water have been used, per month.

The inspiration for looking at this data set was the NYT article about the use of water in fracking. I’d thus like to know, per month, how much water is really being used — and then, I want to create a line graph with that information.

Creating such a line plot requires that we have a series whose index is the months, and whose values are the total amount of water used per month.

The first step in doing this is to turn our “JobStartDate” column (a datetime64 dtype) into the index of our data frame:

```
df.set_index('JobStartDate')
```

But this isn’t quite enough; we now have a time series (i.e., a data frame with datetime values as the index), but we now want to get, for every month in the data set, the total number of gallons of water used.

That’s where the Pandas “[resample](https://www.bambooweekly.com/pandas-resample/)” method comes in: It does a form of groupby on our time series, using whatever time granularity we specify. For example, here I want to know, for every one-month period, the total number of gallons. I can thus say:

```
df.set_index('JobStartDate').resample('1M')['TotalBaseWaterVolume'].sum()
```

The “1M” means that I want to divide the data into one-month chunks. Then, for each month, I want to know the total amount of water used. The result:

```
JobStartDate
2001-11-30       365,929,284.000
2001-12-31                 0.000
2002-01-31                 0.000
2002-02-28                 0.000
2002-03-31                 0.000
                     ...        
2023-05-31   426,150,529,011.844
2023-06-30   344,227,010,996.046
2023-07-31   281,235,487,472.937
2023-08-31   109,114,250,584.584
2023-09-30     2,614,358,393.000
Freq: M, Name: TotalBaseWaterVolume, Length: 263, dtype: float64
```

Notice two things here:

- First, the index of the resulting series contains dates — the final date of each month. The result of resampling will always be a series, and you’ll get one value back for each chunk in the resampling, with a timestamp at the end of that chunk. So if you resample on “1Y” (one year), you’ll get December 31st of each year. If you resample on “1D”, you’ll get 23:59:59 of each day.
- Second, we have a bunch of zeroes near the start of the data. We didn’t get any data during that period, but when you resample, you’ll get values back for every period that exists from the earliest date to the latest one. This is different from “groupby”, which will simply ignore months for which there is no data.

With this series in place, we can now create our line plot:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2f823a6e82-13ad-4650-8fcb-1a5869314502_534x448.png)

We can see that there are low values at the start — thanks, in part, to some early dates that didn’t include water information. But once we hit 2012 or so, we start to get lots of data. I’m guessing — and this is just a guess, without checking — that the downturns in water use reflect downturns in the price of oil, when it wasn’t worthwhile to extract so much.

The drop in water usage at the end of the group doesn’t (in my mind) reflect less fracking so much as a delay in getting the data. We saw that in August of this year, we used 109,114,250,584.584 gallons, but in September (which isn’t even over!), we only used 2,614,358,393.000 gallons. I would expect to see September numbers rise as they are reported and incorporated.

### Create a line graph showing how many (total) gallons of water have been used, per month, in each of the 10 most commonly mentioned states in this data set.

I again asked you to plot how many gallons of water are used each month — but this time, I wanted to see it broken up by state. Because there are so many states in our data set, I asked you to restrict the plot to only the 10 most commonly occurring states in the data set.

Creating this sort of plot is easy, once we have a data frame whose index consists of months — or more accurately, years and months — and whose columns are the most commonly mentioned states. The values would then be the result of invoking “sum” on all of the rows for a given state and a given month.

The easiest way to create such a data frame is via a pivot table, which I like to think of as a 2D groupby:

- The index will consist of the unique year-month combinations in the “JobStartDate” column
- The columns will consist of the unique values in the “StateName” column
- The values will be the water usage
- We’ll run the “sum” aggregation method

Given the above description, we can create the pivot table with the “[pivot\_table](https://www.bambooweekly.com/pandas-pivot-table/)” method:

```
(
    df.pivot_table(index=[df['JobStartDate'].dt.year, df['JobStartDate'].dt.month],
                   columns='StateName', 
                   aggfunc='sum',
                   values='TotalBaseWaterVolume')
 )
```

Notice how I can pass a list of columns to the “index” keyword argument. This lets me group on two or more columns, rather than on just one. Here, I’m extracting the year and month from the JobStartDate column using the “dt” accessor, which lets me retrieve parts of the datetime object.

The above returns a pivot table. But how can I keep only those columns from the 10 most commonly mentioned states?

I can go back to my use of “value\_counts” on the “StateName” column. Then I can grab the 10 most common values with [head](https://www.bambooweekly.com/pandas-head/). Then I can grab the index from the resulting series, and put that index inside of square brackets. That’ll give me a subset of the columns from our data frame:

```
(
    df.pivot_table(index=[df['JobStartDate'].dt.year, df['JobStartDate'].dt.month],
                   columns='StateName', 
                   aggfunc='sum',
                   values='TotalBaseWaterVolume')
    [df['StateName'].value_counts().head(10).index]
)
```

Finally, with that subset in place, I can plot the water use:

```
(
    df.pivot_table(index=[df['JobStartDate'].dt.year, df['JobStartDate'].dt.month],
                   columns='StateName', 
                   aggfunc='sum',
                   values='TotalBaseWaterVolume')
    [df['StateName'].value_counts().head(10).index]
    .plot.line()
)
```

The resulting plot looks like this:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fe933a3f2-74fa-443f-b48d-ca5e56c10cbc_551x448.jpg)

As we can see, the water use in Texas is far, far beyond what being used in other states. Even during the downturns, the amount of water used is much greater than other states’ maximum usages.

### Create a line graph showing the mean number gallons of water have been used, per month, in each of the 10 most commonly mentioned states in this data set. Do all locations seem to use the same amount of water?

This question is almost identical to the previous one, except that we’re asking about the mean water use, rather than the total water use. When we calculate the total, it’s across all fracking projects in a state — which, given the size of Texas, is certain to be bigger than other states. But if we check the mean water use per fracking location, we can see if there are any differences between how much water is used in different states.

The query we can use is:

```
(
    df.pivot_table(index=[df['JobStartDate'].dt.year, df['JobStartDate'].dt.month],
                   columns='StateName', 
                   aggfunc='mean',
                   values='TotalBaseWaterVolume')
    [df['StateName'].value_counts().head(10).index]
    .plot.line(figsize=(10,10)
)
```

Notice that I made the figure much larger (10x10) than the default, simply because it was then easier to read. The result:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fce97849a-7872-41f5-a438-9ec4cbdebe41_813x848.jpg)

This graph is messier, for sure. But if you look carefully, you’ll see that while Texas uses the most total water (in the earlier graph), each fracking site in Texas is actually in the middle of the road. It would seem that fracking sites in Ohio and Louisiana use the most — and that appears to be fairly consistent over time.

Is that because of local regulations? The type of soil they have in those places? The companies that are operating? How much money they invest? I’m not sure, but it is kind of interesting, I think.

### What five counties have used the most total water in the last two years?

Now let’s ask another question: Which five counties have used the most total water in the last two years?

First, we’ll need to restrict our data to just be from the last two years.

Then, when we’ve done that, we’ll want to run a “groupby” on the counties, summing up their total water use.

Then we’ll want to sort the results by the amount of water, grabbing the five top locations.

In other words, we can use the following code:

```
(
    df.set_index('JobStartDate')
    .loc['2021-09-27':]
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .sum()
    .sort_values(ascending=False)
    .head(5)
)
```

First, we set the data frame’s index to be our “JobStartDate” column, which is a datetime. With that in place, we can use “[loc](https://www.bambooweekly.com/pandas-loc/)” to get a slice on dates. Here, I chose September 27th, 2021 (i.e., two years ago) as the starting point. The result is a data frame whose values all come from the two most recent years.

I then run a “[groupby](https://www.bambooweekly.com/pandas-groupby/)” — but I don’t do it on the county name alone, because that might not be unique across states. Instead, I pass a list of columns to “groupby”.

I then sum them up, and sort the values in descending order with “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)”. Finally, I grab the top five values with “head”.

The result:

```
StateName   CountyName
Texas       Midland      957,555,334,531.000
            Martin       774,378,955,559.613
New Mexico  Lea          618,030,287,825.347
Texas       Howard       563,190,493,767.549
Colorado    Weld         541,857,100,918.033
Name: TotalBaseWaterVolume, dtype: float64
```

That’s a lot of water! And we can see that three of the five most water-using counties are in Texas.

### What are the mean and median amounts of water used for all of the wells in each county? Where do you see the greatest difference between mean and median — and what does that mean?

One of my favorite data-analysis jokes is as follows: Bill Gates walks into a bar. On average, everyone in the bar is now a millionaire.

This demonstrates the problem with using the mean as a measure; if there are a few very large or very small values, they can skew the mean, and give us a falsely high or low interpretation of the data.

For this reason, it’s common to use the median for many measurements. We can calculate the median by sorting the data, and then either taking the middle value (if there’s an odd number of them) or averaging the two middle values (if there’s an even number of them).

- If the mean is higher than the median, then there are some high values pulling it up.
- If the mean is lower than the median, then there are some low values pulling it down.

Of course, it’s always nice when the mean and median are the same. But is that true with our data here?

In order to find the mean and median amounts of water used by each county, we’ll once again run “groupby” on the combination of state and county name, looking at our water usage:

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
)
```

Normally, after running “groupby”, we run an aggregation method. But we actually want to run two different methods, both “median” and “mean”. We can do that with the “[agg](https://www.bambooweekly.com/pandas-agg/)” method, which lets us specify the names of two different methods we want to run:

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .agg(['median', 'mean'])
)
```

The result is a data frame:

- The index is a multi-index of state name and county name
- The left column, called “median”, contains the median water usage for that county, and
- The right column, called “mean”, contains the mean water usage for that county.

How can I calculate the difference between median and mean? I can use the “diff” method, which normally calculates the difference from one row to the next. But by specifying that our axis should be columns, we can get the diff from left to right:

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .agg(['median', 'mean'])
    .diff(axis='columns')
)
```

a

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .agg(['median', 'mean'])
    .diff(axis='columns')
)
```

After doing this, the “median” column will contain NaN, and the “mean” column will contain the numeric difference between the median and the mean.

Now we want to find the greatest difference between the mean and median. How can we do that? Well, we can sort the values that we got. But because the difference might be super high or super low, we want to take the absolute value into account when sorting. We can do that by using the “key” keyword argument, and passing the “[abs](https://docs.python.org/3/library/functions.html?highlight=abs&ref=bambooweekly.com#abs)” function as its value. In other words:

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .agg(['median', 'mean'])
    .diff(axis='columns')
    .sort_values('mean', key=abs, ascending=False)
)
```

I then retrieved just the “mean” column, and grabbed the 10 top differences:

```
(
    df
    .groupby(['StateName', 'CountyName'])['TotalBaseWaterVolume']
    .agg(['median', 'mean'])
    .diff(axis='columns')
    .sort_values('mean', key=abs, ascending=False)
    ['mean']
    .head(10)
)
```

The result:

```
StateName  CountyName
Colorado   Elbert        8,722,233.354
Texas      Milam         7,464,308.996
Oklahoma   Murray       -7,180,873.418
Louisiana  Webster       7,039,210.397
Texas      Wood          6,948,416.927
           Borden        6,786,341.303
           Ward          6,698,820.924
Oklahoma   Okfuskee      6,339,050.218
Louisiana  Bienville     5,789,484.121
Texas      Winkler       5,390,212.270
Name: mean, dtype: float64
```

In almost all cases, the mean was bigger than the median, often by a *lot*. In other words, there were some values might higher than the median, which pulled the mean higher. That might be because of many entries with 0 gallons recorded; I’m guessing that removing those would make the analysis a bit more sound. (But I’ve already written too much…)

### Do federally owned wells use, on average, more water or less than commercially run wells?

Finally, many of the wells are owned by the federal government. I was curious to know if there’s a big difference in the water usage between federally owned fracking sites and privately owned ones. Here, we can again do a “groupby”:

```
df.groupby('FederalWell')['TotalBaseWaterVolume'].mean()
```

And the result:

```
FederalWell
False   8,816,817.314
True    7,122,002.634
Name: TotalBaseWaterVolume, dtype: float64
```

So yes, it would seem that federally operated fracking sites use less water than privately owned ones. But both use a lot of water, that’s for sure!

That’s it for this week. Comments, questions, or anything else? Please let me know!

Meanwhile, you can get my Jupyter notebook here: [https://drive.google.com/file/d/1CvdwS2hnVsX\_yOnnbrLW2438JLxXosJl/view?usp=sharing](https://drive.google.com/file/d/1CvdwS2hnVsX%5FyOnnbrLW2438JLxXosJl/view?usp=sharing&ref=bambooweekly.com)

I’ll be back next Wednesday with more Pandas problems from current events.

Reuven