import pandas as pdcsv stands for comma-separated values. Our target is to import csv file into a PandasDataFrame. The first step is to understands what is the delimiter of the file. Then we use the function read_csv in pandas.
Suppose f is a csv file with tab as delimiters, then we do this:
df = pd.read_csv(f,delimiter ='\t')Note that sometimes data may be messy, explore options of read_csv. For example, some files may have extra spaces at the beginning of the fields, you need
df = pd.read_csv(f,delimiter=' ',skipinitalspace=True)You can also use 'usecols' to specify the columns you want.
json files are human readable, you can think of them as python dictionaries. We want to load them into pandas DataFrame. The furst step is to view your data to get a sense. The second step is to use a function called json_normalize.
from pandas.io.json import json_normalize
df = json_normalize(yourjsonfile,['key1', 'key2', ...], max_level=3)Before we begin, have some basic knowledge on how the internet works and what is http, and what is API.
Getting data from web API usually require reading API manuals for specific APIs.
I recommend using package requests for this task in Python.
# Assign URL to variable: url
url = 'yoururl' # Consult your api documents here.
header = {"Accept-Encoding":"gzip"} # Consult your api documents and requests package website here.
# Package the request, send the request and catch the response: r
param_dict = {your_parameters}# Consult your api documents and requests package website here.
with requests.Session() as s:
download = s.post(url, data=param_dict,headers=header) ## consult post, get, and other http requests.Here I will use a MySQL database for example. We are going to get data from MySQL database using SQL commands in python.
There are abundant choices of packages here. I use mysql-connector package.
import mysql.connector
import pandas as pd
cnx = mysql.connector.connect(user='yourusername', password='yourpassword',
host='yourhostaddress',
database='yourdatabase') ## some arguments are optional!
cursor = cnx.cursor()
query = ("""
YourSQLCode Here
""")
cursor.execute(query)
table_rows = cursor.fetchall()
df= pd.DataFrame(table_rows, columns=cursor.column_names)
cnx.close()Here, I will also include some techniques about joining tables using SQL code.
Self-join is just treat the same table as two tables.
## Suppose populations is a table with column size, country_code and year
SELECT p1.country_code,
p1.size AS size2010,
p2.size AS size2015,
-- 2. From populations (alias as p1)
FROM populations AS p1
-- 3. Join to itself (alias as p2)
INNER JOIN populations AS p2
-- 4. Match on country code
ON p1.country_code = p2.country_code
-- 5. and year (with calculation)
AND p1.year = p2.year - 5;Case when and then: Sometimes you want to put numerical data into groups, it's time to use CASE WHEN THEN ELSE END commands.
Use INTO statements to save query results into a table:
SELECT country_code, size,
CASE WHEN size > 50000000
THEN 'large'
WHEN size > 1000000
THEN 'medium'
ELSE 'small' END
AS popsize_group
INTO pop_plus
FROM populations
WHERE year = 2015;
-- 5. Select fields
select *
-- 1. From countries_plus (alias as c)
from countries_plus as c
-- 2. Join to pop_plus (alias as p)
inner join pop_plus as p
-- 3. Match on country code
on c.code = p.country_code
-- 4. Order the table
order by geosize_group asc;Before you do anything to your newly gained data, it's always a good idea to look at it first.
import pandas as pd
## Suppose the data is loaded in a Pandas DataFrame data
## Look at the table as a whole:
data.info()
data.head()
data.describe()Deal with categorical data:
First, save the names of non-numerical data in a list: LAEBLS.
Then, Convert them into suitable types.
Finally, take a look at unique values of each type.
# Define the lambda function: categorize_label
categorize_label = lambda x: x.astype('category')
# Convert df[LABELS] to a categorical type
df[LABELS] = df[LABELS].apply(categorize_label,axis=0)
# Print the converted dtypes
print(df[LABELS].dtypes)
# Import matplotlib.pyplot
import matplotlib.pyplot as plt
# Calculate number of unique values for each label: num_unique_labels
num_unique_labels = df[LABELS].apply(pd.Series.nunique)
# Plot number of unique values for each label
num_unique_labels.plot(kind='bar')
# Label the axes
plt.xlabel('Labels')
plt.ylabel('Number of unique values')
# Display the plot
plt.show()Basic str manipulations are quite handy in data cleaning.
## This document displays common tools for dealing with datetime data
from datetime import date
from datetime import datetime
### Example1
## Suppose we have a list of strings, representing datetimes, we are going to convert them into datetime objects
example1 = ['2019-12-09 00:00:00', '2019-12-10 00:00:00', '2019-12-11 00:00:00', '2019-12-12 00:00:00', '2019-12-13 00:00:00']
## First we identify time formats
time_format = '%Y-%m-%d %H:%M:%S'
## Then we use strptime method of datetime class to convert them into datetime objects
example1_datetime_list = list(map(lambda x:datetime.strptime(x,time_format),example1))
## We can check out date using ctimp method
## Let's check out the first date in our list
example1_datetime_list[0].ctime()
## Sometimes we only concern about date.
## The classic format called 'iso format' for date is 'YYYY-MM-DD'
## Let's check out the first date in our list in iso format:
example1_datetime_list[0].date().isoformat()
## Convert timedelta to floats for calculations
aFloat = example1_datetime_list[0]/np.timedelta64(1,'D')### This document introduces some common operations of lists in Python
## Concatenate list of lists:
## Suppose x is an example list
x=[[1],[2],[3]]
## If we want [1,2,3] we can do:
new_list=[x[i][j] for i in range(len(x)) for j in range(len(x[i]))]import pandas as pd
## Suppose df is a loaded DataFrame, 'column_name' is the name of one of its columns.
## Set a column as index column
df.set_index('column_name')
## suppose you have df1 and df2 and they both have a column named 'column_name'
## You can merge them using inner join. Save the merged DataFrame as df.
df = df1.merge(df2,how='inner',on='column_name')
### Deal with nans
df['DataFrame Column'] = df['DataFrame Column'].fillna(0)
df.fillna(0)
df.replace(np.nan,0)PCA has the effects of decorrelating radom variables.
Take an example of grain samples with 2 measurements:length and width.
# Import PCA
from sklearn.decomposition import PCA
# Create PCA instance: model
model = PCA()
# Apply the fit_transform method of model to grains: pca_features
pca_features = model.fit_transform(grains)
# Assign 0th column of pca_features: xs
xs = pca_features[:,0]
# Assign 1st column of pca_features: ys
ys = pca_features[:,1]
# Scatter plot xs vs ys
plt.scatter(xs, ys)
plt.axis('equal')
plt.show()
# Calculate the Pearson correlation of xs and ys
correlation, pvalue = pearsonr(xs, ys)
# Display the correlation
print(correlation)
# Show PCA on plot
# Make a scatter plot of the untransformed points
plt.scatter(grains[:,0], grains[:,1])
# Create a PCA instance: model
model = PCA()
# Fit model to points
model.fit(grains)
# Get the mean of the grain samples: mean
mean = model.mean_
# Get the first principal component: first_pc
first_pc = model.components_[0,:]
# Plot first_pc as an arrow, starting at mean
plt.arrow(mean[0], mean[1], first_pc[0], first_pc[1], color='red', width=0.01)
# Keep axes on same scale
plt.axis('equal')
plt.show()
## Show explained variable of PCA
plt.close()
# Perform the necessary imports
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler
from sklearn.pipeline import make_pipeline
import matplotlib.pyplot as plt
# Create scaler: scaler
scaler = StandardScaler()
# Create a PCA instance: pca
pca = PCA()
# Create pipeline: pipeline
pipeline = make_pipeline(scaler,pca)
# Fit the pipeline to 'samples'
pipeline.fit(samples)
# Plot the explained variances
features = range(pca.n_components_)
plt.bar(features, pca.explained_variance_)
plt.xlabel('PCA feature')
plt.ylabel('variance')
plt.xticks(features)
plt.show()Use previous analysis to find intrinsic dimension of the dataset, then specify the number of dimensions to keep in the following PCA dimension reduction. Finally, PCA for feature dimension reduction.
# When the matrices are not sparse
# Import PCA
from sklearn.decomposition import PCA
# Create a PCA model with 2 components: pca
pca = PCA(n_components=2)
# Fit the PCA instance to the scaled samples
pca.fit(scaled_samples)
# Transform the scaled samples: pca_features
pca_features = pca.transform(scaled_samples)
# Print the shape of pca_features
print(pca_features.shape)Let's consider a more detailed example: clustering documents
# Import TfidfVectorizer
from sklearn.feature_extraction.text import TfidfVectorizer
# Create a TfidfVectorizer: tfidf
tfidf = TfidfVectorizer()
# Apply fit_transform to document: csr_mat
csr_mat = tfidf.fit_transform(documents)
# Print result of toarray() method
print(csr_mat.toarray())
# Get the words: words
words = tfidf.get_feature_names()
# Print words
print(words)
## Below, articles is a sparse matrix like csr_mat
# For sparse matrices, we have to use TruncatedSVD instead of PCA.
# Perform the necessary imports
from sklearn.decomposition import TruncatedSVD
from sklearn.cluster import KMeans
from sklearn.pipeline import make_pipeline
# Create a TruncatedSVD instance: svd
svd = TruncatedSVD(n_components=50)
# Create a KMeans instance: kmeans
kmeans = KMeans(n_clusters=6)
# Create a pipeline: pipeline
pipeline = make_pipeline(svd,kmeans)
# Import pandas
import pandas as pd
# Fit the pipeline to articles
pipeline.fit(articles)
# Calculate the cluster labels: labels
labels = pipeline.predict(articles)
# Create a DataFrame aligning labels and titles: df
df = pd.DataFrame({'label': labels, 'article': titles})
# Display df sorted by cluster label
print(df.sort_values('label'))Interpretable Dimension Reduction for non-negative arrays.
# Perform the necessary imports
from sklearn.decomposition import NMF
from sklearn.preprocessing import Normalizer, MaxAbsScaler
from sklearn.pipeline import make_pipeline
# Create a MaxAbsScaler: scaler
scaler = MaxAbsScaler()
# Create an NMF model: nmf
nmf = NMF(n_components=20)
# Create a Normalizer: normalizer
normalizer = Normalizer()
# Create a pipeline: pipeline
pipeline = make_pipeline(scaler,nmf,normalizer)
# Apply fit_transform to artists: norm_features
norm_features = pipeline.fit_transform(artists)
# Import pandas
import pandas as pd
# Create a DataFrame: df
df = pd.DataFrame(norm_features,index=artist_names)
# Select row of 'Bruce Springsteen': artist
artist = df.loc['Bruce Springsteen']
# Compute cosine similarities: similarities
similarities = df.dot(artist)
# Display those with highest cosine similarity
print(similarities.nlargest())