# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Introduction to Python  
#
# ### Pandas Intro

# ### Pandas

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os
# %matplotlib inline

datapath = "../Data/"

# ### Pandas Data Structures: Series

obj = pd.Series([4, 12, -5, 3, 5])
obj

obj.values

obj.index

obj.index = ['Bob', 'Steve', 'Jeff', 'Ryan', 'Fernie']
obj

obj2 = pd.Series([4, 7, -5, 3], index=['d', 'b', 'a', 'c'])
obj2

obj2['c']

obj2[['c', 'a', 'd']]

obj2[obj2 < 0]

obj2 * 2

np.exp(obj2)

sdata = {'Ohio': 35000, 'Texas': 71000, 'Oregon': 16000, 'Utah': 5000}
obj3 = pd.Series(sdata)
obj3

states = ['California', 'Ohio', 'Oregon', 'Texas']
obj4 = pd.Series(sdata, index=states)
obj4

pd.isnull(obj4)

pd.notnull(obj4)

obj3.add(obj4, fill_value=10)

obj4.name = 'Population'
obj4.index.name = 'State'
obj4

# #### Pandas Data Structures: Dataframe

data = {'state': ['Ohio', 'Ohio', 'Ohio', 'Nevada', 'Nevada'],
        'year': [2000, 2001, 2002, 2001, 2002],
        'pop': [1.5, 1.7, 3.6, 2.4, 2.9]}
frame = pd.DataFrame(data)
frame

d = pd.DataFrame(data, columns=['year', 'state', 'pop'])
d

d.set_index('year', inplace=True)

d.drop(2000, axis=0)

d

frame2 = pd.DataFrame(data, columns=['year', 'state', 'pop', 'debt'],
                      index=['one', 'two', 'three', 'four', 'five'])
frame2

frame2['nova'] = 13
frame2

frame2.loc['three']

frame2.iloc[2]

frame2.nova = 23
frame2

frame2.columns

print(frame2['state'])
print()
print(type(frame2['state']))

frame2.state

#frame2.loc['three']
frame2.loc['three','state']

frame2['debt'] = 16.5
frame2

frame2['debt'] = np.arange(5.)
frame2

val = pd.Series([-1.2, -1.5, -1.7], index=['two', 'four', 'five'])
frame2['debt'] = val
frame2

frame2['eastern'] = frame2.state == 'Ohio'
frame2

del frame2['eastern']
frame2.columns

pivot = frame2.pivot(index= 'year', columns='state', values='pop') 
pivot

transpose = pivot.T
transpose

pop = {'Nevada': {2001: 2.4, 2002: 2.9},'Ohio': {2000: 1.5, 2001: 1.7, 2002: 3.6}}
frame3 = pd.DataFrame(pop)
frame3

frame3.T

type(pop)

pd.DataFrame(pop, index=[2001, 2002, 2003])

pdata = {'Ohio': frame3['Ohio'][:-1],'Nevada': frame3['Nevada'][:2]}
pd.DataFrame(pdata)

frame3.index.name = 'year'; frame3.columns.name = 'state'
frame3

pop = {'Nevada': {2001: 2.4, 2002: 2.9},'Ohio': {2000: 1.5, 2001: 1.7, 2002: 3.6}}
frame4 = pd.DataFrame(pop)
frame4

frame4.loc[2000,'Nevada'] = 2
frame4

frame5 = pd.concat([frame4, frame4], axis=0)
frame5.iloc[3,1] = 32
frame5

frame5.drop_duplicates(['Nevada'])

dates = pd.date_range("20160101", periods=10, freq='D')
data = np.random.random((10,3))
column_names = ['Column1', 'Column2', 'Column3']

dates

data

df = pd.DataFrame(data, index=dates, columns=column_names)
df.head(10)

df.iloc[0:11,1]

df[1:3]

df['20160104':'20160107']

df[(df.index>'20150101')&(df.index<'20160106')]

df.query('(Column1 < Column2) & (Column1 < Column3)')

df.loc['20160101':'20160102',['Column1','Column3']]

df.info()

df.iloc[3:5, 0:2]

df.describe()

df.info()

df.sort_index(axis=0, ascending=True,) # inplace=True)

df[sorted(df.columns)]

df.sort_values(by='Column2')

# +
dates1 = pd.date_range("20160101", periods=6)
data1 = np.random.random((6,2))
column_names1 = ['ColumnA', 'ColumnB']

dates2 = pd.date_range("20160101", periods=7)
data2 = np.random.random((7,2))
column_names2 = ['ColumnC', 'ColumnD']

df1 = pd.DataFrame(data1, index=dates1, columns=column_names1)
df2 = pd.DataFrame(data2, index=dates2, columns=column_names2)
# -

df1

df2

#https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html
df1.join(df2, how='left')

df1.join(df2, how='right')

df1.join(df2, how='outer')

df1.join(df2, how='inner')

df2['ColumnA'] = df1.ColumnA+1
df1.join(df2, how='left', rsuffix='_df2')

# +
del df2['ColumnA']
df3 = df1.join(df2)

# add a column to df to group on
df3['ProfitLoss'] = pd.Series(['Profit', 
                               'Loss', 
                               'Profit', 
                               'Same', 
                               'Profit', 
                               'Loss', 
                               'Profit', 
                               'Profit', 
                               'Same', 
                               'Loss'], index=dates)
# -

df3['Aluno'] = pd.Series(['Alessandra',
                         'Alessandra',
                         'Alessandra',
                         'Marcos',
                         'Ana',
                         'Ana',
                         'Marcos',
                         'Ana',
                         'Ana',
                         'Juliana'], index=dates)

df3

grupos = df3.groupby('ProfitLoss')#.mean()
grupos.mean()

max(['abacaxi', 'laranja', 'maçã'])

df4 = df3.groupby(['Aluno','ProfitLoss']).max()
df4

df4.index.get_level_values('Aluno')

df4.loc[('Ana','Profit'), 'ColumnA']

# + active=""
# #### https://hackernoon.com/fundamental-python-data-science-libraries-a-cheatsheet-part-2-4-fcf5fab9cdf1
# !pip install quandl
#
# import quandl
#
# # set up the Quandl connection
# api_key = 'GETYOURAPIKEY'
# quandl.ApiConfig.api_key = api_key
# quandl_code = "BITSTAMP/USD"
#
# # get the data from the API
# bitcoin_data = quandl.get(quandl_code, start_date="2017-01-01", end_date="2018-01-17", returns="numpy")
#
# # set up the data in pandas
# df = pd.DataFrame(data=bitcoin_data, columns=['Date', 'High', 'Low', 'Last', 'Bid', 'Ask', 'Volume', 'VWAP'])
#
# # make the 'Date' column the index
# df.set_index('Date', inplace=True) 
#
# # find a rolling 30 day average
# df['RollingMean'] = df['Last'].rolling(window=30).mean().shift(1)
#
# # label when the last price is less than L30D average
# df['Buy'] = df['Last'] < df['RollingMean']
#
# # create a strategic trading DataFrame
# trading_info = df.loc[:,['Last', 'RollingMean', 'Buy']]
#
# trading_info.tail(10) # lets look at last 10 days
# -

# Plotting with Pandas  
# --

dfvote = pd.read_excel(os.path.join('../Data','votesurvey.xls'), 'votesurvey')
dfvote.head()

dfvote.sort_values(by=['Age','Expected salary'], ascending=[True, False])[0:10]

for i in dfvote.index:
    dfvote.loc[i, 'Random'] = np.random.randint(10)
dfvote.Random = dfvote.Random.astype(int)
dfvote.head()

# Histogram  

fig=plt.figure(figsize=(15,8))
#Create one or more subplots using add_subplot, because you can't create blank figure
ax = fig.add_subplot(1,1,1)
#Variable
ax.hist(dfvote['Age'],bins = 7) # Here you can play with number of bins Labels and Tit
plt.title('Age distribution')
plt.xlabel('Age')
plt.ylabel('#Citizens')
plt.show()

# Box Plot  

dfvote.Age.hist();

fig=plt.figure()
ax = fig.add_subplot(1,1,1)
ax.boxplot(dfvote['Age'])
plt.show()

dfvote.boxplot(by='Gender');

# Violin Plot (using Seaborn)  
# Obs: Seaborn changes some settings on matplotlib  An alternative is to import this way:  
# import seaborn.apionly as sns

import seaborn as sns
sns.violinplot(dfvote['Age'], dfvote['Gender'])
sns.despine()

# Bar Chart  

#var = df.groupby('Gender').Random.sum() #grouped sum of <randoms> at Gender level
var = dfvote.groupby('Gender').Random.mean() #grouped mean of <randoms> at Gender level
fig = plt.figure()
ax1 = fig.add_subplot(1,1,1)
ax1.set_xlabel('Gender')
ax1.set_ylabel('Sum of Sales')
ax1.set_title("Gender wise mean of <randoms>") #sum or mean
var.plot(kind='bar');

# Line Chart  

var = dfvote.groupby('Candidate').Age.mean()
fig = plt.figure()
ax1 = fig.add_subplot(1,1,1)
#ax1.set_xlabel('Candidate')
ax1.set_ylabel('Mean of Ages')
ax1.set_title("Candidate wise mean of ages")
var.plot(kind='line')

# Stacked Column Chart  

var = dfvote.groupby(['Age','Gender']).Random.sum()
var.unstack().plot(kind='bar',stacked=True,  color=['red','blue'], grid=False)

# Scatter Plot  

fig = plt.figure()
ax = fig.add_subplot(1,1,1)
ax.scatter(dfvote['Age'],dfvote['Random'],s=dfvote['Expected salary']/500) #You can also add more variables here to represent color and size.
plt.show()

dfvote.plot.scatter(x='Age',y='Expected salary', c='Random', cmap='jet');

# Bubble Plot  

fig = plt.figure()
ax = fig.add_subplot(1,1,1)
# Added third variable income as size of the bubble
ax.scatter(dfvote['Age'],dfvote['Expected salary'], s=dfvote['Random']**3)
plt.show()

# Pie chart  

var=dfvote.groupby(['Gender']).sum().stack()
temp=var.unstack()
type(temp)
x_list = temp['Random']
label_list = temp.index
#The pie chart is oval by default. To make it a circle use plt.axis("equal")
plt.axis("equal")
#To show the percentage of each pie slice, pass an output format to the autopctparameter 
plt.pie(x_list,labels=label_list,autopct="%1.1f%%") 
plt.title("Gender Distribution")
plt.show()

# Heat Map  

# +
#Generate a random number, you can refer your data values also
data = np.random.rand(8,2)
rows = list('12345678') #rows categories
columns = list('MF') #column categories

fig,ax=plt.subplots()
#Advance color controls
ax.pcolor(data,cmap=plt.cm.Reds,edgecolors='k')
# Here we position the tick labels for x and y axis
ax.set_xticks(np.arange(0,2)+0.5)
ax.set_yticks(np.arange(0,8)+0.5)
ax.xaxis.tick_top()
ax.yaxis.tick_left()
#Values against each labels
ax.set_xticklabels(columns,minor=False,fontsize=20)
ax.set_yticklabels(rows,minor=False,fontsize=20)
plt.show()
print(data)
# -

# ## An example: Baby names in the USA

names1880 = pd.read_csv(os.path.join('../Data','names','yob1880.txt'), names=['name', 'sex', 'births'])

names1880[0:20]

#names1880.head()
names1880.tail()

names1880.groupby('sex').births.sum()

years = range(1880, 2013)
pieces = []
columns = ['name', 'sex', 'births']
for year in years:
    path = os.path.join('../Data','names','yob{}.txt'.format(year))
    frame = pd.read_csv(path, names=columns)
    frame['year'] = year
    pieces.append(frame)
# Concatenate everything into a single DataFrame
names = pd.concat(pieces, ignore_index=True)

names
names.head(10)

names.groupby('sex').births.sum()

total_births = names.pivot_table('births', index='year', columns='sex', aggfunc=sum)

total_births

total_births.tail()

total_births.plot(title='Total births by sex and year', figsize=(12,6));


# +
def add_prop(group):
    # Integer division floors
    births = group.births.astype(float)
    group['percent'] = births / births.sum()
    return group

names = names.groupby(['year', 'sex']).apply(add_prop)
# -

#names
names.head()

names[names.percent > 0.085]

names[names.name.str.startswith('Renat')]

np.allclose(names.groupby(['year', 'sex']).percent.sum(), 1)


# +
def get_top1000(group):
    return group.sort_values(by='births', ascending=False)[:1000]

grouped = names.groupby(['year', 'sex'])
top1000 = grouped.apply(get_top1000)
# -

#top1000
pd.options.display.float_format = '{:,.3f}'.format
top1000[:15]

boys = top1000[top1000.sex == 'M']
girls = top1000[top1000.sex == 'F']

Walter_names = boys[boys.name=='Bob']
Walter_names[:10]

total_births_top1000 = top1000.pivot_table('births', index='year', columns='name', aggfunc=sum)

subset = total_births_top1000[['John', 'Harry', 'Mary', 'Marilyn']]
subset.plot(subplots=True, figsize=(12, 10), grid=False,
title="Number of births per year")

table = top1000.pivot_table('percent', index='year', columns='sex', aggfunc=sum)
table.plot(title='Sum of table1000.percent by year and sex', yticks=np.linspace(0, 1.2, 13), xticks=range(1880, 2020, 10))

df = boys[boys.year == 2010]
prop_cumsum = df.sort_values(by='percent', ascending=False).percent.cumsum()


# +
def get_quantile_count(group, q=0.5):
    group = group.sort_values(by='percent', ascending=False)
    return group.percent.cumsum().values.searchsorted(q) + 1

prop_cumsum.values.searchsorted(0.5)
# -

diversity = top1000.groupby(['year', 'sex']).apply(get_quantile_count)
diversity = diversity.unstack('sex')
diversity.head()

diversity.plot(title="Number of popular names in top 50%")

# extract last letter from name column
get_last_letter = lambda x: x[-1]
last_letters = names.name.map(get_last_letter)
last_letters.name = 'last_letter'
table = names.pivot_table('births', index=last_letters, columns=['sex', 'year'], aggfunc=sum)
subtable = table.reindex(columns=[1910, 1960, 2010], level='year')
subtable.head()

subtable.sum()

letter_prop = subtable / subtable.sum().astype(float)

fig, axes = plt.subplots(2, 1, figsize=(10, 8))
letter_prop['M'].plot(kind='bar', rot=0, ax=axes[0], title='Male')
letter_prop['F'].plot(kind='bar', rot=0, ax=axes[1], title='Female',legend=False)

letter_prop = table / table.sum().astype(float)
dny_ts = letter_prop.ix[['d', 'n', 'y'], 'M'].T
dny_ts.head()

dny_ts.plot()

all_names = top1000.name.unique()
mask = np.array(['lesl' in x.lower() for x in all_names])
lesley_like = all_names[mask]
lesley_like

filtered = top1000[top1000.name.isin(lesley_like)]
filtered.groupby('name').births.sum()

table = filtered.pivot_table('births', index='year', columns='sex', aggfunc='sum')
table = table.div(table.sum(1), axis=0)
table.tail()

table.plot(style={'M': 'k-', 'F': 'k--'})

StarcoderdataJupyterScriptsDedupFiltered