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

[xSicKxBot]

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
   Code:

   csv
   and
   Code:

   json
   libraries
   
   
2. Open the CSV as a file object in reading mode using the
   Code:

   open(path_to_csv, 'r')
   function in a context manager (=
   Code:

   with
   environment).
   
   
3. Load the CSV content into Python using the
   Code:

   csv.DictReader(fobj)
   and pass the file object just created.
   
   
4. Iterate over each
   Code:

   row
   and update a newly-created dictionary
   Code:

   my_json
   using one of the column values as key:
   Code:

   my_json[key] = row
   
   
5. Store the
   Code:

   my_json
   dictionary data in a JSON file using the
   Code:

   json.dumps(my_json)
   function.
   

Code:

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:

Code:

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

Output JSON File:

Code:

{ "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

Code:

.csv

to an

Code:

.xlsx

(Excel) in Python is to use the Pandas library.

1. Install the
   Code:

   pandas
   library with
   Code:

   pip install pandas
   
   
2. Install the
   Code:

   openpyxl
   library that is used internally by pandas with
   Code:

   pip install openpyxl
   
   
3. Import the
   Code:

   pandas
   libray with
   Code:

   import pandas as pd
   
   
4. Read the CSV file into a DataFrame
   Code:

   df
   by using the expression
   Code:

   df = pd.read_csv('my_file.csv')
   
   
5. Store the DataFrame in an Excel file by calling
   Code:

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

Code:

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

Code:

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

Code:

f

using

Code:

open("my_file.csv")

and pass it in the

Code:

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.

Code:

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
  Code:

  pip install pandas
  if the module is not already installed in your environment.
  
  
• Step 2: Run
  Code:

  pip install pyarrow
  to install
  Code:

  pyarrow
  module
  
  
• Step 3: Run
  Code:

  pip install fastparquet
  to install the
  Code:

  fastparquet
  module
  
  
• Step 4: import pandas using
  Code:

  import pandas as pd
  
  
• Step 5: Read the CSV file into a DataFrame using
  Code:

  df = pd.read_csv('my_file.csv')
  .
  
  
• Step 6: Write the Parquet file using
  Code:

  df.to_parquet('my_file.parquet')

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

Code:

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

Code:

csv.DictReader(file)

function:

Code:

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

Code:

'my_file.csv'

into a list of lists in Python, use the

Code:

csv.reader(file_obj)

method to create a CSV file reader. Then convert the resulting object to a list using the

Code:

list()

constructor.

Code:

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

Output:

Code:

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

Code:

'my_file.csv'

into a list of tuples in Python, use

Code:

csv.reader(file_obj)

to create a CSV file reader that holds an iterable of lists, one per row. Now, use the

Code:

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:

Code:

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:

Code:

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

Code:

','

) in the CSV file unmodified, the conversion is simple: read the

Code:

.csv

file and write its content into a new

Code:

.txt

file using the

Code:

open()

,

Code:

read()

, and

Code:

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:

Code:

# 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

Code:

pd.read_csv('my_file.csv')

function that returns a DataFrame created with the content in the CSV file

Code:

'my_file.csv'

.

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

Code:

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

Output:

Code:

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.,
  Code:

  <Name>
  ,
  Code:

  <Job>
  ,
  Code:

  <Age>
  , and
  Code:

  <Income>
  in our example).
  
  
• Create a function
  Code:

  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
  Code:

  csv.reader()
  and convert each CSV row to XML using your function
  Code:

  convert_row()
  .
  

Here’s the code:

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(r) 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

Code:

np.loadtxt()

with two arguments: the

Code:

filename

and the

Code:

delimiter

string. For example, the expression

Code:

np.loadtxt('my_file.csv', delimiter=',')

returns a NumPy array from the

Code:

'my_file.csv'

with delimiter symbols

Code:

','

.

Here’s an example:

Code:

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

Output:

Code:

[[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
   Code:

   f
   using
   Code:

   open("my_file.csv")
   and pass it in the
   Code:

   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
   Code:

   list()
   function.
   

Here’s the code to convert that CSV file to a list of dictionaries, one dictionary per row by using the

Code:

csv.DictReader(file)

function:

Code:

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 (

Code:

.xlsx

) |
https://blog.finxter.com/convert-csv-to-...in-python/

Python CSV to Dictionary |
https://blog.finxter.com/convert-csv-to-...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-...-of-lists/

Python CSV to List of Tuples |
https://blog.finxter.com/convert-csv-to-...in-python/

Python CSV to Text File (

Code:

.txt

) |
https://blog.finxter.com/python-convert-...sv-to-txt/

Python CSV to DataFrame (Pandas) |
https://blog.finxter.com/read-a-csv-file...dataframe/

Python CSV to XML File (

Code:

.xml

) |
https://blog.finxter.com/csv-to-xml-how-...in-python/

Python CSV to NumPy Array (

Code:

ndarray

) |
https://blog.finxter.com/how-to-convert-...in-python/

Python CSV to List of Dictionaries |
https://blog.finxter.com/python-convert-...tionaries/

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

Programmer Humor

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