Pandas Fundamentals

Installation

pip install pandas

Supported File Formats

Format          Description                          Reader
------          -----------                          ------
csv, tsv, txt   Delimited text files                 pd.read_csv
excel           Microsoft xls or xlsx                 pd.read_excel
mysql           Relational database                  pd.read_sql

Getting Started

import pandas as pd

Reading Text Files

CSV with default headers and comma delimiter:

filepath = "data.csv"
df = pd.read_csv(filepath, encoding='utf-8')
print(df.head())

Tab-separated file with out headers:

df = pd.read_csv(
    filepath,
    sep=r"\t",
    header=None,
    names=['username', 'score']
)

Reading Excel Files

pip install xlrd openpyxl

filepath = r"spreadsheet.xlsx"
workbook = pd.read_excel(filepath)
print(workbook)

Reading from MySQL

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='admin',
    password='secret123',
    charset='utf8mb4',
    database='app_db'
)

result = pd.read_sql('SELECT title, author FROM library_book', con=connection)
print(result)

Core Data Structures

Series represents one-dimensional labeled data, similar to a single column or row.

DataFrame represents two-dimensional labeled data with rows and columns, like a spreadsheet.

Creating Series Objects

# Basic one-dimensional array
s1 = pd.Series([5, 'b', 7.8, 9])
print(s1)
print(s1.index)
print(s1.values)

# Series with custom index labels
s2 = pd.Series([10, 'c', 6.5, 3], index=['x', 'y', 'z', 'w'])
print(s2)
print(s2.index)

# Create Series from dictionary
population = {'beijing': 2154, 'shanghai': 2428, 'shenzhen': 1303, 'guangzhou': 1490}
s3 = pd.Series(population)
print(s3)

# Access single element
print(s3['shanghai'])

# Access multiple elements
print(s3[['beijing', 'guangzhou']])

Working with DataFrames

records = {
    'region': ['north', 'south', 'east', 'west'],
    'sales': [450, 380, 520, 290],
    'growth': [1.8, 2.1, 3.2, 0.9]
}
df = pd.DataFrame(records)
print(df)

# Column data types
print(df.dtypes)

# Access index and column names
print(df.columns)
print(df.index)

Selecting columns:

print(df['sales'])
print(df[['sales', 'growth']])

Selecting rows:

# Single row returns Series
print(df.loc[1])
print(type(df.loc[1]))

Selecting multiple rows:

print(df.loc[0:2])
print(type(df.loc[0:2]))