2017 Alexander C.S. Hendorf, Königsweg GmbH, Mannheim
In [1]:
%run '2 helpers.py'
%config InlineBackend.figure_format = 'retina';
Effective Data Analysis with Pandas Indexes¶
Pandas: Data Selection & Indexes¶
In [2]:
import numpy as np
import pandas as pd
import random
import seaborn as sns
%matplotlib inline
Series¶
Series = Column
Series do have a data tpye.
In [3]:
series = pd.Series([3, 62, 75, 83, 47, 43, 39, 16, 19, 2])
In [4]:
series
Out[4]:
In [5]:
pd.Series([3, 62, 75, 83, 47, 43, 39, 16, 19, 2, 2.66676])
Out[5]:
In [6]:
pd.Series([3, 62, 75, 83, 47, 43, 39, 16, 19, 2, "2"])
Out[6]:
In [7]:
# Falle: int -> Float bedingt durch Implementierung in NumPy.
pd.Series([3, None, 6])
Out[7]:
Selection via Position / Slice¶
In [8]:
series[0]
Out[8]:
In [9]:
series[3:6]
Out[9]:
iloc uses square backets [ ]¶
In [10]:
# series[3:6]
series.iloc[3:6]
Out[10]:
Selection via Label¶
In [11]:
# set alpha label as new index for the series
series.index = [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"][:len(series)]
In [12]:
series
Out[12]:
In [13]:
series['C']
Out[13]:
In [14]:
series.loc['C']
Out[14]:
In [15]:
series['D':'F']
# by label: slice includes end!
Out[15]:
In [16]:
series[2]
# position, pythonic
Out[16]:
What about not unique indexes?¶
In [17]:
# set alpha label as new index for the series
series.index = [x for x in "GATTACAXYZ"][:len(series)]
In [18]:
series
Out[18]:
In [19]:
series.loc['G']
Out[19]:
In [20]:
try:
series.loc['G':'A']
except Exception as e:
print("ERROR:", e)
# non-unique values breaks slicing
In [21]:
series.loc['X':'Z']
# while unique values are still slicable in a non-unique index
Out[21]:
In [22]:
series.index.is_monotonic
Out[22]:
In [23]:
series.index.is_unique
Out[23]:
In [24]:
series.index = range(series.size)
series
Out[24]:
In [25]:
series
Out[25]:
In [26]:
series.index.is_unique
Out[26]:
In [27]:
series.index.is_monotonic
Out[27]:
In [28]:
series.index.is_monotonic_increasing
Out[28]:
Achtung: Panels are deprecated use MultiIndex¶
DataFrames, 2D Data¶
In [29]:
df = pd.read_json('data/sampledf.json')
In [30]:
df
Out[30]:
DataFrame[n] selektiert die Spalte an der Position n
In [31]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[:, 2]))
df[2]
Out[31]:
Das Slice DataFrame[start:stop] selektiert die Zeilen von start bis stop (stop ist exklusive!)
In [32]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[range(2, 4), :]))
# column
df[2:4]
Out[32]:
In [33]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[range(2, 4), range(2, 4)]))
# segment
df.iloc[2:4, 2:4]
Out[33]:
In [34]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[:, range(2, 4)]))
# column slice
df.iloc[:, 2:4]
Out[34]:
In [35]:
df
Out[35]:
In [36]:
df.index = ["R{:02d}".format(i) for i in range(len(df))]
In [37]:
df.columns = ["C{:02d}".format(i) for i in range(len(df.columns))]
In [38]:
df
Out[38]:
In [39]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[:, 'C05']))
df['C05']
Out[39]:
In [40]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice['R02':'R05', :]))
df['R02':'R05']
Out[40]:
In [41]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice[:, 'C02':'C05']))
df.loc[:, 'C02':'C05']
Out[41]:
In [42]:
# visualisation of below - for presentation
display_html(df.style.apply(highlight, subset=pd.IndexSlice['R02':'R05', 'C04':'C05']))
# segment
df.loc['R02':'R05', 'C04':'C05']
Out[42]:
Welcome to the Bluth Online Store¶
In [43]:
sales_data = pd.read_excel('data/blooth_sales_data_clean.xlsx')
sales_data.head(5)
Out[43]:
In [44]:
sales_data.info()
Boolean Index¶
Ein Boolean Index (oder mask) ist ein Array mit True/False Werten: [1, 0, 1, 1, 0, 0, 1, …]
In [45]:
sales_data['units'] > 40
Out[45]:
In [46]:
sales_data[
sales_data['units'] > 40
]
Out[46]:
In [47]:
sales_data[
(sales_data['units'] > 40) & (sales_data['unitprice'] < 10) # AND
]
Out[47]:
In [48]:
sales_data[
(sales_data['units'] > 40) | (sales_data['unitprice'] < 10) # OR
]
Out[48]:
Multi -Index¶
In [49]:
somany = 20
dfm = pd.DataFrame(
{'city':[x for x in ['Paris', 'London', 'Berlin', 'New York', 'Mannheim']*10][:20],
'category': [random.randint(1, 3) for x in range(somany)],
'price': [random.randint(150, 300) for x in range(somany)],
'rating': [random.randint(0, 10) for x in range(somany)]})
dfm['country'] = dfm['city'].map(
{'Paris': 'FR', 'London': 'GB', 'Berlin': 'DE', 'New York': 'US', 'Mannheim': 'DE'})
In [50]:
dfm
Out[50]:
In [51]:
dfg = dfm.groupby(['country', 'city', 'category']).mean()
dfg
Out[51]:
In [52]:
dfg.index
Out[52]:
In [53]:
dfg.index.levels
Out[53]:
In [54]:
dfg.index.names
Out[54]:
In [55]:
pd.set_option('display.multi_sparse', False) # Tipp
dfg
Out[55]:
In [56]:
pd.set_option('display.multi_sparse', True)
In [57]:
dfg.index.get_level_values(2)
Out[57]:
In [58]:
dfg.index.get_level_values(1)
Out[58]:
In [59]:
dfg.loc['DE']
Out[59]:
In [60]:
dfg.loc[('DE', 'Mannheim')]
Out[60]:
In [61]:
dfg.loc[('DE', 'Mannheim')].max() # max of column
Out[61]:
In [62]:
dfg.loc[('DE', 'Mannheim', 3)]
Out[62]:
In [63]:
type(dfg.loc[('DE', 'Mannheim', 3)]) # know the type you're working with
Out[63]:
In [64]:
dfg.unstack()
Out[64]:
In [65]:
dfg.unstack().unstack()
Out[65]:
DateTime Index¶
In [66]:
df = pd.read_csv('data/tempm.csv', header=None)
df.columns = ['timestamp', 'celsius']
df.head(5)
Out[66]:
In [67]:
df[:100].plot()
Out[67]:
In [68]:
df.info()
In [69]:
df.index
Out[69]:
In [70]:
df.index = pd.to_datetime(df['timestamp'])
Easy way to work with timeseries
In [71]:
df.head()
Out[71]:
In [72]:
df.index
Out[72]:
In [73]:
df.index.is_monotonic
Out[73]:
In [74]:
df.index.is_unique
Out[74]:
In [75]:
df[:100].plot()
Out[75]:
In [76]:
df['celsius'].groupby([df.index.year, df.index.week]).mean().plot.bar();
In [77]:
df['celsius'].groupby([df.index.year, df.index.dayofyear]).mean().plot();
In [78]:
df['celsius'].groupby([df.index.year, df.index.dayofyear]).agg([min, max]).plot();
In [79]:
df['weekday'] = df.index.weekday
df['weekend'] = df['weekday'].isin({5, 6})
df.groupby(['weekend', df.index.hour])['celsius'].mean().unstack(level=0).plot()
Out[79]:
In [80]:
# selecting ranges
df["2014-08-27":"2014-08-28"]['celsius'].plot()
Out[80]:
In [81]:
df[(df.index.hour > 12) & (df.index.hour <=16)]['celsius'].plot()
Out[81]:
Resampling¶
In [82]:
df['celsius'].resample('D').max().head()
Out[82]:
In [83]:
df['celsius'].resample('M').mean().head()
Out[83]:
In [84]:
df['celsius'].resample('3D').mean().plot();
In [85]:
df['celsius'].resample('D').agg(['min', 'max']).plot();
Categorical¶
In [86]:
sales_data.head()
Out[86]:
In [87]:
sales_data.info()
In [88]:
sales_data.memory_usage()
Out[88]:
In [89]:
m1 = sales_data.memory_usage(deep=True)
m1
Out[89]:
In [90]:
sales_data = sales_data.astype({'customer': 'category', 'product': 'category', 'name': 'category'})
In [91]:
m2 = sales_data.memory_usage(deep=True)
m2
Out[91]:
In [92]:
m2/m1
Out[92]:
In [93]:
m1.sum()-m2.sum(), m2.sum()/m1.sum()
Out[93]:
In [94]:
sales_data.index = sales_data['product']
In [95]:
sales_data.head()
Out[95]:
In [96]:
favorites = ['Harry Potter book', 'Rubik’s Cube', 'Corolla', 'Rubik’s Cube', 'iPhone', 'iPad',
'PlayStation', 'Star Wars', 'Thriller record',
'banana', 'Lipitor']
In [97]:
pd.Categorical(['a', 'b', 'c', 'a', 'b', 'c'])
Out[97]:
In [98]:
a = pd.Categorical(values=['bad', 'neutral', 'nice', 'good', 'excellent'],
categories=['bad', 'neutral', 'nice', 'good', 'excellent'],
ordered=True)
a.max()
Out[98]:
In [99]:
a.min()
Out[99]:
In [ ]:
In [ ]:
In [ ]: