Loading Data

Loading Data#

Before any analysis can begin, data must be loaded into Python. pandas supports a wide range of file formats and data sources out of the box.

import pandas as pd

CSV Files#

CSV (Comma-Separated Values) is one of the most common data exchange formats. Use pd.read_csv() to load CSV files.

Tip

In many European countries, CSV files use ; as separator instead of ,. Always check and set the sep parameter accordingly.

# Load a CSV file with semicolon separator
df = pd.read_csv(
    "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatTempHum.csv",
    sep=";"
)
df.head()
time FlatA_Hum FlatA_Temp FlatB_Hum FlatB_Temp FlatC_Hum FlatC_Temp FlatD_Hum FlatD_Temp
0 2018-10-03 00:00:00 53.0 24.43 38.8 22.40 44.0 24.5 49.0 24.43
1 2018-10-03 01:00:00 53.0 24.40 38.8 22.40 44.0 24.5 49.0 24.40
2 2018-10-03 02:00:00 53.0 24.40 39.3 22.40 44.7 24.5 48.3 24.38
3 2018-10-03 03:00:00 53.0 24.40 40.3 22.40 45.0 24.5 48.0 24.33
4 2018-10-03 04:00:00 53.3 24.40 41.0 22.37 45.2 24.5 47.7 24.30

Useful read_csv parameters#

Parameter

Description

Example

sep

Column separator

sep=";"

parse_dates

Columns to parse as datetime

parse_dates=["time"]

index_col

Column to use as row index

index_col="time"

usecols

Only load specific columns

usecols=["time", "FlatA_Temp"]

nrows

Only read first N rows

nrows=100

encoding

File encoding

encoding="utf-8"

 
# Load with datetime parsing and index
df_parsed = pd.read_csv(
    "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatTempHum.csv",
    sep=";",
    parse_dates=["time"],
    index_col="time"
)
df_parsed.head()
FlatA_Hum FlatA_Temp FlatB_Hum FlatB_Temp FlatC_Hum FlatC_Temp FlatD_Hum FlatD_Temp
time
2018-10-03 00:00:00 53.0 24.43 38.8 22.40 44.0 24.5 49.0 24.43
2018-10-03 01:00:00 53.0 24.40 38.8 22.40 44.0 24.5 49.0 24.40
2018-10-03 02:00:00 53.0 24.40 39.3 22.40 44.7 24.5 48.3 24.38
2018-10-03 03:00:00 53.0 24.40 40.3 22.40 45.0 24.5 48.0 24.33
2018-10-03 04:00:00 53.3 24.40 41.0 22.37 45.2 24.5 47.7 24.30 
df_parsed.dtypes

FlatA_Hum     float64
FlatA_Temp    float64
FlatB_Hum     float64
FlatB_Temp    float64
FlatC_Hum     float64
FlatC_Temp    float64
FlatD_Hum     float64
FlatD_Temp    float64
dtype: object

Loading from URLs#

pd.read_csv() works directly with URLs — no need to download the file first.

url = "https://raw.githubusercontent.com/retomarek/edap/main/edap/sampleData/flatElectricity.csv"

df_elec = pd.read_csv(url, sep=";")
df_elec.head()
time FlatA_Ele FlatB_Ele FlatC_Ele FlatD_Ele
0 2018-11-30 23:45:00 5619.889 7246.254 5125.006 8387.030
1 2018-12-01 00:00:00 5619.904 7246.551 5125.039 8387.085
2 2018-12-01 00:15:00 5619.929 7246.753 5125.077 8387.137
3 2018-12-01 00:30:00 5619.952 7246.933 5125.117 8387.181
4 2018-12-01 00:45:00 5619.973 7247.005 5125.153 8387.222

Excel Files#

Use pd.read_excel() to load Excel files (.xlsx, .xls).

openpyxl required

Reading Excel files requires the openpyxl package. Install it with:

pip install openpyxl

# Example: reading an Excel file (not executed here)
# df_excel = pd.read_excel("path/to/file.xlsx", sheet_name="Sheet1")
# df_excel.head()

Useful read_excel parameters#

Parameter

Description

Example

sheet_name

Name or index of sheet to read

sheet_name="Data"

header

Row number for column headers

header=0

skiprows

Rows to skip at the start

skiprows=2

usecols

Columns to read

usecols="A:D"

JSON Files#

JSON (JavaScript Object Notation) is commonly used for data exchange, especially with web APIs and IoT devices. Many building monitoring systems export sensor data as JSON.

A JSON file typically contains a list of records (like rows) or a nested structure.

Example: sensor readings as JSON#

Imagine a building management system exports sensor readings in this format:

import json
from io import StringIO

# Simulated JSON export from a building management system
json_string = """[
    {"timestamp": "2024-01-15 08:00", "sensor": "Office_101", "temperature": 21.3, "co2": 420},
    {"timestamp": "2024-01-15 09:00", "sensor": "Office_101", "temperature": 22.1, "co2": 580},
    {"timestamp": "2024-01-15 10:00", "sensor": "Office_101", "temperature": 22.8, "co2": 720},
    {"timestamp": "2024-01-15 11:00", "sensor": "Office_101", "temperature": 23.1, "co2": 850}
]"""

# Load JSON string into a DataFrame
df_json = pd.read_json(StringIO(json_string))
df_json
timestamp sensor temperature co2
0 2024-01-15 08:00:00 Office_101 21.3 420
1 2024-01-15 09:00:00 Office_101 22.1 580
2 2024-01-15 10:00:00 Office_101 22.8 720
3 2024-01-15 11:00:00 Office_101 23.1 850

What happens here?

  1. The JSON string contains a list of objects — each object is one measurement with timestamp, sensor ID, temperature, and CO2 level

  2. pd.read_json() automatically converts each object into a row and each key into a column

  3. StringIO() wraps the string so pandas reads it like a file

To load a JSON file from disk instead: df = pd.read_json("sensor_data.json")

Loading JSON from a web API#

Many data providers offer REST APIs that return JSON. Use the requests library to fetch the data:

# Example: fetching sensor data from an API
# import requests
#
# response = requests.get("https://api.building-system.com/sensors/Office_101/readings")
# data = response.json()  # returns a Python list/dict
# df_api = pd.DataFrame(data)
# df_api.head()

Saving Data#

After processing, data can be saved back to various formats.

# Save to CSV
# df.to_csv("output.csv", sep=";", index=False)

# Save to Excel
# df.to_excel("output.xlsx", index=False)

Tip

Use index=False when saving to prevent pandas from writing the row index as an extra column.

Using pyedautils for Data I/O#

I/O stands for Input/Output — reading data into Python (input) and writing data to files (output).

The pyedautils package provides save_data() and load_data() functions that simplify saving and loading DataFrames with preserved data types.

ModuleNotFoundError?

If you get a ModuleNotFoundError: No module named 'pyedautils', install it first:

pip install pyedautils

Then restart the kernel and run the cell again.

import os
import tempfile
from pyedautils.data_io import save_data, load_data

# Create a sample DataFrame
df_sample = pd.DataFrame({
    "timestamp": pd.date_range("2024-01-01", periods=5, freq="h"),
    "temperature": [21.3, 21.5, 22.0, 22.4, 22.1],
    "humidity": [45.0, 44.8, 43.5, 42.0, 43.2]
})

# Save the DataFrame (here to the temp folder; any path works, e.g. "my_sensor_data.pkl")
file_path = os.path.join(tempfile.gettempdir(), "my_sensor_data.pkl")
save_data(df_sample, file_path)
print("Data saved.")

Data saved.

save_data() and load_data() — full reference

save_data(data, file_path, index=False) saves a DataFrame to a file. The format is determined by the file extension:

Extension

Format

Best for

.csv

CSV (text)

Data exchange with Excel or other tools

.pkl

Pickle (binary)

Fast intermediate storage, preserves all data types

.pklz

Compressed Pickle

Same as pickle but smaller file size

.json

JSON (text)

Data exchange with web APIs or JavaScript

The index parameter controls whether the DataFrame index is written to the file (default: False).

load_data(file_path) loads data back from any of the above formats. The format is automatically detected from the file extension. For CSV files, the separator is auto-detected.

Examples:

# Save as CSV (readable in Excel)
save_data(df, "output/measurements.csv")

# Save as pickle (fast, preserves dtypes)
save_data(df, "output/measurements.pkl")

# Save as compressed pickle (smaller file)
save_data(df, "output/measurements.pklz")

# Save with index column
save_data(df, "output/measurements.csv", index=True)

# Load any format back
df = load_data("output/measurements.pkl")

Why pickle instead of CSV?

  • Data types preserved — datetime, float, int, categorical all stay intact. CSV converts everything to text

  • Faster — binary format is much quicker to read/write, especially for large datasets

  • Exact reproduction — the loaded DataFrame is identical to the saved one

Use CSV for sharing data with others, pickle for your own intermediate storage.

# Load it back
df_loaded = load_data(file_path)
df_loaded
timestamp temperature humidity
0 2024-01-01 00:00:00 21.3 45.0
1 2024-01-01 01:00:00 21.5 44.8
2 2024-01-01 02:00:00 22.0 43.5
3 2024-01-01 03:00:00 22.4 42.0
4 2024-01-01 04:00:00 22.1 43.2 
# Verify: dtypes are preserved (timestamp is still datetime, not a string)
df_loaded.dtypes

timestamp      datetime64[us]
temperature           float64
humidity              float64
dtype: object