{
"cells": [
{
"cell_type": "markdown",
"id": "3b4e19f4",
"metadata": {},
"source": [
"# Records as tabular data\n",
"\n",
"Sometimes it's helpful to thing of speeches, characters, or conversations as spreadsheet tables rather than as objects. A really useful tool for converting data back and forth between DICES object classes and tabular formats is [Pandas](https://pandas.pydata.org/docs/getting_started/intro_tutorials/01_table_oriented.html)."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "872563ac",
"metadata": {},
"outputs": [],
"source": [
"from dicesapi import DicesAPI\n",
"import pandas as pd\n",
"\n",
"# create a connection to DICES\n",
"api = DicesAPI(logfile='dices.log')"
]
},
{
"cell_type": "markdown",
"id": "dbf19e4c",
"metadata": {},
"source": [
"Pandas provides the DataFrame class: this is a really convenient container for tabular data.\n",
"\n",
"### Example\n",
"\n",
"#### Download some speeches"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "dfd9bfd5",
"metadata": {},
"outputs": [],
"source": [
"speeches = api.getSpeeches(spkr_name='Achilles')\n",
"print('Got', len(speeches), 'speeches')"
]
},
{
"cell_type": "markdown",
"id": "b7452e7b",
"metadata": {},
"source": [
"#### Convert to a DataFrame\n",
"\n",
"The basic move for building a data frame is to create a list of records ('dictionaries' in Python) where each record is made up of key-value pairs. The key is the column heading for this value. The pattern looks like this:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e92f8b8c",
"metadata": {},
"outputs": [],
"source": [
"table = pd.DataFrame(dict(\n",
" speech_id = s.id,\n",
" author = s.author.name,\n",
" work = s.work.title,\n",
" language = s.lang,\n",
" first_line = s.l_fi,\n",
" last_line = s.l_la,\n",
" speakers = s.getSpkrString(),\n",
" addressees = s.getAddrString(),\n",
" type = s.type,\n",
") for s in speeches)"
]
},
{
"cell_type": "markdown",
"id": "e48ba760",
"metadata": {},
"source": [
"The first and last lines in this block set up the data frame as a list of records, one per speech. The lines in the middle define the fields and values for each record. The column names are on the left sides of the equals signs, and the values for a given speech are on the right. Each time through the loop, `s` will be a different speech. \n",
"\n",
"Let's see the result:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fe50fc8f",
"metadata": {},
"outputs": [],
"source": [
"display(table)"
]
},
{
"cell_type": "markdown",
"id": "187b73be",
"metadata": {},
"source": [
"By default, Pandas only shows the first and last rows of the table. This is helpful if it's really large. If you want to see the whole thing at once, you can do this:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7596eec2",
"metadata": {},
"outputs": [],
"source": [
"with pd.option_context('display.max_rows', None,):\n",
" display(table)"
]
},
{
"cell_type": "markdown",
"id": "cb528f4a",
"metadata": {},
"source": [
"## Export CSV\n",
"\n",
"If at this point you want to move your data to Excel, you can save a Pandas DataFrame to CSV format using the `to_csv()` method. (**Note**: if you're running this in Binder or another cloud-based platform, you'll have to download the resulting file afterward.)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "745d88b7",
"metadata": {},
"outputs": [],
"source": [
"table.to_csv('achilles.csv')"
]
},
{
"cell_type": "markdown",
"id": "c6105d53",
"metadata": {},
"source": [
"## Pivot Tables\n",
"\n",
"If you want to continue working with your data in Python, you'll find that most of the tasks you might do in Excel have straightforward analogues in Pandas.\n",
"\n",
"In particular, aggregating and summarizing data in a Pivot Table is done with the `pivot_table()` method. The most common arguments are:\n",
"- `index`: which fields to use for the rows of the new table\n",
"- `columns`: which fields to use for the columns of the new table\n",
"- `values`: which fields to summarize in the cells of the table\n",
"- `aggfunc`: the function used to summarize multiple data. Common choices are `'count'`, `'sum'`, `'mean'`, `'max'`, etc. See [here](https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html) for details, or type `help(pd.DataFrame.pivot_table)`\n",
"\n",
"\n",
"### Example\n",
"\n",
"This produces a table showing how many speeches are of each type. I want one row for each `type`, and then the values should be a count of speeches: I'll count the `speech_id` column here."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7b101e0f",
"metadata": {},
"outputs": [],
"source": [
"table.pivot_table(\n",
" index = 'type',\n",
" values = 'speech_id',\n",
" aggfunc = 'count',\n",
")"
]
},
{
"cell_type": "markdown",
"id": "2ec9c6ec",
"metadata": {},
"source": [
"## Working with columns and rows\n",
"\n",
"### Subsetting columns by name\n",
"\n",
"To get a new DataFrame using only selected columns, you can use doubled square brackets after the table name."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "695133d7",
"metadata": {},
"outputs": [],
"source": [
"table[['first_line', 'last_line']]"
]
},
{
"cell_type": "markdown",
"id": "3f108642",
"metadata": {},
"source": [
"### Extracting one column as a Series\n",
"\n",
"You can get a single column out of the DataFrame as a list-like, one-dimensional series by putting the name in single square brackets or using a dot after the table name.\n",
"\n",
"We'll see how this form is useful when we subset rows by column values below."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1c36e09a",
"metadata": {},
"outputs": [],
"source": [
"table['type']"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8fce3f82",
"metadata": {},
"outputs": [],
"source": [
"table.addressees"
]
},
{
"cell_type": "markdown",
"id": "9dcd6415",
"metadata": {},
"source": [
"### Subsetting rows\n",
"\n",
"If you want specific rows, you can put a range of numbers in square brackets:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "63192220",
"metadata": {},
"outputs": [],
"source": [
"table[0:10]"
]
},
{
"cell_type": "markdown",
"id": "b3bc7ee2",
"metadata": {},
"source": [
"You can also select rows based on the values in certain columns:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b6fd44ea",
"metadata": {},
"outputs": [],
"source": [
"table[table.addressees=='Agamemnon']"
]
},
{
"cell_type": "markdown",
"id": "ad3e2a8c",
"metadata": {},
"source": [
"### Creating new columns\n",
"\n",
"You can add columns to the table by assigning values to them. Here, we create a new column with the book number for each speech by performing a operation on one of the other columns. We split the value of `first_line` on the `'.'` character, and then take only the first element of the result."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "e6e03dfb",
"metadata": {},
"outputs": [],
"source": [
"table['book'] = [l.split('.')[0] for l in table.first_line]"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fa5089e5",
"metadata": {},
"outputs": [],
"source": [
"table"
]
},
{
"cell_type": "markdown",
"id": "fb1b6624",
"metadata": {},
"source": [
"### Putting it together\n",
"\n",
"Let's count Achilles' speeches in the Iliad by book number, and make a graph:"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b9241bd7",
"metadata": {},
"outputs": [],
"source": [
"subset = table[table.work=='Iliad']\n",
"\n",
"subset.pivot_table(\n",
" index = 'book',\n",
" values = 'speech_id',\n",
" aggfunc = 'count',\n",
" sort = False,\n",
")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c1dd5b51",
"metadata": {},
"outputs": [],
"source": [
"subset.pivot_table(\n",
" index = 'book',\n",
" values = 'speech_id',\n",
" aggfunc = 'count',\n",
" sort = False,\n",
").plot.bar(\n",
" legend = False,\n",
" rot = False,\n",
")"
]
}
],
"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.11.7"
}
},
"nbformat": 4,
"nbformat_minor": 5
}