Data 3-2 Pandas I/O

Data 3-2 Pandas I/O#

Loading / saving, file formats json, csv, parquet, pickle, HTML, json_normalize

import pandas as pd

Pandas reads data in a variety of formats#

Text: CSV / Delimited
pd.read_csv("file.csv", sep=",", header=0)
Semi- Structured: JSON, HTML, XML
pd.read_json("file.json", orient="records")
Microsoft Excel
pd.read_excel("file.xlsx", sheet_name="Sheet 1")
Big Data formats (ORC, Parquet, HDF5)
pd.read_parquet("file.parquet")
SQL Databases

https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html

Pandas reads from almost anywhere#

Local files
pd_read_csv("./folder/file.csv")
Files over the network using http / https
pd.read_csv("https://website/folder/file.csv")
File-like: binary / text streams

with open('file.csv', 'r') as file:
    data = file.read()
    df = pd.read_csv(pd.compat.StringIO(data))  # text stream

Reading CSV / Delimited Text#

pd.read_csv("file.csv", sep=",", header=0)

for processing text files one record per line with values separated by a delimiter (typically a “,”)
Common named arguments:
- sep= the delimiter, default is a comma.
- header= Which row, amontg those not skipped is the header
- names= list of column names to use in the DataFrame
- skiprows= how many lines to skip before the data begins?

https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-read-csv-table

# examples of reading in the same data in different ways.
# In every case, the output is the same dataframe
# https://github.com/mafudge/datasets/tree/master/delimited to view the files
location = "https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/delimited"

# Header is first row, Comma-delimited
students = pd.read_csv(f'{location}/students-header.csv') 

# No header in first row, Comma-delimited
students = pd.read_csv(f'{location}/students-no-header.csv', header=None, names =['Name','Grade','Year'])

# No header in first row, Pipe-delimited  "|"
students = pd.read_csv(f'{location}/students-header.psv', sep="|")

# Header not in first row, header in 6th row, Comma-delimited"
students = pd.read_csv(f'{location}/students-header-blanks.csv', skiprows=5)

# no header, data starts in 6th row, semicolon-delimited"
students = pd.read_csv(f'{location}/students-no-header-blanks.ssv', skiprows=5, header=None, sep=";", names =['Name','Grade','Year'])

students

	Name	Grade	Year
0	Abby	7.0	Freshman
1	Bob	9.0	Sophomore
2	Chris	10.0	Senior
3	Dave	8.0	Freshman
4	Ellen	7.0	Sophomore
5	Fran	10.0	Senior
6	Greg	8.0	Freshman
7	Helen	NaN	Sophomore
8	Iris	10.0	Senior
9	Jimmy	8.0	Freshman
10	Karen	7.5	Freshman
11	Lynne	10.0	Sophomore
12	Mike	10.0	Sophomore
13	Nico	NaN	Junior
14	Pete	8.0	Freshman

Challenge 3-2-1#

Read this file into a pandas dataframe:

https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/delimited/webtraffic.log

What is the delimiter?
is there a header? Which row?
Do you need to skip lines?

Display only data where the time taken > 500 (msec) and the sc-status is 200

as a streamlit

Reading JSON Text#

pd.read_json("file.json", orient="columns")

pd.read_json("file.json", orient="records", lines=True) <== Line-oriented json

orientations:
- split: dict like {index -> [index]; columns -> [columns]; data -> [values]}
- records: list like [{column -> value} …]
- index: dict like {index -> {column -> value}}
- columns: dict like {column -> {index -> value}}
- values: just the values array
- table adhering to the JSON Table Schema https://specs.frictionlessdata.io/table-schema/#descriptor

https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-json-reader

# examples of reading in the same JSON data in different ways.
# In every case, the output is the same dataframe
# https://github.com/mafudge/datasets/tree/master/json-formats to view the files
location = "https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/json-formats"

# Row-oriented JSON [ { "Name": "Alice", "Grade": 12, "Year": 2021 }, { "Name": "Bob", "Grade": 11, "Year": 2022 } ]
students = pd.read_json(f'{location}/students-records.json', orient='records')

# line-oriented JSON { "Name": "Alice", "Grade": 12, "Year": 2021 }\n { "Name": "Bob", "Grade": 11, "Year": 2022 }\n
students = pd.read_json(f'{location}/students-lines.json', orient='records', lines=True)

# column-oriented JSON { "Name": ["Alice", "Bob"], "Grade": [12, 11], "Year": [2021, 2022] }
students = pd.read_json(f'{location}/students-columns.json', orient='columns')

students

	Name	Grade	Year
0	Abby	7.0	Freshman
1	Bob	9.0	Sophomore
2	Chris	10.0	Senior
3	Dave	8.0	Freshman
4	Ellen	7.0	Sophomore
5	Fran	10.0	Senior
6	Greg	8.0	Freshman
7	Helen	NaN	Sophomore
8	Iris	10.0	Senior
9	Jimmy	8.0	Freshman
10	Karen	7.5	Freshman
11	Lynne	10.0	Sophomore
12	Mike	10.0	Sophomore
13	Nico	NaN	Junior
14	Pete	8.0	Freshman

Handling Nested JSON#

The read_json() method does not perform well on nested JSON structures. For example consider the following JSON file of customer orders:

The file orders.json:

[
    {
        "Customer" : { "FirstName" : "Abby", "LastName" : "Kuss"}, 
        "Items" : [
            { "Name" : "T-Shirt", "Price" : 10.0, "Quantity" : 3},
            { "Name" : "Jacket", "Price" : 20.0, "Quantity" : 1}
        ]
    },
    {
        "Customer" : { "FirstName" : "Bette", "LastName" : "Alott"}, 
        "Items" : [
            { "Name" : "Shoes", "Price" : 25.0, "Quantity" : 1}, 
            { "Name" : "Jacket", "Price" : 20.0, "Quantity" : 1}
        ]
    },
    {
        "Customer" : { "FirstName" : "Chris", "LastName" : "Peanugget"}, 
        "Items" : [
            { "Name" : "T-Shirt", "Price" : 10.0, "Quantity" : 1}
        ]
    }
]

When we read this with read_json_() we get the three orders but only two columns. One for the "Customer" key, and the other for the "Items" key.

orders = pd.read_json("https://raw.githubusercontent.com/mafudge/datasets/master/json-samples/orders.json")
orders

	Customer	Items
0	{'FirstName': 'Abby', 'LastName': 'Kuss'}	[{'Name': 'T-Shirt', 'Price': 10.0, 'Quantity'...
1	{'FirstName': 'Bette', 'LastName': 'Alott'}	[{'Name': 'Shoes', 'Price': 25.0, 'Quantity': ...
2	{'FirstName': 'Chris', 'LastName': 'Peanugget'}	[{'Name': 'T-Shirt', 'Price': 10.0, 'Quantity'...

What we want is one row per item on the the order and the customer name to be in separate columns. the json_normalize() method can help here.

It is important to note that json_normalize() does not take a file as input, but rather de-serialized json.

import requests
response = requests.get("https://raw.githubusercontent.com/mafudge/datasets/master/json-samples/orders.json")
json_data = response.json()  #de-serialize
orders = pd.json_normalize(json_data)
orders

	Items	Customer.FirstName	Customer.LastName
0	[{'Name': 'T-Shirt', 'Price': 10.0, 'Quantity'...	Abby	Kuss
1	[{'Name': 'Shoes', 'Price': 25.0, 'Quantity': ...	Bette	Alott
2	[{'Name': 'T-Shirt', 'Price': 10.0, 'Quantity'...	Chris	Peanugget

Better but, only processed nested dict and not nested list. we still need to handle the list of Items. To accomplish this we

set the record_path to be the nested list 'Items'. This tells json_normalize() to use that JSON key as the row level. So now we will have 5 rows (one for each item) instead of 3.
Then we set the meta named argument to a list of each of the other values we wish to include, in this instance last name and first name.

NOTE: The meta syntax is a bit weird. Its a list of JSON paths (also represented as lists) to each item in the JSON. For example:

The meta Argument        ==> Matches This in the JSON           ==> And Displays As This Pandas Column
["Customer","FirstName"] ==> { "Customer" : { "FirstName": ...} ==> Customer.Firstname

orders = pd.json_normalize(json_data, record_path="Items", meta=[["Customer","FirstName"],["Customer","LastName"]])
orders

	Name	Price	Quantity	Customer.FirstName	Customer.LastName
0	T-Shirt	10.0	3	Abby	Kuss
1	Jacket	20.0	1	Abby	Kuss
2	Shoes	25.0	1	Bette	Alott
3	Jacket	20.0	1	Bette	Alott
4	T-Shirt	10.0	1	Chris	Peanugget

Yes it seems complicated, because conceptually it is a bit complicated. Let’s try another example, with some abstract values.

In the following example we want to generate a normalized table with 3 rows and 4 columns.

The rows are based on the "A" record_path. There are three: 101, 111 and 201
The meta data are based on columns "B", and "C1"

json_data = [
    {
        "A": [
            {"A1": 101, "A2": 102},
            {"A1": 111, "A2": 112}
        ],
        "B": 103,
        "C": {"C1": 104}
    },
    {
        "A": [
            {"A1": 201, "A2": 202}
        ],
        "B": 203,
        "C": {"C1": 204}
    }
]

df = pd.json_normalize(json_data, record_path="A", meta=["B", ["C", "C1"]])
df

	A1	A2	B	C.C1
0	101	102	103	104
1	111	112	103	104
2	201	202	203	204

Challenge 3-2-2#

Write a streamlit to tabularize this JSON data, using json_normalize

read the file using requests like the example

https://raw.githubusercontent.com/mafudge/datasets/refs/heads/master/json-samples/employees.json

The final table should have these columns: dept, age, firstname, lastname

Reading Excel files#

pd.read_excel('file.xlsx', sheet_name=None)

This will read in all sheets as a dict the key is the sheet name, the value is the contents as a dataframe.

See 3-2-streamlit-excel.py for an example

Reading HTML Tables#

You can scrape an HTML table off a webpage using

pd.read_html(url)

What is returned is a list of all HTML tables on the page.

Each HTML table is a dataframe.

See 3-2-streamlit-html.py for an example

Writing Dataframes#

Once the data is in a pd.DataFrame is can be written out with one of the to() methods such as to_csv(), to_json(), to_parquet() etc.
This makes pandas a superior data conversion tool.
If you include a file, the to() method writes to the file, otherwise the binary contents are returned.
https://pandas.pydata.org/pandas-docs/stable/reference/io.html

df.to_csv("demo.csv", header=True, index=False)
file = df.to_csv(header=True, index=False)
# then use file in a download widget...

Challenge 3-2-3#

Excel to JSON#

Write a streamlit accept an Excel file via file uploader and then writes out a record-oriented JSON file from the first tab in the excel file.

The program should display the contents of the dataframe and provide a download button for the converted the csv file.

Advice: