Reading, inspecting,
and cleaning data
from CSV
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N
Stefan Jansen
Instructor
Import and clean data
Ensure that [Link]() is same as CSV source file
Stock exchange listings: [Link]
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
How pandas stores data
Each column has its own data format ( dtype )
dtype affects your calculation and visualization
pandas dtype Column characteristics
object Text, or a mix of text and numeric data
int64 Numeric: whole numbers - 64 bits (≤ 264 )
float64 Numeric: Decimals, or whole numbers with missing values
datetime64 Date and time information
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Import & inspect
import pandas as pd
amex = pd.read_csv('[Link]')
[Link]() # To inspect table structure & data types
RangeIndex: 360 entries, 0 to 359
Data columns (total 8 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 360 non-null object
1 Company Name 360 non-null object
2 Last Sale 346 non-null float64
3 Market Capitalization 360 non-null float64
4 IPO Year 105 non-null float64
5 Sector 238 non-null object
6 Industry 238 non-null object
7 Last Update 360 non-null object
dtypes: float64(3), object(5)
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Dealing with missing values
# Replace 'n/a' with [Link]
amex = pd.read_csv('[Link]', na_values='n/a')
[Link]()
RangeIndex: 360 entries, 0 to 359
Data columns (total 8 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 360 non-null object
1 Company Name 360 non-null object
2 Last Sale 346 non-null float64
3 Market Capitalization 360 non-null float64
4 IPO Year 105 non-null float64
5 Sector 238 non-null object
6 Industry 238 non-null object
7 Last Update 360 non-null object
dtypes: float64(3), object(5)
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Properly parsing dates
amex = pd.read_csv('[Link]',
na_values='n/a',
parse_dates=['Last Update'])
[Link]()
RangeIndex: 360 entries, 0 to 359
Data columns (total 8 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 360 non-null object
1 Company Name 360 non-null object
2 Last Sale 346 non-null float64
3 Market Capitalization 360 non-null float64
4 IPO Year 105 non-null float64
5 Sector 238 non-null object
6 Industry 238 non-null object
7 Last Update 360 non-null datetime64[ns]
dtypes: datetime64[ns](1), float64(3), object(4)
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Showing off the result
[Link](2) # Show first n rows (default: 5)
Stock Symbol Company Name
0 XXII 22nd Century Group, Inc
1 FAX Aberdeen Asia-Pacific Income Fund Inc
Last Sale Market Capitalization IPO Year
0 1.3300 1.206285e+08 NaN
1 5.0000 1.266333e+09 1986.0
Sector Industry Last Update
0 Non-Durables Farming/Seeds/Milling 2017-04-26
1 NaN NaN 2017-04-25
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Let's practice!
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N
Read data from
Excel worksheets
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N
Stefan Jansen
Instructor
Import data from Excel
pd.read_excel(file, sheet_name=0)
Select first sheet by default with sheet_name=0
Select by name with sheet_name='amex'
Import several sheets with list such as sheet_name=['amex', 'nasdaq']
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Import data from one sheet
amex = pd.read_excel('[Link]',
sheet_name='amex',
na_values='n/a')
[Link]()
RangeIndex: 360 entries, 0 to 359
Data columns (total 7 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 360 non-null object
1 Company Name 360 non-null object
2 Last Sale 346 non-null float64
3 Market Capitalization 360 non-null float64
4 IPO Year 105 non-null float64
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Import data from two sheets
listings = pd.read_excel('[Link]',
sheet_name=['amex', 'nasdaq'], # keys = sheet name
na_values='n/a') # values = DataFrame
listings['nasdaq'].info()
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 3167 non-null object
1 Company Name 3167 non-null object
2 Last Sale 3165 non-null float64
3 Market Capitalization 3167 non-null float64
4 IPO Year 1386 non-null float64
...
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Get sheet names
xls = [Link]('[Link]') # [Link] object
exchanges = xls.sheet_names
exchanges
['amex', 'nasdaq', 'nyse']
nyse = pd.read_excel(xls,
sheet_name=exchanges[2],
na_values='n/a')
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Get sheet names
[Link]()
RangeIndex: 3147 entries, 0 to 3146
Data columns (total 7 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 3147 non-null object
1 Company Name 3147 non-null object
... ...
6 Industry 2177 non-null object
dtypes: float64(3), object(4)
memory usage: 172.2+ KB
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Let's practice!
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N
Combine data from
multiple worksheets
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N
Stefan Jansen
Instructor
Combine DataFrames
Concatenate or "stack" a list of [Link] s
Syntax: [Link]([amex, nasdaq, nyse])
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Combine DataFrames
Concatenate or "stack" a list of [Link] s
Syntax: [Link]([amex, nasdaq, nyse])
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Combine DataFrames
Concatenate or "stack" a list of [Link] s
Syntax: [Link]([amex, nasdaq, nyse])
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Concatenate two DataFrames
amex = pd.read_excel('[Link]',
sheet_name='amex',
na_values=['n/a'])
nyse = pd.read_excel('[Link]',
sheet_name='nyse',
na_values=['n/a'])
[Link]([amex, nyse]).info()
Int64Index: 3507 entries, 0 to 3146
Data columns (total 7 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 3507 non-null object
...
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Add a reference column
amex['Exchange'] = 'AMEX' # Add column to reference source
nyse['Exchange'] = 'NYSE'
listings = [Link]([amex, nyse])
[Link](2)
Stock Symbol ... Exchange
0 XXII ... AMEX
1 FAX ... AMEX
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Combine three DataFrames
xls = [Link]('[Link]')
exchanges = xls.sheet_names
# Create empty list to collect DataFrames
listings = []
for exchange in exchanges:
listing = pd.read_excel(xls, sheet_name=exchange)
# Add reference col
listing['Exchange'] = exchange
# Add DataFrame to list
[Link](listing)
# List of DataFrames
combined_listings = [Link](listings)
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Combine three DataFrames
combined_listings.info()
Int64Index: 6674 entries, 0 to 3146
Data columns (total 8 columns):
# Column Non-Null Count Dtype
-- ------ -------------- -----
0 Stock Symbol 6674 non-null object
1 Company Name 6674 non-null object
2 Last Sale 6590 non-null float64
3 Market Capitalization 6674 non-null float64
4 IPO Year 2852 non-null float64
5 Sector 5182 non-null object
6 Industry 5182 non-null object
7 Exchange 6674 non-null object
dtypes: float64(3), object(5)
IMPORTING AND MANAGING FINANCIAL DATA IN PYTHON
Let's practice!
I M P O R T I N G A N D M A N A G I N G F I N A N C I A L D ATA I N P Y T H O N