> ## 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 #43: Financial protection (solutions) - URL: https://www.bambooweekly.com/bw-43-financial-protection-solution/ - Published: 2023-12-07T16:08:50.000Z - Updated: 2026-08-23T09:37:00.000Z - Description: Get better at: CSV, memory optimization, formatting, PyArrow, dates and times, and plotting. - Author: Reuven M. Lerner - Tags: csv, memory-optimization, formatting, pyarrow, datetime, plotting *\[*Administrative note*: I’ll be holding office hours this coming Sunday. Look for e-mail in the coming day with Zoom details. I look forward to seeing you there!\]* This week, we’re looking at the database of complaints sent to the Consumer Financial Protection Bureau ([https://www.consumerfinance.gov/](https://www.consumerfinance.gov/?ref=bambooweekly.com), [https://en.wikipedia.org/wiki/Consumer\_Financial\_Protection\_Bureau](https://en.wikipedia.org/wiki/Consumer%5FFinancial%5FProtection%5FBureau?ref=bambooweekly.com)), a US government agency that tries to help consumers who have had problems with financial products and services. The database is large (about 3 GB) and describes a wide variety of complaints over more than a decade, giving us a chance to explore it and quite a bit of Pandas functionality. Let’s get it to it! ![](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-2f00d02764-9892-401c-abd6-06ece83c9981_1024x1024.png) From ChatGPT: “Create a picture of a panda complaining to a bank manager about being swindled.” ### Data and 8 questions This week’s data set is the database of consumer complaints, available at the CFPB's site: ``` https://www.consumerfinance.gov/data-research/consumer-complaints/ ``` You should download the entire database in CSV format from ``` https://files.consumerfinance.gov/ccdb/complaints.csv.zip ``` The zipfile is about 690 MB in size, and the unzipped file is about 3 GB in size, so make sure your computer has enough RAM to handle it! Here are this week's eight tasks and questions: ### Read the CFPB complaint data. Read all columns, and don't specify any dtypes. How long did the read take? How much memory does the resulting data frame take? I started off by loading Pandas into memory, with the standard import and alias: ``` import pandas as pd ``` I also defined a “filename” variable with the CSV file I want to load. I’ll be loading it a large number of times, and decided ``` filename = 'complaints.csv' ``` I asked you to read the entire file (all columns) into a data frame. That’s easy enough to do with “[read\_csv](https://www.bambooweekly.com/pandas-read-csv/)”: ``` df = pd.read_csv(filename) ``` However, when I gave the above instruction, I got a warning from Pandas: ``` DtypeWarning: Columns (16) have mixed types. Specify dtype option on import or set low_memory=False. ``` What does this warning mean? Well, the data frame is large, with 4,367,328 rows and 18 columns. Instead of reading it all into memory, analyzing the contents of each column, and deciding what dtype to use, Pandas read it into memory in chunks. That saves memory, but it also means that the dtype it decides on from earlier chunks might not work with later chunks, leading to an incorrectly determined dtype. Pandas suggests getting rid of this warning by either setting low\_memory=False (thus letting it examine the entirety of a column’s data before setting the dtype) or by explicitly telling Pandas what dtypes to use. I decided that it might be interesting to see just what kind of effect these suggestions might have. For starters, I decided to set low\_memory=False ``` df = pd.read_csv(filename, low_memory=False) ``` I wanted to know how long this took — and for such tasks, I’m a big fan of the magic “timeit” commands in Jupyter. %timeit runs a line of code repeatedly, giving you the mean run time, and [%%timeit](https://ipython.readthedocs.io/en/stable/interactive/magics.html?ref=bambooweekly.com#magic-timeit) does so within a full cell. Note that if you’re using %%timeit, then it must be the first line in the cell. Python comments (starting with #) and other text must come *after* the call to %timeit. ``` %%timeit df = pd.read_csv(filename, low_memory=False) ``` The result? Loading the data on my system takes quite some time: ``` 19.6 s ± 96.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each) ``` In other words, it ran the code 7 times, and found that on average, it took 19.6 seconds to load. How much memory does this data frame take up? An easy way to check is with the “[memory\_usage](https://www.bambooweekly.com/pandas-memory-usage/)” method, which returns the memory usage for each column: ``` df.memory_usage() ``` I get the following results: ``` Index 132 Date received 34938624 Product 34938624 Sub-product 34938624 Issue 34938624 Sub-issue 34938624 Consumer complaint narrative 34938624 Company public response 34938624 Company 34938624 State 34938624 ZIP code 34938624 Tags 34938624 Consumer consent provided? 34938624 Submitted via 34938624 Date sent to company 34938624 Company response to consumer 34938624 Timely response? 34938624 Consumer disputed? 34938624 Complaint ID 34938624 dtype: int64 ``` Does it seem a bit suspicious that each column takes up precisely the same amount of memory? It should — that’s because Pandas is telling us how much memory it is using, but not how much the Python strings contained in the data frame are using. In other words, this isn’t a real measure of how much memory is being used. To get that, we need to pass deep=True, which takes much longer but gives an actual answer: df.memory\_usage(deep=True) This time, we get: ``` Index 132 Date received 292610976 Product 462396306 Sub-product 317189659 Issue 422208545 Sub-issue 373518838 Consumer complaint narrative 1812265991 Company public response 380293226 Company 364080378 State 256506475 ZIP code 269867646 Tags 157331396 Consumer consent provided? 286252782 Submitted via 264345302 Date sent to company 292610976 Company response to consumer 355644139 Timely response? 261984666 Consumer disputed? 160648459 Complaint ID 34938624 dtype: int64 ``` Since this is a series, we can total it up with “[sum](https://www.bambooweekly.com/pandas-sum/)”: ``` usage = df.memory_usage(deep=True).sum() print(f'{usage:,}') ``` Displaying usage in an f-string lets us put commas between the digits: ``` 6,764,694,516 ``` The total memory usage of this data frame is… 6.7 GB! Wow, that’s a lot of memory. ### Now specify categorical and date dtypes. How long did the read take? How much memory does this take? Let’s see if we can shrink that memory usage a bit. One of the easiest ways to reduce the size of a text-based column is with a “category.” Consider a column containing country names. The country names likely repeat many times, with each mention of a country taking additional memory. If we could replace each country name with an integer, and then store the mapping from integers to country names on the side, we could save a lot of memory. That’s precisely how a category works — except that Pandas handles it all for us automatically. Moreover, the fact that it’s a categorical column is pretty much invisible to us; we can still use all of the string functionality that Pandas makes available. If we already have a series (including a column) of strings, then we can turn it into a category by saying: ``` s.astype(‘category’) ``` But in this case, we’ll go one better: We’ll tell Pandas that we want the text columns to be turned into categories as they’re loaded. This does mean that Pandas will need to spend some time analyzing the data in each column, finding the unique values, creating the category objects, and then rewriting the columns. So it might take a bit more time, but the memory savings could be substantial. The way we do this is by passing the “dtype” keyword argument to “read\_csv”. This argument takes a dict as a value; the dict’s keys are column names, and the values are the dtypes we want to assign to the columns. In this particular case, we’ll just want a ton of category columns: ``` df = pd.read_csv(filename, low_memory=False, dtype={"Product":'category', "Sub-product":'category', "Issue":'category', "Sub-issue":'category', "Company":'category', "State":'category', "Submitted via": 'category', 'Company response to consumer': 'category', 'ZIP code': 'category', 'Consumer consent provided?':'category', 'Company public response':'category', 'Timely response?':'category', 'Consumer disputed?':'category' }) ``` But wait, we can do even better: There are two columns that contain date information, which we can have Pandas parse into datetime columns. We do this by passing the “parse\_dates” keyword argument. All together, we then have the following query: ``` %%timeit df = pd.read_csv(filename, low_memory=False, parse_dates=['Date received', 'Date sent to company'], dtype={"Product":'category', "Sub-product":'category', "Issue":'category', "Sub-issue":'category', "Company":'category', "State":'category', "Submitted via": 'category', 'Company response to consumer': 'category', 'ZIP code': 'category', 'Consumer consent provided?':'category', 'Company public response':'category', 'Timely response?':'category', 'Consumer disputed?':'category' }) ``` Running the above, I got the following results from %%timeit: ``` 20.4 s ± 254 ms per loop (mean ± std. dev. of 7 runs, 1 loop each) ``` The extra analysis and building of categories did take some extra time. True, we needed the dates to be parsed, so that was useful. But the big question is whether we saved any memory. Let’s see: ``` usage = df.memory_usage(deep=True).sum() print(f'{usage:,}') ``` The result? ``` 2,161,433,792 ``` That’s right — we have reduced our memory usage by about 70 percent, simply by asking Pandas to use dtypes and categories. Not a bad savings for several minutes of work. ### The PyArrow engine can often read CSV files more quickly than the default Pandas engine. Does it work here? Why or why not? Historically, Pandas has used NumPy for storing its values and for some of its underlying functionality. But over the last few years, the core Pandas developers have been making steps toward the use of [Apache Arrow](https://arrow.apache.org/?ref=bambooweekly.com), a library that provides high-speed, cross-platform, in-memory storage of Pandas-like data structures. When you create a data frame with read\_csv, you can specify the dtype\_backend keyword argument, indicating that you want to use “pyarrow” for storing data. (We talked about this back in [BW #10](https://www.bambooweekly.com/bw-10-oil-prices-solution/).) Even if you don’t want to use PyArrow for back-end storage, you can still take advantage of its multi-threaded, high-performance implementation for reading CSV files. Given that it takes nearly 20 seconds to load our 3GB CSV file with CFPB complaints, I thought that it might be worthwhile comparing the speed of PyArrow for loading CSV files. I thus ran: ``` df = pd.read_csv(filename, engine='pyarrow') ``` I was surprised to get an error message: ``` ArrowInvalid: CSV parse error: Expected 18 columns, got 2: The second issue that I have is I never received any documents about this account. No informatio ... ``` I didn’t quite understand what was going on here. Why did it get the wrong number of columns? The CSV file seemed OK when I loaded it using pure Pandas. The problem is that the CSV file includes several multi-line fields. That is, the field opens with double quotes, includes some newline characters, and then closes with double quotes several lines later. Some implementations of CSV allow for this, and from what I can tell, Apache Arrow’s implementation does, as well — but not when run via Pandas. I tried a variety of techniques to get around this problem and wasn’t able to find any. There was an issue update just in the last few days on GitHub ([https://github.com/pandas-dev/pandas/issues/52266](https://github.com/pandas-dev/pandas/issues/52266?ref=bambooweekly.com)) that seemed to indicate a fix might be in the works. The bottom line is that PyArrow is a fantastic project and offers a lot of great, high-speed functionality that the Pandas world is starting to enjoy. But it’s still less flexible and capable than the core Pandas functionality, and it might be some time before we can switch over to PyArrow, even for loading CSV files. ### Create a line plot showing how many complaints were filed in each calendar year. What sort of trend do we see? There are two date-related columns in this data set, and we made sure to turn them into datetime columns when we read the data into Pandas with the “parse\_dates” keyword argument to read\_csv. We can thus address these columns, including “Date received”, with the “dt” accessor, exposing the various parts of dates and times. For example, if we want to get just the year from each of the values in “Date received”, we can say: ``` ( df['Date received'] .dt.year ) ``` Retrieving “[dt.year](https://www.bambooweekly.com/pandas-dt-year/)” returns a column of integers, namely the year in which each complaint arrived at the CFPB. But we actually want to know how many times each year was mentioned in this column. For that, we have “[value\_counts](https://www.bambooweekly.com/pandas-value-counts/)”, which returns a series whose index contains the unique values from this column, and whose values counts how often each appears: ``` ( df['Date received'] .dt.year .value_counts() ) ``` With this in place, we can then invoke “[plot.line](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html?ref=bambooweekly.com)”: ``` ( df['Date received'] .dt.year .value_counts() .plot.line() ) ``` The result is a line plot whose x axis is the years and whose y axis shows the number of complaints in each year: ![](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-2fa53db3ba-1d6c-4d59-9b7c-c3dbc6ba62d0_547x448.png) I don’t know about you, but I see a pretty clear upward trend in complaints, especially starting around 2021\. I know that there were some attempts to hobble the CFPB during the Trump administration, which ended in early 2021, but I really don’t know if there’s a connection there or not. You could also argue that as interest rates have gone up over the last few years, there have been more opportunities for financial fraud, thus leading to a surge in complaints. ### Show the proportion of each product among complaints filed in 2023. When people file complaints with the CFPB, what exactly are they complaining about? Since the complaints are categorized by product, I thought that it might be worth looking at the proportion of complaints filed for each product. I asked you to look only at complaints in 2023\. There are a few ways to do this, but it’s easiest (I think) when we make the date column into the index. We can do that with “[set\_index](https://www.bambooweekly.com/pandas-set-index/)”: ``` ( df .set_index('Date received') ) ``` Now that the “Date received” column is the index, we can use “[loc](https://www.bambooweekly.com/pandas-loc/)” to retrieve only those items from 2023\. When the index contains datetime values, we can pass a string that contains some or all of the date. Any part that we don’t pass is treated as a wildcard, meaning that passing “2023” will match anything in 2023\. However, we need to do this as part of a slice, whose second part I left open. Of course, “loc” can take two arguments. The first is a row selector, and the second is a column selector. I thus pass “Product”, the column of interest to me, as the second argument: ``` ( df .set_index('Date received') .loc['2023':, 'Product'] ) ``` Finally, I invoke value\_counts to count how often each product is listed. But because I wanted the proportion, not the raw numbers, I passed normalize=True as a keyword argument: ``` ( df .set_index('Date received') .loc['2023':, 'Product'] .value_counts(normalize=True) ) ``` The results are a bit hard to read, because some of the lines are long, but I’ll paste them here anyway: ``` Product Credit reporting, credit repair services, or other personal consumer reports 0.559155 Credit reporting or other personal consumer reports 0.252571 Debt collection 0.052303 Checking or savings account 0.038820 Credit card or prepaid card 0.030615 Mortgage 0.017656 Credit card 0.013586 Money transfer, virtual currency, or money service 0.010495 Vehicle loan or lease 0.010007 Student loan 0.007669 Payday loan, title loan, or personal loan 0.004062 Payday loan, title loan, personal loan, or advance loan 0.001571 Prepaid card 0.001197 Debt or credit management 0.000292 Bank account or service 0.000000 Payday loan 0.000000 Other financial service 0.000000 Money transfers 0.000000 Credit reporting 0.000000 Consumer Loan 0.000000 Virtual currency 0.000000 Name: proportion, dtype: float64 ``` In other words, more than half (55 percent!) of the complaints had to do with credit reporting, credit repair services, or personal consumer reports. The first two items both had to do with credit reporting, and were about 75 percent of all complaints in 2023\. I’m not sure if that reflects something odd happening in the last year, or if credit reports are always so problematic in the US, but that strikes me as a very high number. ### Show how many complaints there were for each kind of product in each calendar year. Next, I asked you to find how many complaints there were for each product in each year. Consider: - We have the date on which each complaint was filed, including its year, in the “Date received” column - We have the name of the product in each complaint in the “Product” column In other words, this sounds like a perfect match for a pivot table: - The index of the pivot table will be the years from “Date received” - The columns of the pivot table will be the unique values from “Product” - The aggregation function will be “count” - We’ll run it on the “Complaint ID” column, just because we need to count something The query looks like this: ``` df.pivot_table(index='Product', columns=df['Date received'].dt.year, values='Complaint ID', aggfunc='count') ``` Notice that whereas we normally pass a string to “index”, “columns”, and “values” indicating which column we want to use, here I’ve passed the result of invoking dt.year on the “Date received” column. Here’s what I got, as captured by a (partial) screenshot on my computer screen: ![](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-2f655a93a2-0220-4449-8fdf-c566c869b8b9_3684x2078.png) To me, one of the most striking aspects of this report is that there have been some massive shifts in what products were named and tracked. That’s a shame, since it makes it that much harder to track things over time. For example, we have “Credit card” as a product from 2011-207, and then “Prepaid card” from 2014-2017,” but “Credit card or prepaid card” from 2017-2023\. I’m sure that there are reasons for the change, but should these be combined, or kept separate? ### Show the mean time it took from the time the CFPB receives a complaint until it is sent to a company for each year. Let’s say you submit a complaint to the CFPB. How long will it take before the complaint is passed along to the responsible company? My (cynical) assumption, based on my experience with government agencies, was that it would we a number of weeks. But hey, let’s see how long it took the CFPB. I’ll figure need to calculate the amount of time it took for each complaint to be filed, after it was initially submitted to the CFPB. Because the “Date received” and “Date sent to company” columns are both timestamps, we can engage in some programmer math, subtracting one from the other and getting a “timedelta” value, sometimes known as an “interval.” You can think of a timedelta as the distance between two timestamps, measured in days and seconds, but not anchored to any particular time. It’s the difference between when a meeting starts (which we would measure as a timestamp) and how long it goes (which we measure as a timedelta). I started by using the “[assign](https://www.bambooweekly.com/pandas-assign/)” method on my data frame, temporarily creating a new column (“filing\_time”) with the timedelta created from this difference: ``` ( df .assign(filing_time = df['Date sent to company'] - df['Date received']) ) ``` I could calculate the mean of the “filing\_time” column, and thus find out how long it took, on average, for the complaint to be passed along. However, I wanted to find out how long it took, on average, *per year*. Calculating that is a bit more complex, and involves the “[resample](https://www.bambooweekly.com/pandas-resample/)” method. That method can only be invoked when the data frame’s index contains timestamp data, so I set the index to be “Date received”: ``` ( df .assign(filing_time = df['Date sent to company'] - df['Date received']) .set_index('Date received') ) ``` Finally, I invoked resample with a period of “1Y” (for one year), invoking it on the “filing\_time” column, and the invoking the “[mean](https://www.bambooweekly.com/pandas-mean/)” method on the result: ``` ( df .assign(filing_time = df['Date sent to company'] - df['Date received']) .set_index('Date received') .resample('1Y')['filing_time'].mean() ) ``` The result? For each year in our data frame, we get the mean filing time: ``` Date received 2011-12-31 7 days 11:32:44.668769716 2012-12-31 6 days 20:37:49.474382357 2013-12-31 7 days 07:18:59.564755348 2014-12-31 4 days 09:05:22.353294861 2015-12-31 3 days 06:40:27.725570047 2016-12-31 3 days 07:01:46.100274768 2017-12-31 2 days 00:06:22.803048707 2018-12-31 1 days 06:55:57.021728228 2019-12-31 1 days 04:28:46.604134886 2020-12-31 0 days 16:26:52.622130592 2021-12-31 1 days 04:17:39.048525329 2022-12-31 0 days 17:46:01.350551735 2023-12-31 0 days 13:00:23.630855035 Freq: A-DEC, Name: filing_time, dtype: timedelta64[ns] ``` Notice that when we use resample, the resulting series usually has an index for the final day of the period we requested. For that reason, the index is all December 31st. Even a quick scan of this timing shows that the numbers have gone down over the years. But it’s even more striking if we then create a line plot from it: ``` ( df .assign(filing_time = df['Date sent to company'] - df['Date received']) .set_index('Date received') .resample('1Y')['filing_time'].mean() .plot.line() ) ``` 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-2fc21969b7-360e-4f21-b5b5-58455a52ebda_534x448.png) From what I can tell, the time that a complaint waits in the CFPB’s offices between receipt and being sent to the responsible company has declined dramatically over the years — just as we’ve seen the volume of complaints has increased. I’m impressed. ### From which 10 zip codes were the most complaints fired? Only consider the first three digits in a zip code, since only the first three digits are displayed for complaints from small locations. Finally, I’m curious to know what zip codes (i.e., locations) in the US are responsible for the greatest number of complaints. In theory, we could just run “value\_counts” on the “ZIP code” column, and be done with it. But CFPB’s privacy rules block out zip codes with small populations, presumably so that companies cannot cause trouble for people living there who file complaints. And sometimes, there is no zip code at all. For this reason, I decided to remove the NaN values: ``` ( df['ZIP code'] .dropna() ) ``` Next, I wanted to remove any zip code containing the letter X. I used loc on the NaN-less column, applying an anonymous function (aka lambda). The function negated used “[str.contains](https://www.bambooweekly.com/pandas-str-contains/)” to find those values containing the letter X, which returned a boolean series. I then applied \~ (tilde) to the boolean series to flip its logic. This meant that loc kept the rows with only digits, and discarded those with any X: ``` ( df['ZIP code'] .dropna() .loc[lambda s_: ~s_.str.contains('X')] ) ``` This looks great… but it isn’t, because (as reader pointed out): - There are a bunch of non-digit characters other than X, and - I didn’t do what I said, namely look at only the first three digits. I thus decided to change things a bit, first grabbing only the first three characters of the zip code and then only keeping those with three digits: ``` ( df['ZIP code'] .dropna() .str.slice(0, 3) .loc[lambda s_: s_.str.isdigit()] .value_counts() .head(10) ) ``` The above query: 1. Grabs the ZIP code column 2. Removes NaN values 3. Grabs the first three characters from the zip code with “[str.slice](https://www.bambooweekly.com/pandas-str-slice/)” 4. Keeps only those with digits, ignoring non-digit characters 5. Counts how often each of these appears 6. Keeps the 10 most common ones The result: ``` ZIP code 300 88196 191 86047 770 80945 330 73394 331 72997 606 62832 303 58434 112 53876 900 48776 750 47090 Name: count, dtype: int64 ``` This week’s Jupyter notebook is downloadable from here: [https://drive.google.com/file/d/1Or-Bwz2SN\_58LUk4p44KM7MHH5nwEgAU/view?usp=drive\_link](https://drive.google.com/file/d/1Or-Bwz2SN%5F58LUk4p44KM7MHH5nwEgAU/view?usp=drive%5Flink&ref=bambooweekly.com). Comments? Questions? Thoughts? Leave them here! I’ll be back next Wednesday with more Pandas problems related to current events. Reuven