<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Tools - pandas
The pandas library provides high-performance, easy-to-use data structures and data analysis tools. The main data structure is the DataFrame, which you can think of as an in-memory 2D table (like a spreadsheet, with column names and row labels). Many features available in Excel are available programmatically, such as creating pivot tables, computing columns based on other columns, plotting graphs, etc. You can also group rows by column value, or join tables much like in SQL. Pandas is also great at handling time series.
Prerequisites
Step1:  Now let's import pandas. People usually import it as pd
Step2:  Series objects
The pandas library contains these useful data structures
Step3:  Similar to a 1D ndarray
Series objects behave much like one-dimensional NumPy ndarrays, and you can often pass them as parameters to NumPy functions
Step4:  Arithmetic operations on Series are also possible, and they apply elementwise, just like for ndarrays
Step5:  Similar to NumPy, if you add a single number to a Series, that number is added to all items in the Series. This is called * broadcasting*
Step6:  The same is true for all binary operations such as * or /, and even conditional operations
Step7:  Index labels
Each item in a Series object has a unique identifier called the index label. By default, it is simply the rank of the item in the Series (starting at 0) but you can also set the index labels manually
Step8:  You can then use the Series just like a dict
Step9:  You can still access the items by integer location, like in a regular array
Step10:  To make it clear when you are accessing by label or by integer location, it is recommended to always use the loc attribute when accessing by label, and the iloc attribute when accessing by integer location
Step11:  Slicing a Series also slices the index labels
Step12:  This can lead to unexpected results when using the default numeric labels, so be careful
Step13:  Oh look! The first element has index label 2. The element with index label 0 is absent from the slice
Step14:  But remember that you can access elements by integer location using the iloc attribute. This illustrates another reason why it's always better to use loc and iloc to access Series objects
Step15:  Init from dict
You can create a Series object from a dict. The keys will be used as index labels
Step16:  You can control which elements you want to include in the Series and in what order by explicitly specifying the desired index
Step17:  Automatic alignment
When an operation involves multiple Series objects, pandas automatically aligns items by matching index labels.
Step18:  The resulting Series contains the union of index labels from s2 and s3. Since "colin" is missing from s2 and "charles" is missing from s3, these items have a NaN result value. (ie. Not-a-Number means missing).
Automatic alignment is very handy when working with data that may come from various sources with varying structure and missing items. But if you forget to set the right index labels, you can have surprising results
Step19:  Pandas could not align the Series, since their labels do not match at all, hence the full NaN result.
Init with a scalar
You can also initialize a Series object using a scalar and a list of index labels
Step20:  Series name
A Series can have a name
Step21:  Plotting a Series
Pandas makes it easy to plot Series data using matplotlib (for more details on matplotlib, check out the matplotlib tutorial). Just import matplotlib and call the plot method
Step22:  There are many options for plotting your data. It is not necessary to list them all here
Step23:  This DatetimeIndex may be used as an index in a Series
Step24:  Let's plot this series
Step25:  Resampling
Pandas let's us resample a time series very simply. Just call the resample method and specify a new frequency
Step26:  Let's take a look at the result
Step27:  Note how the values have automatically been aggregated into 2-hour periods. If we look at the 6-8pm period, for example, we had a value of 5.1 at 6
Step28:  Upsampling and interpolation
This was an example of downsampling. We can also upsample (ie. increase the frequency), but this creates holes in our data
Step29:  One solution is to fill the gaps by interpolating. We just call the interpolate method. The default is to use linear interpolation, but we can also select another method, such as cubic interpolation
Step30:  Timezones
By default datetimes are naive
Step31:  Note that -04
Step32:  You may have noticed that the UTC offset changes from +02
Step33:  Now 02
Step34:  Fortunately using the ambiguous argument we can tell pandas to infer the right DST (Daylight Saving Time) based on the order of the ambiguous timestamps
Step35:  Periods
The period_range function returns a PeriodIndex instead of a DatetimeIndex. For example, let's get all quarters in 2016 and 2017
Step36:  Adding a number N to a PeriodIndex shifts the periods by N times the PeriodIndex's frequency
Step37:  The asfreq method lets us change the frequency of the PeriodIndex. All periods are lengthened or shortened accordingly. For example, let's convert all the quarterly periods to monthly periods (zooming in)
Step38:  By default, the asfreq zooms on the end of each period. We can tell it to zoom on the start of each period instead
Step39:  And we can zoom out
Step40:  Of course we can create a Series with a PeriodIndex
Step41:  We can convert periods to timestamps by calling to_timestamp. By default this will give us the first day of each period, but by setting how and freq, we can get the last hour of each period
Step42:  And back to periods by calling to_period
Step43:  Pandas also provides many other time-related functions that we recommend you check out in the documentation. To whet your appetite, here is one way to get the last business day of each month in 2016, at 9am
Step44:  DataFrame objects
A DataFrame object represents a spreadsheet, with cell values, column names and row index labels. You can define expressions to compute columns based on other columns, create pivot-tables, group rows, draw graphs, etc. You can see DataFrames as dictionaries of Series.
Creating a DataFrame
You can create a DataFrame by passing a dictionary of Series objects
Step45:  A few things to note
Step46:  You can also get multiple columns at once
Step47:  If you pass a list of columns and/or index row labels to the DataFrame constructor, it will guarantee that these columns and/or rows will exist, in that order, and no other column/row will exist. For example
Step48:  Another convenient way to create a DataFrame is to pass all the values to the constructor as an ndarray, or a list of lists, and specify the column names and row index labels separately
Step49:  To specify missing values, you can either use np.nan or NumPy's masked arrays
Step50:  Instead of an ndarray, you can also pass a DataFrame object
Step51:  It is also possible to create a DataFrame with a dictionary (or list) of dictionaries (or list)
Step52:  Multi-indexing
If all columns are tuples of the same size, then they are understood as a multi-index. The same goes for row index labels. For example
Step53:  You can now get a DataFrame containing all the "public" columns very simply
Step54:  Dropping a level
Let's look at d5 again
Step55:  There are two levels of columns, and two levels of indices. We can drop a column level by calling droplevel (the same goes for indices)
Step56:  Transposing
You can swap columns and indices using the T attribute
Step57:  Stacking and unstacking levels
Calling the stack method will push the lowest column level after the lowest index
Step58:  Note that many NaN values appeared. This makes sense because many new combinations did not exist before (eg. there was no bob in London).
Calling unstack will do the reverse, once again creating many NaN values.
Step59:  If we call unstack again, we end up with a Series object
Step60:  The stack and unstack methods let you select the level to stack/unstack. You can even stack/unstack multiple levels at once
Step61:  Most methods return modified copies
As you may have noticed, the stack and unstack methods do not modify the object they apply to. Instead, they work on a copy and return that copy. This is true of most methods in pandas.
Accessing rows
Let's go back to the people DataFrame
Step62:  The loc attribute lets you access rows instead of columns. The result is Series object in which the DataFrame's column names are mapped to row index labels
Step63:  You can also access rows by integer location using the iloc attribute
Step64:  You can also get a slice of rows, and this returns a DataFrame object
Step65:  Finally, you can pass a boolean array to get the matching rows
Step66:  This is most useful when combined with boolean expressions
Step67:  Adding and removing columns
You can generally treat DataFrame objects like dictionaries of Series, so the following work fine
Step68:  When you add a new colum, it must have the same number of rows. Missing rows are filled with NaN, and extra rows are ignored
Step69:  When adding a new column, it is added at the end (on the right) by default. You can also insert a column anywhere else using the insert method
Step70:  Assigning new columns
You can also create new columns by calling the assign method. Note that this returns a new DataFrame object, the original is not modified
Step71:  Note that you cannot access columns created within the same assignment
Step72:  The solution is to split this assignment in two consecutive assignments
Step73:  Having to create a temporary variable d6 is not very convenient. You may want to just chain the assigment calls, but it does not work because the people object is not actually modified by the first assignment
Step74:  But fear not, there is a simple solution. You can pass a function to the assign method (typically a lambda function), and this function will be called with the DataFrame as a parameter
Step75:  Problem solved!
Evaluating an expression
A great feature supported by pandas is expression evaluation. This relies on the numexpr library which must be installed.
Step76:  Assignment expressions are also supported, and contrary to the assign method, this does not create a copy of the DataFrame, instead it directly modifies it
Step77:  You can use a local or global variable in an expression by prefixing it with '@'
Step78:  Querying a DataFrame
The query method lets you filter a DataFrame based on a query expression
Step79:  Sorting a DataFrame
You can sort a DataFrame by calling its sort_index method. By default it sorts the rows by their index label, in ascending order, but let's reverse the order
Step80:  Note that sort_index returned a sorted copy of the DataFrame. To modify people directly, we can set the inplace argument to True. Also, we can sort the columns instead of the rows by setting axis=1
Step81:  To sort the DataFrame by the values instead of the labels, we can use sort_values and specify the column to sort by
Step82:  Plotting a DataFrame
Just like for Series, pandas makes it easy to draw nice graphs based on a DataFrame.
For example, it is trivial to create a line plot from a DataFrame's data by calling its plot method
Step83:  You can pass extra arguments supported by matplotlib's functions. For example, we can create scatterplot and pass it a list of sizes using the s argument of matplotlib's scatter function
Step84:  Again, there are way too many options to list here
Step85:  You can apply NumPy mathematical functions on a DataFrame
Step86:  Similarly, adding a single value to a DataFrame will add that value to all elements in the DataFrame. This is called broadcasting
Step87:  Of course, the same is true for all other binary operations, including arithmetic (*,/,**...) and conditional (&gt;, ==...) operations
Step88:  Aggregation operations, such as computing the max, the sum or the mean of a DataFrame, apply to each column, and you get back a Series object
Step89:  The all method is also an aggregation operation
Step90:  Most of these functions take an optional axis parameter which lets you specify along which axis of the DataFrame you want the operation executed. The default is axis=0, meaning that the operation is executed vertically (on each column). You can set axis=1 to execute the operation horizontally (on each row). For example, let's find out which students had all grades greater than 5
Step91:  The any method returns True if any value is True. Let's see who got at least one grade 10
Step92:  If you add a Series object to a DataFrame (or execute any other binary operation), pandas attempts to broadcast the operation to all rows in the DataFrame. This only works if the Series has the same size as the DataFrames rows. For example, let's substract the mean of the DataFrame (a Series object) from the DataFrame
Step93:  We substracted 7.75 from all September grades, 8.75 from October grades and 7.50 from November grades. It is equivalent to substracting this DataFrame
Step94:  If you want to substract the global mean from every grade, here is one way to do it
Step95:  Automatic alignment
Similar to Series, when operating on multiple DataFrames, pandas automatically aligns them by row index label, but also by column names. Let's create a DataFrame with bonus points for each person from October to December
Step96:  Looks like the addition worked in some cases but way too many elements are now empty. That's because when aligning the DataFrames, some columns and rows were only present on one side, and thus they were considered missing on the other side (NaN). Then adding NaN to a number results in NaN, hence the result.
Handling missing data
Dealing with missing data is a frequent task when working with real life data. Pandas offers a few tools to handle missing data.
Let's try to fix the problem above. For example, we can decide that missing data should result in a zero, instead of NaN. We can replace all NaN values by a any value using the fillna method
Step97:  It's a bit unfair that we're setting grades to zero in September, though. Perhaps we should decide that missing grades are missing grades, but missing bonus points should be replaced by zeros
Step98:  That's much better
Step99:  Now let's call the interpolate method. By default, it interpolates vertically (axis=0), so let's tell it to interpolate horizontally (axis=1).
Step100:  Bob had 0 bonus points in October, and 2 in December. When we interpolate for November, we get the mean
Step101:  Great, now we have reasonable bonus points everywhere. Let's find out the final grades
Step102:  It is slightly annoying that the September column ends up on the right. This is because the DataFrames we are adding do not have the exact same columns (the grades DataFrame is missing the "dec" column), so to make things predictable, pandas orders the final columns alphabetically. To fix this, we can simply add the missing column before adding
Step103:  There's not much we can do about December and Colin
Step104:  Now let's remove columns that are full of NaNs by setting the axis argument to 1
Step105:  Aggregating with groupby
Similar to the SQL language, pandas allows grouping your data into groups to run calculations over each group.
First, let's add some extra data about each person so we can group them, and let's go back to the final_grades DataFrame so we can see how NaN values are handled
Step106:  Now let's group data in this DataFrame by hobby
Step107:  We are ready to compute the average grade per hobby
Step108:  That was easy! Note that the NaN values have simply been skipped when computing the means.
Pivot tables
Pandas supports spreadsheet-like pivot tables that allow quick data summarization. To illustrate this, let's create a simple DataFrame
Step109:  Now we can call the pivot_table function for this DataFrame, asking to group by the name column. By default, pivot_table computes the mean of each numeric column
Step110:  We can change the aggregation function by setting the aggfunc attribute, and we can also specify the list of columns whose values will be aggregated
Step111:  We can also specify the columns to aggregate over horizontally, and request the grand totals for each row and column by setting margins=True
Step112:  Finally, we can specify multiple index or column names, and pandas will create multi-level indices
Step113:  Overview functions
When dealing with large DataFrames, it is useful to get a quick overview of its content. Pandas offers a few functions for this. First, let's create a large DataFrame with a mix of numeric values, missing values and text values. Notice how Jupyter displays only the corners of the DataFrame
Step114:  The head method returns the top 5 rows
Step115:  Of course there's also a tail function to view the bottom 5 rows. You can pass the number of rows you want
Step116:  The info method prints out a summary of each columns contents
Step117:  Finally, the describe method gives a nice overview of the main aggregated values over each column
Step118:  Saving & loading
Pandas can save DataFrames to various backends, including file formats such as CSV, Excel, JSON, HTML and HDF5, or to a SQL database. Let's create a DataFrame to demonstrate this
Step119:  Saving
Let's save it to CSV, HTML and JSON
Step120:  Done! Let's take a peek at what was saved
Step121:  Note that the index is saved as the first column (with no name) in a CSV file, as &lt;th&gt; tags in HTML and as keys in JSON.
Saving to other formats works very similarly, but some formats require extra libraries to be installed. For example, saving to Excel requires the openpyxl library
Step122:  Loading
Now let's load our CSV file back into a DataFrame
Step123:  As you might guess, there are similar read_json, read_html, read_excel functions as well.  We can also read data straight from the Internet. For example, let's load all U.S. cities from simplemaps.com
Step124:  There are more options available, in particular regarding datetime format. Check out the documentation for more details.
Combining DataFrames
SQL-like joins
One powerful feature of pandas is it's ability to perform SQL-like joins on DataFrames. Various types of joins are supported
Step125:  Now let's join these DataFrames using the merge function
Step126:  Note that both DataFrames have a column named state, so in the result they got renamed to state_x and state_y.
Also, note that Cleveland, Salt Lake City and Houston were dropped because they don't exist in both DataFrames. This is the equivalent of a SQL INNER JOIN. If you want a FULL OUTER JOIN, where no city gets dropped and NaN values are added, you must specify how="outer"
Step127:  Of course LEFT OUTER JOIN is also available by setting how="left"
Step128:  If the key to join on is actually in one (or both) DataFrame's index, you must use left_index=True and/or right_index=True. If the key column names differ, you must use left_on and right_on. For example
Step129:  Concatenation
Rather than joining DataFrames, we may just want to concatenate them. That's what concat is for
Step130:  Note that this operation aligned the data horizontally (by columns) but not vertically (by rows). In this example, we end up with multiple rows having the same index (eg. 3). Pandas handles this rather gracefully
Step131:  Or you can tell pandas to just ignore the index
Step132:  Notice that when a column does not exist in a DataFrame, it acts as if it was filled with NaN values. If we set join="inner", then only columns that exist in both DataFrames are returned
Step133:  You can concatenate DataFrames horizontally instead of vertically by setting axis=1
Step134:  In this case it really does not make much sense because the indices do not align well (eg. Cleveland and San Francisco end up on the same row, because they shared the index label 3). So let's reindex the DataFrames by city name before concatenating
Step135:  This looks a lot like a FULL OUTER JOIN, except that the state columns were not renamed to state_x and state_y, and the city column is now the index.
The append method is a useful shorthand for concatenating DataFrames vertically
Step136:  As always in pandas, the append method does not actually modify city_loc
Step137:  Right now the eco_code column is full of apparently meaningless codes. Let's fix that. First, we will create a new categorical column based on the eco_codes
Step138:  Now we can give each category a meaningful name
Step139:  Note that categorical values are sorted according to their categorical order, not their alphabetical order
<ASSISTANT_TASK:>
Python Code:
from __future__ import division, print_function, unicode_literals
Explanation: Tools - pandas
The pandas library provides high-performance, easy-to-use data structures and data analysis tools. The main data structure is the DataFrame, which you can think of as an in-memory 2D table (like a spreadsheet, with column names and row labels). Many features available in Excel are available programmatically, such as creating pivot tables, computing columns based on other columns, plotting graphs, etc. You can also group rows by column value, or join tables much like in SQL. Pandas is also great at handling time series.
Prerequisites:
* NumPy – if you are not familiar with NumPy, we recommend that you go through the NumPy tutorial now.
Setup
First, let's make sure this notebook works well in both python 2 and 3:
End of explanation
import pandas as pd
Explanation: Now let's import pandas. People usually import it as pd:
End of explanation
s = pd.Series([2,-1,3,5])
s
Explanation: Series objects
The pandas library contains these useful data structures:
* Series objects, that we will discuss now. A Series object is 1D array, similar to a column in a spreadsheet (with a column name and row labels).
* DataFrame objects. This is a 2D table, similar to a spreadsheet (with column names and row labels).
* Panel objects. You can see a Panel as a dictionary of DataFrames. These are less used, so we will not discuss them here.
Creating a Series
Let's start by creating our first Series object!
End of explanation
import numpy as np
np.exp(s)
Explanation: Similar to a 1D ndarray
Series objects behave much like one-dimensional NumPy ndarrays, and you can often pass them as parameters to NumPy functions:
End of explanation
s + [1000,2000,3000,4000]
Explanation: Arithmetic operations on Series are also possible, and they apply elementwise, just like for ndarrays:
End of explanation
s + 1000
Explanation: Similar to NumPy, if you add a single number to a Series, that number is added to all items in the Series. This is called * broadcasting*:
End of explanation
s < 0
Explanation: The same is true for all binary operations such as * or /, and even conditional operations:
End of explanation
s2 = pd.Series([68, 83, 112, 68], index=["alice", "bob", "charles", "darwin"])
s2
Explanation: Index labels
Each item in a Series object has a unique identifier called the index label. By default, it is simply the rank of the item in the Series (starting at 0) but you can also set the index labels manually:
End of explanation
s2["bob"]
Explanation: You can then use the Series just like a dict:
End of explanation
s2[1]
Explanation: You can still access the items by integer location, like in a regular array:
End of explanation
s2.loc["bob"]
s2.iloc[1]
Explanation: To make it clear when you are accessing by label or by integer location, it is recommended to always use the loc attribute when accessing by label, and the iloc attribute when accessing by integer location:
End of explanation
s2.iloc[1:3]
Explanation: Slicing a Series also slices the index labels:
End of explanation
surprise = pd.Series([1000, 1001, 1002, 1003])
surprise
surprise_slice = surprise[2:]
surprise_slice
Explanation: This can lead to unexpected results when using the default numeric labels, so be careful:
End of explanation
try:
    surprise_slice[0]
except KeyError as e:
    print("Key error:", e)
Explanation: Oh look! The first element has index label 2. The element with index label 0 is absent from the slice:
End of explanation
surprise_slice.iloc[0]
Explanation: But remember that you can access elements by integer location using the iloc attribute. This illustrates another reason why it's always better to use loc and iloc to access Series objects:
End of explanation
weights = {"alice": 68, "bob": 83, "colin": 86, "darwin": 68}
s3 = pd.Series(weights)
s3
Explanation: Init from dict
You can create a Series object from a dict. The keys will be used as index labels:
End of explanation
s4 = pd.Series(weights, index = ["colin", "alice"])
s4
Explanation: You can control which elements you want to include in the Series and in what order by explicitly specifying the desired index:
End of explanation
print(s2.keys())
print(s3.keys())
s2 + s3
Explanation: Automatic alignment
When an operation involves multiple Series objects, pandas automatically aligns items by matching index labels.
End of explanation
s5 = pd.Series([1000,1000,1000,1000])
print("s2 =", s2.values)
print("s5 =", s5.values)
s2 + s5
Explanation: The resulting Series contains the union of index labels from s2 and s3. Since "colin" is missing from s2 and "charles" is missing from s3, these items have a NaN result value. (ie. Not-a-Number means missing).
Automatic alignment is very handy when working with data that may come from various sources with varying structure and missing items. But if you forget to set the right index labels, you can have surprising results:
End of explanation
meaning = pd.Series(42, ["life", "universe", "everything"])
meaning
Explanation: Pandas could not align the Series, since their labels do not match at all, hence the full NaN result.
Init with a scalar
You can also initialize a Series object using a scalar and a list of index labels: all items will be set to the scalar.
End of explanation
s6 = pd.Series([83, 68], index=["bob", "alice"], name="weights")
s6
Explanation: Series name
A Series can have a name:
End of explanation
%matplotlib inline
import matplotlib.pyplot as plt
temperatures = [4.4,5.1,6.1,6.2,6.1,6.1,5.7,5.2,4.7,4.1,3.9,3.5]
s7 = pd.Series(temperatures, name="Temperature")
s7.plot()
plt.show()
Explanation: Plotting a Series
Pandas makes it easy to plot Series data using matplotlib (for more details on matplotlib, check out the matplotlib tutorial). Just import matplotlib and call the plot method:
End of explanation
dates = pd.date_range('2016/10/29 5:30pm', periods=12, freq='H')
dates
Explanation: There are many options for plotting your data. It is not necessary to list them all here: if you need a particular type of plot (histograms, pie charts, etc.), just look for it in the excellent Visualization section of pandas' documentation, and look at the example code.
Handling time
Many datasets have timestamps, and pandas is awesome at manipulating such data:
* it can represent periods (such as 2016Q3) and frequencies (such as "monthly"),
* it can convert periods to actual timestamps, and vice versa,
* it can resample data and aggregate values any way you like,
* it can handle timezones.
Time range
Let's start by creating a time series using timerange. This returns a DatetimeIndex containing one datetime per hour for 12 hours starting on October 29th 2016 at 5:30pm.
End of explanation
temp_series = pd.Series(temperatures, dates)
temp_series
Explanation: This DatetimeIndex may be used as an index in a Series:
End of explanation
temp_series.plot(kind="bar")
plt.grid(True)
plt.show()
Explanation: Let's plot this series:
End of explanation
temp_series_freq_2H = temp_series.resample("2H")
temp_series_freq_2H
Explanation: Resampling
Pandas let's us resample a time series very simply. Just call the resample method and specify a new frequency:
End of explanation
temp_series_freq_2H.plot(kind="bar")
plt.show()
Explanation: Let's take a look at the result:
End of explanation
temp_series_freq_2H = temp_series.resample("2H", how=np.min)
temp_series_freq_2H
Explanation: Note how the values have automatically been aggregated into 2-hour periods. If we look at the 6-8pm period, for example, we had a value of 5.1 at 6:30pm, and 6.1 at 7:30pm. After resampling, we just have one value of 5.6, which is the mean of 5.1 and 6.1. Computing the mean is the default behavior, but it is also possible to use a different aggregation function, for example we can decide to keep the minimum value of each period:
End of explanation
temp_series_freq_15min = temp_series.resample("15Min")
temp_series_freq_15min.head(n=10) # `head` displays the top n values
Explanation: Upsampling and interpolation
This was an example of downsampling. We can also upsample (ie. increase the frequency), but this creates holes in our data:
End of explanation
temp_series_freq_15min = temp_series.resample("15Min").interpolate(method="cubic")
temp_series_freq_15min.head(n=10)
temp_series.plot(label="Period: 1 hour")
temp_series_freq_15min.plot(label="Period: 15 minutes")
plt.legend()
plt.show()
Explanation: One solution is to fill the gaps by interpolating. We just call the interpolate method. The default is to use linear interpolation, but we can also select another method, such as cubic interpolation:
End of explanation
temp_series_ny = temp_series.tz_localize("America/New_York")
temp_series_ny
Explanation: Timezones
By default datetimes are naive: they are not aware of timezones, so 2016-10-30 02:30 might mean October 30th 2016 at 2:30am in Paris or in New York. We can make datetimes timezone aware by calling the tz_localize method:
End of explanation
temp_series_paris = temp_series_ny.tz_convert("Europe/Paris")
temp_series_paris
Explanation: Note that -04:00 is now appended to all the datetimes. This means that these datetimes refer to UTC - 4 hours.
We can convert these datetimes to Paris time like this:
End of explanation
temp_series_paris_naive = temp_series_paris.tz_localize(None)
temp_series_paris_naive
Explanation: You may have noticed that the UTC offset changes from +02:00 to +01:00: this is because France switches to winter time at 3am that particular night (time goes back to 2am). Notice that 2:30am occurs twice! Let's go back to a naive representation (if you log some data hourly using local time, without storing the timezone, you might get something like this):
End of explanation
try:
    temp_series_paris_naive.tz_localize("Europe/Paris")
except Exception as e:
    print(type(e))
    print(e)
Explanation: Now 02:30 is really ambiguous. If we try to localize these naive datetimes to the Paris timezone, we get an error:
End of explanation
temp_series_paris_naive.tz_localize("Europe/Paris", ambiguous="infer")
Explanation: Fortunately using the ambiguous argument we can tell pandas to infer the right DST (Daylight Saving Time) based on the order of the ambiguous timestamps:
End of explanation
quarters = pd.period_range('2016Q1', periods=8, freq='Q')
quarters
Explanation: Periods
The period_range function returns a PeriodIndex instead of a DatetimeIndex. For example, let's get all quarters in 2016 and 2017:
End of explanation
quarters + 3
Explanation: Adding a number N to a PeriodIndex shifts the periods by N times the PeriodIndex's frequency:
End of explanation
quarters.asfreq("M")
Explanation: The asfreq method lets us change the frequency of the PeriodIndex. All periods are lengthened or shortened accordingly. For example, let's convert all the quarterly periods to monthly periods (zooming in):
End of explanation
quarters.asfreq("M", how="start")
Explanation: By default, the asfreq zooms on the end of each period. We can tell it to zoom on the start of each period instead:
End of explanation
quarters.asfreq("A")
Explanation: And we can zoom out:
End of explanation
quarterly_revenue = pd.Series([300, 320, 290, 390, 320, 360, 310, 410], index = quarters)
quarterly_revenue
quarterly_revenue.plot(kind="line")
plt.show()
Explanation: Of course we can create a Series with a PeriodIndex:
End of explanation
last_hours = quarterly_revenue.to_timestamp(how="end", freq="H")
last_hours
Explanation: We can convert periods to timestamps by calling to_timestamp. By default this will give us the first day of each period, but by setting how and freq, we can get the last hour of each period:
End of explanation
last_hours.to_period()
Explanation: And back to periods by calling to_period:
End of explanation
months_2016 = pd.period_range("2016", periods=12, freq="M")
one_day_after_last_days = months_2016.asfreq("D") + 1
last_bdays = one_day_after_last_days.to_timestamp() - pd.tseries.offsets.BDay()
last_bdays.to_period("H") + 9
Explanation: Pandas also provides many other time-related functions that we recommend you check out in the documentation. To whet your appetite, here is one way to get the last business day of each month in 2016, at 9am:
End of explanation
people_dict = {
    "weight": pd.Series([68, 83, 112], index=["alice", "bob", "charles"]),
    "birthyear": pd.Series([1984, 1985, 1992], index=["bob", "alice", "charles"], name="year"),
    "children": pd.Series([0, 3], index=["charles", "bob"]),
    "hobby": pd.Series(["Biking", "Dancing"], index=["alice", "bob"]),
}
people = pd.DataFrame(people_dict)
people
Explanation: DataFrame objects
A DataFrame object represents a spreadsheet, with cell values, column names and row index labels. You can define expressions to compute columns based on other columns, create pivot-tables, group rows, draw graphs, etc. You can see DataFrames as dictionaries of Series.
Creating a DataFrame
You can create a DataFrame by passing a dictionary of Series objects:
End of explanation
people["birthyear"]
Explanation: A few things to note:
* the Series were automatically aligned based on their index,
* missing values are represented as NaN,
* Series names are ignored (the name "year" was dropped),
* DataFrames are displayed nicely in Jupyter notebooks, woohoo!
You can access columns pretty much as you would expect. They are returned as Series objects:
End of explanation
people[["birthyear", "hobby"]]
Explanation: You can also get multiple columns at once:
End of explanation
d2 = pd.DataFrame(
        people_dict,
        columns=["birthyear", "weight", "height"],
        index=["bob", "alice", "eugene"]
     )
d2
Explanation: If you pass a list of columns and/or index row labels to the DataFrame constructor, it will guarantee that these columns and/or rows will exist, in that order, and no other column/row will exist. For example:
End of explanation
values = [
            [1985, np.nan, "Biking",   68],
            [1984, 3,      "Dancing",  83],
            [1992, 0,      np.nan,    112]
         ]
d3 = pd.DataFrame(
        values,
        columns=["birthyear", "children", "hobby", "weight"],
        index=["alice", "bob", "charles"]
     )
d3
Explanation: Another convenient way to create a DataFrame is to pass all the values to the constructor as an ndarray, or a list of lists, and specify the column names and row index labels separately:
End of explanation
masked_array = np.ma.asarray(values, dtype=np.object)
masked_array[(0, 2), (1, 2)] = np.ma.masked
d3 = pd.DataFrame(
        values,
        columns=["birthyear", "children", "hobby", "weight"],
        index=["alice", "bob", "charles"]
     )
d3
Explanation: To specify missing values, you can either use np.nan or NumPy's masked arrays:
End of explanation
d4 = pd.DataFrame(
         d3,
         columns=["hobby", "children"],
         index=["alice", "bob"]
     )
d4
Explanation: Instead of an ndarray, you can also pass a DataFrame object:
End of explanation
people = pd.DataFrame({
    "birthyear": {"alice":1985, "bob": 1984, "charles": 1992},
    "hobby": {"alice":"Biking", "bob": "Dancing"},
    "weight": {"alice":68, "bob": 83, "charles": 112},
    "children": {"bob": 3, "charles": 0}
})
people
Explanation: It is also possible to create a DataFrame with a dictionary (or list) of dictionaries (or list):
End of explanation
d5 = pd.DataFrame(
  {
    ("public", "birthyear"):
        {("Paris","alice"):1985, ("Paris","bob"): 1984, ("London","charles"): 1992},
    ("public", "hobby"):
        {("Paris","alice"):"Biking", ("Paris","bob"): "Dancing"},
    ("private", "weight"):
        {("Paris","alice"):68, ("Paris","bob"): 83, ("London","charles"): 112},
    ("private", "children"):
        {("Paris", "alice"):np.nan, ("Paris","bob"): 3, ("London","charles"): 0}
  }
)
d5
Explanation: Multi-indexing
If all columns are tuples of the same size, then they are understood as a multi-index. The same goes for row index labels. For example:
End of explanation
d5["public"]
d5["public", "hobby"]  # Same result as d4["public"]["hobby"]
Explanation: You can now get a DataFrame containing all the "public" columns very simply:
End of explanation
d5
Explanation: Dropping a level
Let's look at d5 again:
End of explanation
d5.columns = d5.columns.droplevel(level = 0)
d5
Explanation: There are two levels of columns, and two levels of indices. We can drop a column level by calling droplevel (the same goes for indices):
End of explanation
d6 = d5.T
d6
Explanation: Transposing
You can swap columns and indices using the T attribute:
End of explanation
d7 = d6.stack()
d7
Explanation: Stacking and unstacking levels
Calling the stack method will push the lowest column level after the lowest index:
End of explanation
d8 = d7.unstack()
d8
Explanation: Note that many NaN values appeared. This makes sense because many new combinations did not exist before (eg. there was no bob in London).
Calling unstack will do the reverse, once again creating many NaN values.
End of explanation
d9 = d8.unstack()
d9
Explanation: If we call unstack again, we end up with a Series object:
End of explanation
d10 = d9.unstack(level = (0,1))
d10
Explanation: The stack and unstack methods let you select the level to stack/unstack. You can even stack/unstack multiple levels at once:
End of explanation
people
Explanation: Most methods return modified copies
As you may have noticed, the stack and unstack methods do not modify the object they apply to. Instead, they work on a copy and return that copy. This is true of most methods in pandas.
Accessing rows
Let's go back to the people DataFrame:
End of explanation
people.loc["charles"]
Explanation: The loc attribute lets you access rows instead of columns. The result is Series object in which the DataFrame's column names are mapped to row index labels:
End of explanation
people.iloc[2]
Explanation: You can also access rows by integer location using the iloc attribute:
End of explanation
people.iloc[1:3]
Explanation: You can also get a slice of rows, and this returns a DataFrame object:
End of explanation
people[np.array([True, False, True])]
Explanation: Finally, you can pass a boolean array to get the matching rows:
End of explanation
people[people["birthyear"] < 1990]
Explanation: This is most useful when combined with boolean expressions:
End of explanation
people
people["age"] = 2016 - people["birthyear"]  # adds a new column "age"
people["over 30"] = people["age"] > 30      # adds another column "over 30"
birthyears = people.pop("birthyear")
del people["children"]
people
birthyears
Explanation: Adding and removing columns
You can generally treat DataFrame objects like dictionaries of Series, so the following work fine:
End of explanation
people["pets"] = pd.Series({"bob": 0, "charles": 5, "eugene":1})  # alice is missing, eugene is ignored
people
Explanation: When you add a new colum, it must have the same number of rows. Missing rows are filled with NaN, and extra rows are ignored:
End of explanation
people.insert(1, "height", [172, 181, 185])
people
Explanation: When adding a new column, it is added at the end (on the right) by default. You can also insert a column anywhere else using the insert method:
End of explanation
people.assign(
    body_mass_index = people["weight"] / (people["height"] / 100) ** 2,
    has_pets = people["pets"] > 0
)
Explanation: Assigning new columns
You can also create new columns by calling the assign method. Note that this returns a new DataFrame object, the original is not modified:
End of explanation
try:
    people.assign(
        body_mass_index = people["weight"] / (people["height"] / 100) ** 2,
        overweight = people["body_mass_index"] > 25
    )
except KeyError as e:
    print("Key error:", e)
Explanation: Note that you cannot access columns created within the same assignment:
End of explanation
d6 = people.assign(body_mass_index = people["weight"] / (people["height"] / 100) ** 2)
d6.assign(overweight = d6["body_mass_index"] > 25)
Explanation: The solution is to split this assignment in two consecutive assignments:
End of explanation
try:
    (people
         .assign(body_mass_index = people["weight"] / (people["height"] / 100) ** 2)
         .assign(overweight = people["body_mass_index"] > 25)
    )
except KeyError as e:
    print("Key error:", e)
Explanation: Having to create a temporary variable d6 is not very convenient. You may want to just chain the assigment calls, but it does not work because the people object is not actually modified by the first assignment:
End of explanation
(people
     .assign(body_mass_index = lambda df: df["weight"] / (df["height"] / 100) ** 2)
     .assign(overweight = lambda df: df["body_mass_index"] > 25)
)
Explanation: But fear not, there is a simple solution. You can pass a function to the assign method (typically a lambda function), and this function will be called with the DataFrame as a parameter:
End of explanation
people.eval("weight / (height/100) ** 2 > 25")
Explanation: Problem solved!
Evaluating an expression
A great feature supported by pandas is expression evaluation. This relies on the numexpr library which must be installed.
End of explanation
people.eval("body_mass_index = weight / (height/100) ** 2")
people
Explanation: Assignment expressions are also supported, and contrary to the assign method, this does not create a copy of the DataFrame, instead it directly modifies it:
End of explanation
overweight_threshold = 30
people.eval("overweight = body_mass_index > @overweight_threshold")
people
Explanation: You can use a local or global variable in an expression by prefixing it with '@':
End of explanation
people.query("age > 30 and pets == 0")
Explanation: Querying a DataFrame
The query method lets you filter a DataFrame based on a query expression:
End of explanation
people.sort_index(ascending=False)
Explanation: Sorting a DataFrame
You can sort a DataFrame by calling its sort_index method. By default it sorts the rows by their index label, in ascending order, but let's reverse the order:
End of explanation
people.sort_index(axis=1, inplace=True)
people
Explanation: Note that sort_index returned a sorted copy of the DataFrame. To modify people directly, we can set the inplace argument to True. Also, we can sort the columns instead of the rows by setting axis=1:
End of explanation
people.sort_values(by="age", inplace=True)
people
Explanation: To sort the DataFrame by the values instead of the labels, we can use sort_values and specify the column to sort by:
End of explanation
people.plot(kind = "line", x = "body_mass_index", y = ["height", "weight"])
plt.show()
Explanation: Plotting a DataFrame
Just like for Series, pandas makes it easy to draw nice graphs based on a DataFrame.
For example, it is trivial to create a line plot from a DataFrame's data by calling its plot method:
End of explanation
people.plot(kind = "scatter", x = "height", y = "weight", s=[40, 120, 200])
plt.show()
Explanation: You can pass extra arguments supported by matplotlib's functions. For example, we can create scatterplot and pass it a list of sizes using the s argument of matplotlib's scatter function:
End of explanation
grades_array = np.array([[8,8,9],[10,9,9],[4, 8, 2], [9, 10, 10]])
grades = pd.DataFrame(grades_array, columns=["sep", "oct", "nov"], index=["alice","bob","charles","darwin"])
grades
Explanation: Again, there are way too many options to list here: the best option is to scroll through the Visualization page in pandas' documentation, find the plot you are interested in and look at the example code.
Operations on DataFrames
Although DataFrames do not try to mimick NumPy arrays, there are a few similarities. Let's create a DataFrame to demonstrate this:
End of explanation
np.sqrt(grades)
Explanation: You can apply NumPy mathematical functions on a DataFrame: the function is applied to all values:
End of explanation
grades + 1
Explanation: Similarly, adding a single value to a DataFrame will add that value to all elements in the DataFrame. This is called broadcasting:
End of explanation
grades >= 5
Explanation: Of course, the same is true for all other binary operations, including arithmetic (*,/,**...) and conditional (&gt;, ==...) operations:
End of explanation
grades.mean()
Explanation: Aggregation operations, such as computing the max, the sum or the mean of a DataFrame, apply to each column, and you get back a Series object:
End of explanation
(grades > 5).all()
Explanation: The all method is also an aggregation operation: it checks whether all values are True or not. Let's see during which months all students got a grade greater than 5:
End of explanation
(grades > 5).all(axis = 1)
Explanation: Most of these functions take an optional axis parameter which lets you specify along which axis of the DataFrame you want the operation executed. The default is axis=0, meaning that the operation is executed vertically (on each column). You can set axis=1 to execute the operation horizontally (on each row). For example, let's find out which students had all grades greater than 5:
End of explanation
(grades == 10).any(axis = 1)
Explanation: The any method returns True if any value is True. Let's see who got at least one grade 10:
End of explanation
grades - grades.mean()  # equivalent to: grades - [7.75, 8.75, 7.50]
Explanation: If you add a Series object to a DataFrame (or execute any other binary operation), pandas attempts to broadcast the operation to all rows in the DataFrame. This only works if the Series has the same size as the DataFrames rows. For example, let's substract the mean of the DataFrame (a Series object) from the DataFrame:
End of explanation
pd.DataFrame([[7.75, 8.75, 7.50]]*4, index=grades.index, columns=grades.columns)
Explanation: We substracted 7.75 from all September grades, 8.75 from October grades and 7.50 from November grades. It is equivalent to substracting this DataFrame:
End of explanation
grades - grades.values.mean() # substracts the global mean (8.00) from all grades
Explanation: If you want to substract the global mean from every grade, here is one way to do it:
End of explanation
bonus_array = np.array([[0,np.nan,2],[np.nan,1,0],[0, 1, 0], [3, 3, 0]])
bonus_points = pd.DataFrame(bonus_array, columns=["oct", "nov", "dec"], index=["bob","colin", "darwin", "charles"])
bonus_points
grades + bonus_points
Explanation: Automatic alignment
Similar to Series, when operating on multiple DataFrames, pandas automatically aligns them by row index label, but also by column names. Let's create a DataFrame with bonus points for each person from October to December:
End of explanation
(grades + bonus_points).fillna(0)
Explanation: Looks like the addition worked in some cases but way too many elements are now empty. That's because when aligning the DataFrames, some columns and rows were only present on one side, and thus they were considered missing on the other side (NaN). Then adding NaN to a number results in NaN, hence the result.
Handling missing data
Dealing with missing data is a frequent task when working with real life data. Pandas offers a few tools to handle missing data.
Let's try to fix the problem above. For example, we can decide that missing data should result in a zero, instead of NaN. We can replace all NaN values by a any value using the fillna method:
End of explanation
fixed_bonus_points = bonus_points.fillna(0)
fixed_bonus_points.insert(0, "sep", 0)
fixed_bonus_points.loc["alice"] = 0
grades + fixed_bonus_points
Explanation: It's a bit unfair that we're setting grades to zero in September, though. Perhaps we should decide that missing grades are missing grades, but missing bonus points should be replaced by zeros:
End of explanation
bonus_points
Explanation: That's much better: although we made up some data, we have not been too unfair.
Another way to handle missing data is to interpolate. Let's look at the bonus_points DataFrame again:
End of explanation
bonus_points.interpolate(axis=1)
Explanation: Now let's call the interpolate method. By default, it interpolates vertically (axis=0), so let's tell it to interpolate horizontally (axis=1).
End of explanation
better_bonus_points = bonus_points.copy()
better_bonus_points.insert(0, "sep", 0)
better_bonus_points.loc["alice"] = 0
better_bonus_points = better_bonus_points.interpolate(axis=1)
better_bonus_points
Explanation: Bob had 0 bonus points in October, and 2 in December. When we interpolate for November, we get the mean: 1 bonus point. Colin had 1 bonus point in November, but we do not know how many bonus points he had in September, so we cannot interpolate, this is why there is still a missing value in October after interpolation. To fix this, we can set the September bonus points to 0 before interpolation.
End of explanation
grades + better_bonus_points
Explanation: Great, now we have reasonable bonus points everywhere. Let's find out the final grades:
End of explanation
grades["dec"] = np.nan
final_grades = grades + better_bonus_points
final_grades
Explanation: It is slightly annoying that the September column ends up on the right. This is because the DataFrames we are adding do not have the exact same columns (the grades DataFrame is missing the "dec" column), so to make things predictable, pandas orders the final columns alphabetically. To fix this, we can simply add the missing column before adding:
End of explanation
final_grades_clean = final_grades.dropna(how="all")
final_grades_clean
Explanation: There's not much we can do about December and Colin: it's bad enough that we are making up bonus points, but we can't reasonably make up grades (well I guess some teachers probably do). So let's call the dropna method to get rid of rows that are full of NaNs:
End of explanation
final_grades_clean = final_grades_clean.dropna(axis=1, how="all")
final_grades_clean
Explanation: Now let's remove columns that are full of NaNs by setting the axis argument to 1:
End of explanation
final_grades["hobby"] = ["Biking", "Dancing", np.nan, "Dancing", "Biking"]
final_grades
Explanation: Aggregating with groupby
Similar to the SQL language, pandas allows grouping your data into groups to run calculations over each group.
First, let's add some extra data about each person so we can group them, and let's go back to the final_grades DataFrame so we can see how NaN values are handled:
End of explanation
grouped_grades = final_grades.groupby("hobby")
grouped_grades
Explanation: Now let's group data in this DataFrame by hobby:
End of explanation
grouped_grades.mean()
Explanation: We are ready to compute the average grade per hobby:
End of explanation
bonus_points
more_grades = final_grades_clean.stack().reset_index()
more_grades.columns = ["name", "month", "grade"]
more_grades["bonus"] = [np.nan, np.nan, np.nan, 0, np.nan, 2, 3, 3, 0, 0, 1, 0]
more_grades
Explanation: That was easy! Note that the NaN values have simply been skipped when computing the means.
Pivot tables
Pandas supports spreadsheet-like pivot tables that allow quick data summarization. To illustrate this, let's create a simple DataFrame:
End of explanation
pd.pivot_table(more_grades, index="name")
Explanation: Now we can call the pivot_table function for this DataFrame, asking to group by the name column. By default, pivot_table computes the mean of each numeric column:
End of explanation
pd.pivot_table(more_grades, index="name", values=["grade","bonus"], aggfunc=np.max)
Explanation: We can change the aggregation function by setting the aggfunc attribute, and we can also specify the list of columns whose values will be aggregated:
End of explanation
pd.pivot_table(more_grades, index="name", values="grade", columns="month", margins=True)
Explanation: We can also specify the columns to aggregate over horizontally, and request the grand totals for each row and column by setting margins=True:
End of explanation
pd.pivot_table(more_grades, index=("name", "month"), margins=True)
Explanation: Finally, we can specify multiple index or column names, and pandas will create multi-level indices:
End of explanation
much_data = np.fromfunction(lambda x,y: (x+y*y)%17*11, (10000, 26))
large_df = pd.DataFrame(much_data, columns=list("ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
large_df[large_df % 16 == 0] = np.nan
large_df.insert(3,"some_text", "Blabla")
large_df
Explanation: Overview functions
When dealing with large DataFrames, it is useful to get a quick overview of its content. Pandas offers a few functions for this. First, let's create a large DataFrame with a mix of numeric values, missing values and text values. Notice how Jupyter displays only the corners of the DataFrame:
End of explanation
large_df.head()
Explanation: The head method returns the top 5 rows:
End of explanation
large_df.tail(n=2)
Explanation: Of course there's also a tail function to view the bottom 5 rows. You can pass the number of rows you want:
End of explanation
large_df.info()
Explanation: The info method prints out a summary of each columns contents:
End of explanation
large_df.describe()
Explanation: Finally, the describe method gives a nice overview of the main aggregated values over each column:
* count: number of non-null (not NaN) values
* mean: mean of non-null values
* std: standard deviation of non-null values
* min: minimum of non-null values
* 25%, 50%, 75%: 25th, 50th and 75th percentile of non-null values
* max: maximum of non-null values
End of explanation
my_df = pd.DataFrame(
    [["Biking", 68.5, 1985, np.nan], ["Dancing", 83.1, 1984, 3]], 
    columns=["hobby","weight","birthyear","children"],
    index=["alice", "bob"]
)
my_df
Explanation: Saving & loading
Pandas can save DataFrames to various backends, including file formats such as CSV, Excel, JSON, HTML and HDF5, or to a SQL database. Let's create a DataFrame to demonstrate this:
End of explanation
my_df.to_csv("my_df.csv")
my_df.to_html("my_df.html")
my_df.to_json("my_df.json")
Explanation: Saving
Let's save it to CSV, HTML and JSON:
End of explanation
for filename in ("my_df.csv", "my_df.html", "my_df.json"):
    print("#", filename)
    with open(filename, "rt") as f:
        print(f.read())
        print()
Explanation: Done! Let's take a peek at what was saved:
End of explanation
try:
    my_df.to_excel("my_df.xlsx", sheet_name='People')
except ImportError as e:
    print(e)
Explanation: Note that the index is saved as the first column (with no name) in a CSV file, as &lt;th&gt; tags in HTML and as keys in JSON.
Saving to other formats works very similarly, but some formats require extra libraries to be installed. For example, saving to Excel requires the openpyxl library:
End of explanation
my_df_loaded = pd.read_csv("my_df.csv", index_col=0)
my_df_loaded
Explanation: Loading
Now let's load our CSV file back into a DataFrame:
End of explanation
us_cities = None
try:
    csv_url = "http://simplemaps.com/files/cities.csv"
    us_cities = pd.read_csv(csv_url, index_col=0)
    us_cities = us_cities.head()
except IOError as e:
    print(e)
us_cities
Explanation: As you might guess, there are similar read_json, read_html, read_excel functions as well.  We can also read data straight from the Internet. For example, let's load all U.S. cities from simplemaps.com:
End of explanation
city_loc = pd.DataFrame(
    [
        ["CA", "San Francisco", 37.781334, -122.416728],
        ["NY", "New York", 40.705649, -74.008344],
        ["FL", "Miami", 25.791100, -80.320733],
        ["OH", "Cleveland", 41.473508, -81.739791],
        ["UT", "Salt Lake City", 40.755851, -111.896657]
    ], columns=["state", "city", "lat", "lng"])
city_loc
city_pop = pd.DataFrame(
    [
        [808976, "San Francisco", "California"],
        [8363710, "New York", "New-York"],
        [413201, "Miami", "Florida"],
        [2242193, "Houston", "Texas"]
    ], index=[3,4,5,6], columns=["population", "city", "state"])
city_pop
Explanation: There are more options available, in particular regarding datetime format. Check out the documentation for more details.
Combining DataFrames
SQL-like joins
One powerful feature of pandas is it's ability to perform SQL-like joins on DataFrames. Various types of joins are supported: inner joins, left/right outer joins and full joins. To illustrate this, let's start by creating a couple simple DataFrames:
End of explanation
pd.merge(left=city_loc, right=city_pop, on="city")
Explanation: Now let's join these DataFrames using the merge function:
End of explanation
all_cities = pd.merge(left=city_loc, right=city_pop, on="city", how="outer")
all_cities
Explanation: Note that both DataFrames have a column named state, so in the result they got renamed to state_x and state_y.
Also, note that Cleveland, Salt Lake City and Houston were dropped because they don't exist in both DataFrames. This is the equivalent of a SQL INNER JOIN. If you want a FULL OUTER JOIN, where no city gets dropped and NaN values are added, you must specify how="outer":
End of explanation
pd.merge(left=city_loc, right=city_pop, on="city", how="right")
Explanation: Of course LEFT OUTER JOIN is also available by setting how="left": only the cities present in the left DataFrame end up in the result. Similarly, with how="right" only cities in the right DataFrame appear in the result. For example:
End of explanation
city_pop2 = city_pop.copy()
city_pop2.columns = ["population", "name", "state"]
pd.merge(left=city_loc, right=city_pop2, left_on="city", right_on="name")
Explanation: If the key to join on is actually in one (or both) DataFrame's index, you must use left_index=True and/or right_index=True. If the key column names differ, you must use left_on and right_on. For example:
End of explanation
result_concat = pd.concat([city_loc, city_pop])
result_concat
Explanation: Concatenation
Rather than joining DataFrames, we may just want to concatenate them. That's what concat is for:
End of explanation
result_concat.loc[3]
Explanation: Note that this operation aligned the data horizontally (by columns) but not vertically (by rows). In this example, we end up with multiple rows having the same index (eg. 3). Pandas handles this rather gracefully:
End of explanation
pd.concat([city_loc, city_pop], ignore_index=True)
Explanation: Or you can tell pandas to just ignore the index:
End of explanation
pd.concat([city_loc, city_pop], join="inner")
Explanation: Notice that when a column does not exist in a DataFrame, it acts as if it was filled with NaN values. If we set join="inner", then only columns that exist in both DataFrames are returned:
End of explanation
pd.concat([city_loc, city_pop], axis=1)
Explanation: You can concatenate DataFrames horizontally instead of vertically by setting axis=1:
End of explanation
pd.concat([city_loc.set_index("city"), city_pop.set_index("city")], axis=1)
Explanation: In this case it really does not make much sense because the indices do not align well (eg. Cleveland and San Francisco end up on the same row, because they shared the index label 3). So let's reindex the DataFrames by city name before concatenating:
End of explanation
city_loc.append(city_pop)
Explanation: This looks a lot like a FULL OUTER JOIN, except that the state columns were not renamed to state_x and state_y, and the city column is now the index.
The append method is a useful shorthand for concatenating DataFrames vertically:
End of explanation
city_eco = city_pop.copy()
city_eco["eco_code"] = [17, 17, 34, 20]
city_eco
Explanation: As always in pandas, the append method does not actually modify city_loc: it works on a copy and returns the modified copy.
Categories
It is quite frequent to have values that represent categories, for example 1 for female and 2 for male, or "A" for Good, "B" for Average, "C" for Bad. These categorical values can be hard to read and cumbersome to handle, but fortunately pandas makes it easy. To illustrate this, let's take the city_pop DataFrame we created earlier, and add a column that represents a category:
End of explanation
city_eco["economy"] = city_eco["eco_code"].astype('category')
city_eco["economy"].cat.categories
Explanation: Right now the eco_code column is full of apparently meaningless codes. Let's fix that. First, we will create a new categorical column based on the eco_codes:
End of explanation
city_eco["economy"].cat.categories = ["Finance", "Energy", "Tourism"]
city_eco
Explanation: Now we can give each category a meaningful name:
End of explanation
city_eco.sort_values(by="economy", ascending=False)
Explanation: Note that categorical values are sorted according to their categorical order, not their alphabetical order:
End of explanation
<END_TASK>