# Notes on data science and artificial intelligence technology

Keywords: IPython

# XIX. Data sorting (5)

Author: Chris Albon

Translator: Flying dragon

Agreement: CC BY-NC-SA 4.0

## Standardization column

# Import required modules
import pandas as pd
from sklearn import preprocessing

# Set chart inline
%matplotlib inline

# Create a sample data frame with an denormalized column
data = {'score': [234,24,14,27,-74,46,73,-18,59,160]}
df = pd.DataFrame(data)
df
score
0 234
1 24
2 14
3 27
4 -74
5 46
6 73
7 -18
8 59
9 160
# View as denormalized data
df['score'].plot(kind='bar')

# <matplotlib.axes._subplots.AxesSubplot at 0x11b9c88d0>
png
# Create x, where the value of the resulting column of X is a floating-point number
x = df[['score']].values.astype(float)

# Creating a minmax processor object
min_max_scaler = preprocessing.MinMaxScaler()

# Create an object, transform the data, and fit the minmax processor
x_scaled = min_max_scaler.fit_transform(x)

# Running the normalizer on a data frame
df_normalized = pd.DataFrame(x_scaled)

# View data frame
df_normalized
0
0 1.000000
1 0.318182
2 0.285714
3 0.327922
4 0.000000
5 0.389610
6 0.477273
7 0.181818
8 0.431818
9 0.759740
# Draw data frame
df_normalized.plot(kind='bar')

# <matplotlib.axes._subplots.AxesSubplot at 0x11ba31c50>
png

# Import module
import pandas as pd

raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks', 'Dragoons', 'Dragoons', 'Dragoons', 'Dragoons', 'Scouts', 'Scouts', 'Scouts', 'Scouts'],
'company': ['1st', '1st', '2nd', '2nd', '1st', '1st', '2nd', '2nd','1st', '1st', '2nd', '2nd'],
'TestScore': [4, 24, 31, 2, 3, 4, 24, 31, 2, 3, 2, 3]}
df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'TestScore'])
df
regiment company TestScore
0 Nighthawks 1st 4
1 Nighthawks 1st 24
2 Nighthawks 2nd 31
3 Nighthawks 2nd 2
4 Dragoons 1st 3
5 Dragoons 1st 4
6 Dragoons 2nd 24
7 Dragoons 2nd 31
8 Scouts 1st 2
9 Scouts 1st 3
10 Scouts 2nd 2
11 Scouts 2nd 3
# Create a PivotTable of group mean by company and team
pd.pivot_table(df, index=['regiment','company'], aggfunc='mean')
TestScore
regiment company
Dragoons 1st 3.5
2nd 27.5
Nighthawks 1st 14.0
2nd 16.5
Scouts 1st 2.5
2nd 2.5
# Create a PivotTable of group counts by company and team
df.pivot_table(index=['regiment','company'], aggfunc='count')
TestScore
regiment company
Dragoons 1st 2
2nd 2
Nighthawks 1st 2
2nd 2
Scouts 1st 2
2nd 2

## Quick modification of string columns in Pandas

I often need or want to change the case of all items in a string (for example, Brazil to Brazil, etc.). There are many ways to achieve this, but I've determined that this is the easiest and fastest way.

# Import pandas
import pandas as pd

# Create a list of names
first_names = pd.Series(['Steve Murrey', 'Jane Fonda', 'Sara McGully', 'Mary Jane'])

# Print column
first_names

'''
0    Steve Murrey
1      Jane Fonda
2    Sara McGully
3       Mary Jane
dtype: object
'''

# Print column lowercase
first_names.str.lower()

'''
0    steve murrey
1      jane fonda
2    sara mcgully
3       mary jane
dtype: object
'''

# Print upper case of columns
first_names.str.upper()

'''
0    STEVE MURREY
1      JANE FONDA
2    SARA MCGULLY
3       MARY JANE
dtype: object
'''

first_names.str.title()

'''
0    Steve Murrey
1      Jane Fonda
2    Sara Mcgully
3       Mary Jane
dtype: object
'''

# Print columns separated by spaces
first_names.str.split(" ")

'''
0    [Steve, Murrey]
1      [Jane, Fonda]
2    [Sara, McGully]
3       [Mary, Jane]
dtype: object
'''

# Print initial capital columns
first_names.str.capitalize()

'''
0    Steve murrey
1      Jane fonda
2    Sara mcgully
3       Mary jane
dtype: object
'''

I see. More string methods in Here.

## Random sampling data frame

# Import module
import pandas as pd
import numpy as np

raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
'last_name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze'],
'age': [42, 52, 36, 24, 73],
'preTestScore': [4, 24, 31, 2, 3],
'postTestScore': [25, 94, 57, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'preTestScore', 'postTestScore'])
df
first_name last_name age preTestScore postTestScore
0 Jason Miller 42 4 25
1 Molly Jacobson 52 24 94
2 Tina Ali 36 31 57
3 Jake Milner 24 2 62
4 Amy Cooze 73 3 70
# Do not put back a random subset of size 2
df.take(np.random.permutation(len(df))[:2])
first_name last_name age preTestScore postTestScore
1 Molly Jacobson 52 24 94
4 Amy Cooze 73 3 70

## Row ranking of data frames

# Import module
import pandas as pd

# Create data frame
data = {'name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
'year': [2012, 2012, 2013, 2014, 2014],
'reports': [4, 24, 31, 2, 3],
'coverage': [25, 94, 57, 62, 70]}
df = pd.DataFrame(data, index = ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'])
df
coverage name reports year
Cochice 25 Jason 4 2012
Pima 94 Molly 24 2012
Santa Cruz 57 Tina 31 2013
Maricopa 62 Jake 2 2014
Yuma 70 Amy 3 2014

5 rows × 4 columns

# Create a new column that is the ascending rank of the coverage value
df['coverageRanked'] = df['coverage'].rank(ascending=1)
df
coverage name reports year coverageRanked
Cochice 25 Jason 4 2012 1
Pima 94 Molly 24 2012 5
Santa Cruz 57 Tina 31 2013 2
Maricopa 62 Jake 2 2014 3
Yuma 70 Amy 3 2014 4

5 rows × 5 columns

## Regular expression basis

# Import regular package
import re

import sys

text = 'The quick brown fox jumped over the lazy black bear.'

three_letter_word = '\w{3}'

pattern_re = re.compile(three_letter_word); pattern_re

re.compile(r'\w{3}', re.UNICODE)

re_search = re.search('..own', text)

if re_search:
# Print search results
print(re_search.group())

# brown

re.match

re.match() is only used to match the beginning of a string or the entire string. For anything else, use re.search.

### Match all three letter words in text

# Match all three letter words in text
re_match = re.match('..own', text)

if re_match:
# Print all matches
print(re_match.group())
else:
# Print this
print('No matches')

# No matches

re.split

# Use 'e' as the separator to split the string.
re_split = re.split('e', text); re_split

# ['Th', ' quick brown fox jump', 'd ov', 'r th', ' lazy black b', 'ar.']

re.sub

Replace the regular expression pattern string with something else. 3 represents the maximum number of replacements to make.

# Replace the first three 'e' instances with 'e' and print them out
re_sub = re.sub('e', 'E', text, 3); print(re_sub)

# ThE quick brown fox jumpEd ovEr the lazy black bear.

## Regular expression example

# Import regex
import re

# Create some data
text = 'A flock of 120 quick brown foxes jumped over 30 lazy brown, bears.'

re.findall('^A', text)

# ['A']

re.findall('bears.\$', text)

# ['bears.']

re.findall('f..es', text)

# ['foxes']

# Find all vowels
re.findall('[aeiou]', text)

# ['o', 'o', 'u', 'i', 'o', 'o', 'e', 'u', 'e', 'o', 'e', 'a', 'o', 'e', 'a']

# Find all characters that are not lowercase vowels
re.findall('[^aeiou]', text)

'''
['A',
' ',
'f',
'l',
'c',
'k',
' ',
'f',
' ',
'1',
'2',
'0',
' ',
'q',
'c',
'k',
' ',
'b',
'r',
'w',
'n',
' ',
'f',
'x',
's',
' ',
'j',
'm',
'p',
'd',
' ',
'v',
'r',
' ',
'3',
'0',
' ',
'l',
'z',
'y',
' ',
'b',
'r',
'w',
'n',
',',
' ',
'b',
'r',
's',
'.']
'''

re.findall('a|A', text)

# ['A', 'a', 'a']

# Find any instances of 'fox'
re.findall('(foxes)', text)

# ['foxes']

# Find all five letter words
re.findall('\w\w\w\w\w', text)

# ['flock', 'quick', 'brown', 'foxes', 'jumpe', 'brown', 'bears']

re.findall('\W\W', text)

# [', ']

re.findall('\s', text)

# [' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ']

re.findall('\S\S', text)

'''
['fl',
'oc',
'of',
'12',
'qu',
'ic',
'br',
'ow',
'fo',
'xe',
'ju',
'mp',
'ed',
'ov',
'er',
'30',
'la',
'zy',
'br',
'ow',
'n,',
'be',
'ar',
's.']
'''

re.findall('\d\d\d', text)

# ['120']

re.findall('\D\D\D\D\D', text)

'''
['A flo',
'ck of',
' quic',
'k bro',
'wn fo',
'xes j',
'umped',
' over',
' lazy',
' brow',
'n, be']
'''

re.findall('\AA', text)

# ['A']

re.findall('bears.\Z', text)

# ['bears.']

re.findall('\b[foxes]', text)

# []

re.findall('\n', text)

# []

re.findall('[Ff]oxes', 'foxes Foxes Doxes')

# ['foxes', 'Foxes']

re.findall('[Ff]oxes', 'foxes Foxes Doxes')

# ['foxes', 'Foxes']

re.findall('[a-z]', 'foxes Foxes')

# ['f', 'o', 'x', 'e', 's', 'o', 'x', 'e', 's']

re.findall('[A-Z]', 'foxes Foxes')

# ['F']

re.findall('[a-zA-Z0-9]', 'foxes Foxes')

# ['f', 'o', 'x', 'e', 's', 'F', 'o', 'x', 'e', 's']

re.findall('[^aeiou]', 'foxes Foxes')

# ['f', 'x', 's', ' ', 'F', 'x', 's']

re.findall('[^0-9]', 'foxes Foxes')

# ['f', 'o', 'x', 'e', 's', ' ', 'F', 'o', 'x', 'e', 's']

re.findall('foxes?', 'foxes Foxes')

# ['foxes']

re.findall('ox*', 'foxes Foxes')

# ['ox', 'ox']

re.findall('ox+', 'foxes Foxes')

# ['ox', 'ox']

re.findall('\d{3}', text)

# ['120']

re.findall('\d{2,}', text)

# ['120', '30']

re.findall('\d{2,3}', text)

# ['120', '30']

re.findall('^A', text)

# ['A']

re.findall('bears.\$', text)

# ['bears.']

re.findall('\AA', text)

# ['A']

re.findall('bears.\Z', text)

# ['bears.']

re.findall('bears(?=.)', text)

# ['bears']

re.findall('foxes(?!!)', 'foxes foxes!')

# ['foxes']

re.findall('foxes|foxes!', 'foxes foxes!')

# ['foxes', 'foxes']

re.findall('fox(es!)', 'foxes foxes!')

# ['es!']

re.findall('foxes(!)', 'foxes foxes!')

# ['!']

## Re index sequence and data frame

# Import module
import pandas as pd
import numpy as np

# Create a fire risk sequence for Southern Arizona
brushFireRisk = pd.Series([34, 23, 12, 23], index = ['Bisbee', 'Douglas', 'Sierra Vista', 'Tombstone'])
brushFireRisk

'''
Bisbee          34
Douglas         23
Sierra Vista    12
Tombstone       23
dtype: int64
'''

# Reindex the sequence and create a new sequence variable
brushFireRiskReindexed = brushFireRisk.reindex(['Tombstone', 'Douglas', 'Bisbee', 'Sierra Vista', 'Barley', 'Tucson'])
brushFireRiskReindexed

'''
Tombstone       23.0
Douglas         23.0
Bisbee          34.0
Sierra Vista    12.0
Barley           NaN
Tucson           NaN
dtype: float64
'''

# Reindex the sequence and fill in 0 at any missing index
brushFireRiskReindexed = brushFireRisk.reindex(['Tombstone', 'Douglas', 'Bisbee', 'Sierra Vista', 'Barley', 'Tucson'], fill_value = 0)
brushFireRiskReindexed

'''
Tombstone       23
Douglas         23
Bisbee          34
Sierra Vista    12
Barley           0
Tucson           0
dtype: int64
'''

# Create data frame
data = {'county': ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'],
'year': [2012, 2012, 2013, 2014, 2014],
'reports': [4, 24, 31, 2, 3]}
df = pd.DataFrame(data)
df
county reports year
0 Cochice 4 2012
1 Pima 24 2012
2 Santa Cruz 31 2013
3 Maricopa 2 2014
4 Yuma 3 2014
# Change row order (index)
df.reindex([4, 3, 2, 1, 0])
county reports year
4 Yuma 3 2014
3 Maricopa 2 2014
2 Santa Cruz 31 2013
1 Pima 24 2012
0 Cochice 4 2012
# Change column order (index)
columnsTitles = ['year', 'reports', 'county']
df.reindex(columns=columnsTitles)
year reports county
0 2012 4 Cochice
1 2012 24 Pima
2 2013 31 Santa Cruz
3 2014 2 Maricopa
4 2014 3 Yuma

## Rename column title

Come from StackOverflow Upper rgalbo.

# Import required modules
import pandas as pd

# Create a dictionary for the list as a value
raw_data = {'0': ['first_name', 'Molly', 'Tina', 'Jake', 'Amy'],
'1': ['last_name', 'Jacobson', 'Ali', 'Milner', 'Cooze'],
'2': ['age', 52, 36, 24, 73],
'3': ['preTestScore', 24, 31, 2, 3]}

# Create data frame
df = pd.DataFrame(raw_data)

# View data frame
df
0 1 2 3
0 first_name last_name age preTestScore
1 Molly Jacobson 52 24
2 Tina Ali 36 31
3 Jake Milner 24 2
4 Amy Cooze 73 3
# Create a new variable named header from the first row of the dataset

'''
0      first_name
1       last_name
2             age
3    preTestScore
Name: 0, dtype: object
'''

# Replace the data frame with a new data frame without the first row
df = df[1:]

# Rename column values for data frames using the header variable
first_name last_name age preTestScore
1 Molly Jacobson 52 24
--- --- --- --- ---
2 Tina Ali 36 31
--- --- --- --- ---
3 Jake Milner 24 2
--- --- --- --- ---
4 Amy Cooze 73 3
--- --- --- --- ---

## Rename column names for multiple data frames

# Import module
import pandas as pd

# Set the maximum row display of ipython
pd.set_option('display.max_row', 1000)

# Set the maximum column width of ipython
pd.set_option('display.max_columns', 50)

# Create sample data frame
data = {'Commander': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
'Date': ['2012, 02, 08', '2012, 02, 08', '2012, 02, 08', '2012, 02, 08', '2012, 02, 08'],
'Score': [4, 24, 31, 2, 3]}
df = pd.DataFrame(data, index = ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'])
df
Commander Date Score
Cochice Jason 2012, 02, 08 4
Pima Molly 2012, 02, 08 24
Santa Cruz Tina 2012, 02, 08 31
Maricopa Jake 2012, 02, 08 2
Yuma Amy 2012, 02, 08 3
# Rename column name

df
Cochice Jason 2012, 02, 08 4
Pima Molly 2012, 02, 08 24
Santa Cruz Tina 2012, 02, 08 31
Maricopa Jake 2012, 02, 08 2
Yuma Amy 2012, 02, 08 3

df
Commander Time Score
Cochice Jason 2012, 02, 08 4
Pima Molly 2012, 02, 08 24
Santa Cruz Tina 2012, 02, 08 31
Maricopa Jake 2012, 02, 08 2
Yuma Amy 2012, 02, 08 3

## Replacement value

# Import module
import pandas as pd
import numpy as np

raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
'last_name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze'],
'age': [42, 52, 36, 24, 73],
'preTestScore': [-999, -999, -999, 2, 1],
'postTestScore': [2, 2, -999, 2, -999]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'preTestScore', 'postTestScore'])
df
first_name last_name age preTestScore postTestScore
0 Jason Miller 42 -999 2
1 Molly Jacobson 52 -999 2
2 Tina Ali 36 -999 -999
3 Jake Milner 24 2 2
4 Amy Cooze 73 1 -999
# Replace all - 999 with NAN
df.replace(-999, np.nan)
first_name last_name age preTestScore postTestScore
0 Jason Miller 42 NaN 2.0
1 Molly Jacobson 52 NaN 2.0
2 Tina Ali 36 NaN NaN
3 Jake Milner 24 2.0 2.0
4 Amy Cooze 73 1.0 NaN

## Save data frame as CSV

# Import module
import pandas as pd

raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
'last_name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze'],
'age': [42, 52, 36, 24, 73],
'preTestScore': [4, 24, 31, 2, 3],
'postTestScore': [25, 94, 57, 62, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'preTestScore', 'postTestScore'])
df
first_name last_name age preTestScore postTestScore
0 Jason Miller 42 4 25
1 Molly Jacobson 52 24 94
2 Tina Ali 36 31 57
3 Jake Milner 24 2 62
4 Amy Cooze 73 3 70

Save the data frame named df as csv.

df.to_csv('example.csv')

Posted by mingmangat on Sun, 01 Dec 2019 21:15:41 -0800