{ "cells": [ { "cell_type": "markdown", "id": "61534958", "metadata": {}, "source": [ "# 10.5 Larger datasets - beyond pandas and CSV" ] }, { "cell_type": "markdown", "id": "6e4ba9e2", "metadata": {}, "source": [ "*Estimated time for this notebook: 20 minutes.*" ] }, { "cell_type": "markdown", "id": "f0bc6f4c", "metadata": {}, "source": [ "Much of the data that we deal with can be represented in tabular form, and can be handled in data structures such as the *pandas DataFrame*.\n", "We have already (briefly) seen how we can read and write csv files from pandas, and there are also methods for reading the results of SQL queries into _pandas DataFrames_.\n", "\n", "However, if we have very large datasets (millions of rows), or cases where we need fast and intensive processing on these tables, _pandas_ may not be the best choice." ] }, { "cell_type": "markdown", "id": "2d194647", "metadata": {}, "source": [ "## Row-wise vs column-wise\n", "\n", "Let's read a csv file containing international men's football results into a _pandas DataFrame_:" ] }, { "cell_type": "code", "execution_count": 1, "id": "6d7e016b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Unnamed: 0datehome_teamaway_teamhome_scoreaway_scoretournamentcitycountryneutral
001872-11-30ScotlandEngland00FriendlyGlasgowScotlandFalse
111873-03-08EnglandScotland42FriendlyLondonEnglandFalse
221874-03-07ScotlandEngland21FriendlyGlasgowScotlandFalse
331875-03-06EnglandScotland22FriendlyLondonEnglandFalse
441876-03-04ScotlandEngland30FriendlyGlasgowScotlandFalse
\n", "
" ], "text/plain": [ " Unnamed: 0 date home_team away_team home_score away_score \\\n", "0 0 1872-11-30 Scotland England 0 0 \n", "1 1 1873-03-08 England Scotland 4 2 \n", "2 2 1874-03-07 Scotland England 2 1 \n", "3 3 1875-03-06 England Scotland 2 2 \n", "4 4 1876-03-04 Scotland England 3 0 \n", "\n", " tournament city country neutral \n", "0 Friendly Glasgow Scotland False \n", "1 Friendly London England False \n", "2 Friendly Glasgow Scotland False \n", "3 Friendly London England False \n", "4 Friendly Glasgow Scotland False " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "\n", "df = pd.read_csv(\"match_results.csv\")\n", "df.head()" ] }, { "cell_type": "markdown", "id": "8618b921", "metadata": {}, "source": [ "The obvious way to think of this table is _\"row-wise\"_ i.e. each row is a single match, with various attributes (the columns).\n", "If we want to look at a certain match, we can pick it out using its index, and then look at it in detail:" ] }, { "cell_type": "code", "execution_count": 2, "id": "36d832d3", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "England 2:2 Scotland\n" ] } ], "source": [ "match = df.iloc[3]\n", "print(f\"{match.home_team} {match.home_score}:{match.away_score} {match.away_team}\")" ] }, { "cell_type": "markdown", "id": "5afa383a", "metadata": {}, "source": [ "Similarly, when more matches get played, we can simply append more rows to the end of the table.\n", "\n", "However, for storing the data and for performing some types of operation on it, this is far from the most efficient approach.\n", "Note that the different columns here have different _types_ - we have dates, strings, integers, bools.\n", "If we look at the data in a *columnar* way, we can make use of this, and use some compression tricks.\n", "\n", "Since some data types such as integers have fixed and known sizes, we can easily imagine that it's more efficient to pack these together, so the \"home_score\" column would be \\[0,4,2,2,3,...\\], without having to worry about other columns containing e.g. strings, of varying size.\n", "\n", "However, we can do even better.\n" ] }, { "cell_type": "markdown", "id": "669da736", "metadata": {}, "source": [ "### Run length compression\n", "\n", "If we look at the last column, which is a boolean telling us whether the match was at a \"neutral\" venue (e.g. at a World Cup or European Championship).\n", "Most matches will be at non-neutral venues, but every two or four years there will be a cluster of \"neutral\" matches." ] }, { "cell_type": "code", "execution_count": 3, "id": "4fa2ef88", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "longest run is: 198\n" ] } ], "source": [ "# find the longest run of matches with the same value of 'neutral'\n", "previous_val = df.neutral.values[0]\n", "run = 0\n", "longest_run = 0\n", "for val in df.neutral.values[1:]:\n", " if val == previous_val:\n", " run += 1\n", " if run > longest_run:\n", " longest_run = run\n", " else:\n", " run = 0\n", "print(\"longest run is:\", longest_run)" ] }, { "cell_type": "markdown", "id": "17d5549f", "metadata": {}, "source": [ "We could save a _lot_ of space, with no loss of information, if rather than saving every value, we instead save something that means \"False 198 times, followed by True 64 times, followed by ...\"." ] }, { "cell_type": "markdown", "id": "e93f1613", "metadata": {}, "source": [ "### Dictionary compression\n", "\n", "Often the most space-consuming type in a dataset is a string.\n", "Each character will take one or two bytes (based on either UTF-8 or UTF-16 encoding), and we could either store each one as a variable-length string (so short strings will take less space than long strings), or we can decide on a maximum length for our string field, and pad the shorter strings.\n", "The former option takes less space, but is much less efficient when it comes to looking up values.\n", "\n", "However, in many data tables, the same values are repeated many times." ] }, { "cell_type": "code", "execution_count": 4, "id": "a8cc2e19", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Table has 44060 rows\n", "Number of unique home_team values: 311\n", "Brazil has been the home team 591 times\n" ] } ], "source": [ "# how many rows in the table?\n", "print(f\"Table has {len(df)} rows\")\n", "# how many unique values in 'home_team' column?\n", "print(f\"Number of unique home_team values: {len(df.home_team.unique())}\")\n", "# how many times does 'Brazil' appear?\n", "print(f\"Brazil has been the home team {df.home_team.value_counts().Brazil} times\")" ] }, { "cell_type": "markdown", "id": "70cad68c", "metadata": {}, "source": [ "Again, we could save a lot of space if we make a lookup table, so we e.g. assign each team name to an integer, and rather than taking 20 bytes to store the longest team name, we would just use 2 bytes for every team." ] }, { "cell_type": "markdown", "id": "89f67448", "metadata": {}, "source": [ "### Delta compression\n", "\n", "If we really want to compress our data as much as possible for writing to disk, and we don't care about making it human readable, we can use further tricks such as delta compression.\n", "For time series data, if something is relatively smoothly varying, we can save a lot of space by storing just the difference from one data point to the next, rather than each value.\n", "As an illustration of how this could work: rather than storing all the dates in the \"date\" table of our dataframe, we could just store the first date, and then for every subsequent row we just store the number of days since the previous row.\n", "\n", "In the case where the column we're trying to compress contains integers or floats, we wouldn't save any space if we were to store the differences as integers or floats as well, but lots of clever schemes exist for packing small deltas within a few bytes.\n", "\n", "For example, given the sequence:\n", "\n", "`5, 3, 3, 4, 2, 1, 2, 0`\n", "\n", "the deltas are:\n", "\n", "`-2, 0, 1, -2, -1, 1, -2`\n", "\n", "we can rescale this set of deltas by subtracting the minimum value (-2) from each element, such that the new minimum is 0, giving:\n", "\n", "`0, 2, 3, 0, 1, 3, 0`\n", "\n", "and finally we can encode this \"block\" along with a \"header\", as follows:\n", "```\n", "header: 8 (block size), 5 (first value)\n", "block: -2 (minimum delta), 2 (bitwidth), 00101100011100b (0,2,3,0,1,3,0 packed on 2 bits)\n", "```\n", "\n", "For this trivial example, we are not actually saving that much space, but we could extend this to have many more blocks, and/or longer blocks, and/or have a block/miniblock structure (e.g. for when we need to change the bitwidth to deal with larger deltas), and the overall saving could be huge.\n", "\n", "Of course, doing all these compression steps by hand is fiddly, and we would be very likely to make a mistake! But luckily, libraries exist that do the hard work for us, and can seamlessly convert between pandas DataFrames and compressed columnar formats." ] }, { "cell_type": "markdown", "id": "f171ea79", "metadata": {}, "source": [ "### Putting this into action: *parquet*\n", "\n", "One data format that implements all these forms of compression (see [here](https://github.com/apache/parquet-format/blob/master/Encodings.md)) is \"_parquet_\": https://parquet.apache.org/\n", "Parquet files can be read in many languages, including Python, R, C++, and Java.\n", "\n", "Let's write our dataframe as a _parquet_ file:" ] }, { "cell_type": "code", "execution_count": 5, "id": "f52597c7", "metadata": {}, "outputs": [], "source": [ "df.to_parquet(\"match_results.parquet\")" ] }, { "cell_type": "markdown", "id": "e07e76a9", "metadata": {}, "source": [ "How much space did we save compared to the csv?" ] }, { "cell_type": "code", "execution_count": 6, "id": "141d4799", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3.4M\tmatch_results.csv\r\n", "700K\tmatch_results.parquet\r\n" ] } ], "source": [ "!du -skh match_results.*" ] }, { "cell_type": "markdown", "id": "136b993b", "metadata": {}, "source": [ "About a factor of 9!" ] }, { "cell_type": "markdown", "id": "94655715", "metadata": {}, "source": [ "## _Arrow_ and _feather_\n", "\n", "You may have noticed that one of the packages that we installed in order to write the parquet file was `pyarrow`.\n", "Apache arrow is one of the under-the-hood technologies that parquet uses to process data.\n", "It is an \"in-memory\" columnar data format with some nice properties: random access is O(1) and each value cell is next to the previous and following one in memory, so it is efficient for iteration." ] }, { "cell_type": "markdown", "id": "3ed9e1d5", "metadata": {}, "source": [ "We can convert our pandas dataframe directly into an arrow table:" ] }, { "cell_type": "code", "execution_count": 7, "id": "987f2dd4", "metadata": {}, "outputs": [], "source": [ "from timeit import timeit\n", "\n", "import pyarrow as pa\n", "import pyarrow.compute as pc" ] }, { "cell_type": "code", "execution_count": 8, "id": "6b6dd6dc", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "pyarrow.Table\n", "Unnamed: 0: int64\n", "date: string\n", "home_team: string\n", "away_team: string\n", "home_score: int64\n", "away_score: int64\n", "tournament: string\n", "city: string\n", "country: string\n", "neutral: bool\n", "----\n", "Unnamed: 0: [[0,1,2,3,4,...,44055,44056,44057,44058,44059]]\n", "date: [[\"1872-11-30\",\"1873-03-08\",\"1874-03-07\",\"1875-03-06\",\"1876-03-04\",...,\"2022-09-27\",\"2022-09-27\",\"2022-09-27\",\"2022-09-27\",\"2022-09-30\"]]\n", "home_team: [[\"Scotland\",\"England\",\"Scotland\",\"England\",\"Scotland\",...,\"Norway\",\"Sweden\",\"Kosovo\",\"Greece\",\"Fiji\"]]\n", "away_team: [[\"England\",\"Scotland\",\"England\",\"Scotland\",\"England\",...,\"Serbia\",\"Slovenia\",\"Cyprus\",\"Northern Ireland\",\"Solomon Islands\"]]\n", "home_score: [[0,4,2,2,3,...,0,1,5,3,0]]\n", "away_score: [[0,2,1,2,0,...,2,1,1,1,0]]\n", "tournament: [[\"Friendly\",\"Friendly\",\"Friendly\",\"Friendly\",\"Friendly\",...,\"UEFA Nations League\",\"UEFA Nations League\",\"UEFA Nations League\",\"UEFA Nations League\",\"MSG Prime Minister's Cup\"]]\n", "city: [[\"Glasgow\",\"London\",\"Glasgow\",\"London\",\"Glasgow\",...,\"Oslo\",\"Stockholm\",\"Pristina\",\"Athens\",\"Luganville\"]]\n", "country: [[\"Scotland\",\"England\",\"Scotland\",\"England\",\"Scotland\",...,\"Norway\",\"Sweden\",\"Kosovo\",\"Greece\",\"Vanuatu\"]]\n", "neutral: [[false,false,false,false,false,...,false,false,false,false,true]]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "table = pa.Table.from_pandas(df)\n", "table" ] }, { "cell_type": "markdown", "id": "a1b56c46", "metadata": {}, "source": [ "Some things such as summing over columns are usually faster than in pandas:" ] }, { "cell_type": "code", "execution_count": 9, "id": "80803026", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Pandas took 0.6115358080000002, Arrow took 0.14301432199999997 to sum this column 10k times\n" ] } ], "source": [ "ptime = timeit(\"df.away_score.sum()\", globals=globals(), number=10000)\n", "atime = timeit('pc.sum(table.column(\"away_score\"))', globals=globals(), number=10000)\n", "print(f\"Pandas took {ptime}, Arrow took {atime} to sum this column 10k times\")" ] }, { "cell_type": "markdown", "id": "dcbe1707", "metadata": {}, "source": [ "So _arrow_ is about a factor 3-4 faster in this particular case.\n", "\n", "Should we always use _arrow_ instead of _pandas_ then? It depends.\n", "Arrow may be faster for some operations, so if you're speed-limited, it could be worth switching (or at least testing whether it's worth it).\n", "But on the other hand, _pandas_ has a healthy userbase, a well-known API, and established interfaces to other tools (e.g. _matplotlib_ for plotting).\n", "The good news is that it's very easy to convert between _pandas DataFrames_ and _arrow Tables_, and vice versa, so it shouldn't be a problem to try both and see what works best for your use-case." ] }, { "cell_type": "markdown", "id": "29e0d4a5", "metadata": {}, "source": [ "### Writing to disk: _feather_\n", "\n", "We have already seen that we can write tabular data in a columnar format to disk as a _parquet_ file.\n", "Another option is _feather_.\n", "_Feather_ is a direct on-disk representation of the in-memory _arrow_ format - it doesn't have the same compression that _parquet_ applies by default.\n", "\n", "Let's write our _arrow_ table to a _feather_ file:" ] }, { "cell_type": "code", "execution_count": 10, "id": "61b6114c", "metadata": {}, "outputs": [], "source": [ "import pyarrow.feather as feather\n", "\n", "feather.write_feather(table, \"match_results.feather\")" ] }, { "cell_type": "markdown", "id": "326c09dc", "metadata": {}, "source": [ "Now we have the same table in csv, _parquet_, and _feather_ format.\n", "Compare the sizes again:" ] }, { "cell_type": "code", "execution_count": 11, "id": "6df0c660", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "3.4M\tmatch_results.csv\r\n", "2.1M\tmatch_results.feather\r\n", "700K\tmatch_results.parquet\r\n" ] } ], "source": [ "! du -skh match_results.*" ] }, { "cell_type": "markdown", "id": "2e1adef4", "metadata": {}, "source": [ "The _feather_ format didn't compress anywhere near as much as the _parquet_ file (but is still much smaller than csv).\n", "\n", "So which is better, _feather_ or _parquet_? Again, it depends what you are doing.\n", "If you will be storing or transferring large quantities of data, _parquet_ is probably preferable.\n", "However, there is a CPU cost to the compression/decompression, so if you are more worried about the speed of reading and writing files, you might want to use _feather_." ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.12" } }, "nbformat": 4, "nbformat_minor": 5 }