read inspect clean data from csv files
play

Read, inspect, & clean data from csv files Importing & - PowerPoint PPT Presentation

Jun 29, 2023 •159 likes •380 views

IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Read, inspect, & clean data from csv files Importing & Managing Financial Data in Python Import & clean data Ensure that pd.DataFrame() is same as csv source file Stock

  1. IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Read, inspect, & clean data from csv files

  2. Importing & Managing Financial Data in Python Import & clean data ● Ensure that pd.DataFrame() is same as csv source file ● Stock exchange listings: amex-listings.csv Source: h � p://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=AMEX

  3. Importing & Managing Financial Data in Python How pandas stores data ● Each column has its own data format stored in dtype ● dtype a ff ects calculation and visualization pandas dtype Column characteristics Text or a mix of text and numeric data object Numeric: Whole numbers - 64 bits ( ≤ 2 64 ) int64 Numeric: Decimals or whole numbers with missing values float64 datetime64 Date and time information

  4. Importing & Managing Financial Data in Python Import & inspect data In [1]: import pandas as pd In [2]: amex = pd.read_csv('amex-listings.csv') In [3]: amex.info() # To inspect table structure & data types RangeIndex: 360 entries, 0 to 359 Data columns (total 8 columns): Stock Symbol 360 non-null object Company Name 360 non-null object Last Sale 360 non-null object Market Capitalization 360 non-null float64 IPO Year 360 non-null object Sector 360 non-null object Industry 360 non-null object Last Update 360 non-null object dtypes: float64(1), object(7) memory usage: 22.6+ KB

  5. Importing & Managing Financial Data in Python Deal with missing values In [7]: amex = pd.read_csv('amex-listings.csv', na_values='n/a') In [8]: amex.info() np.nan: NumPy Not a Number RangeIndex: 360 entries, 0 to 359 Data columns (total 8 columns): Stock Symbol 360 non-null object Company Name 360 non-null object Last Sale 346 non-null float64 Market Capitalization 360 non-null float64 IPO Year 105 non-null float64 Sector 238 non-null object Industry 238 non-null object Last Update 360 non-null object dtypes: float64(3), object(5)

  6. Importing & Managing Financial Data in Python Properly parse dates In [7]: amex = pd.read_csv('amex-listings.csv', na_values='n/a', parse_dates=['Last Update']) In [8]: amex.info() RangeIndex: 360 entries, 0 to 359 Data columns (total 8 columns): Stock Symbol 360 non-null object Company Name 360 non-null object Last Sale 346 non-null float64 Market Capitalization 360 non-null float64 IPO Year 105 non-null float64 Sector 238 non-null object Industry 238 non-null object Last Update 360 non-null datetime64[ns] dtypes: datetime64[ns](1) float64(3), object(4)

  7. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 Importing & Managing Financial Data in Python Show o ff the result In [7]: amex.head() # Show first n rows (default: 5) 
 Out[7]: Stock Symbol Company Name \ 0 XXII 22nd Century Group, Inc 1 FAX Aberdeen Asia-Pacific Income Fund Inc 2 IAF Aberdeen Australia Equity Fund Inc 3 CH Aberdeen Chile Fund, Inc. 4 ABE Aberdeen Emerging Markets Smaller Company Oppo... Last Sale Market Capitalization IPO Year Sector \ 0 1.3300 1.206285e+08 NaN Consumer Non-Durables 1 5.0000 1.266333e+09 1986.0 NaN 2 6.1500 1.398653e+08 NaN NaN 3 7.2201 6.756346e+07 NaN NaN 4 13.3600 1.288430e+08 NaN NaN Industry Last Update 0 Farming/Seeds/Milling 2017-04-26 1 NaN 2017-04-25 2 NaN 2017-04-23 3 NaN 2017-04-26 4 NaN 2017-04-25

  8. IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Let’s practice!

  9. IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Read data from Excel worksheets

  10. Importing & Managing Financial Data in Python Import data from Excel ● pd.read_excel(file, sheetname=0) ● Select first sheet by default with sheetname=0 ● Select by name with sheetname='amex' ● Import several sheets with list such as sheetname=['amex', 'nasdaq']

  11. Importing & Managing Financial Data in Python Import data from one sheet In [1]: amex = pd.read_excel('listings.xlsx', sheetname='amex', na_values='n/a') In [2]: amex.info() RangeIndex: 360 entries, 0 to 359 Data columns (total 8 columns): Stock Symbol 360 non-null object Company Name 360 non-null object Last Sale 346 non-null float64 Market Capitalization 360 non-null float64 IPO Year 105 non-null float64 Sector 238 non-null object Industry 238 non-null object dtypes: datetime64[ns](1) float64(3), object(4)

  12. Importing & Managing Financial Data in Python Import data from two sheets In [3]: listings = pd.read_excel('listings.xlsx', sheetname=['amex', 'nasdaq'], na_values='n/a') Listings: dictionary ● Keys: sheet names In [4]: listings['nasdaq'].info() ● Values: DataFrame RangeIndex: 3167 entries, 0 to 3166 Data columns (total 7 columns): Stock Symbol 3167 non-null object Company Name 3167 non-null object Last Sale 3165 non-null float64 Market Capitalization 3167 non-null float64 IPO Year 1386 non-null float64 Sector 2767 non-null object Industry 2767 non-null object dtypes: float64(3), object(4)

  13. Importing & Managing Financial Data in Python Get sheet names In [5]: xls = pd.ExcelFile('listings.xlsx') # pd.ExcelFile object In [6]: exchanges = xls.sheet_names In [7]: exchanges Out[7]: ['amex', 'nasdaq', 'nyse'] In [8]: nyse = pd.read_excel(xls, sheetname=exchanges[2], na_values= ' n/ a' ) In [14]: nyse.info() RangeIndex: 3147 entries, 0 to 3146 Data columns (total 7 columns): Stock Symbol 3147 non-null object Company Name 3147 non-null object … Industry 2177 non-null object dtypes: float64(3), object(4) memory usage: 172.2+ KB

  14. IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Let’s practice!

  15. IMPORTING & MANAGING FINANCIAL DATA IN PYTHON Combine data from multiple worksheets

  16. Importing & Managing Financial Data in Python Combine data frames ● Concatenate or “stack” a list of pd.DataFrames ● Syntax: pd.concat([amex, nasdaq, nyse]) Matches on column names Exchanges Symbol Name … Last Sale NASDAQ Symbol Name … Last Sale 0 GOOG Google … 623.21 1 … … … … 0 GOOG Google 623.21 2 … … … … NYSE Symbol Name … Last Sale 1 3 … … … … JP axis=0 0 JPM 84.40 2 0 JPM JP 84.40 Mortan AMEX Symbol Name … Last Sale 1 3 Morga 1 … … … … 0 BTI British … 67.24 2 2 … … … … 3 … … … … 1 IMO … … … 3 0 BTI British 67.24 2 … … … … 1 … … … … 3 … … … … 2 … … … … 3 … … … …

  17. Importing & Managing Financial Data in Python Concatenate two data frames In [1]: amex = pd.read_excel('listings.xlsx', sheetname='amex', na_values='n/a') In [2]: nyse = pd.read_excel('listings.xlsx', sheetname='nyse', na_values='n/a') In [3]: pd.concat([amex, nyse]).info() Int64Index: 3507 entries, 0 to 3146 Data columns (total 7 columns): Stock Symbol 3507 non-null object Company Name 3507 non-null object Last Sale 3425 non-null float64 Market Capitalization 3507 non-null float64 IPO Year 1466 non-null float64 Sector 2415 non-null object Industry 2415 non-null object dtypes: float64(3), object(4)

  18. Importing & Managing Financial Data in Python Add a reference column In [4]: amex['Exchange'] = 'AMEX' # Add column to reference source In [5]: nyse['Exchange'] = 'NYSE' In [6]: listings = pd.concat([amex, nyse]) In [7]: listings.head(2) Out[22]: Stock Symbol Company Name Last Sale \ 0 XXII 22nd Century Group, Inc 1.33 1 FAX Aberdeen Asia-Pacific Income Fund Inc 5.00 Market Capitalization IPO Year Sector \ 0 1.206285e+08 NaN Consumer Non-Durables 1 1.266333e+09 1986.0 NaN Industry Exchange 0 Farming/Seeds/Milling AMEX 1 NaN AMEX

  19. Importing & Managing Financial Data in Python Combine three data frames In [1]: xls = pd.ExcelFile('listings.xlsx') In [2]: exchanges = xls.sheet_names In [3]: listings = [] # Create empty list to collect DataFrames In [4]: for exchange in exchanges: ...: listing = pd.read_excel(xls, sheetname=exchange) ...: listing['Exchange'] = exchange # Add reference column ...: listings.append(listing) # Add DataFrame to list In [5]: combined_listings = pd.concat(listings) # List of DataFrames

Recommend

Reading and writing files We can interact with files in three modes 1. read mode ("r"):

Reading and writing files We can interact with files in three modes 1. read mode ("r"):

Reading and writing files We can interact with files in three modes 1. read mode ("r"): Read data from file into python variables. File remains unchanged. 2. write mode ("w"): Write data from python variables to file.

288 views • 16 slides
Using files ITEC 1630 We save data in files on disk or some Week 9: Files & Streams

Using files ITEC 1630 We save data in files on disk or some Week 9: Files & Streams

Using files ITEC 1630 We save data in files on disk or some Week 9: Files & Streams other media so that we dont lose it even if the computer is shut off Files can store more data than may fit in Yves Lesprance working memory

248 views • 4 slides
Interacting with Files Python Files Files Basic container of data in modern computing

Interacting with Files Python Files Files Basic container of data in modern computing

Interacting with Files Python Files Files Basic container of data in modern computing system Organized into a hierarchy of directories Files / /etc /Applications /var /tmp /Users /etc/Apache /etc/master.passwd

232 views • 19 slides
Accessing Files in Python Learning Objectives Concepts about files in Python How to open

Accessing Files in Python Learning Objectives Concepts about files in Python How to open

Accessing Files in Python Learning Objectives Concepts about files in Python How to open files Different ways of reading files How to write out files How to read then search, count, etc. on files Concepts about

615 views • 26 slides
Data Analysis Modeling and Parsing 15-110 Friday 11/13 Learning Goals Read and write

Data Analysis Modeling and Parsing 15-110 Friday 11/13 Learning Goals Read and write

Data Analysis Modeling and Parsing 15-110 Friday 11/13 Learning Goals Read and write data from files Interpret data according to different protocols: plaintext, CSV, and JSON Reformat data to find, add, remove, or reinterpret

428 views • 28 slides
Introduction read.csv Importing Data in R Importing data in R ? Importing Data in R 5 types

Introduction read.csv Importing Data in R Importing data in R ? Importing Data in R 5 types

IMPORTING DATA IN R Introduction read.csv Importing Data in R Importing data in R ? Importing Data in R 5 types Flat files Data from Excel Databases Web Statistical so ware Importing Data in R Flat

245 views • 21 slides
Manipulating Data Files in Python Learning Objectives Working with CSV files Reading

Manipulating Data Files in Python Learning Objectives Working with CSV files Reading

Manipulating Data Files in Python Learning Objectives Working with CSV files Reading and writing Moving into and out of data structures Accessing files in other folders JSON files Reading and writing Regular

519 views • 36 slides
INTRO TO OOP FOR STREAMS AND DATA SCIENCE FILES PROF. JOHN GAUCH OVERVIEW OVERVIEW OVERVIEW

INTRO TO OOP FOR STREAMS AND DATA SCIENCE FILES PROF. JOHN GAUCH OVERVIEW OVERVIEW OVERVIEW

5/18/20 INTRO TO OOP FOR STREAMS AND DATA SCIENCE FILES PROF. JOHN GAUCH OVERVIEW OVERVIEW OVERVIEW Many programs are "data processing" applications Files are very useful for data processing applications Read the input data

337 views • 8 slides
Data Explorer Inspect, Visualize, and Collaborate from Any PDI Step Ben Hopkins Pentaho Senior

Data Explorer Inspect, Visualize, and Collaborate from Any PDI Step Ben Hopkins Pentaho Senior

Data Explorer Inspect, Visualize, and Collaborate from Any PDI Step Ben Hopkins Pentaho Senior Product Manager, Hitachi Vantara Agenda Today, we will cover: Background on Data Explorer (DE) and its main use cases Deeper dive on specific

382 views • 22 slides
CSCI26I File I/O in Detail ? Review Programs can read and write files to disk And from

CSCI26I File I/O in Detail ? Review Programs can read and write files to disk And from

CSCI26I File I/O in Detail ? Review Programs can read and write files to disk And from any stream ! #include <fstream> ifstream my_file_object(filename.ext) Use while loops to repeatedly read from the stream

781 views • 14 slides
File Systems: Fundamentals File operations Files Create, Open, Read, Write, Seek, Delete,

File Systems: Fundamentals File operations Files Create, Open, Read, Write, Seek, Delete,

Files Fundamental Ontology of File Systems What is a file? Metadata A named collection of related information recorded on secondary The index node (inode) is the fundamental data structure storage (e.g., disks) The superblock also has

448 views • 5 slides
Welcome to the course! Importing Data in Python I Import data Flat files, e.g. .txts,

Welcome to the course! Importing Data in Python I Import data Flat files, e.g. .txts,

IMPORTING DATA IN PYTHON I Welcome to the course! Importing Data in Python I Import data Flat files, e.g. .txts, .csvs Files from other so ware Relational databases Importing Data in Python I Plain text files Source:

537 views • 39 slides
Disks and Files (Part 2) Disk technology and how to make disk read/writes faster (last time)

Disks and Files (Part 2) Disk technology and how to make disk read/writes faster (last time)

Outline Disks and Files (Part 2) Disk technology and how to make disk read/writes faster (last time) Buffer management Storing database files on disk (Chapter 9 of textbook)

447 views • 7 slides
Files and File Systems files: persistent, named data objects data consists of a sequence of

Files and File Systems files: persistent, named data objects data consists of a sequence of

File Systems 1 Files and File Systems files: persistent, named data objects data consists of a sequence of numbered bytes file may change size over time file has associated meta-data examples: owner, access controls, file type,

459 views • 32 slides
STATS 701 Data Analysis using Python Lecture 6: Files Persistent data So far, we only know how

STATS 701 Data Analysis using Python Lecture 6: Files Persistent data So far, we only know how

STATS 701 Data Analysis using Python Lecture 6: Files Persistent data So far, we only know how to write transient programs Data disappears once the program stops running Files allow for persistence Work done by a program can be saved to

577 views • 23 slides
Objectives become familiar with the concept of an I/O stream Streams and File I/O

Objectives become familiar with the concept of an I/O stream Streams and File I/O

Objectives become familiar with the concept of an I/O stream Streams and File I/O understand the difference between binary files and text files Chapter 9 learn how to save data in a file learn how to read data from a file

168 views • 13 slides
Files l Mostly handle like any sequential data type A sequence of characters if a text file, or

Files l Mostly handle like any sequential data type A sequence of characters if a text file, or

Starting chapter 5 Files l Mostly handle like any sequential data type A sequence of characters if a text file, or a sequence of bytes if a binary file l First open file, and say purpose read or write inputFile = open('mydata.txt', 'r')

625 views • 16 slides
Data types CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Prepare and clean data name

Data types CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Prepare and clean data name

Data types CLEAN IN G DATA IN P YTH ON Daniel Chen Instructor Prepare and clean data name sex treatment a treatment b 0 Daniel male - 42 1 John male 12 31 2 Jane female 24 27 CLEANING DATA IN PYTHON Data types print(df.dtypes)

771 views • 62 slides
Importing text files Importing and Managing Financial Data in R getSymbols() with CSV files

Importing text files Importing and Managing Financial Data in R getSymbols() with CSV files

IMPORTING AND MANAGING FINANCIAL DATA IN R Importing text files Importing and Managing Financial Data in R getSymbols() with CSV files Well-forma ed One instrument per file Columns: date, open, high, low, close, volume,

543 views • 28 slides
Distributed File Systems Security: Anonymous access all files available to all users 14B.

Distributed File Systems Security: Anonymous access all files available to all users 14B.

6/6/2017 Distributed File Systems Security: Anonymous access all files available to all users 14B. Remote Data: Security no authentication required may be limited to read-only access 14C. Remote Data: Reliability & Robustness

89 views • 8 slides
For Friday Read chapter 22 Program 4 due Program 4 Any questions? Learning

For Friday Read chapter 22 Program 4 due Program 4 Any questions? Learning

For Friday Read chapter 22 Program 4 due Program 4 Any questions? Learning mini-project Worth 2 homeworks Due Monday Foil6 is available in /home/mecalif/public/itk340/foil A manual and sample data files are there as

539 views • 21 slides
Learning to denoise without clean data Joshua Batson hep-ai seminar 10/18/18 Noisy data is

Learning to denoise without clean data Joshua Batson hep-ai seminar 10/18/18 Noisy data is

Learning to denoise without clean data Joshua Batson hep-ai seminar 10/18/18 Noisy data is clean data + noise Noisy data is clean data + noise We want to predict You need a prior Prior: nearby pixels are similar Denoising strategy: local

546 views • 27 slides
STATS 507 Data Analysis in Python Lecture 5: Files, Classes, Operators and Inheritance

STATS 507 Data Analysis in Python Lecture 5: Files, Classes, Operators and Inheritance

STATS 507 Data Analysis in Python Lecture 5: Files, Classes, Operators and Inheritance Persistent data So far, we only know how to write transient programs Data disappears once the program stops running Files allow for persistence Work

1.35k views • 69 slides
Distributed File Systems Security: Anonymous access all files available to all users 14B.

Distributed File Systems Security: Anonymous access all files available to all users 14B.

5/30/2018 Distributed File Systems Security: Anonymous access all files available to all users 14B. Remote Data Access: Security no authentication required may be limited to read-only access 14C. Remote Data: Robustness examples:

391 views • 9 slides

More recommend