Skip to article frontmatterSkip to article content
Site not loading correctly?

This may be due to an incorrect BASE_URL configuration. See the MyST Documentation for reference.

Data formats

Data come in many forms and formats, depending on source and function.

“Flat” text files

  • .txt, .dat, .csv, .fasta, ...

  • Often with a fixed format

    • Comma separated columns

    • Tabulator separated columns

    • Fixed width columns

    • Headers, subheaders

    • Sections with keywords

import pandas as pd
planets_DF = pd.read_csv('../../data/planets.csv')
planets_DF.head()
# Using planets_DF as input, remove " AU" from the "distance" column and convert it to a float
planets_DF['distance'] = planets_DF['distance'].str.replace(' AU', '').astype(float)
print(planets_DF.dtypes)
planets_DF.head()

Plotting directly with Pandas

Instead of converting data to NumPy arrays and plotting, it is possible to plot directly from Pandas with special plot commands and .plot.xxx().

# Bar plot
# pd.options.plotting.backend = "matplotlib"
import matplotlib.pyplot as plt
planets_DF.plot.bar(x='planet', y='distance')
plt.show()
# Try: color, rot, add more with plt.xxx()

Exercise

  • Plot the planets as circles instead of bars.

  • Use the diameter of the planet to adjust the circle size.

# Plot concentric ellipses for each planet where the size of the ellipse is proportional to 
# the planet's distance. Add planet names to the plot. Let the xlim and ylim extend from -31 to 31.
import matplotlib.pyplot as plt
import numpy as np
fig, ax = plt.subplots(figsize=(10,10))
ax.set_xlim(-31, 31)
ax.set_ylim(-31, 31)
ax.set_aspect('equal')
ax.set_facecolor('black')
for i in range(len(planets_DF)):
    ax.add_artist(plt.Circle((0,0), planets_DF['distance'][i], color='white', fill=False))
    ax.text(planets_DF['distance'][i], 0, planets_DF['planet'][i], color='white', ha='center', va='center')
plt.show()

Excel files

Look at the Budget.xlsx file in the data folder to justify the choice of import below.
Should this be adjusted?

pd.read_excel()

Budget = pd.read_excel('../../data/Budget.xlsx', sheet_name='2019', skiprows=2, index_col=0)
Budget
# Remove redundant row and column (sums)
Budget = Budget.iloc[:12, :12]
# Exchange NaN values with 0
Budget.replace(np.nan, 0, inplace=True)
Budget
# Plot monthly budget as boxplot
Budget.boxplot()
plt.show()

Pandas plotting with different backend

It is possible to exchange the matplotlib backend in Pandas with something else, e.g., the more interactive Plotly. This may not be a full replacement.

pd.options.plotting.backend = "plotly"
fig = Budget.boxplot()
fig.show() # Exchanged plt.show() with fig.show() to avoid mixing matplotlib and plotly

Other proprietary formats

Media files

  • Most image formats and many video formats are more or less directly readable using the Pillow or or PIL libraries.

  • Medical imaging formats have some Python support, e.g., the DICOM format

  • Sound files can be opened using pySoundFile, librosa or similar.

JSON - JavaScript Object Notation

  • Originally part of the 1999 JavaScript standard.

  • Popular format for data storage and transfer, supported by a large range of programming languages.

  • Text-based with fixed formatting but highly flexible structure and contents.

  • Basics:

    • a string has limitations/functionality with regard to backslashes , otherwise plain text.

    • a number is limited to integers, decimal numbers and scientific (float) numbers.

    • a value can be a string, number, object, array, true, false, or null.

    • an object is enclosed by curly brackets {} and contains (comma separated) string : value pairs.

    • an array is enclosed by brackets [] and contains (comma separated) values.

    • The JSON object is an unordered set.

    • Nesting is frequently used.

# Simplest? JSON object
import json
my_string = "banana"
my_string_as_json = json.dumps(my_string)
my_string_as_json
# Check validity of JSON object (exception thrown if not valid)
json.loads(my_string_as_json)
# An object, quite similar to a Python dictionary
my_object = {"fruit" : "banana", "color" : "yellow" }
my_object_as_json = json.dumps(my_object)
my_object_as_json
# Check validity of JSON object (exception thrown if not valid)
json.loads(my_object_as_json)
my_object_as_dict = json.loads(my_object_as_json)
my_object_as_dict.keys()

JSON variants

  • Though JSON itself is highly flexible, standardisation can ease implementation and transferability.

  • JSON-stat is a format adoptet by, e.g., Statistics Norway and Eurostat.

    • Standard libraries for handling data.

    • Especially useful for tabular data.

    • json-stat2 is the newest standard as of 2023.

# Read text file containing JSON object
import json
with open('../../data/traffic_accidents.txt') as f:
    json_string = f.read()
json_object = json.loads(json_string)
json_object
# Convert json_string to a JSON-stat object and then to a Pandas dataframe
from pyjstat import pyjstat
dataset = pyjstat.Dataset.read(json_string)
df = dataset.write('dataframe')
print(df.shape)
df.head()

Exercise

We want to make a family tree consisting of child-parent JSON objects.
Create a JSON list which contains two objects of equal structure (i.e., same nesting and names).
Each object should contain a name : value pair with the child name and a list of parents (name : value pairs).

Think:

  • Are there alternative ways to code this? (hint: unique dictionary keys)

  • If this JSON object was to be parsed for printing, inclusion in a database or other, are there exceptions one would have to consider?

Extensible Markup Language (XML)

  • Defined by the World Wide Web Consortium in 1998 (v1.0); current version from 2006 (v1.1, 2nd edition).

  • Uses tags like HTML code, but much more flexible.

  • Hundreds of document formats, several industry data standards and communication protocols, web page formats etc. are based on XML.

  • As with JSON, there are many standardised formats defined using the base rules of XML.

    • More overhead in the file format, less readable, needs parser, more standardised, overlapping use in data transfer.

  • Accepts most of the UTF-8 encoding (see below) including Chinese, Armenian, Cyrillic.

''' A valid XML file
<?xml version="1.0" encoding="UTF-8"?>
<俄语 լեզու="ռուսերեն">данные</俄语>
'''
# Equivalent JSON and XML documents
''' 
{"guests":[
  { "firstName":"John", "lastName":"Doe" },
  { "firstName":"María", "lastName":"García" },
  { "firstName":"Nikki", "lastName":"Wolf" }
]}

<guests>
  <guest>
    <firstName>John</firstName> <lastName>Doe</lastName>
  </guest>
  <guest>
    <firstName>María</firstName> <lastName>García</lastName>
  </guest>
  <guest>
    <firstName>Nikki</firstName> <lastName>Wolf</lastName>
  </guest>
</guests>
'''

Large data

  • Real data can be exeedingly large; gigabytes, terabytes, ...

  • Pandas takes you only part of the way.

  • Modin is an example of a simple Pandas replacement that scales much further (>1TB).

    • Choose backend: Ray, Dask, Unidist, and use import modin.pandas as pd

  • PySpark with Apache Spark takes this to a distributed level but also demands more installation and configuration.

HDF5

  • Data container for large and/or heterogeneous data.

  • Appears as a single file on a computer.

  • Can be accessed and browsed without loading/unpacking its contents.

  • Can group objects together, e.g., measurements and metadata.

  • Data can be accessed through different views that require different relations or hierachies.

  • Often used quite simply with data, models, and results in a single file.

  • Resembles a flexible NumPy array.

Encoding

  • Most modern data storage use UTF-8 encoding

    • Universal Coded Character Set Transformation Format - 8-bit, or just Unicode Transformation Format.

    • One to four 8-bit code units enabling 1 112 064 character codes.

    • JSON are among formats one assumes are UTF-8 encoded.

  • However, there are many standards, so no guarantees are given.

    • ASCII, WINDOWS-1252 (Nordic), ISO-88xx-y, etc. may need special treatment or translation, especially if different connected apps or services have their own basic assumptions.

    • Old data files and vendor specific file version are most likely to be problematic.

Notebook Cell
# Dummy cell to ensure Plotly graphics are shown
import plotly.graph_objects as go
f = go.FigureWidget([go.Scatter(x=[1,1], y=[1,1], mode='markers')])
Data Sources
Static and streaming data
Data Sources
Streaming data