How to Parse and Convert JSON to CSV in Python
JSON to CSV Python : In this tutorial we will show you how to convert a JSON file to a CSV file in the python programming language.
Overview
JSON literally means Javascript Object Notation. It allows to represent information in a structured way like XML for example. A Json file contains two types of structural elements:
- A set of keys/values
- Ordered lists of values (these can be objects, arrays or generic values).
To give you an idea of what a Json file represents, here is an example that will illustrate our different examples :
This file represents the pokemons as well as the characteristics associated with each one of them, if you wish to recover the complete list of the Pokedex, you will find it at this address :
We will see at the end of this tutorial how to convert this type of file to csv and see how to do it in python. Let’s go !
Parsing JSON
The first step is to load the json file into a python object. There are several ways to do this:
- Use the pandas library and its read_jsonfunction
- Use the json module to read the JSON file
Parsing Json File using Pandas
Pandas is a python library that allows to easily manipulate data to be analyzed. It is often used to read JSON files. Indeed a lot of python API returns as a result of JSON and with pandas it is very easy to exploit this data directly. To read a JSON file we can use the read_json function. Here is an example with the pokedex.json file :
Parsing Json File using JSON module
You can also use the json module to read a json file :
Converting JSON to CSV in python
The CSV format (which stands for Comma Separated Values) is the most common import and export format used for Excel spreadsheets and databases. This is a main reason why we want to convert a Json file to CSV.
Converting JSON file to CSV file using Pandas
To convert our Json file, there is a function in Pandas called to_csv() that saves our file in CSV format. Using our previous example where we parsed our JSON file into a Pandas dataframe, we can export our dataframe to CSV like this:
The CSV file has been exported in the directory you have chosen ( In the example, we have exported the file in the same directory as the python file but you can put an absolute path to store it in another directory).
Converting JSON file to CSV file using cvs module
We can also use the CSV module to export our Json file as a CSV file. Here’s an example that allows us to export only the pokemon names to the CSV file:
Output:
We were able to export the different names of the Pokémon in the CSV. To retrieve the header we need to use the keys() function which allows us to get the keys of each “Name” element of our JSON file. Of course it’s possible to get all the JSON file data.
Summary
As we have seen, it may be easy to convert a Json file to a CSV file. On the other hand, JSON files can have much more complex structures than CSV files, so a direct conversion is not always possible and will require us to rework our structure of the file concerned.
Don’t hesitate to tell me in comments if you have any concerns about the use of these codes, I would be happy to answer ��
If you want to learn more about python, you can read this book (As an Amazon Partner, I make a profit on qualifying purchases) :
I’m a data scientist. Passionate about new technologies and programming I created this website mainly for people who want to learn more about data science and programming 🙂
On 08/21/2020 at 15 h 06 min, ALAN said:
Thanks for writing a clear and easy to understand post! I found this very helpful and useful.
How to convert JSON to CSV in Python?
JSON (JavaScript Object Notation) is a popular data storing and transferring tool used in many server-side programming. Python supports JSON using its in-built package called json. We can convert a JSON structure into a comma-delimited textual structure. In this article, you will learn about the different methods to convert JSON to CSV structure.
What are CSV files?
CSV (Comma Separated Values) is a file format utilized for storing data in a tabular fashion. The structure of a CSV file can be interpreted in a spreadsheet or database format. It can store data in plain text (usually, string & number data type). Each line of a .csv file defines a data record. The record will contain one or more fields that are comma-separated values.
Converting JSON to CSV
JSON data usually contains data in key-value pairs. These keys will be the headers for the CSV file and the values as descriptive data that remain indented in json.
Method 1: Using CSV module:
It is a built-in Python module that implements classes for reading & writing tabular data in CSV structure. Using this, programmers can write this data in the format approved by Excel or read data from excel or CSV files. Programmers and developers can also represent the CSV formats recognized by other apps or define their special-purpose CSV formats. It is the most widely used module. Since it is a built-in module, you do not need to install it separately.
Program:
Explanation:
First, we will import the json and csv modules. Next, we will open the JSON file & load its data in the ‘data’ object. Next, we have to open a CSV file for writing in write mode. We assigned the count variable to zero. Then, use the for loop to fetch the data from the stud_data. Now, assign the cnt.keys() in the header. Provide the csv_writer.writerow() method and pass the header as the argument. Once you count the values, you should close the data_file.close() .
Method 2: Using Pandas Library:
It is a fast, flexible, powerful, & easy to implement open-source data analysis tool developed on top of Python language. It is a data manipulation & analysis library that worked well with conversations and various files like CSV, JSON, etc. Here, we will use the to_scv() method to convert a file to CSV.
Program:
Explanation:
First, we will import the pandas library, path, and json modules. Fetch the json file path using the Path() constructor and assign it to the jsonpath object. Next, you have to read the json file and assign it to the ‘dat’ object. Then, create the Dataframe using json_normalize() method and pass the ‘dat’ object into it as the parameter. Finally, convert the df.to_csv() method to convert the dataframe object to csv format with the filename ‘datafile.csv‘, having encoding scheme as UTF-8, and index as False.
Conclusion:
Both these methods are used to convert a JSON structure to a CSV file. But using the CSV module is beneficial because it is a built-in module and hence lighter as compare to the Pandas library. Also, the dataframe conversion takes more time making it complex. Hence, the first method should be preferred.
Экспорт файла JSON в CSV с помощью Python
JSON – это аббревиатура от JavaScript Object Notation. Это исполняемый скрипт, который позволяет нам легко хранить и передавать данные с одной платформы на другую. Мы часто сталкиваемся с ситуациями, когда нам нужно удалить данные из определенных источников. Итак, какова форма вывода очищенных данных?
Этот формат позволяет нам иметь данные в виде пар ключ-значение. Все данные хранятся в виде «строки» в формате значения ключа.
Но что, если я хочу, чтобы эти данные в JSON отправлялись дальше в базу данных для манипуляций? Самое простое решение, которое приходит мне на ум, – это экспорт файла JSON в виде файла CSV.
Итак, давайте теперь посмотрим на шаги, которые нам нужно выполнить, чтобы преобразовать и экспортировать файл JSON в формат CSV (значения, разделенные запятыми).
1. Импорт библиотеки
Чтобы работать с CSV-файлами, нам нужно импортировать встроенный модуль, доступный, как показано:
2. Сохранить значения заголовков и столбцов в списке
При работе с экспортом данных JSON в формат CSV для нас очень важно указать значения заголовков или имена столбцов для каждого столбца.
Таким образом, мы создаем отдельный список и передаем ключевые теги в созданный список, как показано ниже:
Важно отметить, что если имена столбцов, хранящиеся в созданной выше переменной, не соответствуют одному из ключевых тегов в файле JSON, это обязательно вызовет ошибку.
3. Передать путь к CSV-файлу
Теперь пришло время открыть файл CSV и указать на него объект. Далее, мы используем функцию csv.DictWriter() для записи и экспорта данных JSON в форму CSV.
Мы создаем экземпляр функции DictWriter(), а затем помещаем значения столбцов в файл CSV. В конце концов, мы должны экспортировать значения данных с помощью функции writerows(), как показано ниже.
How to Convert JSON to CSV in Python
JavaScript Object Notation (JSON) is one of the most common formats you’ll encounter when working with data – especially web data. Being able to convert the format into other formats, such as CSV, is an important skill. In this tutorial, you’ll learn how to convert data stored in the JSON format into the CSV format using Python.
There are two main ways to accomplish this:
- Using the popular Pandas library to convert JSON to CSV, and
- Using only the built-in JSON and CSV libraries
Let’s dive into how to use both of these methods to convert JSON to CSV.
Table of Contents
Loading a Sample JSON File
To follow along, we’ll be using a pre-built JSON string. If you’re working with your own string, feel free to use that. However, your results will, of course, vary. Let’s take a look at what the string has:
In the pretty-printed string above, we can see that we have three records, each with three fields inside of them. Let’s condense this JSON object and turn it into a string:
Now that we have our JSON string loaded, let’s see how we can use Pandas to convert it to a CSV file.
Use Pandas to Convert JSON to CSV
Pandas makes it easy to convert a JSON file to CSV using the pd.read_json() function, coupled with the .to_csv() method.
Let’s see how we can use Pandas to convert a JSON string to a CSV file:
The following steps convert a JSON string to a CSV file using Python:
-
Import Pandas
Import Pandas using import pandas as pd
Load the DataFrame using pd.read_json(json_string)
Use the df.to_csv() method to convert the DataFrame to a CSV file, by specifying the filename in the method.
Use Only Python to Convert JSON to CSV
If you’re looking to convert a JSON string into a CSV file without using Pandas, you can use the built-in json and csv libraries. This allows you to write code that don’t have external dependencies, perhaps making your code more transferable.
Let’s see how we can use just built-in Python library to convert a JSON string to a CSV file:
The code above has a bit more going on. Let’s break down what the code block is doing:
- We import both json and csv and load the json_string , as before
- We then load the string into an object, which in this case is a list of dictionaries using the json.loads() function
- We then create a header object by accessing the keys of the first item (though, any item would do)
- We then use a context manager to open a new file
- We create a new writer DictWriter object, passing in the new file and pass in our headers into the field_names= parameter
- We then write the header by using the .writeheader() method
- Finally, we write the rows of the data using the .writerows() method
In the final section below, you’ll learn how to convert a JSON file to a CSV file using Python.
Convert a JSON File to a CSV File Using Pandas
So far, we have explored how to convert a JSON string to a CSV file using Python. However, you may have a JSON file that you want to convert to a CSV file. The simplest way to accomplish this is using Pandas.
The pd.read_json() function also accepts a JSON file as its argument, allowing you to load a file directly into a DataFrame. Let’s see how we can replicate our process above using the pd.read_json() function:
We load the JSON file using the pd.read_json() function in the code block above. Then, we convert the DataFrame to a CSV file using the .to_csv() method.
Conclusion
In this tutorial, you learned how to convert a JSON string or file to a CSV file. You first learned the simplest way of doing this, using the Pandas library. Following this, you learned how to accomplish this using only built-in libraries, specifically json and csv. Finally, you learned how to convert a JSON file to a CSV file, using the Pandas method for simplicity.