pandas DataFrame

# Introduction
I would like to introduce important features of pandas DataFrames.
My article will resemble a cookbook with specific recipes.

# pandas DataFrame basics
The fastest way to generate a DataFrame is to load a csv file into a dataframe

```python
import os
import pandas as pd

df = pd.read_csv("some_file.csv")
df = pd.read_csv(os.path.join("C:\some_location\","some_file.csv"))
```

pandas DataFrame has 3 components, ```values```, ```columns```, ```index```

- ```df.values``` returns 2 dimensional representation of the dataframe
- ```df.columns``` returns the column names
- ```df.index``` returns the labels assigned to DataFrame rows

To view first five rows of the DataFrame

```python
df.head()
```

To view info about each column, such as the number of non-null values in each column

```python
df.info()
```

To see statistical summaries of the DataFrame numerical columns

```python
df.describe()
```

# pandas DataFrame sorting

To sort DataFrame by a specific column in ascending order

```python
df = df.sort_values("column_name")
```

To sort DataFrame by a specific column in descending order

```python
df = df.sort_values("column_name", ascending = False)
```

To sort DataFrame by more than one column

```python
df = df.sort_values(["column_one","column_two"])
```

To sort DataFrame by more than one column and with different order types

```python
df = df.sort_values(["col_one","col_two"], ascending = [True, False])
```

# pandas DataFrame subsetting
To subset certain columns

```python
sub_df = df[["col_one","col_two"]]
```

Subset a certain row
```python
df = df[ df["height"]>200 ]
```

Subset based on text data

```python
df = df[ df["name"] == "Jack" ]
```

Subset based on date

```python
df = df[df["birth_date"] > "2000-01-01"]
```

Subset based on multiple conditions

```python
equity_df = equity_df[(equity_df["price"]>400) & (equity_df["industry"] == "Information Technology")]
```

Subsetting using ```isin```

```python
eq_df = eq_df[eq_df["industry"].isin(["Automative","Tourism","Networking"])]
```

# pandas DataFrame : Summarizing numerical data

You can use following methods to summarize numerical columns of the DataFrame

- mean
- median
- max
- min
- sum
- var
- std
- quantile

```python
mean_height = df["height"].mean()
```

We can use ```agg()``` function to summarize numerical column by applying custom functions

```python
def pct30(column):
    return column.quantile(0.3)

df["weight"].agg(pct30)
```

Summaries on multiple columns

```python
df[["weight","height"]].agg(pct30)
```

Multiple summaries

```python
df["weight"].agg([pct30,pct40])
```

Cumulative statistics

```python
df["cum_sales"] = df["sales"].cumsum()
```

To calculate compounded return

```python
df["one_minus_daily_return"] = 1 - df["daily_return"]
df["compounded_return"] = df["one_minus_daily_return"].cumprod()
```

Cumulative statistics

- cummin()
- cummax()
- cumsum()
- cumprod()

# pandas DataFrame : grouping and aggregation

Before counting the values in a column we might want to get rid of duplicates

```python
df.drop_duplicates(subset="name")
df.drop_duplicates(subset=["name","breed"])
```

To count values of a column

```python
df["breed"].value_counts()
```

Count and sort

```python
df["breed"].value_counts(sort=True)
```

To count and show proportion of each value against total instead of absolute counts

```python
df["breed"].value_counts(normalize=True)
```

To group by a column's values

```python
df.groupby("color")["sales"].mean()
```

Multiple grouped summaries

```python
df.groupby("color")["sales"].agg(np.mean, np.min, np.max)
```

Grouping by multiple variables

```python
df.groupby(["color","style"])["sales"].mean()
```

Many groups many summaries

```python
df.groupby(["color","breed"])[["weight","height"]].mean()
```

Many groups many summaries and applying multiple aggregation functions

```python
df.groupby(["color","breed"])[["weight","height"]].agg([np.mean,'sum','min','max'])
```


We can also use ```pivot_table``` to group data

```python
df.pivot_table(values="weight", index="color")
```

By default ```pivot_table``` applies ```mean``` as a summary metric for the grouped data. To change that behavior specify ```aggfunc``` parameter.

```python
df.pivot_table(values="weight", index="color", aggfunc=np.median)
```

You can specify multiple aggfunc methods on the data

```python
df.pivot_table(values="weight", index="color", aggfunc=[np.median, np.max])
```

We can add additional grouping dimension by specifying ```columns``` parameter

```python
df.pivot_table(values="weight", index="color", columns="breed")
```

When a certain value of a DataFrame column specified in ```values``` has no match for a value of a DataFrame column specified in ```columns```, ```pivot_table``` puts ```NaN```. We can change that behavior by specifying ```fill_value``` parameter

```python
df.pivot_table(values="weight", index="color", columns="breed", fill_value=0)
```

Sometimes it is useful to see summed data by rows and columns. You can do that by setting ```margins``` parameter to ```True```

```python
df.pivot_table(values="weight", index="color", columns="breed", fill_value=0, margins=True)
```

# Indexing
Explicit indexing has benefits of subsetting the dataframe more concisely. Let's take an example of ```temperatures``` dataset, with columns like date, city and temperature. If we wanted to subset this dataset by cities like Moscow and London we could do

```python
sub_df = temperatures_df[temperatures_df['city'].isin(['Moscow','London'])]
```

Alternatively we can set this DataFrame's index to city names and then subset like below

```python
temp_city_ind_df = temperatures_df.set_index('city')
subset_df = temp_city_ind_df.loc[['Moscow','London']]
```

## Multilevel indexing
Multilevel indexing comes in handy when we have hierarchical categorical data that can be nested within each other, such as country and cities, products and their styles etc.

In the above example of temperatures dataset, we could set index to two columns, country and city and then subset by country and city.

```python
temp_multi_ind_df = temperatures_df.set_index(['country','city'])
rows_to_keep = [('Brazil','Rio De Janeiro'),('Pakistan','Lahore')]

# Subset for rows to keep
print(temp_multi_ind_df.loc[rows_to_keep])
```

## Sorting by index values

```python
# Sort temperatures_ind by index values
print(temperatures_ind.sort_index())

# Sort temperatures_ind by index values at the city level
print(temperatures_ind.sort_index(level='city'))

# Sort temperatures_ind by country then descending city
print(temperatures_ind.sort_index(level=['country','city'],ascending=[True,False]))
```

## Slicing
### Slicing index values
Slicing lets you select consecutive elements of an object using first:last syntax. DataFrames can be sliced by index values or by row/column number; we'll start with the first case. This involves slicing inside the .loc[] method.

Compared to slicing lists, there are a few things to remember.

You can only slice an index if the index is sorted (using .sort_index()).
To slice at the outer level, first and last can be strings.
To slice at inner levels, first and last should be tuples.
If you pass a single slice to .loc[], it will slice the rows.
pandas is loaded as pd. temperatures_ind has country and city in the index, and is available.

```python
# Sort the index of temperatures_ind
temperatures_srt = temperatures_ind.sort_index()

# Subset rows from Pakistan to Russia
print(temperatures_srt.loc["Pakistan":"Russia"])

# Try to subset rows from Lahore to Moscow
print(temperatures_srt.loc["Lahore":"Moscow"])

# Subset rows from Pakistan, Lahore to Russia, Moscow
print(temperatures_srt.loc[("Pakistan", "Lahore"):("Russia", "Moscow")])
```

You can subset by rows and columns simultaneously

```python
# Subset rows from India, Hyderabad to Iraq, Baghdad
print(temperatures_srt.loc[("India","Hyderabad"):("Iraq","Baghdad")])

# Subset columns from date to avg_temp_c
print(temperatures_srt.loc[:,"date":"avg_temp_c"])

# Subset in both directions at once
print(temperatures_srt.loc[("India","Hyderabad"):("Iraq","Baghdad"),"date":"avg_temp_c"])
```

Slicing comes in handy when working with dates. Because it is most common to subset dataset by a date range

```python
# Use Boolean conditions to subset temperatures for rows in 2010 and 2011
temperatures_bool = temperatures[(temperatures['date'] >= "2010-01-01") & (temperatures['date'] <= "2011-12-31")]
print(temperatures_bool)

# Set date as an index and sort the index
temperatures_ind = temperatures.set_index('date').sort_index()

# Use .loc[] to subset temperatures_ind for rows in 2010 and 2011
print(temperatures_ind.loc["2010":"2011"])

# Use .loc[] to subset temperatures_ind for rows from Aug 2010 to Feb 2011
print(temperatures_ind.loc["2010-08":"2011-02"])
```

# Pivot temperature by city and year
It's interesting to see how temperatures for each city change over time—looking at every month results in a big table, which can be tricky to reason about. Instead, let's look at how temperatures change by year.

You can access the components of a date (year, month and day) using code of the form ```dataframe["column"].dt.component```. For example, the month component is ```dataframe["column"].dt.month```, and the year component is ```dataframe["column"].dt.year```.

Once you have the year column, you can create a pivot table with the data aggregated by city and year.

```python
# Add a year column to temperatures
temperatures['year']=temperatures['date'].dt.year

# Pivot avg_temp_c by country and city vs year
temp_by_country_city_vs_year = temperatures.pivot_table(values='avg_temp_c',index=['country','city'],columns="year")

# See the result
print(temp_by_country_city_vs_year)
```

You can apply slicing techniques to pivoted DataFrames as well

```python
# Subset for Egypt to India
print(temp_by_country_city_vs_year.loc["Egypt":"India"])

# Subset for Egypt, Cairo to India, Delhi
print(temp_by_country_city_vs_year.loc[("Egypt","Cairo"):("India","Delhi")])

# Subset in both directions at once
print(temp_by_country_city_vs_year.loc[("Egypt","Cairo"):("India","Delhi"),"2005":"2010"])
```

Pivoting is the first step to get insight into your data

```python
# Get the worldwide mean temp by year
mean_temp_by_year = temp_by_country_city_vs_year.mean()

# Filter for the year that had the highest mean temp
print(mean_temp_by_year[mean_temp_by_year==max(mean_temp_by_year)])

# Get the mean temp by city
mean_temp_by_city = temp_by_country_city_vs_year.mean(axis="columns")

# Filter for the city that had the lowest mean temp
print(mean_temp_by_city[mean_temp_by_city==min(mean_temp_by_city)])
```