{ "cells": [ { "cell_type": "markdown", "id": "fc5d538a", "metadata": {}, "source": [ "# Add, change and delete data\n", "\n", "For many data sets, you may want to perform a transformation based on the values in an array, series or column in a DataFrame. For this, we look at the first Unicode characters:" ] }, { "cell_type": "code", "execution_count": 1, "id": "199a054d", "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "id": "2445adae", "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", "
CodeDecimalOctalKey
0U+00000001NUL
1U+00011002Ctrl-A
2U+00022003Ctrl-B
3U+00033004Ctrl-C
4U+00044004Ctrl-D
5U+00055005Ctrl-E
\n", "
" ], "text/plain": [ " Code Decimal Octal Key\n", "0 U+0000 0 001 NUL\n", "1 U+0001 1 002 Ctrl-A\n", "2 U+0002 2 003 Ctrl-B\n", "3 U+0003 3 004 Ctrl-C\n", "4 U+0004 4 004 Ctrl-D\n", "5 U+0005 5 005 Ctrl-E" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.DataFrame(\n", " {\n", " \"Code\": [\"U+0000\", \"U+0001\", \"U+0002\", \"U+0003\", \"U+0004\", \"U+0005\"],\n", " \"Decimal\": [0, 1, 2, 3, 4, 5],\n", " \"Octal\": [\"001\", \"002\", \"003\", \"004\", \"004\", \"005\"],\n", " \"Key\": [\"NUL\", \"Ctrl-A\", \"Ctrl-B\", \"Ctrl-C\", \"Ctrl-D\", \"Ctrl-E\"],\n", " }\n", ")\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "ede547c7", "metadata": {}, "source": [ "## Add data\n", "\n", "Suppose you want to add a column where the characters are assigned to the `C0` or `C1` control code:" ] }, { "cell_type": "code", "execution_count": 3, "id": "7f9c5b67", "metadata": {}, "outputs": [], "source": [ "control_code = {\n", " \"u+0000\": \"C0\",\n", " \"u+0001\": \"C0\",\n", " \"u+0002\": \"C0\",\n", " \"u+0003\": \"C0\",\n", " \"u+0004\": \"C0\",\n", " \"u+0005\": \"C0\",\n", "}" ] }, { "cell_type": "markdown", "id": "a9cb2f89", "metadata": {}, "source": [ "The `map` method for a series accepts a function or dict-like object that contains an assignment, but here we have a small problem because some of the codes in `control_code` are lower case, but not in our DataFrame. Therefore, we need to convert each value to lower case using the `str.lower method`:" ] }, { "cell_type": "code", "execution_count": 4, "id": "b91fa766", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 u+0000\n", "1 u+0001\n", "2 u+0002\n", "3 u+0003\n", "4 u+0004\n", "5 u+0005\n", "Name: Code, dtype: object" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "lowercased = df[\"Code\"].str.lower()\n", "\n", "lowercased" ] }, { "cell_type": "code", "execution_count": 5, "id": "f5ad5395", "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", "
CodeDecimalOctalKeyControl code
0U+00000001NULC0
1U+00011002Ctrl-AC0
2U+00022003Ctrl-BC0
3U+00033004Ctrl-CC0
4U+00044004Ctrl-DC0
5U+00055005Ctrl-EC0
\n", "
" ], "text/plain": [ " Code Decimal Octal Key Control code\n", "0 U+0000 0 001 NUL C0\n", "1 U+0001 1 002 Ctrl-A C0\n", "2 U+0002 2 003 Ctrl-B C0\n", "3 U+0003 3 004 Ctrl-C C0\n", "4 U+0004 4 004 Ctrl-D C0\n", "5 U+0005 5 005 Ctrl-E C0" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[\"Control code\"] = lowercased.map(control_code)\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "d8af08db", "metadata": {}, "source": [ "We could also have passed a function that does all the work:" ] }, { "cell_type": "code", "execution_count": 6, "id": "69534a0c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 C0\n", "1 C0\n", "2 C0\n", "3 C0\n", "4 C0\n", "5 C0\n", "Name: Code, dtype: object" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df[\"Code\"].map(lambda x: control_code[x.lower()])" ] }, { "cell_type": "markdown", "id": "a2cdcbfe", "metadata": {}, "source": [ "Using `map` is a convenient way to perform element-wise transformations and other data cleaning operations." ] }, { "cell_type": "markdown", "id": "98c2e35c", "metadata": {}, "source": [ "## Change data" ] }, { "cell_type": "markdown", "id": "e7f0f3db", "metadata": {}, "source": [ "
\n", " \n", "**Note:**\n", "\n", "Replacing missing values is described in [Managing missing data with pandas](../../clean-prep/nulls.ipynb).\n", "
" ] }, { "cell_type": "code", "execution_count": 7, "id": "59f30f1a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Personalpower\n", "1 man-made\n", "dtype: object" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.Series([\"Manpower\", \"man-made\"]).str.replace(\"Man\", \"Personal\", regex=False)" ] }, { "cell_type": "code", "execution_count": 8, "id": "769fb6fd", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Personal-Power\n", "1 Personal-made\n", "dtype: object" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.Series([\"Man-Power\", \"man-made\"]).str.replace(\"[Mm]an\", \"Personal\", regex=True)" ] }, { "cell_type": "markdown", "id": "b3c3dd15", "metadata": {}, "source": [ "
\n", " \n", "**Note:**\n", "\n", "The [replace](https://pandas.pydata.org/docs/reference/api/pandas.Series.replace.html) method differs from [str.replace](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.replace.html) in that it replaces strings element by element.\n", "
" ] }, { "cell_type": "markdown", "id": "1151b4eb", "metadata": {}, "source": [ "## Delete data\n", "\n", "Deleting one or more entries from an axis is easy if you already have an index array or a list without these entries.\n", "\n", "To delete duplicates, see [Deduplicating data](../../clean-prep/deduplicate.ipynb).\n", "\n", "Since this may require a bit of set theory, we return the drop method as a new object without the deleted values:" ] }, { "cell_type": "code", "execution_count": 9, "id": "108e37e0", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 0.721314\n", "1 0.566529\n", "2 0.149298\n", "3 -1.076892\n", "4 -0.174988\n", "5 -0.844397\n", "6 -0.894065\n", "dtype: float64" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "rng = np.random.default_rng()\n", "s = pd.Series(rng.normal(size=7))\n", "\n", "s" ] }, { "cell_type": "code", "execution_count": 10, "id": "0b48b731", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 0.721314\n", "1 0.566529\n", "3 -1.076892\n", "4 -0.174988\n", "5 -0.844397\n", "6 -0.894065\n", "dtype: float64" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "new = s.drop(2)\n", "\n", "new" ] }, { "cell_type": "code", "execution_count": 11, "id": "46c88617", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 0.721314\n", "1 0.566529\n", "4 -0.174988\n", "5 -0.844397\n", "6 -0.894065\n", "dtype: float64" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "new = s.drop([2, 3])\n", "\n", "new" ] }, { "cell_type": "markdown", "id": "2821e7cf", "metadata": {}, "source": [ "With DataFrames, index values can be deleted on both axes. To illustrate this, we first create an example DataFrame:" ] }, { "cell_type": "code", "execution_count": 12, "id": "20dcc415", "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", "
CodeDecimalOctalKey
0U+00000001NUL
1U+00011002Ctrl-A
2U+00022003Ctrl-B
3U+00033004Ctrl-C
4U+00044004Ctrl-D
5U+00055005Ctrl-E
\n", "
" ], "text/plain": [ " Code Decimal Octal Key\n", "0 U+0000 0 001 NUL\n", "1 U+0001 1 002 Ctrl-A\n", "2 U+0002 2 003 Ctrl-B\n", "3 U+0003 3 004 Ctrl-C\n", "4 U+0004 4 004 Ctrl-D\n", "5 U+0005 5 005 Ctrl-E" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data = {\n", " \"Code\": [\"U+0000\", \"U+0001\", \"U+0002\", \"U+0003\", \"U+0004\", \"U+0005\"],\n", " \"Decimal\": [0, 1, 2, 3, 4, 5],\n", " \"Octal\": [\"001\", \"002\", \"003\", \"004\", \"004\", \"005\"],\n", " \"Key\": [\"NUL\", \"Ctrl-A\", \"Ctrl-B\", \"Ctrl-C\", \"Ctrl-D\", \"Ctrl-E\"],\n", "}\n", "\n", "df = pd.DataFrame(data)\n", "\n", "df" ] }, { "cell_type": "code", "execution_count": 13, "id": "79134d78", "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", "
CodeDecimalOctalKey
2U+00022003Ctrl-B
3U+00033004Ctrl-C
4U+00044004Ctrl-D
5U+00055005Ctrl-E
\n", "
" ], "text/plain": [ " Code Decimal Octal Key\n", "2 U+0002 2 003 Ctrl-B\n", "3 U+0003 3 004 Ctrl-C\n", "4 U+0004 4 004 Ctrl-D\n", "5 U+0005 5 005 Ctrl-E" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.drop([0, 1])" ] }, { "cell_type": "markdown", "id": "a5fc4626", "metadata": {}, "source": [ "You can also remove values from the columns by passing `axis=1` or `axis='columns'`:" ] }, { "cell_type": "code", "execution_count": 14, "id": "1086db39", "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", "
CodeOctalKey
0U+0000001NUL
1U+0001002Ctrl-A
2U+0002003Ctrl-B
3U+0003004Ctrl-C
4U+0004004Ctrl-D
5U+0005005Ctrl-E
\n", "
" ], "text/plain": [ " Code Octal Key\n", "0 U+0000 001 NUL\n", "1 U+0001 002 Ctrl-A\n", "2 U+0002 003 Ctrl-B\n", "3 U+0003 004 Ctrl-C\n", "4 U+0004 004 Ctrl-D\n", "5 U+0005 005 Ctrl-E" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.drop(\"Decimal\", axis=1)" ] }, { "cell_type": "markdown", "id": "9f3cfb13", "metadata": {}, "source": [ "Many functions such as `drop` that change the size or shape of a row or DataFrame can manipulate an object in place without returning a new object:" ] }, { "cell_type": "code", "execution_count": 15, "id": "16434f27", "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", "
CodeDecimalOctalKey
1U+00011002Ctrl-A
2U+00022003Ctrl-B
3U+00033004Ctrl-C
4U+00044004Ctrl-D
5U+00055005Ctrl-E
\n", "
" ], "text/plain": [ " Code Decimal Octal Key\n", "1 U+0001 1 002 Ctrl-A\n", "2 U+0002 2 003 Ctrl-B\n", "3 U+0003 3 004 Ctrl-C\n", "4 U+0004 4 004 Ctrl-D\n", "5 U+0005 5 005 Ctrl-E" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.drop(0, inplace=True)\n", "\n", "df" ] }, { "cell_type": "markdown", "id": "9bdac634", "metadata": {}, "source": [ "
\n", "\n", "**Warning:**\n", "\n", "Be careful with the `inplace` function, as the data will be irretrievably deleted.\n", "
" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.13 Kernel", "language": "python", "name": "python313" }, "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.13.0" }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 5 }