2. explore_view_subset_selecting (python data science).md

[TOC]

Selecting

Cells

Y = data['TV'] # column
Y = data.TV
df.ix['Arizona', 2]  # Select the third cell in the row named Arizona
df.ix[2, 'deaths']  # Select the third cell down in the column named deaths

df[:2] # first two rows
df.ix['Maricopa'] # view a row
df.ix[:, 'coverage'] # view a column
df.ix['Yuma', 'coverage'] # view the value based on a row and column

Rows

by index

df.iloc[:2] # rows by row number
df.iloc[1:2] # Select the second and third row
df.iloc[2:] # Select every row after the third row
df.iloc[3:6,0:3] #  

by label

df.loc[:'Arizona'] # all rows by index label
df.ix[['Arizona', 'Texas']]   # .ix is the combination of both .loc and .iloc. Integers are first considered labels,if not found, falls back on pos indexing

conditional

df.query('A > C')
df.query('A > 0') 
df.query('A > 0 & A < 1')
df.query('A > B | A > C')
df[df['coverage'] > 50] # all rows where coverage is more than 50
df[(df['deaths'] > 500) | (df['deaths'] < 50)]
df[(df['score'] > 1) & (df['score'] < 5)]
df[~(df['regiment'] == 'Dragoons')] # Select all the regiments not named "Dragoons"

df[df['age'].notnull() & df['sex'].notnull()]  # ignore the missing data points

is in

df[df.name.isin(value_list)]   # value_list = ['Tina', 'Molly', 'Jason']
df[~df.name.isin(value_list)]

partial matching

df2[df2.E.str.contains("tw|ou")]

regex

df['raw'].str.contains('....-..-..', regex=True)  # regex

where cells are arrays

df[df['country'].map(lambda country: 'Syria' in country)]

random

df.take(np.random.permutation(len(df))[:2])

## Cell ranges

```python
df['x3'][:1]  # take column 'x3', 

Columns

df.iloc[:,:2] # Select the first 2 columns
feature_cols = ['TV','Radio','Newspaper']
x = data[feature_cols]
data[['TV','Radio','Newspaper']]

by column labels

df.loc[:,['A','B']]  # syntax is: df.loc[rows_index, cols_index]

conditional

df.filter(like='data')
df['preTestScore'].where(df['postTestScore'] > 50) # Find where a value exists in a column

Sorting

df.sort_values(by='reports', ascending=0)  # Sort the dataframe's rows by reports, in descending order
df.sort_values(by=['coverage', 'reports'])  # Sort the dataframe's rows by coverage and then by reports, in ascending order

Exploring

general

data.head()
data.tail()
data.tail().transpose()

dimensions

data.shape()

List unique values in the df['name'] column

df.name.unique()

Stats

Descriptive statistics by group

data['preTestScore'].groupby(df['company']).describe()
data['preTestScore'].describe()

Count the number of non-NA values

data['preTestScore'].count()
data['preTestScore'].min()

Value counts

data.group.value_counts()

Correlation Matrix Of Values

df.corr()

pd.rolling_mean(df, 2)  # Calculate the moving average. That is, take
                        # the first two values, average them, 
                        # then drop the first and add the third, etc.

Mean preTestScores grouped by regiment and company

#regiment    company
#Dragoons    1st         3.5
#            2nd        27.5
#Nighthawks  1st        14.0
#            2nd        16.5
#Scouts      1st         2.5
#            2nd         2.5
#dtype: float64

data['preTestScore'].groupby([df['regiment'], data['company']]).mean()

Mean preTestScores grouped by regiment and company without heirarchical indexing

#company	1st	2nd
#regiment		
#Dragoons	3.5	27.5
#Nighthawks	14.0	16.5
#Scouts	2.5	2.5

data['preTestScore'].groupby([data['regiment'], data['company']]).mean().unstack()

Group the entire dataframe by regiment and company

#preTestScore	postTestScore
#regiment	company		
#Dragoons	1st	3.5	47.5
#2nd	27.5	75.5
#Nighthawks	1st	14.0	59.5
#2nd	16.5	59.5
#Scouts	1st	2.5	66.0
#2nd	2.5	66.0

df.groupby(['regiment', 'company']).mean()

Count the number of times each number of deaths occurs in each regiment

 Input
	guardCorps	corps1	corps2	corps3	corps4	corps5	corps6	corps7	corps8	corps9	corps10	corps11	corps14	corps15
 1875	0	0	0	0	0	0	0	1	1	0	0	0	1	0
 1876	2	0	0	0	1	0	0	0	0	0	0	0	1	1
 1877	2	0	0	0	0	0	1	1	0	0	1	0	2	0

result = horsekick.apply(pd.value_counts).fillna(0); result

Create a crosstab table by company and regiment

 regiment	company	experience	name	preTestScore	postTestScore
 0	Nighthawks	infantry	veteran	Miller	4	25
 1	Nighthawks	infantry	rookie	Jacobson	24	94
 2	Nighthawks	cavalry	veteran	Ali	31	57
 -->
 company	cavalry	infantry	All
 regiment			
 Dragoons	2	2	4
 Nighthawks	2	2	4

Counting the number of observations by regiment and category

pd.crosstab(df.regiment, df.company, margins=True)

?

df['preTestScore'].idxmax() # row with max value