<jupyter_start><jupyter_text>**CincyPy Discussion Group** Jul 2019<jupyter_code>import numpy as np
import pandas as pd

import matplotlib as mpl
import matplotlib.pyplot as plt

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = 'all'

%matplotlib inline<jupyter_output><empty_output><jupyter_text>1. Definition of a dataframe<jupyter_code>def colorTuple(color):
    return(tuple(col/255 for col in color))

fig, currAX = plt.subplots(figsize=(6,6))
currAX.set_xlim([0,10])
currAX.set_ylim([0,10])

currAX.spines['top'].set_visible(False)
currAX.spines['bottom'].set_visible(False)
currAX.spines['left'].set_visible(False)
currAX.spines['right'].set_visible(False)
currAX.set_xticks([])
currAX.set_yticks([])

circle1 = plt.Circle((2, 6), radius=2, facecolor='g', alpha=0.3, edgecolor=colorTuple((32,32,32)), zorder=1)
circle2 = plt.Circle((5, 6), radius=2, facecolor='darkgray', alpha=1, edgecolor=colorTuple((64,64,64)), zorder=-1)
circle3 = plt.Circle((8, 6), radius=2, facecolor='b', alpha=0.3, edgecolor=colorTuple((32,32,32)), zorder=1)


plt.annotate('Excel\n sheet',xy=(2, 6),fontsize=12, ha='center', va='center')
plt.annotate('DataFrame',xy=(5, 6),fontsize=12, ha='center', va='center')
plt.annotate('Database\ntable',xy=(8, 6),fontsize=12, ha='center', va='center')

currAX.add_artist(circle1)
currAX.add_artist(circle2)
currAX.add_artist(circle3)

plt.tight_layout()

plt.show();
fig, currAX = plt.subplots(figsize=(8,8))

currAX.set_xlim([0,10])
currAX.set_ylim([0,10])

currAX.spines['top'].set_visible(False)
currAX.spines['bottom'].set_visible(False)
currAX.spines['left'].set_visible(False)
currAX.spines['right'].set_visible(False)
currAX.set_xticks([])
currAX.set_yticks([])

innerLines = {'color':'darkgray', 'linestyles':'solid'}
outerBorders = {'color':'black', 'linestyles':'--'}
header = {}

currAX.vlines(x=2, ymin=1, ymax=8, **outerBorders)
currAX.vlines(x=9, ymin=1, ymax=8, **outerBorders)
currAX.hlines(y=1, xmin=2, xmax=9, **outerBorders)
currAX.hlines(y=8, xmin=2, xmax=9, **outerBorders)

currAX.hlines(y=7, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=6, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=5, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=4, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=3, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=2, xmin=1, xmax=9, **innerLines)
currAX.hlines(y=1, xmin=1, xmax=9, **innerLines)

currAX.vlines(x=3, ymin=1, ymax=8, **innerLines)
currAX.vlines(x=5, ymin=1, ymax=8, **innerLines)
currAX.vlines(x=7, ymin=1, ymax=8, **innerLines)

currAX.fill_between(np.arange(2,10,1), 7, 8, color=colorTuple((219, 238, 244)))

plt.annotate('ID',xy=(1.5, 7.5),fontsize=12, fontweight='bold')
plt.annotate('SSN',xy=(2.25, 7.5),fontsize=12, fontweight='bold')
plt.annotate('Name',xy=(3.5, 7.5),fontsize=12, fontweight='bold')
plt.annotate('Address',xy=(5.5, 7.5),fontsize=12, fontweight='bold')
plt.annotate('Other\nDetails',xy=(7.5, 7.25),fontsize=12, fontweight='bold')

plt.suptitle("Structure of a Pandas DataFrame", fontsize=18, fontweight='bold')

plt.annotate('Columns\n(axis=0)',fontsize=12, ha='center', va='center'
                 ,xytext=(11,7.5), xy=(9,7.5)
                 ,arrowprops={'arrowstyle': '-|>',
                         'color':colorTuple((64,64,64)),
                         'lw':1.5,
                         'ls':'-', 'connectionstyle':"angle3,angleA=0,angleB=-90"}
            )
plt.annotate('Index\n(axis=1)',fontsize=12, ha='center', va='center'
                 ,xytext=(0.75,9), xy=(1.5,8)
                 ,arrowprops={'arrowstyle': '-|>',
                         'color':colorTuple((64,64,64)),
                         'lw':1.5,
                         'ls':'-', 'connectionstyle':"angle3,angleA=0,angleB=-90"}
            )

bbox_props = dict(boxstyle="Round,pad=0.3", fc="white", ec="None", lw=1)
currAX.text(5, 5, 'Cells', ha="center", va="center", fontsize=20, fontweight='bold', rotation=45, bbox=bbox_props)



plt.show();<jupyter_output><empty_output><jupyter_text>2. Grounding activity<jupyter_code>pd.__version__ # should be >= 0.23.4
np.__version__ # should be >= 1.16.x<jupyter_output><empty_output><jupyter_text>3. Creating a dataframe  3.1 Create an empty dataframe<jupyter_code>data = pd.DataFrame(columns=['A', 'B', 'C'])
data<jupyter_output><empty_output><jupyter_text>3.2 Create a dataframe with hard-coded data<jupyter_code>data = pd.DataFrame({'ID':np.arange(1, 11, 1), 'A':[np.nan]*10})
data<jupyter_output><empty_output><jupyter_text>3.3 Load from a data source (CSV in this case)Other options include (**See more:** https://pandas.pydata.org/pandas-docs/stable/reference/io.html)- clipboard- Excel- JSON- HTML- HDFStore- Parquet- SAS- SQL (tables, queries)Note: import from 'imports-85.data.csv'<jupyter_code>data = pd.read_csv('Data/imports-85.data.csv')
data.replace('?', np.nan, inplace=True)
#data<jupyter_output><empty_output><jupyter_text>4 Explore structure and contents of the dataframe<jupyter_code>#data.columns.values
#data.dtypes#
#desc = data.describe()
#desc.columns.values
data.info()<jupyter_output><empty_output><jupyter_text>5 Missing data  5.1 Locating missing data<jupyter_code>data.isnull().sum() # count by columns of null values
data[data['num-of-doors'].isnull()]<jupyter_output><empty_output><jupyter_text>5.2 Filling the gaps<jupyter_code>#data[data['num-of-doors'].isnull()]
data2 = data.fillna({'num-of-doors':'UNKNOWN'})
#data2.loc[[0, 27,63], ]

data['normalized-losses'].describe()
len(data['normalized-losses'])<jupyter_output><empty_output><jupyter_text>5.3 Removing missing data<jupyter_code>data2 = data.dropna()
data2.describe()

data = data.dropna(subset=['bore'])<jupyter_output><empty_output><jupyter_text>6 Indexes<jupyter_code>data.set_index(['fuel-type', 'make'], inplace=True)
data

data.reset_index() # restore to former glory<jupyter_output><empty_output><jupyter_text>7 Adding, modifying, deleting data  7.1 Add new rows<jupyter_code>row = pd.Series({'make':'Tesla'})
data = data.append(row, ignore_index=True)
data.tail(5)<jupyter_output><empty_output><jupyter_text>7.2 Add new columns<jupyter_code>data['PRICE_ADJUSTMENT'] = np.random.rand(data.shape[0])
data.columns.values<jupyter_output><empty_output><jupyter_text>7.3 Update a column<jupyter_code>data.loc[data['make']=='mazda', ['make', 'price', 'PRICE_ADJUSTMENT']]
data['price'] = data['price'].astype('float')

data.loc[data['make']=='mazda', 'NEW_PRICE'] = data.loc[data['make']=='mazda','price']*\
                                                (1.0+data.loc[data['make']=='mazda','PRICE_ADJUSTMENT'])
data.loc[data['make']=='mazda', ['make', 'price', 'PRICE_ADJUSTMENT', 'NEW_PRICE']]<jupyter_output><empty_output><jupyter_text>7.4 Update a row<jupyter_code>row = pd.Series({'num-of-doors':2 })
data.tail(3)

data.loc[205] = row
data.tail(3)<jupyter_output><empty_output><jupyter_text>_apply()_ function<jupyter_code>def volCalc(row):
    return row['length']*row['width']*row['height']

data['Volume'] = data.apply(volCalc, axis=1)

data.loc[:, ['make', 'length', 'width', 'height', 'Volume']].head(5)<jupyter_output><empty_output><jupyter_text>Delete rows<jupyter_code>data.drop(labels=206, axis=0, inplace=True)

data.tail(5)<jupyter_output><empty_output><jupyter_text>Delete columns<jupyter_code>data.drop(labels='symboling', axis=1, inplace=True)
data.head(5)<jupyter_output><empty_output><jupyter_text>8 Slicing and Filtering  8.1 Column list<jupyter_code>data.loc[:, ['make', 'fuel-type', 'price']]<jupyter_output><empty_output><jupyter_text>8.2 Filters<jupyter_code>#data.loc[data['make']=='mazda', ['make', 'fuel-type', 'price', 'NEW_PRICE']]
data.loc[data['make'].isin(['mazda', 'Tesla']), ['make', 'fuel-type', 'price', 'NEW_PRICE']]

carCondition = (data['make'].isin(['mazda', 'Tesla']))
fuelTypeCondition = (data['fuel-type']=='diesel')
data.loc[carCondition&fuelTypeCondition, ['make', 'fuel-type', 'price', 'NEW_PRICE']]<jupyter_output><empty_output><jupyter_text>8.3 Positional filters<jupyter_code>data,loc[[23, 67, 32], ['make', 'fuel-type', 'price']]
data.loc[23:25, ['make', 'fuel-type', 'price']]<jupyter_output><empty_output><jupyter_text>9 Aggregates and Summarization  9.1 Unique values<jupyter_code># 9.1.1 One column
data['make'].unique()
data['make'].nunique()

# 9.1.2 Multiple columns
data.loc[:, ['make', 'fuel-type']].drop_duplicates()<jupyter_output><empty_output><jupyter_text>9.2 Counts, sums etc.<jupyter_code>data.loc[data['make'].isin(['mazda', 'Tesla']), ['make', 'fuel-type', 'price', 'NEW_PRICE']].count()

data.loc[data['make'].isin(['mazda', 'Tesla']), ['make', 'fuel-type', 'price', 'NEW_PRICE']].\
    groupby(['make']).count()<jupyter_output><empty_output><jupyter_text>9.3 Multiple aggregates by column<jupyter_code>data['price'] = data['price'].astype(float)

data.loc[data['make'].isin(['mazda', 'Tesla']), ['make', 'fuel-type', 'price', 'NEW_PRICE']].\
        groupby(['make']).\
        agg({'fuel-type':'count', 'price':['min', 'median', np.std, 'max']})<jupyter_output><empty_output><jupyter_text>9.4 Pivot tables<jupyter_code>data.loc[:, ['make', 'fuel-type', 'num-of-cylinders', 'curb-weight']].\
        pivot_table(index=['fuel-type', 'make'], values='curb-weight', columns='num-of-cylinders', aggfunc='count')<jupyter_output><empty_output><jupyter_text>10 Visualization kind =- 'line' : line plot (default)- 'bar' : vertical bar plot- 'barh' : horizontal bar plot- 'hist' : histogram- 'box' : boxplot- 'kde' : Kernel Density Estimation plot- 'density' : same as 'kde'- 'area' : area plot- 'pie' : pie plot- 'scatter' : scatter plot- 'hexbin' : hexbin plot**See more:** https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.html<jupyter_code>data.plot(kind='scatter',x='engine-size',y='curb-weight',color='red')  

plt.show();<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2039072039072039, 'fraction_numerical': 0.03317053317053317, 'mean_word_length': 6.715070643642072, 'pattern_counts': {'":': 0, '<': 105, '<?xml version=': 0, '>': 109, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '548383', 'license': 'MIT', 'n_tokens_mistral': 4027, 'n_tokens_neox': 3852, 'n_words': 549, 'path': 'Pandas Dataframes.ipynb', 'repo_name': 'anandologist/CincyPy_2019', 'size': '9828'}