to_json<\/code> method. This method, found in the DataFrame class, is a powerful tool for converting data from CSV to JSON.<\/p>\nProject Setup<\/h2>\n
For this project, we’ll create some sample data of random people with information. We’ll use this handy Python script<\/a> for generating random personal information, producing the following JSON-format data, saved as a local file named people.json<\/code>.<\/p>\n[{\"id\": 1, \"first_name\": \"Alice\", \"last_name\": \"Smith\", \"location\": {\"City\": \"Denver\", \"State\": \"Colorado\"}, \"age\": 18}, {\"id\": 2, \"first_name\": \"Bob\", \"last_name\": \"Anderson\", \"location\": {\"City\": \"Dallas\", \"State\": \"Texas\"}, \"age\": 24}, {\"id\": 3, \"first_name\": \"Alice\", \"last_name\": \"Jacobs\", \"location\": {\"City\": \"Los Angeles\", \"State\": \"California\"}, \"age\": 18}, {\"id\": 4, \"first_name\": \"Bob\", \"last_name\": \"Williams\", \"location\": {\"City\": \"Dallas\", \"State\": \"Texas\"}, \"age\": 24}, {\"id\": 5, \"first_name\": \"Pat\", \"last_name\": \"Anderson\", \"location\": {\"City\": \"Miami\", \"State\": \"Florida\"}, \"age\": 60}]\r\n<\/pre>\nWith this data in hand, we can leverage the power of Pandas to conveniently load our data in CSV format as a DataFrame object, from which we can then convert to CSV as desired.<\/p>\n
Step 1: Load JSON into DataFrame<\/h2>\n
DataFrames are powerful tools for managing 2-dimensional data (or higher-dimensional data reduced as such) with much attention to low-level optimizations. A DataFrame<\/code> can also quickly and efficiently export data as CSV files — which is what we’re after. We can load the people.json<\/code> file data as a DataFrame<\/code> as such:<\/p>\n# Load via context manager and read_json() method\r\nwith open('people.json', 'r')as file:\r\n df = pd.read_json(file.read())\r\n\r\n# view result\r\nprint(df)\r\n\r\n id first_name last_name location age\r\n0 1 Alice Smith {'City': 'Denver', 'State': 'Colorado'} 18\r\n1 2 Bob Anderson {'City': 'Dallas', 'State': 'Texas'} 24\r\n2 3 Alice Jacobs {'City': 'Los Angeles', 'State': 'California'} 18\r\n3 4 Bob Williams {'City': 'Dallas', 'State': 'Texas'} 24\r\n4 5 Pat Anderson {'City': 'Miami', 'State': 'Florida'} 60<\/pre>\nThis gets us pretty close but there are two noticeable issues, one being of grave importance:<\/p>\n
\n- The
location<\/code> column contains nested JSON data that didn’t import properly.<\/li>\n- The
id<\/code> column should\u00a0could<\/em> be used to index our data (optional).<\/li>\n<\/ol>\nTo approach the first issue, we’ll have to modify the approach by which we loaded our data.<\/p>\n
Step 2.a: Deal with Nested JSON Data<\/h2>\n
In the first step, we loaded our data directly via the read_json<\/code> function in the Pandas library. This takes the raw JSON data and loads it directly into a DataFrame<\/code>. To load nested<\/em> JSON as a DataFrame<\/code> we need to take advantage of the json_normalize<\/code> function. However, this function takes a dict<\/code> object as an argument. As such, we need to first load the JSON data as a dict<\/code> as such:<\/p>\nimport json\r\n\r\n# Load via context manager and read_json() method\r\nwith open('people.json', 'r')as file:\r\n\r\n # load JSON data and parse into Dictionary object\r\n data = json.load(file)\r\n\r\n# Load JSON as DataFrame \r\ndf = pd.json_normalize(data)\r\n\r\n# Print Result\r\nprint(df)\r\n\r\n id first_name last_name age location.City location.State\r\n0 1 Alice Smith 18 Denver Colorado\r\n1 2 Bob Anderson 24 Dallas Texas\r\n2 3 Alice Jacobs 18 Los Angeles California\r\n3 4 Bob Williams 24 Dallas Texas\r\n4 5 Pat Anderson 60 Miami Florida\r\n<\/pre>\nHere we see the keys of the location field automatically being converted into dot-delimited column names.<\/p>\n
Step 2.b: Reindex<\/h2>\n
This data will produce an extra column if exported directly to CSV (the index column.) There are several ways in which one could approach handling this, but a convenient means is the setindex<\/code> method — it will create a visual representation of the change in the DataFrame as well. This is achieved as follows:<\/p>\n# set index\r\ndf.set_index('id', inplace=True)\r\n\r\n# view result\r\nprint(df)\r\n\r\n first_name last_name age location.City location.State\r\nid \r\n1 Alice Smith 18 Denver Colorado\r\n2 Bob Anderson 24 Dallas Texas\r\n3 Alice Jacobs 18 Los Angeles California\r\n4 Bob Williams 24 Dallas Texas\r\n5 Pat Anderson 60 Miami Florida<\/pre>\nStep 3: Export to CSV File<\/h2>\n
At this point, our data is ready to be exported to a CSV file. Fortunately, Pandas has methods to both load and save CSV data<\/a>. We can output our DataFrame to a CSV format file using the to_csv<\/code> method\u00a0 as follows:<\/p>\nimport os\r\n\r\n# output DataFrame to CSV file\r\ndf.to_csv('people.csv')\r\n\r\n# view file metedata\r\nprint(os.stat('people.csv'))\r\n\r\nos.stat_result(st_mode=33206, st_ino=56294995342297639, st_dev=2997963831, st_nlink=1, st_uid=0, st_gid=0, st_size=231, st_atime=1657209167, st_mtime=1657209167, st_ctime=1657209167)\r\n<\/pre>\nHere we save the data to a local file named people.csv<\/code> and also confirm some file metadata after the process including created time and filesize. Let’s take a look at the data loaded in Microsoft Excel to see what our conversion process has produced:<\/p>\n![\"json](\"https:\/\/www.alpharithms.com\/wp-content\/uploads\/3047\/json-to-csv-data-export-pandas-alpharithms.jpg\" "\\"json-to-csv-data-export-pandas-alpharithms\\"")
<\/a>Figure 1: Exported CSV data loaded into Microsoft Excel displaying the result of converting JSON to CSV.<\/figcaption><\/figure>\nHere we see the Microsoft Excel version of our DataFrame represented as expected. This confirms that we have, in fact, converted JSON to CSV data using Pandas’ DataFrame as well as the normalize_json<\/code> and to_csv<\/code> methods.<\/p>\nCommon Issues<\/h2>\n
One often encounters issues when working with JSON formatted files in Python. The json<\/code> library — part of the Python standard library — provides useful tools for addressing such issues. However, sometimes the syntax and logical flow of these tools can be unclear which leads to exceptions of which can cause confusion. Below are some common issues.<\/p>\njson.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)<\/h3>\n
In most cases, this error results from attempting to use the json.loads()<\/code> method on the filepath itself, rather than the contents of the file. For example, the following approach is incorrect:<\/p>\n# Trying to load the filepath\r\ndf = pd.read_json(json.loads('people.json'))\r\n\r\n# Resulting Exception\r\njson.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)\r\n<\/pre>\nThe correct approach would be to first create a file pointer to reference the file object as such:<\/p>\n
# use context manager and get reference to file object\r\nwith open('people.json', 'r')as file:\r\n\r\n # use the loads() method on actual file data\r\n data = json.loads(file.read())<\/pre>\nNote that using the read_json<\/code> method is used in a similar method.<\/p>\nFinal Thoughts<\/h2>\n
Pandas is an incredible tool for data scientists and hobbyists alike. Its powerful low-level optimizations make crunching numbers fast and its well-conceived functions make retain a syntactic simplicity of which one can stand in awe. There are plenty of considerations to make when dealing with data such as replacing missing entries<\/a>, tabulating JSON data<\/a> when necessary (similar to the CSV conversion process), and even aggregating time series data<\/a>. Pandas provides tools for handling all these concerns and more!<\/p>\n","protected":false},"excerpt":{"rendered":"Python is a powerful toolset for data scientists and developers alike. Often, these two disciplines meet at the intersection that is converting data. One common conversion requirement is that of CSV data to JavaScript Object Notation (JSON) data. Fortunately, the Pandas library makes this conversion fast and efficient. Pandas is a powerful data science library […]<\/p>\n","protected":false},"author":2,"featured_media":3049,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[10],"tags":[522,196,161,680,210,143],"acf":[],"_links":{"self":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3047"}],"collection":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/comments?post=3047"}],"version-history":[{"count":1,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3047\/revisions"}],"predecessor-version":[{"id":3050,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/3047\/revisions\/3050"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media\/3049"}],"wp:attachment":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media?parent=3047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/categories?post=3047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/tags?post=3047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}