> ## 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 #73: Avocado hand (solutions)
- URL: https://www.bambooweekly.com/bw-73-avocado-hand-solution/
- Published: 2024-07-04T15:00:35.000Z
- Updated: 2026-08-23T09:36:44.000Z
- Description: Get better at: APIs, comprehensions, plotting, strings, dates and times, and joins
- Author: Reuven M. Lerner
- Tags: api, comprehensions, plotting, strings, datetime, joins

Avocados seem so innocent. (And, might I add, delicious.) But the massive growth in avocados' popularity and consumption over the last two decades has come at a price – a large number of people who have injured themselves when cutting them open.

This injury, known as "avocado hand," was recently mentioned in a Washington Post story ([https://www.washingtonpost.com/wellness/2024/06/26/avocado-hand-injuries-knife/](https://www.washingtonpost.com/wellness/2024/06/26/avocado-hand-injuries-knife/?ref=bambooweekly.com)). Researchers, they reported, found that between 1998 and 2017, there were more than 50,000 avocado-related injuries. Most of these affected people's fingers and hands.

Where did the researchers (whose work can be read at [https://pubmed.ncbi.nlm.nih.gov/31303536/](https://pubmed.ncbi.nlm.nih.gov/31303536/?ref=bambooweekly.com)) get their injury data? From the US Consumer Product Safety Commission (CPSC, at [https://cpsc.gov](https://cpsc.gov/?ref=bambooweekly.com)), and specifically the National Electronic Injury Surveillance System (NEISS, [https://www.cpsc.gov/Research--Statistics/NEISS-Injury-Data](https://www.cpsc.gov/Research--Statistics/NEISS-Injury-Data?ref=bambooweekly.com)). They provide an annual report on injuries in the United States, giving us a chance to learn more about avocado-related injuries.

### Data and seven questions

This week's data came from the database at NEISS:

[https://www.cpsc.gov/cgibin/NEISSQuery/home.aspx](https://www.cpsc.gov/cgibin/NEISSQuery/home.aspx?ref=bambooweekly.com)

NEISS offers data in a variety of formats. We downloaded the annual archived reports from 2020-2023\. I started by working with the Excel files, but found the tab-delimited files to be far easier to work with. And so, aside from one part of one question, I'll assume that you'll use those files, rather than using them in Excel format.

I gave you seven tasks and questions this week. As always, a link to my Jupyter notebook is at the end of the post.

### Load the data from 2020 - 2023 into a single data frame. Make sure that the `Treatment_Date` column is a `datetime`.  
Remove any rows in which the date is invalid or NA.

Let's start by loading Pandas:

```python
import pandas as pd
```

I asked you to create a data frame from four years of data. If you go to the NEISS site and download one of the tab-delimited files, you'll find that each file is at a URL that looks like this:

```
https://www.cpsc.gov/cgibin/NEISSQuery/Data/Archived%20Data/{one_year}/neiss{one_year}.tsv
```

Notice that I've got `one_year` in there as a variable. And that's not an accident; I can use a `for` loop to iterate over a [range](https://docs.python.org/3/library/stdtypes.html??ref=bambooweekly.com#range) of years, downloading each file, one at a time. Better yet, I can use a list comprehension to run [read\_csv](https://www.bambooweekly.com/pandas-read-csv/) on the URL to each file, outputting a list of data frames — one for each of the years we want:

```python
all_dfs = [pd.read_csv(f'https://www.cpsc.gov/cgibin/NEISSQuery/Data/Archived%20Data/{one_year}/neiss{one_year}.tsv', 
                       sep='\t')
           for one_year in range(2020, 2024)]

```

In other words: We'll iterate over each year from 2020 through 2023\. (Remember that [range](https://docs.python.org/3/library/stdtypes.html??ref=bambooweekly.com#range), like most parts of Python, goes up to and *not including* its second argument.) For each of those years, we retrieve the file at the specified URL, passing it to [read\_csv](https://www.bambooweekly.com/pandas-read-csv/). We specify that the columns are separated with tabs by passing `sep='\t'`.

The result is a list of data frames, which we assign to `all_dfs`.

However, if you use the above code, you'll likely get an error. That's because we haven't specified what `dtype` should be used for each column. Pandas thus tries to read a bunch of rows from the CSV file, and makes the best guess it can. However, it's possible that the guess it makes with some chunks won't match the guess it makes with others. That happens with several of these files, resulting in unpleasant warnings.

We can avoid these warnings by specifying the dtypes with the `dtypes` keyword argument. Or, if your computer has enough memory, you can pass the `low_memory=False` keyword argument. In such a case, Pandas will load the entirety of the file into memory, and make its best guess based on more data.

In addition, we want the `Treatment_Date` column to be treated as a `datetime` series. It would be nice to pass `parse_dates=['Treatment_Date']` as a keyword argument to [read\_csv](https://www.bambooweekly.com/pandas-read-csv/), but there are some illegal dates in the input file. So we'll remove that keyword argument, and then do it manually:

```python
all_dfs = [pd.read_csv(f'https://www.cpsc.gov/cgibin/NEISSQuery/Data/Archived%20Data/{one_year}/neiss{one_year}.tsv', 
                       sep='\t',
                       low_memory=False)
           for one_year in range(2020, 2024)]

```

With `all_dfs` defined, we can invoke `pd.concat` on that list, returning a single data frame:

```python
df = pd.concat(all_dfs)
```

Now we can turn the `Treatment_Date` column into a `datetime` dtype:

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

The `errors='coerce'` keyword argument means that if the input string cannot be parsed as a `datetime`, it is left as `NaT`, the time equivalent of `NaN`.

The resulting data frame has 1,311,422 rows and 26 columns.

### In which month do we see the most accidents? The fewest?

In which month of the year does NEISS see the most accidents? To find out, we can use [groupby](https://www.bambooweekly.com/pandas-groupby/), counting how often a given month shows up in our `Treatment_Date` column.

To extract the month from `Treatment_Date`, we'll use the `dt` accessor. This is our way to get that information from a `datetime` column, and we can use it inside of a `groupby`. We can thus `groupby` on each month, and invoke [count](https://www.bambooweekly.com/pandas-count/) on any column we want; I chose `CPSC_Case_Number`, but it's really not important, so long as the column on which we're counting has few or no `NaN` values. I can thus say:

```python
(
    df
    .groupby(df['Treatment_Date'].dt.month)['CPSC_Case_Number'].count()
)
```

To get the months with the largest and smallest number of accidents, we can use [sort\_values](https://www.bambooweekly.com/pandas-sort-values/) on the series we got back from `groupby`, and then use [iloc](https://www.bambooweekly.com/pandas-iloc/) to retrieve the first and last values:

```python
(
    df
    .groupby(df['Treatment_Date'].dt.month)['CPSC_Case_Number'].count()
    .sort_values(ascending=False)
    .iloc[[0, -1]]
)
```

We get the following result:

```
Treatment_Date
5.0     119410
12.0     92301
Name: CPSC_Case_Number, dtype: int64
```

Alternatively, we can use [idxmin](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.idxmin.html?ref=bambooweekly.com) and [idxmax](https://www.bambooweekly.com/pandas-idxmax/) to get the indexes of the lowest and highest values:

```python
(
    df
    .groupby(df['Treatment_Date'].dt.month)['CPSC_Case_Number'].count()
    .agg(['idxmin', 'idxmax'])
)
```

We get the months back, but not the values associated with them:

```
idxmin    12.0
idxmax     5.0
Name: CPSC_Case_Number, dtype: float64
```

Regardless, we see that the greatest number of reported accidents happen in May, whereas the smallest number of accidents happens in December.

### Create a histogram showing the frequency with which a person's age is associated with an accident. Does this seem reasonable to you? Clean the data, and recreate the histogram.

Next, I asked you to create a histogram of the ages in the data set. Are we likely to see young children involved in accidents more than adults? Or senior citizens more involved?

We can create a histogram by invoking [plot.hist](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.hist.html?ref=bambooweekly.com) on a numeric series:

```python
(
    df
    ['Age']
    .plot.hist()
)
```

When we plot, the x axis will represent the people's ages, and the height of the bar will show how often that age showed up in an accident report. Here's the plot that I got:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/2024/07/data-src-image-2da6ee9d-2f00-4735-adfe-b6df09f835cd.png)

Maybe it's just me, but I'm a bit skeptical that there have been 100,000 accidents reported in the last four years by people over the age of 200.

Let's clean this data by keeping only those ages less than 120\. We can do that with a combination of [loc](https://www.bambooweekly.com/pandas-loc/) and `lambda`:

```python
(
    df
    ['Age']
    .loc[lambda s_ : s_ < 120]
    .plot.hist()
)
```

Now that we've excluded the super-elderly, our histogram looks like this:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/2024/07/data-src-image-c555c780-017b-41c7-b28a-28290d85d00e.png)

That looks a lot more reasonable to me. And it shows that a very large proportion of accidents happen with people under the age of 20 – meaning, as we could have guessed, a large number of children and young adults.

### Are men and women involved with avocado-related accidents at similar rates? How about minors (< 18 years old) vs. adults?

Now we'll start to look at avocado-related injuries. The Washington Post story mentioned that women were involved in such incidents at a much higher rate than men. Do we see that in the data?

In order to find out, we'll need two columns from the data frame. One, `Sex`, is fairly self-explanatory; the column actually has a numeric coding in which 1 represents males and 2 represents females. (There are other designations as well, but they don't apply to avocado-related injuries, at least not in our data set.)

But we'll somehow need to also limit our search to injuries having to do with avocados. We can do that by searching through the `Narrative_1` column, which contains a text description of what happened. If the case description includes the word `avocado`, we can assume that it's avocado related; there will be false positives and negatives, but this isn't a bad way to start.

On the face of it, it would seem that we need to (a) find the rows where `Narrative_1` contains the word `avocado`, (b) grab the `Sex` for the corresponding rows, and then (c) count the number of times that 1 and 2 appear in that column.

We can find the rows where `avocado` is in the narrative by using [str.contains](https://www.bambooweekly.com/pandas-str-contains/), a Pandas string method. I could say:

```python
(
    df
    [['Narrative_1', 'Sex']]
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado')]
)
```

However, this quickly gives us an error. That's because any row in which the `Narrative_1` contains `NaN` will give us an error. We can either remove such rows with [dropna](https://www.bambooweekly.com/pandas-dropna/) or replace the `NaN` value with an empty string using [fillna](https://www.bambooweekly.com/pandas-fillna/). I chose to use `dropna`:

```python
(
    df
    [['Narrative_1', 'Sex']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado')]
)
```

However, if we run the above code, we'll find that we get zero rows back. How can that be? Because the narrative is written almost completely in CAPITAL LETTERS. We could use [str.lower](https://www.bambooweekly.com/pandas-str-lower/) to lowercase all of the text before searching, but we can just pass `str.contains` the keyword argument `case=False`, which makes it case insensitive:

```python
(
    df
    [['Narrative_1', 'Sex']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
)
```

Now that we have a data frame with 483 rows and two columns. All that's left to do is count how often each value is in the `Sex` column, which we can calculate with [value\_counts](https://www.bambooweekly.com/pandas-value-counts/). I passed the keyword argument of `normalize=True`, which returns percentages, rather than raw numbers:

```python
(
    df
    [['Narrative_1', 'Sex']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
    ['Sex'].value_counts(normalize=True)
)
```

The result:

```
Sex
2.0    0.757764
1.0    0.242236
Name: proportion, dtype: float64
```

In other words, women are three times as likely (!) to be involved with an avocado-related injury.

I also asked you to find how often minors are involved in such injuries, rather than adults. For that, I again grabbed two columns – `Narrative_1` and `Age`. Then I ran `dropna`, as before, and again used `loc` to find only those rows containing the term `avocado`:

```python
(
    df
    [['Narrative_1', 'Age']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]

)
```

How, though, can I distinguish between adults and minors? I used [assign](https://www.bambooweekly.com/pandas-assign/) to create a new column, `is_adult`, wherever `Age` is >= 18\. Then, with that in place, I can invoke `value_counts` to find the number of adults vs. minors:

```python
(
    df
    [['Narrative_1', 'Age']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
    .assign(is_adult = lambda df_: df_['Age'] >= 18)
    ['is_adult']
    .value_counts(normalize=True)
)
```

Here's the result:

```
is_adult
True     0.892628
False    0.107372
Name: proportion, dtype: float64
```

In other words, about 90 percent of the people involved with avocado injuries are adults.

### Are there months in which we see a consistent spike in avocado-related injuries? Create a line plot showing how many avocado-related injuries there are in a given month.

Next, I was wondering if avocado-related injuries are seasonal. Are there times of the year when they go up (or down) quite a bit? To find out, we'll need `Treatment_Date` (to grab date info) and `Narrative_1` (to look for avocado-related injuries).

We'll start as we did before, keeping only those rows with avocados:

```python

    df
    [['Treatment_Date', 'Narrative_1']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
)
```

Next, we'll turn the `Treatment_Date` column into the data frame's index. Why? Because then we can run [resample](https://www.bambooweekly.com/pandas-resample/), calculating how many times (`count`) we have non-`NaN` rows in `Narrative_1` for each month in our index (`1ME`). This returns a series whose x axis contains the final day of each month, and whose values are integers, indicating how many injuries we saw:

```python

    df
    [['Treatment_Date', 'Narrative_1']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
    .set_index('Treatment_Date')
    .resample('1ME')['Narrative_1'].count()
    )
```

Finally, we invoke [plot.line](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html?ref=bambooweekly.com) on that result:

```python

    df
    [['Treatment_Date', 'Narrative_1']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
    .set_index('Treatment_Date')
    .resample('1ME')['Narrative_1'].count()
    .plot.line()
)
```

Here's what I see:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/2024/07/data-src-image-0ceafe2e-5d17-4302-b437-61fbf1126240.png)

We can see a huge spike in avocado-related injuries in the spring of 2021 — but I'm going to guess that this has more to do with an end to pandemic lockdowns in many places, rather than anything specifically avocado related.

We do see a large dip (and I'm not talking about avocado dip) in October of each year; Might this be because there are fewer avocados available at that time? Maybe; at the [Avocado Buddy](https://avocadobuddy.com/guides/when-is-avocado-season/?ref=bambooweekly.com) site, they say that Mexican avocados are in season from November through February. So perhaps that reflects a drop in the previous year's holdings, just before the new season? I'm really not sure.

No matter how you slice it — just not into your palm, please — we see that there are between 5 and 25 people with avocado-related injuries every month in the US, and that number has stayed pretty stable for the last few years. 

### From the second sheet of the Excel document (ideally from 2023), create a small data frame whose index is integers, and whose values are name of body parts involved in accidents. Using that, find which body parts were most often injured with avocados?

When I initially wrote about downloading the data files, I indicated that the files in Excel format were problematic. I found that they took a very long time to load into Pandas, and that after waiting a very long time, they often gave me errors – something having to do with the file format. I switched to the tab-separated values, and things ran faster and better.

However, the Excel files contained a second sheet, one which provided a data dictionary for the various numeric values in the main data set. For example, it's on that second sheet that I found the meanings of the numbers in the `Sex` column.

Now, the way that this second sheet is constructed is a bit frustrating. But hey, that's what Bamboo Weekly is all about, dealing with messy, real-world data. I thus asked you to create a data frame containing the translations from body-part numbers (used in `df`) and the body-part names that we can recognize.

I downloaded the 2023 data in Excel format, and used [read\_excel](https://www.bambooweekly.com/pandas-read-excel/) to read in the second sheet:

```python
body_parts_df = (
    pd.read_excel('/Users/reuven/Downloads/neiss2022.xlsx', 
                   sheet_name=1,
                   index_col=0) 
)
```

Notice that I asked explicitly for the second sheet (i.e., `sheet_name=1`), and that I asked for the first column to be treated as the index. This then allowed me to retrieve only those rows having to do with body parts, i.e., with an index of `BDYPT`, using [loc](https://www.bambooweekly.com/pandas-loc/):

```python
body_parts_df = (
    pd.read_excel('/Users/reuven/Downloads/neiss2022.xlsx', 
                   sheet_name=1,
                   index_col=0)     
    .loc['BDYPT']
)
```

Next, I'm only interested in two columns, `Starting value for format` and `Format value label`, both of which have long and clunky names. And besides, the first needs to be converted into an integer column, and the second should be fixed up.

I thus used `assign` to create a new column, `body_index`, with the original values of the index column, but with a `dtype` of `int`, thanks to a call to [astype](https://www.bambooweekly.com/pandas-astype/). And I set `body_part` to be the same as the original `Format value label`, except that I used [str.split](https://www.bambooweekly.com/pandas-str-split/) and then [str.get](https://www.bambooweekly.com/pandas-str-get/) to break the string apart and then grab the final value of the resulting list. Note that while we talk about `str.get` being used on strings, we can actually use it on any data structure that supports `[]`.

Finally, we invoke `set_index`, using the (new) `body_index` column as the index of our one-column data frame:

```python
body_parts_df = (
    pd.read_excel('/Users/reuven/Downloads/neiss2022.xlsx', 
                   sheet_name=1,
                   index_col=0)     
    .loc['BDYPT']
    [['Starting value for format', 'Format value label']]
    .assign(body_index=lambda df_: 
              df_['Starting value for format'].astype(int),
           body_part=lambda df_: 
              df_['Format value label'].str.split(None).str.get(-1))
    .drop(['Starting value for format',
           'Format value label'], axis='columns')
    .set_index('body_index')
)
```

Now that I have defined `body_parts_df`, I can use it in a query to report which body parts were most often injured with avocados.

In order to use [join](https://www.bambooweekly.com/pandas-join/) on two data frames, the indexes need to match. We thus start by invoking [set\_index](https://www.bambooweekly.com/pandas-set-index/) on `df` to `Body_Part`, the (numeric) column that identifies which body part was injured. Wwe join that together with `body_parts_df`, resulting in a wide data frame that combines all of the columns in `df` with the one column in `body_parts_df`:

```python
(
    df
    .set_index('Body_Part')
    .join(body_parts_df)
)
```

Now what? First, we restrict ourselves to only two columns, `Narrative_1` and `body_part`. The others aren't of interest to us for this query. We also get rid of the `NaN` values with `dropna`:

```python3
(
    df
    .set_index('Body_Part')
    .join(body_parts_df)
    [['Narrative_1', 'body_part']]
    .dropna()
)
```

Next, we use `loc` and `lambda` to keep those lines that mention avocados. And then we retrieve only the `body_part` column, namely the strings from `body_parts_df`. And then, finally, we use `value_counts` to learn how often each body part was injured:

```python
(
    df
    .set_index('Body_Part')
    .join(body_parts_df)
    [['Narrative_1', 'body_part']]
    .dropna()
    .loc[lambda df_: df_['Narrative_1'].str.contains('avocado', case=False)]
    ['body_part']
    .value_counts()
)
```

The result:

```
body_part
FINGER    332
HAND      283
FACE        3
HEAD        3
TRUNK       2
LEG         1
Name: count, dtype: int64
```

And thus, we see why it's called "Avocado hand." I do have to wonder, though, who managed to hurt their face or head when opening an avocado.

### What are the 10 most commonly used words, containing at least 4 letters and without any digits, used in the narratives for avocado-related injuries?

Let's start by focusing on the `Narrative_1` column, and only keeping those having to do with avocados:

```python
(
    df
    ['Narrative_1']
    .dropna()
    .loc[lambda s_: s_.str.contains('avocado', case=False)]
)
```

We now have a series of strings, each of which describes an occurrence of an avocado-related injury. How can count how often each word appears, though?

The answer lies in a combination of two methods. We start with `str.split`, which is similar to the core Python `str.split` method, in that it returns a list of strings. However, `str.split` in Pandas works on a series, and returns a series of lists. That's nice, but what can we do with a series of lists?

We can run the [explode](https://www.bambooweekly.com/pandas-explode/) method, which takes a series of lists and converts it into a very long series of the individual elements of those lists. That is, if a series contains `[10, 20, 30]` and `[40, 50]`, then running `explode` on that series would result in a 5-element series of 10, 20, 30, 40, and 50.

We thus end up with a series of individual words:

```python
(
    df
    ['Narrative_1']
    .dropna()
    .loc[lambda s_: s_.str.contains('avocado', case=False)]
    .str.split()
    .explode()
)
```

I wanted words containing at least four letters and without any digits. We know (thanks to our use of `str.split` ) that there isn't any whitespace, so I'll just assume that I can use a regular expression that says:

- Starting from the front of the string, `^`
- Look for 4 or more non-digit characters, `\D{4,}`
- Then end the string

The result is a new series of strings, a subset of those that we had gotten back from `explode`:

```python
(
    df
    ['Narrative_1']
    .dropna()
    .loc[lambda s_: s_.str.contains('avocado', case=False)]
    .str.split()
    .explode()
    .loc[lambda s_: s_.str.contains(r'^\D{4,}$', regex=True)]
)
```

Finally, we can run `value_counts` to find out how often each appears, and [head(10)](https://www.bambooweekly.com/pandas-head/) to keep only the top ones:

```python
(
    df
    ['Narrative_1']
    .dropna()
    .loc[lambda s_: s_.str.contains('avocado', case=False)]
    .str.split()
    .explode()
    .loc[lambda s_: s_.str.contains(r'^\D{4,}$', regex=True)]
    .value_counts()
    .head(10)
)
```

The top 10:

```
Narrative_1
FINGER        501
AVOCADO       490
LACERATION    472
KNIFE         453
CUTTING       436
HAND          421
WITH          367
LEFT          345
WHEN          157
WHILE         150
Name: count, dtype: int64
```

This makes a lot of sense, right? People hurt their fingers and hands, most people are right-handed, so they cut the knife into their left palms, and they end up with lacerations.

That's it for this week! My Jupyter notebook is here: [https://drive.google.com/file/d/1Vfpuy-746jyuUGjYKPF4O8293vpJjJiY/view?usp=drive\_link](https://drive.google.com/file/d/1Vfpuy-746jyuUGjYKPF4O8293vpJjJiY/view?usp=drive%5Flink&ref=bambooweekly.com)

Reuven