Pandas DataFrame to JSON string

Overview:

  • JSON notation is a text format for representing data as a collection of name value pairs and sequences. 
  • Historically, JSON format means emitting the object attributes(objects as in JavaScript, C, C++, Python) into a set of name value pairs. An object attribute can be a list, hence JSON format consists of name value pairs and sequences.
  • The to_json() method of a DataFrame converts a DataFrame object into a JSON string.
     

Example 1:

# Example Python program that makes a JSON
# string from a pandas DataFrame object

import pandas as pds

# Field names
tripFields = ("from", "to", "duration", "distance", "through", "start");

# Data
trips   = (("San Jose", "Mountain View", 17, 13.6, "US-101 N", 1621602000),
           ("Mountain View", "Milpitas", 13, 11.2, "CA-237 E", 1621603200),
           ("Milpitas", "San Jose", 13, 8.0, ("I-880 S", "N 10th St"), 1621604100),
           ("San Jose", "Los Gatos", 16, 10.9, ("I-280 N", "CA-17 S"), 1621605300),
           ("Los Gatos", "Cupertino", 12, 8.7, "CA-85 N", 1621606500));

# Create a pandas DataFrame
tripData = pds.DataFrame(data=trips, columns=tripFields);
print("DataFrame:");
print(tripData);

# Convert the pandas DataFrame object to a JSON string
jsonString = tripData.to_json(orient="records");
print("DataFrame as JSON string:");
print(jsonString);

Output:

DataFrame:

            from             to  duration  distance               through       start

0       San Jose  Mountain View        17      13.6              US-101 N  1621602000

1  Mountain View       Milpitas        13      11.2              CA-237 E  1621603200

2       Milpitas       San Jose        13       8.0  (I-880 S, N 10th St)  1621604100

3       San Jose      Los Gatos        16      10.9    (I-280 N, CA-17 S)  1621605300

4      Los Gatos      Cupertino        12       8.7               CA-85 N  1621606500

DataFrame as JSON string:

[{"from":"San Jose","to":"Mountain View","duration":17,"distance":13.6,"through":"US-101 N","start":1621602000},{"from":"Mountain View","to":"Milpitas","duration":13,"distance":11.2,"through":"CA-237 E","start":1621603200},{"from":"Milpitas","to":"San Jose","duration":13,"distance":8.0,"through":["I-880 S","N 10th St"],"start":1621604100},{"from":"San Jose","to":"Los Gatos","duration":16,"distance":10.9,"through":["I-280 N","CA-17 S"],"start":1621605300},{"from":"Los Gatos","to":"Cupertino","duration":12,"distance":8.7,"through":"CA-85 N","start":1621606500}]

Example 2:

# Converting Pandas DataFrame to JSON format - Columns as dictionary entries

import pandas as pds

# Create a DataFrame
attributes = ("location", "beds", "baths", "sqft");
apartments = (("San Jose", 2, 2, 1285),
              ("Santa Clara", 1, 1, 715),
              ("San Jose", 2, 2, 1450),
              ("Santa Clara", 4, 3, 2100));
flatData = pds.DataFrame(data=apartments, columns=attributes);
print("DataFrame containing apartment data:");
print(flatData);

# Each column data becomes an entry in a dictionary
json = flatData.to_json(orient="columns");
print("Apartment data from the DataFrame in JSON format:");
print(json);

Output:

DataFrame containing apartment data:

      location  beds  baths  sqft

0     San Jose     2      2  1285

1  Santa Clara     1      1   715

2     San Jose     2      2  1450

3  Santa Clara     4      3  2100

Apartment data from the DataFrame in JSON format:

{"location":{"0":"San Jose","1":"Santa Clara","2":"San Jose","3":"Santa Clara"},"beds":{"0":2,"1":1,"2":2,"3":4},"baths":{"0":2,"1":1,"2":2,"3":3},"sqft":{"0":1285,"1":715,"2":1450,"3":2100}}

 


Copyright 2024 © pythontic.com