{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"\n",
"![\"Open](\"https://colab.research.google.com/assets/colab-badge.svg\" "\\"Open")
\n",
"\n",
"| - | - | - |\n",
"|-------------------------------------------------------------------------------|-------------------------------------------------------------------------------|-------------------------------------------------------------------------------|\n",
"| [Exercise 1 (split date continues)](<#Exercise-1-(split-date-continues)>) | [Exercise 2 (cycling weather)](<#Exercise-2-(cycling-weather)>) | [Exercise 3 (top bands)](<#Exercise-3-(top-bands)>) |\n",
"| [Exercise 4 (cyclists per day)](<#Exercise-4-(cyclists-per-day)>) | [Exercise 5 (best record company)](<#Exercise-5-(best-record-company)>) | [Exercise 6 (suicide fractions)](<#Exercise-6-(suicide-fractions)>) |\n",
"| [Exercise 7 (suicide weather)](<#Exercise-7-(suicide-weather)>) | [Exercise 8 (bicycle timeseries)](<#Exercise-8-(bicycle-timeseries)>) | [Exercise 9 (commute)](<#Exercise-9-(commute)>) |\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Pandas (continues)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Catenating datasets\n",
"\n",
"We already saw in the NumPy section how we can catenate arrays along an axis: `axis=0` catenates vertically and `axis=1` catenates horizontally, and so on. With the DataFrames of Pandas it works similarly except that the row indices and the column names require extra attention. Also note a slight difference in the name: `np.concatenate` but `pd.concat`.\n",
"\n",
"Let's start by considering catenation along the axis 0, that is, vertical catenation. We will first make a helper function to easily create DataFrames for testing."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def makedf(cols, ind):\n",
" data = {c : [str(c) + str(i) for i in ind] for c in cols}\n",
" return pd.DataFrame(data, ind)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Next we will create some example DataFrames:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"a=makedf(\"AB\", [0,1])\n",
"a"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"b=makedf(\"AB\", [2,3])\n",
"b"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"c=makedf(\"CD\", [0,1])\n",
"c"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"d=makedf(\"BC\", [2,3])\n",
"d"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In the following simple case, the `concat` function works exactly as we expect it would:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([a,b]) # The default axis is 0"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The next, however, will create duplicate indices:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"r=pd.concat([a,a])\n",
"r"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"r.loc[0,\"A\"]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This is not usually what we want! There are three solutions to this. Firstly, deny creation of duplicated indices by giving the `verify_integrity` parameter to the `concat` function:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"try:\n",
" pd.concat([a,a], verify_integrity=True)\n",
"except ValueError as e:\n",
" import sys\n",
" print(e, file=sys.stderr)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Secondly, we can ask for automatic renumbering of rows:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([a,a], ignore_index=True)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Thirdly, we can ask for *hierarchical indexing*. The indices can contain multiple levels, but on this course we don't consider hierarchical indices in detail. Hierarchical indices can make a two dimensional array to work like higher dimensional array."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"r2=pd.concat([a,a], keys=['first', 'second'])\n",
"r2"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"r2[\"A\"][\"first\"][0]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Everything works similarly, when we want to catenate horizontally:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([a,c], axis=1)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We have so far assumed that when concatenating vertically the columns of both DataFrames are the same, and when joining horizontally the indices are the same. This is, however, not required:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([a,d], sort=False) # sort option is used to silence a deprecation message"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It expanded the non-existing cases with `NaN`s. This method is called an *outer join*, which forms the union of columns in the two DataFrames. The alternative is *inner join*, which forms the intersection of columns:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([a,d], join=\"inner\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 1 (split date continues)
\n",
"\n",
"Write function `split_date_continues` that does\n",
"\n",
"* read the bicycle data set\n",
"* clean the data set of columns/rows that contain only missing values\n",
"* drops the `Päivämäärä` column and replaces it with its splitted components as before\n",
"\n",
"Use the `concat` function to do this. The function should return a DataFrame with 25 columns (first five related to the date and then the rest 20 conserning the measument location.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Merging dataframes"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Merging combines two DataFrames based on some common field."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's recall the earlier DataFrame about wages and ages of persons:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df = pd.DataFrame([[1000, \"Jack\", 21], [1500, \"John\", 29]], columns=[\"Wage\", \"Name\", \"Age\"])\n",
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now, create a new DataFrame with the occupations of persons:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df2 = pd.DataFrame({\"Name\" : [\"John\", \"Jack\"], \"Occupation\": [\"Plumber\", \"Carpenter\"]})\n",
"df2"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The following function call will merge the two DataFrames on their common field, and, importantly, will keep the indices *aligned*. What this means is that even though the names are listed in different order in the two frames, the merge will still give correct result."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.merge(df, df2)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"This was an example of a simple one-to-one merge, where the keys in the `Name` columns had 1-to-1 correspondence. Sometimes not all the keys appear in both DataFrames:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"df3 = pd.concat([df2, pd.DataFrame({ \"Name\" : [\"James\"], \"Occupation\":[\"Painter\"]})], ignore_index=True)\n",
"df3"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.merge(df, df3) # By default an inner join is computed"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.merge(df, df3, how=\"outer\") # Outer join"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Also, many-to-one and many-to-many relationships can occur in merges:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"books = pd.DataFrame({\"Title\" : [\"War and Peace\", \"Good Omens\", \"Good Omens\"] , \n",
" \"Author\" : [\"Tolstoi\", \"Terry Pratchett\", \"Neil Gaiman\"]})\n",
"books"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"collections = pd.DataFrame([[\"Oodi\", \"War and Peace\"],\n",
" [\"Oodi\", \"Good Omens\"],\n",
" [\"Pasila\", \"Good Omens\"],\n",
" [\"Kallio\", \"War and Peace\"]], columns=[\"Library\", \"Title\"])\n",
"collections"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"All combinations with matching keys (`Title`) are created:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"libraries_with_books_by = pd.merge(books, collections)\n",
"libraries_with_books_by"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 2 (cycling weather)
\n",
"\n",
"Merge the processed cycling data set (from the previous exercise) and weather data set along the columns year, month, and day. Note that the names of these columns might be different in the two tables: use the `left_on` and `right_on` parameters. Then drop useless columns 'm', 'd', 'Time', and 'Time zone'.\n",
"\n",
"Write function `cycling_weather` that reads the data sets and returns the resulting DataFrame.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 3 (top bands)
\n",
"\n",
"Merge the DataFrames UK top40 and the bands DataFrame that are stored in the `src` folder.\n",
"Do all this in the parameterless function `top_bands`, which should return the merged DataFrame.\n",
"Use the `left_on` and `right_on` parameters to `merge`. Test your function from the `main` function.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Aggregates and groupings\n",
"\n",
"Let us use again the weather dataset. First, we make the column names a bit more uniform and concise. For example the columns `Year`, `m`, and `d` are not uniformly named.\n",
"\n",
"We can easily change the column names with the `rename` method of the DataFrame. Note that we cannot directly change the index `wh.columns` as it is immutable."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh = pd.read_csv(\"https://www.cs.helsinki.fi/u/jttoivon/dap/data/fmi/kumpula-weather-2017.csv\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh3 = wh.rename(columns={\"m\": \"Month\", \"d\": \"Day\", \"Precipitation amount (mm)\" : \"Precipitation\", \n",
" \"Snow depth (cm)\" : \"Snow\", \"Air temperature (degC)\" : \"Temperature\"})\n",
"wh3.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Pandas has an operation that splits a DataFrame into groups, performs some operation on each of the groups, and then combines the result from each group into a resulting DataFrame. This split-apply-combine functionality is really flexible and powerful operation. In Pandas you start by calling the `groupby` method, which splits the DataFrame into groups. In the following example the rows that contain measurements from the same month belong to the same group:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"groups = wh3.groupby(\"Month\")\n",
"groups"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Nothing happened yet, but the `groupby` object knows how the division into groups is done. This is called a lazy operation. We can query the number of groups in the `groupby` object:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"len(groups)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can iterate through all the groups:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"for key, group in groups:\n",
" print(key, len(group))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"groups.get_group(2) # Group with index two is February"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The `groupby` object functions a bit like a DataFrame, so some operations which are allowed for DataFrames are also allowed for the `groupby` object. For example, we can get a subset of columns:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"groups[\"Temperature\"]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For each DataFrame corresponding to a group the Temperature column was chosen. Still nothing was shown, because we haven't applied any operation on the groups."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The common methods also include the aggregation methods. Let's try to apply the `mean` aggregation:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"groups[\"Temperature\"].mean()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now what happened was that after the mean aggregation was performed on each group, the results were automatically combined into a resulting DataFrame. Let's try some other aggregation:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"groups[\"Precipitation\"].sum()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Ok, the -1.0 values in the Precipitation field are causing trouble here, let's convert them to zeros:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh4 = wh3.copy()\n",
"wh4.loc[wh4.Precipitation == -1, \"Precipitation\"] = 0\n",
"wh4.loc[wh4.Snow == -1, \"Snow\"] = 0\n",
"wh4.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh4.groupby(\"Month\")[\"Precipitation\"].sum()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Other ways to operate on groups\n",
"\n",
"The aggregations are not the only possible operations on groups. The other possibilities are filtering, transformation, and application.\n",
"\n",
"In **filtering** some of the groups can be filtered out."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"def myfilter(df): # The filter function must return a boolean value\n",
" return df[\"Precipitation\"].sum() >= 150\n",
"\n",
"wh4.groupby(\"Month\").filter(myfilter) # Filter out months with total precipitation less that 150 mm"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In a **transformation** each group's DataFrame is manipulated in a way that retains its shape. An example of centering values, so that the deviations from the monthly means are shown:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.concat([wh4.iloc[:, 0:3], \n",
" wh4.groupby(\"Month\")[[\"Precipitation\", \"Snow\", \"Temperature\"]].transform(lambda x : x - x.mean())], \n",
" axis=1)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The **apply** method is very generic and only requires that for each group's DataFrame the given function returns a DataFrame, Series, or a scalar. In the following example, we sort within each group by the temperature:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh4.groupby(\"Month\").apply(lambda df : df.sort_values(\"Temperature\"))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 4 (cyclists per day)
\n",
"\n",
"This exercise can give two points at maximum!\n",
"\n",
"Part 1.\n",
"\n",
"Read, clean and parse the bicycle data set as before. Group the rows by year, month, and day. Get the sum for each group.\n",
"Make function `cyclists_per_day` that does the above. The function should return a DataFrame.\n",
"Make sure that the columns Hour and Weekday are not included in the returned DataFrame.\n",
"\n",
"Part 2.\n",
"\n",
"In the `main` function, using the function `cyclists_per_day`, get the daily counts. The index of the DataFrame now consists of tuples (Year, Month, Day). Then restrict this data to August of year 2017, and plot this data. Don't forget to call the `plt.show` function of matplotlib. The x-axis should have ticks from 1 to 31, and there should be a curve to each measuring station. Can you spot the weekends?\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 5 (best record company)
\n",
"\n",
"We use again the UK top 40 data set from the first week of 1964 in the `src` folder. Here we define \"goodness\" of a record company (`Publisher`) based on the sum of the weeks on chart (WoC) of its singles. Return a DataFrame of the singles by the best record company (a subset of rows of the original DataFrame). Do this with function `best_record_company`.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 6 (suicide fractions)
\n",
"\n",
"Load the suicide data set from `src` folder. This data was originally downloaded from [Kaggle](https://www.kaggle.com/szamil/who-suicide-statistics). Kaggle contains lots of interesting open data sets.\n",
"\n",
"Write function `suicide_fractions` that loads the data set and returns a Series that has the country as the (row) index and as the column the mean fraction of suicides per population in that country. In other words, the value is the average of suicide fractions. The information about year, sex and age is not used.\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 7 (suicide weather)
\n",
"\n",
"Copy the function `suicide fractions` from the previous exercise. \n",
"\n",
"Implement function `suicide_weather` as described below.\n",
"We use the dataset of average temperature (over years 1961-1990) in different countries from `src/List_of_countries_by_average_yearly_temperature.html` (https://en.wikipedia.org/wiki/List_of_countries_by_average_yearly_temperature) .\n",
"You can use the function `pd.read_html` to get all the tables from a html page. By default `pd.read_html` does not know which row contains column headers and which column contains row headers. Therefore, you have to give both `index_col` and `header` parameters to `read_html`. Maku sure you use the country as the (row) index for both of the DataFrames. What is the [Spearman correlation](https://en.wikipedia.org/wiki/Spearman%27s_rank_correlation_coefficient) between these variables? Use the `corr` method of Series object. Note the the two Series need not be sorted as the indices of the rows (country names) are used to align them.\n",
"\n",
"The return value of the function `suicide_weather` is a tuple (suicide_rows, temperature_rows, common_rows, spearman_correlation)\n",
"The output from the `main` function should be of the following form:\n",
"\n",
"```\n",
"Suicide DataFrame has x rows\n",
"Temperature DataFrame has x rows\n",
"Common DataFrame has x rows\n",
"Spearman correlation: x.x\n",
"```\n",
"\n",
"You might have trouble when trying to convert the temperatures to float. The is because the negative numbers on that html page use a special *unicode minus sign*, which looks typographically nice, but the float constructor cannot interpret it as a minus sign. You can try out the following example:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"s=\"\\u2212\" \"5\" # unicode minus sign and five\n",
"print(s)\n",
"try:\n",
" float(s)\n",
"except ValueError as e:\n",
" import sys\n",
" print(e, file=sys.stderr)\n",
" "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"But if we explicitly convert unicode minus sign to normal minus sign, it works:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"float(s.replace(\"\\u2212\", \"-\"))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Time series\n",
"\n",
"If a measurement is made at certain points in time, the resulting values with their measurement times is called a time series. In Pandas a Series whose index consists of dates/times is a time series."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's make a copy of the DataFrame that we can mess with:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2 = wh3.copy()\n",
"wh2.columns"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The column names `Year`, `Month`, and `Day` are now in appropriate form for the `to_datetime` function. It can convert these fields into a timestamp series, which we will add to the DataFrame."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2[\"Date\"] = pd.to_datetime(wh2[[\"Year\", \"Month\", \"Day\"]])\n",
"wh2.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can now drop the useless fields:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2=wh2.drop(columns=[\"Year\", \"Month\", \"Day\"])\n",
"wh2.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The following method call will set the Date field as the index of the DataFrame."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2 = wh2.set_index(\"Date\")\n",
"wh2.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can now easily get a set of rows using date slices:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2[\"2017-01-15\":\"2017-02-03\"]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By using the `date_range` function even more complicated sets can be formed. The following gets all the Mondays of July:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"r=pd.date_range(\"2017-07-01\", \"2017-07-31\", freq=\"w-mon\")\n",
"r"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2.index.difference(r)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"wh2.loc[r,:]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The following finds all the business days (Monday to Friday) of July:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"pd.date_range(\"2017-07-01\", \"2017-07-31\", freq=\"b\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can get a general idea about the `Temperature` column by plotting it. Note how the index time series is shown nicely on the x-axis."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"%matplotlib inline\n",
"wh2[\"Temperature\"].plot();"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The graph looks a bit messy at this level of detail. We can smooth it by taking averages over a sliding window of length 30 days:"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"rolling = wh2.Temperature.rolling(30, center=True)\n",
"rolling"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"data = pd.DataFrame({\"Temperature\" : wh2.Temperature, \"Rolling mean\" : rolling.mean()})\n",
"data.plot();"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 8 (bicycle timeseries)
\n",
"\n",
"Write function `bicycle_timeseries` that\n",
"\n",
"* reads the data set\n",
"* cleans it\n",
"* turns its `Päivämäärä` column into (row) DatetimeIndex (that is, to row names) of that DataFrame\n",
"* returns the new DataFrame\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Exercise 9 (commute)
\n",
"\n",
"In function `commute` do the following:\n",
"\n",
"Use the function `bicycle_timeseries` to get the bicycle data. Restrict to August 2017, group by the weekday, aggregate by summing. Set the `Weekday` column to numbers from one to seven. Then set the column `Weekday` as the (row) index. Return the resulting DataFrame from the function.\n",
"\n",
"In the `main` function plot the DataFrame. Xticklabels should be the weekdays. Don't forget to call `show` function!\n",
"\n",
"If you want the xticklabels to be `['Mon', 'Tue', 'Wed', 'Thu', 'Fr', 'Sat', 'Sun']` instead of numbers (1,..,7), then it may get a bit messy. There seems to be a problem with non-numeric `x` values. You could try the following after plotting, but you don't have to:\n",
"\n",
"```python\n",
"weekdays=\"x mon tue wed thu fri sat sun\".title().split()\n",
"plt.gca().set_xticklabels(weekdays)\n",
"```\n",
"\n",
"
"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Additional information"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[Pandas cheat sheet](https://github.com/pandas-dev/pandas/blob/master/doc/cheatsheet/Pandas_Cheat_Sheet.pdf) Summary of most important Pandas' functions and methods.\n",
"\n",
"Read the article [Tidy Data](https://www.jstatsoft.org/article/view/v059i10/v59i10.pdf). The article uses the statistical software R as an example, but the ideas are relevant in general. Pandas operations maintain data in the tidy format.\n",
"\n",
"Pandas handles only one dimensional data (Series) and two dimensional data (DataFrame). While you can use [hierarchical indices](http://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html#hierarchical-indexing-multiindex) to simulate higher dimensional arrays, you should use the [xarray](http://xarray.pydata.org/en/stable/index.html) library, if you need proper higher-dimensional arrays with labels. It is basically a cross between NumPy and Pandas.\n",
"\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"\n",
"\n",
"\n"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"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.7.1"
}
},
"nbformat": 4,
"nbformat_minor": 2
}