<jupyter_start><jupyter_text>DataFrames (Pandas)  Note about Pandas DataFrames/Series A [DataFrame](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html) is a collection of [Series](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.html); The DataFrame is the way Pandas represents a table, and Series is the data-structure Pandas use to represent a column.Pandas is built on top of the [Numpy](http://www.numpy.org/) 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 values To access a value at the position `[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.iloc[..., ...]` to select values by their (entry) position * Can specify location by numerical index analogously to 2D version of character selection in strings.<jupyter_code>import pandas
data = pandas.read_csv('data/gapminder_gdp_europe.csv', index_col='country')
print(data.iloc[0, 0])<jupyter_output>1601.056136<jupyter_text>Use `DataFrame.loc[..., ...]` to select values by their (entry) label. * Can specify location by row name analogously to 2D version of dictionary keys.<jupyter_code>data = pandas.read_csv('data/gapminder_gdp_europe.csv', index_col='country')
print(data.loc["Albania", "gdpPercap_1952"])<jupyter_output>1601.056136<jupyter_text>Use `:` on its own to mean all columns or all rows. * Just like Python's usual slicing notation.<jupyter_code>print(data.loc["Albania", :])<jupyter_output>gdpPercap_1952    1601.056136
gdpPercap_1957    1942.284244
gdpPercap_1962    2312.888958
gdpPercap_1967    2760.196931
gdpPercap_1972    3313.422188
gdpPercap_1977    3533.003910
gdpPercap_1982    3630.880722
gdpPercap_1987    3738.932735
gdpPercap_1992    2497.437901
gdpPercap_1997    3193.054604
gdpPercap_2002    4604.211737
gdpPercap_2007    5937.029526
Name: Albania, dtype: float64<jupyter_text>* Would get the same result printing `data.loc["Albania"]` (without a second index).<jupyter_code>print(data.loc[:, "gdpPercap_1952"])<jupyter_output>country
Albania                    1601.056136
Austria                    6137.076492
Belgium                    8343.105127
Bosnia and Herzegovina      973.533195
Bulgaria                   2444.286648
Croatia                    3119.236520
Czech Republic             6876.140250
Denmark                    9692.385245
Finland                    6424.519071
France                     7029.809327
Germany                    7144.114393
Greece                     3530.690067
Hungary                    5263.673816
Iceland                    7267.688428
Ireland                    5210.280328
Italy                      4931.404155
Montenegro                 2647.585601
Netherlands                8941.571858
Norway                    10095.421720
Poland                     4029.329699
Portugal                   3068.319867
Romania                    3144.613186
Serbia                     3581.459448
Slovak Republic            5074.659104
Slovenia                   4215.041741
Spain            [...]<jupyter_text>* Would get the same result printing `data["gdpPercap_1952"]`* Also get the same result printing `data.gdpPercap_1952` (since it's a column name)  Select multiple columns or rows using `DataFrame.loc` and a named slice.<jupyter_code>print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'])<jupyter_output>gdpPercap_1962  gdpPercap_1967  gdpPercap_1972
country                                                    
Italy           8243.582340    10022.401310    12269.273780
Montenegro      4649.593785     5907.850937     7778.414017
Netherlands    12790.849560    15363.251360    18794.745670
Norway         13450.401510    16361.876470    18965.055510
Poland          5338.752143     6557.152776     8006.506993<jupyter_text>In the above code, we discover that **slicing using `loc` is inclusive at both ends**, which differs from **slicing using `iloc`**, where slicing indicates everything up to but not including the final index.  Result of slicing can be used in further operations. * Usually don't just print a slice.* All the statistical operators that work on entire dataframes work the same way on slices.* E.g., calculate max of a slice.<jupyter_code>print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].max())
print(data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972'].min())<jupyter_output>gdpPercap_1962    4649.593785
gdpPercap_1967    5907.850937
gdpPercap_1972    7778.414017
dtype: float64<jupyter_text>Use comparisons to select data based on value. * Comparison is applied element by element.* Returns a similarly-shaped dataframe of `True` and `False`.<jupyter_code># Use a subset of data to keep output readable.
subset = data.loc['Italy':'Poland', 'gdpPercap_1962':'gdpPercap_1972']
print('Subset of data:\n', subset)

# Which values were greater than 10000 ?
print('\nWhere are values large?\n', subset > 10000)<jupyter_output>Subset of data:
              gdpPercap_1962  gdpPercap_1967  gdpPercap_1972
country                                                    
Italy           8243.582340    10022.401310    12269.273780
Montenegro      4649.593785     5907.850937     7778.414017
Netherlands    12790.849560    15363.251360    18794.745670
Norway         13450.401510    16361.876470    18965.055510
Poland          5338.752143     6557.152776     8006.506993

Where are values large?
              gdpPercap_1962  gdpPercap_1967  gdpPercap_1972
country                                                    
Italy                 False            True            True
Montenegro            False           False           False
Netherlands            True            True            True
Norway                 True            True            True
Poland                False           False           False<jupyter_text>Select values or `NaN` using a Boolean mask. * A frame full of Booleans is sometimes called a *mask* because of how it can be used.<jupyter_code>mask = subset > 10000
print(subset[mask])<jupyter_output>gdpPercap_1962  gdpPercap_1967  gdpPercap_1972
country                                                    
Italy                   NaN     10022.40131     12269.27378
Montenegro              NaN             NaN             NaN
Netherlands     12790.84956     15363.25136     18794.74567
Norway          13450.40151     16361.87647     18965.05551
Poland                  NaN             NaN             NaN<jupyter_text>* Get the value where the mask is true, and NaN (Not a Number) where it is false.* Useful because NaNs are ignored by operations like max, min, average, etc.<jupyter_code>print(subset[subset > 10000].describe())<jupyter_output>gdpPercap_1962  gdpPercap_1967  gdpPercap_1972
count        2.000000        3.000000        3.000000
mean     13120.625535    13915.843047    16676.358320
std        466.373656     3408.589070     3817.597015
min      12790.849560    10022.401310    12269.273780
25%      12955.737547    12692.826335    15532.009725
50%      13120.625535    15363.251360    18794.745670
75%      13285.513523    15862.563915    18879.900590
max      13450.401510    16361.876470    18965.055510<jupyter_text>Select-Apply-Combine operations Pandas 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 themselves according to their GDP.1. We may have a glance by splitting 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.2. We then estimate a *wealthy score* based on the historical (from 1962 to 2007) values, where we account how many times a country has participated in the groups of *lower* or *higher* GDP<jupyter_code>mask_higher = data.apply(lambda x:x>x.mean())
wealth_score = mask_higher.aggregate('sum',axis=1)/len(data.columns)
wealth_score<jupyter_output><empty_output><jupyter_text>Finally, for each group in the `wealth_score` table, we sum their (financial) contribution across the years surveyed:<jupyter_code>data.groupby(wealth_score).sum()<jupyter_output><empty_output><jupyter_text>Questions  Q1: Selection of Individual Values Assume Pandas has been imported into your notebook and the Gapminder GDP data for Europe has been loaded:<jupyter_code>import pandas

df = pandas.read_csv('data/gapminder_gdp_europe.csv', index_col='country')<jupyter_output><empty_output><jupyter_text>Write an expression to find the Per Capita GDP of Serbia in 2007. **Solution**Click on the '...' below to show the solution.<jupyter_code># The selection can be done by using the labels for both the row ("Serbia") and 
# the column ("gdpPercap_2007"):

print(df.loc['Serbia', 'gdpPercap_2007'])

# The output is:

9786.534714<jupyter_output><empty_output><jupyter_text>Q2: Extent of Slicing 1. Do the two statements below produce the same output?2. Based on this, what rule governs what is included (or not) in numerical slices and named slices in Pandas?<jupyter_code>print(data.iloc[0:2, 0:2])
print(data.loc['Albania':'Belgium', 'gdpPercap_1952':'gdpPercap_1962'])<jupyter_output><empty_output><jupyter_text>**Solution**Click on the '...' below to show the solution.<jupyter_code># No, they do not produce the same output! The output of the first statement is:

        gdpPercap_1952  gdpPercap_1957
country                                
Albania     1601.056136     1942.284244
Austria     6137.076492     8842.598030

# The second statement gives:

        gdpPercap_1952  gdpPercap_1957  gdpPercap_1962
country                                                
Albania     1601.056136     1942.284244     2312.888958
Austria     6137.076492     8842.598030    10750.721110
Belgium     8343.105127     9714.960623    10991.206760

# Clearly, the second statement produces an additional column and an additional 
# row compared to the first statement.  What conclusion can we draw? We see that 
# a numerical slice, 0:2, omits the final index (i.e. index 2) in the range 
# provided, while a named slice, gdpPercap_1952 : gdpPercap_1962 , includes 
# the final element.<jupyter_output><empty_output><jupyter_text>Q3: Reconstructing Data Explain what each line in the following short program does: what is in first, second, etc.?<jupyter_code>Explain what each line in the following short program does: what is in first, second, etc.?

first = pandas.read_csv('data/gapminder_all.csv', index_col='country')
second = first[first['continent'] == 'Americas']
third = second.drop('Puerto Rico')
fourth = third.drop('continent', axis = 1)
fourth.to_csv('result.csv')<jupyter_output><empty_output><jupyter_text>**Solution**Click on the '...' below to show the solution.<jupyter_code># Let's go through this piece of code line by line.

first = pandas.read_csv('data/gapminder_all.csv', index_col='country')

# This line loads the dataset containing the GDP data from all countries into a 
# dataframe called `first`. The `index_col='country'` parameter selects which 
# column to use as the row labels in the dataframe.  

second = first[first['continent'] == 'Americas']

# This line makes a selection: only those rows of `first` for which the 
# 'continent' column matches 'Americas' are extracted. Notice how the Boolean 
# expression inside the brackets, `first['continent'] == 'Americas'`, is used 
# to select only those rows where the expression is true. Try printing this 
# expression! Can you print also its individual True/False elements? (hint: 
# first assign the expression to a variable) 

third = second.drop('Puerto Rico')

# As the syntax suggests, this line drops the row from `second` where the label 
# is 'Puerto Rico'. The resulting dataframe `third` has one row less than the 
# original dataframe `second`.

fourth = third.drop('continent', axis = 1)

# Again we apply the drop function, but in this case we are dropping not a row 
# but a whole column. To accomplish this, we need to specify also the `axis` 
# parameter (we want to drop the second column which has index 1).

fourth.to_csv('result.csv')

# The final step is to write the data that we have been working on to a csv 
# file. Pandas makes this easy with the `to_csv()` function. The only required 
# argument to the function is the filename. Note that the file will be written 
# in the directory from which you started the Jupyter or Python session.<jupyter_output><empty_output><jupyter_text>Q4: Selecting Indices Explain in simple terms what idxmin and idxmax do in the short program below. When would you use these methods?<jupyter_code>data = pandas.read_csv('data/gapminder_gdp_europe.csv', index_col='country')
print(data.idxmin())
print(data.idxmax())<jupyter_output><empty_output><jupyter_text>**Solution**Click on the '...' below to show the solution.<jupyter_code># For each column in `data`, `idxmin` will return the index value corresponding 
# to each column's minimum; `idxmax` will do accordingly the same for each 
# column's maximum value.

# You can use these functions whenever you want to get the row index of the 
# minimum/maximum value and not the actual minimum/maximum value.<jupyter_output><empty_output><jupyter_text>Q5: Practice with Selection Assume Pandas has been imported and the Gapminder GDP data for Europe has been loaded. Write an expression to select each of the following:1. GDP per capita for all countries in 1982.2. GDP per capita for Denmark for all years.3. GDP per capita for all countries for years after 1985.4. GDP per capita for each country in 2007 as a multiple of GDP per capita for that country in 1952. **Solution**Click on the '...' below to show the solution.<jupyter_code># 1. -------------------------------------
data['gdpPercap_1982']

# 2. -------------------------------------
data.loc['Denmark',:]

# 3. -------------------------------------
data.loc[:,'gdpPercap_1985':]

# Pandas is smart enough to recognize the number at the end of the column label 
# and does not give you an error, although no column named `gdpPercap_1985` 
# actually exists. This is useful if new columns are added to the CSV file later.

# 4. -------------------------------------
data['gdpPercap_2007']/data['gdpPercap_1952']<jupyter_output><empty_output><jupyter_text>Q6: Using `dir` function to see available methods Python includes a `dir` function that can be used to display all of the available methods (functions) that are built into a data object.  As an example, the  functions available for a [list data type](https://docs.python.org/3/tutorial/datastructures.htmlmore-on-lists) are:<jupyter_code>potatoes = ["Russet", "Norkota", "Yukon Gold", "Pontiac"]
dir(potatoes)<jupyter_output><empty_output><jupyter_text>This command returns:<jupyter_code>['__add__',
...
'__subclasshook__',
'append',
'clear',
'copy',
'count',
'extend',
'index',
'insert',
'pop',
'remove',
'reverse',
'sort']<jupyter_output><empty_output><jupyter_text>The double underscore functions can be ignored for now; functions that are not surrounded by double underscores are the *public interface* of the [list type](https://docs.python.org/3/tutorial/datastructures.htmlmore-on-lists). So, if you want to sort the list of potatoes, according to `dir` you should try,<jupyter_code>potatoes.sort()<jupyter_output><empty_output><jupyter_text>Assume Pandas has been imported and the Gapminder GDP data for Europe has been loaded as `data`.  Then, use `dir` to find the function that prints out the median per-capita GDP across all European countries for each year that information is available.   **Solution**Click on the '...' below to show the solution.<jupyter_code># Among many choices, dir lists the `median()` function as a possibility.  Thus,

data.median()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08865966721925463, 'fraction_numerical': 0.0940013507705532, 'mean_word_length': 3.31813361611877, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 92, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '106787', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 5992, 'n_tokens_neox': 4905, 'n_words': 1674, 'path': '_episodes_rapids/070_data_frames_panda.ipynb', 'repo_name': 'UFResearchComputing/py4ai', 'size': '16287'}