> ## 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 #13: Python developers (solutions)
- URL: https://www.bambooweekly.com/bw-13-python-developers-solution/
- Published: 2023-04-27T13:00:54.000Z
- Updated: 2026-08-23T09:37:16.000Z
- Description: Get better at: CSV files, filtering, memory optimization, speed optimization, dummy values, comprehensions
- Author: Reuven M. Lerner
- Tags: csv, filtering, memory-optimization, speed-optimization, dummy-values, comprehensions

This week, as I slowly return home from PyCon US, I decided to look at data about Python and who uses it. The data came from the annual survey handled by JetBrains, the company behind PyCharm and other editors.

The data is in a single CSV file, which you can download from here:

```
https://drive.google.com/drive/folders/1nlvy45tE4gFX_oWNxG_UTC1-tLZBTcbR?usp=sharing
```

From that page, download the \`sharing\_data.csv\` file onto your computer. I wasn’t able to find an easy way to give you a one-click URL to download it.

Once you’ve downloaded the file, I had a bunch of questions for you to answer:

1. Load the file into a data frame. We'll only look at a handful of the file's (many!) columns:

  1. All columns starting with \`job\_role\`
  2. All columns starting with \`edu\_level\`
  3. All columns starting with \`primary\_proglang\`
2. How many people took the survey?
3. How many people who took the survey have each kind of educational level? What percentage have a master's, doctoral degree, or professional degree?
4. Turn the single \`edu\_level\` column into many different columns, each indicating with a \`True\`/\`False\` value whether this person has that educational level. For example, there should be one column indicating whether they got a bachelor's degree, a second for master's degrees, a third for doctoral degrees, and so forth. Add these new columns to the data frame.
5. Try to turn these columns back into a single one. Why does this fail?
6. What are the 10 most common primary programming languages used by people who took the survey? Are the results surprising?
7. How many people have more than one job role? How many have more than 5?

By the way, you might have noticed that I mislabeled the headline on yesterday’s e-mail as “BW #11,” when it was actually the 13th issue. No, this wasn’t an example of an off-by-two error; it just shows that I’m still traveling, somewhat jet lagged, and not as focused as I’d like to be. I’ve fixed the headline in the archives.

And now, let’s get started!

### Load the file into a data frame. We'll only look at a handful of the file's (many!) columns:

- All columns starting with \`job\_role\`
- All columns starting with \`edu\_level\`
- All columns starting with \`primary\_proglang\`

Let’s start off by loading the necessary modules:

```
import pandas as pd
from pandas import Series, DataFrame
```

With that in place, I can then load the data frame into memory. It’s tempting to use [read\_csv](https://www.bambooweekly.com/pandas-read-csv/) as follows:

```
filename = 'DevEcosystem_2022_sharing_data.csv'
df = pd.read_csv(filename)
```

This will work! But there are a few problems:

1. If you read the entire thing into memory, then you’ll end up with an absolutely enormous data frame, with more than 3,800 columns. It’ll take a long time to read that into memory, and then to analyze what dtype should be assigned to each column, and then just to store the data. You really want to cut down on the columns that you have.
2. If you don’t specify the dtype for each column (which you can do, using the “dtype” keyword argument), then Pandas needs to analyze the values in each column in order to decide on the dtype. With so many rows, and so many columns, this means holding a lot of data in memory in order to make that determination. In such a case, Pandas will give you a warning, telling you that it is really not sure what to do, but that you should either specify dtypes or pass “low\_memory=False”, which tells read\_csv that it can use however much memory it needs in order to perform that analysis.

We’ll pass low\_memory=False. But beyond that, we’re going to select a handful of columns. How can we do that?

The “usecols” keyword argument lets us specify which columns should be read. Normally, I like to specify them by passing a list of strings, the columns that I’d like to keep around. I find that to be the easiest and most readable method. But here, I want to read a lot of different columns, starting with a variety of different strings.

I could do this by reading one row from the CSV file into memory, and grabbing the column names:

```
column_names = pd.read_csv(filename, nrows=1).columns
```

Then I could iterate over those column names, returning only those that matched the pattern I wanted, perhaps with a list comprehension:

```
column_names = [one_column
               for one_column in column_names
               if (one_column.startswith('job_role') or 
                   one_column.startswith('edu_level') or 
                   one_column.startswith('primary_proglang'))
               ]
```

Notice that I’m using the “[str.startswith](https://docs.python.org/3/library/stdtypes.html?highlight=str%20startswith&ref=bambooweekly.com#str.startswith)” method, which returns True if the string in question starts with the string I pass as an argument.

Then I could say:

```
df = pd.read_csv(filename, 
            usecols=column_names,
            low_memory=False)
```

But did you know that str.startswith can instead take a tuple as an argument? If you pass a tuple of strings, then the method returns True if *any* of the strings appears at the start. Meaning, I can rewrite my above list comprehension as:

```
column_names = [one_column
               for one_column in column_names
               if (one_column.startswith(('job_role', 'edu_level',
                  'primary_proglang'))
               ]
```

This is pretty great, and I believe it’s an improvement over our previous code. However, it still feels a bit clunky. That’s because I’m reading from the file to get the names of the columns, so that I can filter through the columns with some code.

We could instead pass a list of integers as the “usecols” argument. However, that’s not really going to help us here.

But there is another option, one which seems particularly appropriate: We can pass a *callable* to usecols, meaning a Python function or class. That callable will be invoked for each of the column names. Wherever the callable returns True, the column will be kept. And wherever the callable returns False, the column will be ignored.

I can thus wrap my list comprehension into a function, and pass the function to “usecols”, as follows:

```
def columns_wanted_filter(column_name):
    return column_name.startswith(('job_role',
                                   'edu_level',
                                   'primary_proglang'))

df = pd.read_csv(filename,
                usecols=columns_wanted_filter,
                low_memory=False)
```

Doing this assigns a data frame to “df” that contains only the columns that are of interest to us. If and when we want to add other columns, or modify the way in which we select columns, I can just change my function.

And yes, I could use a “[lambda](https://docs.python.org/3/glossary.html?ref=bambooweekly.com#term-lambda)” here, and in some cases that might be easiest and shortest. But I’ve shied away from lambda over the last few years, in part because (in my experience) they’re harder for beginner Python developers to understand. And yes, lambda tends to be common in Pandas, and you can’t ignore it completely when using Pandas — but if I can reduce its use, I will.

We end up with a data frame with 58,538 rows and 62 columns. That’s still a lot of columns (and not a small number of rows), but it’s certainly better than what we had before.

### How many people took the survey?

This seems, at first glance, like an easy question to answer: We just need to check how many rows are in our data frame.

But wait: What’s the fastest, best, and easiest way to do that?

It might be tempting to use the “[count](https://www.bambooweekly.com/pandas-count/)” method on our data frame — or, perhaps, on one of the columns in our data frame. However, “count” only counts the number of non-NA (and non-NaN) values that are there. Which means that if you choose a column that contains a lot of NA values, you’ll get a completely wrong answer.

Moreover, calling “count” means that you’re invoking a method, which is generally going to be kind of slow.

That said, let’s see what kind of answer we can get, and how long it takes to run using the “timeit” magic method in Jupyter:

```
%timeit df.count()
```

The result that I get is:

```
64.6 ms ± 6.14 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
```

In other words, “[timeit](https://docs.python.org/3/library/timeit.html?highlight=timeit&ref=bambooweekly.com#module-timeit)” ran this code 10 times, and found that on average, it took 64.6 ms to run. That' might sound pretty fast, but maybe there’s a faster way.

What if I run it on a single column? Let’s try the first one:

```
%timeit df[df.columns[0]].count()
```

How long did that take?

```
1.28 ms ± 86.2 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)
```

That’s a lot faster… but maybe we can get it faster still?

One alternative would be to get the [shape](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.shape.html?highlight=shape&ref=bambooweekly.com#pandas.DataFrame.shape) of our data frame. The “shape” attribute returns a two-element tuple, in which the first (at index 0) contains the number of rows, and the second (at index 1) contains the number of columns. I could thus say:

```
df.shape[0]
```

This will give me the right answer, but will it be any faster than running “count”? Let’s find out:

```
%timeit df.shape[0]
```

Running that results in:

```
433 ns ± 22.8 ns per loop (mean ± std. dev. of 7 runs, 1,000,000 loops each)
```

Wow. Just to remind you, there are 1,000,000 (yes, 1 million) ns in 1 ms. So… yeah, using “shape” is a lot faster than calculating “count” on a single column.

But maybe we can get it to run faster yet?

For example, what if I just run “[len](https://docs.python.org/3/library/functions.html?highlight=len&ref=bambooweekly.com#len)” — yes, the standard Python “len” function — on our data frame? How long will that take?

```
%timeit len(df)
```

The output:

```
267 ns ± 8.07 ns per loop (mean ± std. dev. of 7 runs, 1,000,000 loops each)
```

In other words, using “len” on the data frame takes about half as long as retrieving the result from “shape”.

I was long under the impression that this was the best we could do. But then someone told me about an even faster technique, and I decided to try it: We could run “len” on df.index. That is, we retrieve the index from our data frame, and then calculate the length of that index object:

%timeit len(df.index)

And how long does that take?

```
173 ns ± 2.62 ns per loop (mean ± std. dev. of 7 runs, 10,000,000 loops each)
```

In other words: There are a number of ways to find out how many rows are in our data frame. They’ll all give us the same results. But if you want to count the rows, you’re best off invoking “len” on the data frame’s index.

### How many people who took the survey have each kind of educational level?

If you have been working with Pandas for any length of time — and especially if you’ve been reading anything I write about Pandas — then you likely know about the amazing “[value\_counts](https://www.bambooweekly.com/pandas-value-counts/)” method. I use this method all the time; it’s so handy. It takes the values in a series, and returns those values as the index of a series. The values of the new series are integers, indicating how often each index element appeared. Moreover, the results are sorted, from most common to least common.

I can thus find out how many people taking the survey have each educational level with:

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

We can see, from these results, that the greatest number of people taking the survey have a bachelor’s degree. Only a handful of people finished school after primary/elementary school, or never had any formal education.

What if, instead of asking how many people achieved each level, I wanted to know the percentage? I can just pass normalize=True to the method call:

```
df['edu_level'].value_counts(normalize=True)
```

That returns the percentages, which can often be very helpful and useful.

### What percentage have a master's, doctoral degree, or professional degree?

Obviously, when I total the normalized numbers, I’ll get 100%. But what if I want to only total some of these values?

Remember that “value\_counts” returns a series. The index of that series is made up of the different strings that were the values of the original series. In this case, that means we end up with strings like “Bachelor’s degree (BA, BS, B\_Eng\_, etc\_)” and “Secondary school (e\_g\_ American high school, German Realschule or Gymnasium, etc\_) “.

If we want to retrieve and sum the percentages for three of these rows — those for master’s, doctoral, and professional degrees — then we could, in theory, use “[.loc](https://www.bambooweekly.com/pandas-loc/)” retrieve them, and then sum the values. That would look like this:

```
df['edu_level'].value_counts(normalize=True).loc[["Master’s degree (MA, MS, M_Eng_, MBA, etc_)", "Professional degree (JD, MD, etc_)", "Doctoral degree (Ph_D, Ed_D_, etc_)"]].sum()
```

But… yuck. I mean, there’s nothing technically wrong with what I’ve done here. But we want our code to be readable, both so that we can debug it and so that others will be able to make sense of it down the road. And while I’m usually in favor of text for its self-documenting qualities, this seems like one of those cases when the text just gets in the way.

Maybe I should just change the text so that it’s shorter and punchier. But I instead decided that I’ll use “[.iloc](https://www.bambooweekly.com/pandas-iloc/)”, which lets me select from a series (or data frame) using the numeric row positions. Here, I grabbed the rows at indexes 2, 4, and 5 from the output of “value\_counts”, and then summed them:

```
df['edu_level'].value_counts(normalize=True).iloc[[2, 4, 5]].sum()
```

The answer I got is 0.23436757013789825, precisely the same as what I got using “.loc”, but a bit easier to read.

### Turn the single \`edu\_level\` column into many different columns, each indicating with a \`True\`/\`False\` value whether this person has that educational level.

There are many cases — particularly in machine learning, but also in basic statistical analysis — in which a single categorical column becomes multiple boolean columns. For example, consider this code:

```
s = Series(['a', 'b', 'c', 'b', 'c', 'b'])
```

There isn’t anything *wrong* with this series, per se. But we might want to look at the correlations between rows with “a” and those with another column.

The solution to this problem is “one-hot encoding,” which takes such a categorical column and returns a data frame whose columns are named according to the unique values in the series. In this case, for example, the columns will be named “a”, “b”, and “c”.

The value in each column will reflect whether that column’s value was set for a particular row.

We could do this ourselves, but why work hard? We can instead use the Pandas “[get\_dummies](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.get%5Fdummies.html?highlight=get%5Fdummies&ref=bambooweekly.com#pandas.get%5Fdummies)” function, which returns precisely the sort of data frame we want and need:

```
pd.get_dummies(s)
```

The result 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-2fb7721749-8c61-4f73-b8d5-8ebf3ba9714b_396x496.png)

If we’re interested in turning the “edu\_level” column into such a set of boolean columns, we can say:

```
pd.get_dummies(df['edu_level'])
```

Note that the names of the new data frame’s columns will be precisely what they were in the original categorical column. In some cases, these names might clash with other columns, or might just not lend themselves to easy finding. For that reason, it’s often a good idea to add a prefix to each of the new columns’ names:

```
pd.get_dummies(df['edu_level'], prefix='edu.')
```

But wait: If we run “get\_dummies” on a data frame, rather than on a series, then the name of the original column is used as a prefix. Which means that if we retrieve the “edu\_level” column with double square brackets, thus giving us a data frame, we don’t need to pass “prefix”:

```
pd.get_dummies(df[['edu_level']])
```

### Try to turn these columns back into a single one. Why does this fail?

Just as we can use get\_dummies to turn a single categorical column into a data frame using one-hot encoding, we can use the “[from\_dummies](https://pandas.pydata.org/docs/reference/api/pandas.from%5Fdummies.html?ref=bambooweekly.com)” function to turn them back into a single categorical column.

What happens if we try to run “from\_dummies” on the columns that we created? We can run:

```
pd.from_dummies(pd.get_dummies(df[['edu_level']]))
```

But we get an error message:

```
ValueError: Dummy DataFrame contains unassigned value(s); First instance in row: 0
```

The whole point of “from\_dummies” is to create a single categorical column based on a number of boolean columns, each indicating whether its categorical value is set. What happens, though, if all of the values in a row are False? The “from\_dummies” function doesn’t want to guess, so it raises an exception, failing.

In order to handle this, we’ll need to tell Pandas what category to assign if none are assigned. We can do this with the “default\_category” keyword argument:

```
pd.from_dummies(pd.get_dummies(df[['edu_level']]), default_category='No edu noted')
```

Once we do that, we’re able to collect the numerous one-hot columns into a single categorical column.

There is another type of error that you might encounter when using “from\_dummies” — the opposite problem of what we’ve seen here, where none of the columns had a True value. What, though, if *more* than one of them has a True value? Which category should be named in our single column?

The answer, of course, is that we don’t know. And we cannot know. And so, if more than one row has True values when we pass it to “from\_dummies”, you’ll get an exception.

### What are the 10 most common primary programming languages used by people who took the survey? Are the results surprising?

Our data frame contains 35 columns, all of which are one-hot encoded to indicate which programming language is the person’s primary one. Given what we’ve just seen in the above question, perhaps we could use from\_dummies to get a single column summarizing the primary programming languages. We could then run value\_counts and be done.

But it turns out that survey respondents were allowed to indicate more than one primary programming language. Which means that if we try to use “from\_dummies” on those columns, we’ll get an error message. Remember that “from\_dummies” only works when only one categorical column per row contains a True value.

If we look at these columns, though, we can see that they don’t exactly contain True and False values. Rather, they contain the name of the programming language (i..e, basically the same as the column name) where the person indicated that yes, they use that language. And they contain NaN values where no such indication was made.

Here’s what I decided to do:

- I’ll run the “[isna](https://www.bambooweekly.com/pandas-isna/)” method on the data frame. That’ll return True where the value is NaN, and False where it isn’t.
- If I were to run “.sum” on the data frame, that would tell me how many NaN values there are in each column. That’s precisely the opposite of what I want! I thus need to use \~ to negate the value we get from isna().
- We’ll then call “sum” on the resulting data frame, giving us the number of non-NaN values in each of the columns.
- The series we get back will have an index consisting of these column names, and values indicating how often we have non-NaN values.
- I can then sort the values in descending order, so that we can pick off the most common languages.
- Then I use “head(10)” to grab the top values.

Here’s the code:

```
(~df[[one_column
 for one_column in df.columns
 if one_column.startswith('primary_proglang')]].isna()).sum().sort_values(ascending=False).head(10)
```

The weird thing about this query is that Python isn’t the top result! I’m not sure whether this is because people didn’t mark of Python on the survey (assuming it was obvious), or because so many people are using Python as a second language, but not as their primary one. Or this survey was administered to more than one language/IDE user group!

### How many people have more than one job role? How many have more than 5?

Finally, I asked you to find people with more than one job role, at least as reported in the survey. Because some people have checked off more than one job, we cannot use “from\_dummies” to collect the one-hot encoded columns into a single one.

Instead, we’ll grab all of the columns whose names start with “job\_role”, again with a comprehension:

```
df[[one_column
 for one_column in df.columns
 if one_column.startswith('job_role')]]
```

We can check to see how many job positions people have by summing the *rows* (not the default, the columns), then counting the number of non-NaN values per row:

```
df.loc[df[[one_column
 for one_column in df.columns
 if one_column.startswith('job_role')]].count(axis='columns') > 1]
```

Finally, we can then find out how many rows match our query. As you might remember from above, you can get the length of a data frame most efficiently by calling “len” on the index.

In a nutshell, then:

- We get all of the columns whose names start with “job\_role”
- We retrieve all of the rows where the total number of columns with a non-False value is > 1.
- We get the index of this new data frame
- We count the length of the index.

The answer? I got 8,303, out of a total of 58,538, or about 14 percent.

What about people with more than five job roles? Are there any such people? And if so, how common are they?

We can perform our same query again, just changing the number that count needs to

```
df.loc[df[[one_column
 for one_column in df.columns
 if one_column.startswith('job_role')]].count(axis='columns') > 5]
```

I got 291 people, which is definitely larger than I would have expected, but is smaller than our previous query.

Here’s my Jupyter notebook for this week: [https://drive.google.com/file/d/1GjPA\_uABRsiriyENX\_pc0\_pMWlXyZKhr/view?usp=drive\_link](https://drive.google.com/file/d/1GjPA%5FuABRsiriyENX%5Fpc0%5FpMWlXyZKhr/view?usp=drive%5Flink&ref=bambooweekly.com)

That’s it for this week! Let me know what you think in the comments. And I’ll be back next week, back home in Israel, to share a new set of questions and data set.

Reuven