Python json to csv pandas

Convert JSON to CSV in Python 3 using Pandas

In this tutorial we’ll be converting a JSON file to CSV using Python.

Let’s say we have a JSON file that looks like this:

Step 1: Install pandas

You could technically use the standard json and csv modules from Python to read the JSON file and write a CSV file, but depending on how your JSON file is structured, it can get complicated, and pandas has some great functions to handle these cases, without mentioning it’s really fast, so that’s what we will use.

You can install pandas using pip running this command:

Step 2: Load the JSON file

Let’s load the JSON file using the json Python module:

import json with open('input.json', encoding='utf-8') as file: data = json.loads(file.read())

Step 3: Normalize the JSON data

Because the JSON format can hold structured data like nested objects and arrays, we have to normalize this data so that it can be respresented in the CSV format.

A quick way to do this is to use the pandas.normalize_json function, which will take JSON data and normalize it into a tabular format, you can read more about this function here.

import pandas df = pandas.json_normalize(data)

In our case, if we print the resulting dataframe, it will look something like this:

 id name address.city 0 1 Albert Amsterdam 1 2 Adam Paris 2 3 Sara Madrid

As you can see the address which was an object, was flattened out to a column.

Step 4: Export to CSV

Now that our data is normalized to a tabular format, let’s write our CSV file!

Pandas has a convenient function for this:

df.to_csv('input.csv', index=False, encoding='utf-8')

And here is the resulting CSV file:

id,name,address.city 1,Albert,Amsterdam 2,Adam,Paris 3,Sara,Madrid

If you want to configure the output, check out the documentation page for the to_csv function here

Putting everything together

Here’s the final code to convert JSON to CSV:

import json import pandas with open('input.json', encoding='utf-8') as file: data = json.loads(file.read()) df = pandas.json_normalize(data) df.to_csv('output.csv', index=False, encoding='utf-8')

Online conversion

If your file is too big or your data is a bit more complex, you can try our online JSON to CSV converter, which can handle huge files and more use cases.

Источник

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:

1. Using the popular Pandas library to convert JSON to CSV, and
2. 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.

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:

# Loading a JSON String json_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:

# Using Pandas to Convert a JSON String to a CSV File import pandas as pd json_string = '[, , , ]' df = pd.read_json(json_string) df.to_csv('file.csv')

The following steps convert a JSON string to a CSV file using Python:

1. Import Pandas Import Pandas using import pandas as pd
2. Load the JSON string as a Pandas DataFrame Load the DataFrame using pd.read_json(json_string)
3. Convert the DataFrame to a CSV file 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:

# Convert a JSON String to CSV Using Python import csv import json json_string = '[, , , ]' data = json.loads(json_string) headers = data[0].keys() with open('file.csv', 'w') as f: writer = csv.DictWriter(f, fieldnames=headers) writer.writeheader() writer.writerows(data)

The code above has a bit more going on. Let’s break down what the code block is doing:

1. We import both json and csv and load the json_string , as before
2. We then load the string into an object, which in this case is a list of dictionaries using the json.loads() function
3. We then create a header object by accessing the keys of the first item (though, any item would do)
4. We then use a context manager to open a new file
5. We create a new writer DictWriter object, passing in the new file and pass in our headers into the field_names= parameter
6. We then write the header by using the .writeheader() method
7. 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:

# Convert a JSON File to a CSV File import pandas as pd df = pd.read_json('sample.json') df.to_csv('file.csv')

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.

Additional Resources

To learn more about related topics, check out the tutorials below:

Источник

How to Convert JSON to CSV in Python

To convert JSON to CSV in Python, convert the “json data to a Python object” and then convert an object to csv file using the “ csv.DictWriter()” function.

Example

import json import csv # Sample JSON data json_data = ''' [ , , ] ''' # Load JSON data into a Python object data = json.loads(json_data) # Get the header (keys) from the first dictionary in the data list header = list(data[0].keys()) # Create and write the CSV file with open('output.csv', 'w', newline='', encoding='utf-8') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() # Write the header (column names) writer.writerows(data) # Write the data rows print("JSON data has been converted to CSV and saved to 'output.csv'") 

JSON data has been converted to CSV and saved to 'output.csv'

In this example, we first loaded the JSON data into a Python object using the json.loads() function.

In the next step, we extracted the keys from the first dictionary in the data list to use as column names for the CSV file.

Finally, we opened a new CSV file and used the csv.DictWriter class to write the header and data rows to the file.

Note that this example assumes the JSON data contains a list of dictionaries with the same structure. If your JSON data has a different or nested structure, you may need to modify the code to handle it accordingly.

Pandas JSON to CSV

To convert a json to csv in Pandas, you need to convert a json data to a Python object using the “json.loads()” function, and then “convert an object to DataFrame using the pd.DataFrame(data)” and use the “pd.to_csv()” function to convert a data frame to csv.

Example

import pandas as pd import json # Sample JSON data json_data = ''' [ , , ] ''' # Load JSON data into a Python object data = json.loads(json_data) # Convert Python object to a Pandas DataFrame data_frame = pd.DataFrame(data) # Save the DataFrame as a CSV file data_frame.to_csv('output.csv', index=False) print("JSON data has been converted to CSV and saved to 'output.csv'") 

JSON data has been converted to CSV and saved to 'output.csv'

And in your current project folder, you will see a CSV file called output.csv which looks like this:

name,age,city Krunal,30,New York Ankit,28,San Francisco Rushabh,31,Los Angeles

Источник

JSON to CSV in Python

1. Use the Pandas DataFrames to_csv() Method to Convert JSON to CSV in Python
2. Use the csv Module to Convert JSON to a CSV File

JSON stands for JavaScript Object Notation . It is based on the format of objects in JavaScript and is an encoding technique for representing structured data. It is widely used these days, especially for sharing data between servers and web applications.

A CSV file is used for storing data in a tabular format like Excel Spreadsheets.

In this tutorial, we will learn how to convert JSON data to a CSV file.

Use the Pandas DataFrames to_csv() Method to Convert JSON to CSV in Python

In this method, we will first convert the JSON to a Pandas DataFrame and from there convert it to a CSV file using the to_csv() method. We can read the JSON string using the json.loads() function which is provided in the json library in Python to convert JSON to a DataFrame. Then we pass this JSON object to the json_normalize() function which will return a Pandas DataFrame containing the required data.

The following code snippet will explain how we do it.

import pandas as pd import json  data = '''  "Results":  [  < "id": "1", "Name": "Jay" >,  < "id": "2", "Name": "Mark" >,   ], "status": ["ok"] > '''  info = json.loads(data)  df = pd.json_normalize(info['Results'])  df.to_csv("samplecsv.csv") 

The content of the created CSV file is below.

,id,Name 0,1,Jay 1,2,Mark 2,3,Jack 

Use the csv Module to Convert JSON to a CSV File

In this method, we will use the csv library in Python which is used for reading and writing CSV files. First, we will read the JSON data as we did in the previous method. We open a file in the write mode and use the DictWriter() from the csv module to creates an object which allows us to map and write JSON data to the file. The fieldnames are keys that are identified and matched with the data when we write rows using the writerows() function.

The following code snippet will show how we can implement the above method:

import csv import json  data = '''  "Results":  [  < "id": "1", "Name": "Jay" >,  < "id": "2", "Name": "Mark" >,   ], "status": ["ok"] > '''  info = json.loads(data)['Results']  print(info[0].keys())  with open("samplecsv.csv", 'w') as f:  wr = csv.DictWriter(f, fieldnames = info[0].keys())  wr.writeheader()  wr.writerows(info) 

Manav is a IT Professional who has a lot of experience as a core developer in many live projects. He is an avid learner who enjoys learning new things and sharing his findings whenever possible.

Related Article — Python JSON

Related Article — Python CSV

Copyright © 2023. All right reserved

Источник