[Tut] The Ultimate Guide on Converting a CSV in Python

The Ultimate Guide on Converting a CSV in Python

4/5 – (1 vote)

Abstract: In this article, we’ll quickly overview the best method, respectively, to convert a CSV file to JSON, Excel, dictionary, Parquet, list, list of lists, list of tuples, text file, DataFrame, XML, NumPy array, and list of dictionaries.

In this article, you’ve learned the best ways to perform the following conversions (click to read more):

• How to Convert CSV to JSON in Python?
• How to Convert CSV to Excel (XLSX) in Python?
• How to Convert a CSV to a Dictionary in Python?
• How to Convert a CSV to a Parquet Format in Python?
• How to Convert a CSV to a List in Python?
• How to Convert a CSV to a List of Lists in Python?
• How to Convert a CSV to a List of Tuples in Python?
• How to Convert a CSV to a Text File in Python?
• How to Convert a CSV to a Pandas DataFrame in Python?
• How to Convert a CSV to an XML in Python?
• How to Convert a CSV to a NumPy Array in Python?
• How to Convert a CSV to a List of Dictionaries?

How to Convert CSV to JSON in Python?

You can convert a CSV file to a JSON file by using the following five steps:

1. Import the csv and json libraries
2. Open the CSV as a file object in reading mode using the open(path_to_csv, 'r') function in a context manager (=with environment).
3. Load the CSV content into Python using the csv.DictReader(fobj) and pass the file object just created.
4. Iterate over each row and update a newly-created dictionary my_json using one of the column values as key: my_json[key] = row
5. Store the my_json dictionary data in a JSON file using the json.dumps(my_json) function.

import csv

import json csv_file = 'my_file.csv'

json_file = 'my_file.json' my_json = {}

with open(csv_file, 'r') as fobj: reader = csv.DictReader(fobj) for row in reader: # Use one of the CSV column names as a key key = row['Name'] my_json[key] = row with open(json_file,'w') as fobj: fobj.write(json.dumps(my_json, indent=2))

Input CSV File:

Name,Job,Age,Income

Alice,Programmer,23,110000

Bob,Executive,34,90000

Carl,Sales,45,50000

Output JSON File:

{ "Alice": { "Name": "Alice", "Job": "Programmer", "Age": "23", "Income": "110000" }, "Bob": { "Name": "Bob", "Job": "Executive", "Age": "34", "Income": "90000" }, "Carl": { "Name": "Carl", "Job": "Sales", "Age": "45", "Income": "50000" }

}

There are many more details to it, so if this didn’t answer your question yet, go here:

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert CSV to Excel (XLSX) in Python?

The most pythonic way to convert a .csv to an .xlsx (Excel) in Python is to use the Pandas library.

1. Install the pandas library with pip install pandas
2. Install the openpyxl library that is used internally by pandas with pip install openpyxl
3. Import the pandas libray with import pandas as pd
4. Read the CSV file into a DataFrame df by using the expression df = pd.read_csv('my_file.csv')
5. Store the DataFrame in an Excel file by calling df.to_excel('my_file.xlsx', index=None, header=True)

import pandas as pd df = pd.read_csv('my_file.csv')

df.to_excel('my_file.xlsx', index=None, header=True)

Note that there are many ways to customize the to_excel() function in case

• you don’t need a header line,
• you want to fix the first line in the Excel file,
• you want to format the cells as numbers instead of strings, or
• you have an index column in the original CSV and want to consider it in the Excel file too.

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a Dictionary in Python?

The best way to convert a CSV file to a Python dictionary is to create a CSV file object f using open("my_file.csv") and pass it in the csv.DictReader(f) method. The return value is an iterable of dictionaries, one per row in the CSV file, that maps the column header from the first row to the specific row value.

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.DictReader(f) for row in reader: print(row)

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a Parquet Format in Python?

Here’s a step-by-step approach to reading a CSV and converting its contents to a Parquet file using the Pandas library:

• Step 1: Run pip install pandas if the module is not already installed in your environment.
• Step 2: Run pip install pyarrow to install pyarrow module
• Step 3: Run pip install fastparquet to install the fastparquet module
• Step 4: import pandas using import pandas as pd
• Step 5: Read the CSV file into a DataFrame using df = pd.read_csv('my_file.csv').
• Step 6: Write the Parquet file using df.to_parquet('my_file.parquet')

The code snippet to convert a CSV file to a Parquet file is quite simple (steps 4-6):

import pandas as pd

df = pd.read_csv('my_file.csv')

df.to_parquet('my_file.parquet')

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a List in Python?

Here’s the code to convert that CSV file to a list of dictionaries, one dictionary per row by using the csv.DictReader(file) function:

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.DictReader(f) lst = list(*reader)

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a List of Lists in Python?

To convert a CSV file 'my_file.csv' into a list of lists in Python, use the csv.reader(file_obj) method to create a CSV file reader. Then convert the resulting object to a list using the list() constructor.

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.reader(f) lst = list(reader)

Output:

print(lst)

# [['9', '8', '7'], ['6', '5', '4'], ['3', '2', '1']]

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a List of Tuples in Python?

To convert a CSV file 'my_file.csv' into a list of tuples in Python, use csv.reader(file_obj) to create a CSV file reader that holds an iterable of lists, one per row. Now, use the list(tuple(line) for line in reader) expression with a generator expression to convert each inner list to a tuple.

Here’s a simple example that converts our CSV file to a list of tuples using this approach:

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.reader(f) lst = list(tuple(line) for line in reader)

Output:

print(lst)

# [('9', '8', '7'), ('6', '5', '4'), ('3', '2', '1')]

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a Text File in Python?

If you want to keep the content (including the delimiter ',') in the CSV file unmodified, the conversion is simple: read the .csv file and write its content into a new .txt file using the open(), read(), and write() functions without importing any library.

In other words, perform the three steps to write a CSV to a TXT file unmodified:

1. Open the CSV file in reading mode and the TXT file in writing mode.
2. Read the CSV file and store it in a variable.
3. Write the content into the TXT file.

Here’s the code snippet that solves our basic challenge:

# 1. Open the CSV file in reading mode and the TXT file in writing mode

with open('my_file.csv', 'r') as f_in, open('my_file.txt', 'w') as f_out: # 2. Read the CSV file and store in variable content = f_in.read() # 3. Write the content into the TXT file f_out.write(content)

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a Pandas DataFrame in Python?

To import a given CSV file into a newly-created Pandas DataFrame, use the pd.read_csv('my_file.csv') function that returns a DataFrame created with the content in the CSV file 'my_file.csv'.

Here’s a quick and generic code snippet showcasing this approach:

import pandas as pd

df = pd.read_csv('my_file.csv')

print(df)

Output:

 Name Job Age Income

0 Alice Programmer 23 110000

1 Bob Executive 34 90000

2 Carl Sales 45 50000

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to an XML in Python?

You can convert a CSV to an XML using the following approach:

• Read the whole CSV file into your Python script.
• Store the first row as header data that is needed to name your custom XML tags (e.g., <Name>, <Job>, <Age>, and <Income> in our example).
• Create a function convert_row() that converts each row separately to an XML representation of that row using basic string formatting.
• Iterate over the data row-wise using csv.reader() and convert each CSV row to XML using your function convert_row().

Here’s the code:

# Convert CSV file to XML string

import csv filename = 'my_file.csv' def convert_row(headers, row): s = f'<row id="{row[0]}">\n' for header, item in zip(headers, row): s += f' <{header}>' + f'{item}' + f'</{header}>\n' return s + '</row>' with open(filename, 'r') as f: r = csv.reader(f) headers = next® xml = '<data>\n' for row in r: xml += convert_row(headers, row) + '\n' xml += '</data>' print(xml)

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a NumPy Array in Python?

You can convert a CSV file to a NumPy array simply by calling np.loadtxt() with two arguments: the filename and the delimiter string. For example, the expression np.loadtxt('my_file.csv', delimiter=',') returns a NumPy array from the 'my_file.csv' with delimiter symbols ','.

Here’s an example:

import numpy as np array = np.loadtxt('my_file.csv', delimiter=',')

print(array)

Output:

[[9. 8. 7.] [6. 5. 4.] [3. 2. 1.]]

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

How to Convert a CSV to a List of Dictionaries?

Convert a CSV file to a list of Python dictionaries in three steps:

1. Create a CSV file object f using open("my_file.csv") and pass it in the csv.DictReader(f) method.
2. The return value is an iterable of dictionaries, one per row in the CSV file. Each dictionary maps the column header from the first row to the specific row value.
3. As the last step, convert the iterable of dictionaries to a list using the Python built-in list() function.

Here’s the code to convert that CSV file to a list of dictionaries, one dictionary per row by using the csv.DictReader(file) function:

import csv csv_filename = 'my_file.csv' with open(csv_filename) as f: reader = csv.DictReader(f) lst = list(*reader)

Learn More: Feel free to learn more about this conversion goal in our full guide on the Finxter blog with multiple CSV conversion methods and step-by-step explanations.

Summary

You can find a more detailed article on each topic in the following table:

Goal Conversion	Full Article
Python CSV to JSON	https://blog.finxter.com/convert-csv-to-json-in-python/
Python CSV to Excel (.xlsx)	https://blog.finxter.com/convert-csv-to-excel-xlsx-in-python/
Python CSV to Dictionary	https://blog.finxter.com/convert-csv-to-dictionary-in-python/
Python CSV to Parquet	https://blog.finxter.com/python-convert-csv-to-parquet/
Python CSV to List	https://blog.finxter.com/python-convert-csv-to-list/
Python CSV to List of Lists	https://blog.finxter.com/python-convert-csv-to-list-of-lists/
Python CSV to List of Tuples	https://blog.finxter.com/convert-csv-to-list-of-tuples-in-python/
Python CSV to Text File (.txt)	https://blog.finxter.com/python-convert-csv-to-text-file-csv-to-txt/
Python CSV to DataFrame (Pandas)	https://blog.finxter.com/read-a-csv-file-to-a-pandas-dataframe/
Python CSV to XML File (.xml)	https://blog.finxter.com/csv-to-xml-how-to-convert-in-python/
Python CSV to NumPy Array (ndarray)	https://blog.finxter.com/how-to-convert-a-csv-to-numpy-array-in-python/
Python CSV to List of Dictionaries	https://blog.finxter.com/python-convert-csv-to-list-of-dictionaries/

Feel free to check out the Finxter email academy to keep improving your coding skills. We have cheat sheets!

Programmer Humor

It’s hard to train deep learning algorithms when most of the positive feedback they get is sarcastic. — from xkcd

https://www.sickgaming.net/blog/2022/07/...in-python/