<jupyter_start><jupyter_text>02.04 - PANDAS<jupyter_code>!wget --no-cache -O init.py -q https://raw.githubusercontent.com/rramosp/20201.xai4eng/master/content/init.py
import init; init.init(force_download=False); init.get_weblink()
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline<jupyter_output><empty_output><jupyter_text>`pandas` is mostly about manipulating tables of datasee this cheat sheet: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf  Pandas main object is a `DataFrame`- can read .csv, .excel, etc.<jupyter_code>!head local/data/internet_facebook.dat
!wc local/data/weather_data_austin_2010.csv
df = pd.read_csv('local/data/internet_facebook.dat', index_col='# Pais')
df
df.head()
df.tail()
df.columns
df.index<jupyter_output><empty_output><jupyter_text>**fix the index name**<jupyter_code>df.index.name="Pais"
df.head()
df.describe()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 33 entries, Argentina to Venezuela
Data columns (total 2 columns):
Uso_Internet    33 non-null float64
Uso_Facebook    33 non-null float64
dtypes: float64(2)
memory usage: 792.0+ bytes<jupyter_text>**a dataframe is made of `Series`**. Observe that each series has **its own type**<jupyter_code>s1 = df["Uso_Internet"]
type(s1)
s1<jupyter_output><empty_output><jupyter_text>if the column name is not too fancy (empy spaces, accents, etc.) we can use columns names as python syntax.<jupyter_code>df.Uso_Facebook<jupyter_output><empty_output><jupyter_text>DataFrame indexingis **NOT** exactly like numpy- first index    - if string refers to columns    - if `Series` of booleans is used as a filter    - for selecting columns:    - use `.loc` to select by Index    - use `.iloc` to select by position<jupyter_code>df["Colombia"]
df.loc["Colombia"]<jupyter_output><empty_output><jupyter_text>Index semantics is exact!!<jupyter_code>df.loc["Colombia":"Spain"]
df.iloc[10:15]<jupyter_output><empty_output><jupyter_text>filtering<jupyter_code>df[df.Uso_Internet>80]<jupyter_output><empty_output><jupyter_text>combined conditions<jupyter_code>df[(df.Uso_Internet>50)&(df.Uso_Facebook>50)]
df[(df.Uso_Internet>50)|(df.Uso_Facebook>50)]<jupyter_output><empty_output><jupyter_text>Managing data         observe csv structure:- missing column name- missing data<jupyter_code>!head local/data/comptagevelo2009.csv
d = pd.read_csv("local/data/comptagevelo2009.csv")
d
d.columns, d.shape<jupyter_output><empty_output><jupyter_text>numerical features<jupyter_code>d.describe()
d["Berri1"].head()
d["Unnamed: 1"].unique()

d["Berri1"].unique()

d["Berri1"].dtype, d["Date"].dtype, d["Unnamed: 1"].dtype

d.index<jupyter_output><empty_output><jupyter_text>Fixing dataobserve we set one column as the index one, and we **convert** it to date object type<jupyter_code>d.Date
d.index = pd.to_datetime(d.Date)
del(d["Date"])
del(d["Unnamed: 1"])
d.head()
d.index<jupyter_output><empty_output><jupyter_text>let's fix columns names<jupyter_code>d.columns=["Berri", "Mneuve1", "Mneuve2", "Brebeuf"]
d.head()
for col in d.columns:
    print (col, np.sum(pd.isnull(d[col])))
d.shape
d['Brebeuf'].describe()
plt.hist(d.Brebeuf, bins=30);<jupyter_output><empty_output><jupyter_text>**fix missing**!!!<jupyter_code>d.Brebeuf.fillna(d.Brebeuf.mean(), inplace=True)

d['Brebeuf'].describe()
plt.hist(d.Brebeuf, bins=30);
d<jupyter_output><empty_output><jupyter_text>let's make sure it is sorted<jupyter_code>d.sort_index(inplace=True)
d.head()<jupyter_output><empty_output><jupyter_text>Filtering<jupyter_code>d[d.Berri>6000]

d[(d.Berri>6000) & (d.Brebeuf<7000)]<jupyter_output><empty_output><jupyter_text>Locating<jupyter_code>d[d.Berri>5500].sort_index(axis=0)

d.iloc[100:110]<jupyter_output><empty_output><jupyter_text>**dates as INDEX have special semantics**<jupyter_code>d.loc["2009-10-01":"2009-10-10"]<jupyter_output><empty_output><jupyter_text>can do sorting across any criteria<jupyter_code>d.sort_values(by="Berri").head()<jupyter_output><empty_output><jupyter_text>and chain operations<jupyter_code>d.sort_values(by="Berri").loc["2009-10-01":"2009-10-10"]<jupyter_output><empty_output><jupyter_text>Time series operations<jupyter_code>d.rolling(3).mean().head(10)

d.index = d.index + pd.Timedelta("5m")
d.head()
d.shift(freq=pd.Timedelta(days=365)).head()<jupyter_output><empty_output><jupyter_text>Downsampling<jupyter_code>d.resample(pd.Timedelta("2d")).first().head()

d.resample(pd.Timedelta("2d")).mean().head()<jupyter_output><empty_output><jupyter_text>Upsampling<jupyter_code>d.resample(pd.Timedelta("12h")).first().head()

d.resample(pd.Timedelta("12h")).fillna(method="pad").head()<jupyter_output><empty_output><jupyter_text>Building Dataframes from other structures<jupyter_code>a = np.random.randint(10,size=(20,5))
a
k = pd.DataFrame(a, columns=["uno", "dos", "tres", "cuatro", "cinco"], index=range(10,10+len(a)))
k<jupyter_output><empty_output><jupyter_text>`.values` access the underlying `numpy` structure<jupyter_code>d.values<jupyter_output><empty_output><jupyter_text>some out-of-the-box plottingbut recall that we always can do custom plotting<jupyter_code>d.plot(figsize=(15,3))

plt.figure(figsize=(15,3))
plt.plot(d.Berri)
d.Berri.cumsum().plot()

plt.scatter(d.Berri, d.Brebeuf)

pd.plotting.scatter_matrix(d, figsize=(10,10));<jupyter_output><empty_output><jupyter_text>Grouping<jupyter_code>d["month"] = [i.month for i in d.index]
d.head()
d.groupby("month").max()

d.groupby("month").count()<jupyter_output><empty_output><jupyter_text>Time seriesobserve we can **establish at load time** many thing if the dataset is relatively clean<jupyter_code>tiempo=pd.read_csv('local/data/weather_data_austin_2010.csv',parse_dates=['Date'], dayfirst=True ,index_col='Date')
tiempo
tiempo.loc['2010-08-01':'2010-10-30']

tiempo.loc['2010-06'].head()

tiempo.sample(10)

tiempo.sample(frac=0.01)<jupyter_output><empty_output><jupyter_text>Resampling<jupyter_code>tiempo.head()

tiempo.resample("5d").mean().head()
tiempo.resample("5d").mean().head()

tiempo.resample("5d").mean().head()

tiempo.resample("30min").mean()[:15]


subt=tiempo.between_time(start_time='1:00',end_time='12:00')
subt
tiempo.index.weekday

tiempo.index.month

tiempo.index.day

tiempo.plot(style='.')

tiempo['2010-01'].plot()
tiempo['2010-01-04'].plot()<jupyter_output><empty_output><jupyter_text>Rolling operations<jupyter_code>import pandas as pd
### permite obtener data frames directamente de internet
!pip install yfinance
import yfinance as yf

#define the ticker symbol
tickerSymbol = 'MSFT'

#get data on this ticker
tickerData = yf.Ticker(tickerSymbol)

#get the historical prices for this ticker
gs = tickerData.history(period='1d', start='2010-1-1', end='2020-1-25')

#see your data
gs
gs.Close.rolling(10).mean().head(20)

plt.figure(figsize=(20,3))
plt.plot(gs.Close)
plt.plot(gs.Close.rolling(50).mean())

plt.figure(figsize=(20,3))
plt.plot(gs.iloc[:400].Close, label="original")
plt.plot(gs.iloc[:400].Close.rolling(50).mean(), label="rolling")
plt.plot(gs.iloc[:400].Close.rolling(50, center=True).mean(), label="center")
plt.legend();
plt.figure(figsize=(20,3))
plt.plot(gs.iloc[:400].Close.rolling(10).mean())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16671301446051168, 'fraction_numerical': 0.0364293659621802, 'mean_word_length': 8.365885416666666, 'pattern_counts': {'":': 3, '<': 122, '<?xml version=': 0, '>': 129, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '193864', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3059, 'n_tokens_neox': 2856, 'n_words': 412, 'path': 'content/NOTES 02.04 - PANDAS.ipynb', 'repo_name': 'rramosp/20201.xai4eng', 'size': '7192'}