<jupyter_start><jupyter_text>Software Carpentry with Python: Part 2 Data wrangling with the pandas libraryFor November 21, 2019Data needs to be downloaded at:https://go.gwu.edu/pythondatahttps://swcarpentry.github.io/python-novice-gapminder/files/python-novice-gapminder-data.zip Starting in the same spot:Remember, we're in the python-lesson directory. 1. Let's create a New > Folder. Click the checkbox and Rename it: data. 2. Then click to go into it. 2. We need to put our gapminder data here. You should have already downloaded that file as part of the set-up. If not, go to https://go.gwu.edu/gapminder and download it now. Unzip it!3. Click Upload and upload the unzipped data file. We're setting things up this was so that we all have the same file structure and can follow along. Also, this is generally a good practice, to create a data folder and put your original data files in there. A quick aside that there are Python libraries like OS Library that can work with our directory structure, however, that is not our focus today.Lessons used: Software Carpentry: https://swcarpentry.github.io/python-novice-gapminder/08-data-frames/index.html Why use Python for data analysis? * We can automate the process of performing data manipulations in Python. * It’s efficient to spend time building the code to perform these tasks because once it’s built, we can use it over and over on different datasets that use a similar format. * This makes our methods easily reproducible. We can also easily share our code with colleagues and they can replicate the same analysis.  Working With Pandas DataFramesOne of the best options for working with tabular data in Python is to use the Python library pandas. The pandas library provides data structures, produces high quality plots with matplotlib and integrates nicely with other libraries that use NumPy (which is another Python library) arrays.**Python doesn’t load all of the libraries available to it by default.** We have to add an import statement to our code in order to use library functions. To import a library, we use the syntax import libraryName. If we want to give the library a nickname to shorten the command, we can add **as nickNameHere**. An example of importing the pandas library using the common nickname pd is below.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Reading CSV Data Using PandasWe will begin by locating and reading our data which are in CSV format. CSV stands for Comma-Separated Values and is a common way store formatted data. We can use Pandas’ read_csv function to pull the file directly into a DataFrame.<jupyter_code>pd.read_csv('data/gapminder_all.csv')<jupyter_output><empty_output><jupyter_text>This output is the rows in our CSV file, now as a pandas DataFrame object. The first column is the index of the DataFrame. The index is used to identify the position of the data, but it is not an actual column of the DataFrame. It looks like the read_csv function in Pandas read our file properly. However, we haven’t saved any data to memory so we can work with it. We need to assign the DataFrame to a variable. Remember that a variable is a name for a value, such as x, or data. We can create a new object with a variable name by assigning a value to it using =.<jupyter_code>data = pd.read_csv("data/gapminder_all.csv", index_col="country")<jupyter_output><empty_output><jupyter_text>There are many ways to summarize and access the data stored in DataFrames, using attributes and methods provided by the DataFrame object.Methods are called on a DataFrame object using the syntax df_object.method(). As an example, `data.head()` gets the first few rows in the DataFrame surveys_df using the head() method. With a method, we can supply extra information in the parens to control behaviour.<jupyter_code># nothing in parens defaults to first 5 rows
data.head()
type(data)
data.dtypes<jupyter_output><empty_output><jupyter_text>The DataFrame.columns variable stores information about the dataframe’s columns. Note that this is data, not a method. Like math.pi. So do not use () to try to call it.<jupyter_code>data.columns
data.describe()<jupyter_output><empty_output><jupyter_text>Indexing and Slicing in PythonWe often want to work with subsets of a DataFrame object. There are different ways to accomplish this including: using:* labels (column headings)* numeric ranges* specific x,y index locations. Selecting data using Labels (Column Headings)We use square brackets [] to select a subset of a Python object. For example, we can select all data from a column named lifeExp2007 from the data DataFrame by name. There are two ways to do this:<jupyter_code># Method 1: select a 'subset' of the data using the column name
data['lifeExp_2007']
# Method 2: use the column name as an 'attribute'; gives the same output
data.lifeExp_2007<jupyter_output><empty_output><jupyter_text>A DataFrame is a collection of Series; The DataFrame is the way Pandas represents a table, and Series is the data-structure Pandas use to represent a column.What we did above by taking a column was creating a Series.Pandas is built on top of the Numpy library, which in practice means that most of the methods defined for Numpy Arrays apply to Pandas Series/DataFrames.What makes Pandas so attractive is the powerful interface to access individual records of the table, proper handling of missing values, and relational-databases operations between DataFrames.  Selecting using slices of rows Slicing using the `[]` operator selects a set of rows and/or columns from a DataFrame, not counting the labels. To slice out a set of rows, you use the following syntax: data[start:stop]. When slicing in pandas the start bound is included in the output. The stop bound is one step BEYOND the row you want to select. So if you want to select rows 0, 1 and 2 your code would look like this:<jupyter_code>data[0:3]
# Can also leave out the 0
data[:3]<jupyter_output><empty_output><jupyter_text>Selecting valuesTo access a value at the position row i, column j [i,j] of a DataFrame, we have two options, depending on what is the meaning of i in use. Remember that a DataFrame provides a index as a way to identify the rows of the table; a row, then, has a position inside the table as well as a label, which uniquely identifies its entry in the DataFrame.Use DataFrame.loc[..., ...] to select values by their label.Use `: ` on its own to mean all columns or all rows<jupyter_code>data.loc["Algeria", "gdpPercap_1952"]<jupyter_output><empty_output><jupyter_text>Use `DataFrame.iloc[..., ...]` to select values by their (entry) positionCan specify location by numerical index analogously to 2D version of character selection in strings. (Or items in lists). The labels aren't included in the counting, they apply to the data.<jupyter_code>data.iloc[0,0]<jupyter_output><empty_output><jupyter_text>Let's do the same thing to get at the gdp in 1952 for Algeria:<jupyter_code>data.iloc[0, 1]<jupyter_output><empty_output><jupyter_text>**Exercise:**Practice Series and slicing.1) Get the lifeExp for all countries in 1992 and assign it to a variable called lifeExp1992. 2) Get the GDP in 1962 for New Zealand using multiple methods of slicing. Remember we can use .tail() to see the end of the data. **Answer 1**<jupyter_code>lifeExp1992 = data["lifeExp_1992"]
print(lifeExp1992)<jupyter_output>country
Algeria           67.744
Angola            40.647
Benin             53.919
Botswana          62.745
Burkina Faso      50.260
                   ...  
Switzerland       78.030
Turkey            66.146
United Kingdom    76.420
Australia         77.560
New Zealand       76.330
Name: lifeExp_1992, Length: 142, dtype: float64<jupyter_text>**Answer 2**<jupyter_code>data.loc["New Zealand", "gdpPercap_1962"]
#There are 142 rows in our dataframe, however, since we count starting with zero, the last row is 141. 
data.iloc[141,3]

# answer is 13175.678000
#help(pd.DataFrame.loc)<jupyter_output><empty_output><jupyter_text>**Using labels on multiple rows and columns:**To slice the life expectancy from 1992, 2002, 2007, for all of the countries:Specify the rows we want and then the columns we want.<jupyter_code>data.loc[:,["lifeExp_1992","lifeExp_2002","lifeExp_2007"]]
# A list of the labels of the columns we want.

data.loc[["Benin", "Turkey", "Afghanistan"],["lifeExp_1992","lifeExp_2002","lifeExp_2007"]]<jupyter_output><empty_output><jupyter_text>Creating slices lets us then use methods on those subsets. For example, we earlier grabbed just the column that had the lifeExp for 1992. We can find the max and min for that slice as follows:<jupyter_code>lifeExp1992 = data["lifeExp_1992"]
print(lifeExp1992.min())
print(lifeExp1992.max())<jupyter_output>23.599
79.36<jupyter_text>23.599 is Rwanda79.36 is Japan When pandas selects a single column from a DataFrame, pandas creates a view and not a copy. A view just means that no new object has been created. No new object is created, just a new reference to the one already in existence. Since no new data has been created, the assignment will modify the original DataFrame.  Subsetting Data Using CriteriaWe can also select a subset of our data using criteria.<jupyter_code>data[data.lifeExp_2007 > 80]<jupyter_output><empty_output><jupyter_text>Quick review of conditions: `==  != > = <= ` **Exercise 5**Create a subset of the data that contains rows for countries where the GDP per capita in 2007 was less than $1000<jupyter_code>data[data.gdpPercap_2007 <= 1000]<jupyter_output><empty_output><jupyter_text>Using a mask to identify a specific condition.A mask can be useful to locate where a particular subset of values exist or don't exist, for example, NaN or "not a number". Comparison or function is applied element by element. Returns a similarly-shaped dataframe of True and False.Boolean is a Python data type, True or False. False is Python's way of saying "No."<jupyter_code>x = 1
x > 5<jupyter_output><empty_output><jupyter_text>There is a way with pandas' methods to check for null values, (missing data or NaN).<jupyter_code>pd.isnull(data)<jupyter_output><empty_output><jupyter_text>This is very clean data, all data is here, none of the cells have a value of True, isnull? = False.Let's confirm by taking a closer look, applying a filter to the data. using the any() method, which looks for only True values.<jupyter_code>data[pd.isnull(data).any(axis=1)]<jupyter_output><empty_output><jupyter_text>Group By: split-apply-combinePandas vectorizing methods and grouping operations are features that provide users much flexibility to analyse their data.For instance, let’s say we want to have a clearer view on how the European countries split according to their GDP.We can split the countries in two groups during the years surveyed, those who presented a GDP higher than the European average and those with a lower GDP.Remember we can use methods like .mean() on a dataframe. .mean() is calculated per column.<jupyter_code># first create a new DataFrame that is a subset, just those countries in the continent of Europe.
europe_df = data[data.continent == "Europe"].copy()
europe_df.head()<jupyter_output><empty_output><jupyter_text>Looks like we still have ALL of the columns, not just GDP, so let's further slice the df. Going to use `.iloc` because there are a dozen columns out of the 37 we want and I don't want to list them all.<jupyter_code># overwrite our existing dataframe, and use iloc to get all rows, just the columns with the GDP per capita variables.
europe_df = europe_df.iloc[:,1:13]
europe_df.head()<jupyter_output><empty_output><jupyter_text>We can use mean() to get the mean of each column.<jupyter_code>europe_df.mean()<jupyter_output><empty_output><jupyter_text>What is the result of taking mean() on a whole DataFrame?<jupyter_code>type(europe_df.mean())<jupyter_output><empty_output><jupyter_text>Now we know the average GDP for each of the years. Next is to figure out whether each country's GDP is over the mean, by creating a boolean mask like we did earlier. Remember the mask is a DataFrame<jupyter_code>mask = europe_df > europe_df.mean()
mask_higher = europe_df > europe_df.mean()
mask_higher.head()<jupyter_output><empty_output><jupyter_text>We then estimate a wealthy score based on the historical (from 1962 to 2007) values, where we count how many times a country has participated in the groups of lower or higher GDP. So, need to count how many Trues there are in each row.We can use the **aggregate()** method to count (or sum), and then use axis=1 because we're applying this horizontally, across the all of the columns in a row. Axis = 0 is often the default in pandas and that applies something down a column.<jupyter_code>wealth_score = mask_higher.aggregate('sum', axis=1) / len(europe_df.columns)
wealth_score
type(wealth_score)<jupyter_output><empty_output><jupyter_text>We can now add this back to our dataframe since it's a pandas Series and it has the same index. (They'll be able to matched up)<jupyter_code>europe_df["wealth_score"] = wealth_score
europe_df.head()<jupyter_output><empty_output><jupyter_text>Groupby() We often want to calculate summary statistics grouped by subsets or attributes within fields of our data. For example, we might want to calculate the average life expectancy in a particular year. Remember we've done this on a column using the describe() method. We can calculate basic statistics for all records in a single column using the syntax below:<jupyter_code>data["lifeExp_2007"].describe()<jupyter_output><empty_output><jupyter_text>But if we want to summarize by one or more variables, for example continent, and then apply statistics. So we can use Pandas’ .groupby() method. Once we’ve created a groupby DataFrame, we can quickly calculate summary statistics by a group of our choice.<jupyter_code>grouped_data = data.copy()
grouped_data = grouped_data.groupby("continent")
type(grouped_data)<jupyter_output><empty_output><jupyter_text>We can now look at descriptive statistics for each of the columns in the original DataFrame, grouped by continent.<jupyter_code>grouped_data.describe()<jupyter_output><empty_output><jupyter_text>We can look at a specific statistic, mean, applied across the groupby DataFrame:<jupyter_code>grouped_data.mean()<jupyter_output><empty_output><jupyter_text>Now let's look at a specific column.<jupyter_code>grouped_data["lifeExp_2007"].describe()<jupyter_output><empty_output><jupyter_text>Backup content on Transforms.<jupyter_code>data2 = data.T
data2.head()<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup