31. Reading and Writing Data in Pandas

By Bernd Klein. Last modified: 26 Apr 2023.

All the powerful data structures like the Series and the DataFrames would avail to nothing, if the Pandas module wouldn't provide powerful functionalities for reading in and writing out data. It is not only a matter of having a functions for interacting with files. To be useful to data scientists it also needs functions which support the most important data formats like

• Delimiter-separated files, like e.g. csv
• Microsoft Excel files
• HTML
• XML
• JSON

Delimiter-separated Values

Most people take csv files as a synonym for delimter-separated values files. They leave the fact out of account that csv is an acronym for "comma separated values", which is not the case in many situations. Pandas also uses "csv" and contexts, in which "dsv" would be more appropriate.

Delimiter-separated values (DSV) are defined and stored two-dimensional arrays (for example strings) of data by separating the values in each row with delimiter characters defined for this purpose. This way of implementing data is often used in combination of spreadsheet programs, which can read in and write out data as DSV. They are also used as a general data exchange format.

We call a text file a "delimited text file" if it contains text in DSV format.

For example, the file dollar_euro.txt is a delimited text file and uses tabs (\t) as delimiters.

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Enrol here

Reading CSV and DSV Files

Pandas offers two ways to read in CSV or DSV files to be precise:

• DataFrame.from_csv
• read_csv

There is no big difference between those two functions, e.g. they have different default values in some cases and read_csv has more paramters. We will focus on read_csv, because DataFrame.from_csv is kept inside Pandas for reasons of backwards compatibility.

import pandas as pd

exchange_rates = pd.read_csv("../data1/dollar_euro.txt",
                             sep="\t")
print(exchange_rates)

    Year   Average  Min USD/EUR  Max USD/EUR  Working days
0   2016  0.901696     0.864379     0.959785           247
1   2015  0.901896     0.830358     0.947688           256
2   2014  0.753941     0.716692     0.823655           255
3   2013  0.753234     0.723903     0.783208           255
4   2012  0.778848     0.743273     0.827198           256
5   2011  0.719219     0.671953     0.775855           257
6   2010  0.755883     0.686672     0.837381           258
7   2009  0.718968     0.661376     0.796495           256
8   2008  0.683499     0.625391     0.802568           256
9   2007  0.730754     0.672314     0.775615           255
10  2006  0.797153     0.750131     0.845594           255
11  2005  0.805097     0.740357     0.857118           257
12  2004  0.804828     0.733514     0.847314           259
13  2003  0.885766     0.791766     0.963670           255
14  2002  1.060945     0.953562     1.165773           255
15  2001  1.117587     1.047669     1.192748           255
16  2000  1.085899     0.962649     1.211827           255
17  1999  0.939475     0.848176     0.998502           261

As we can see, read_csv used automatically the first line as the names for the columns. It is possible to give other names to the columns. For this purpose, we have to skip the first line by setting the parameter "header" to 0 and we have to assign a list with the column names to the parameter "names":

import pandas as pd

exchange_rates = pd.read_csv("../data1/dollar_euro.txt",
                             sep="\t",
                             header=0,
                             names=["year", "min", "max", "days"])
print(exchange_rates)

          year       min       max  days
2016  0.901696  0.864379  0.959785   247
2015  0.901896  0.830358  0.947688   256
2014  0.753941  0.716692  0.823655   255
2013  0.753234  0.723903  0.783208   255
2012  0.778848  0.743273  0.827198   256
2011  0.719219  0.671953  0.775855   257
2010  0.755883  0.686672  0.837381   258
2009  0.718968  0.661376  0.796495   256
2008  0.683499  0.625391  0.802568   256
2007  0.730754  0.672314  0.775615   255
2006  0.797153  0.750131  0.845594   255
2005  0.805097  0.740357  0.857118   257
2004  0.804828  0.733514  0.847314   259
2003  0.885766  0.791766  0.963670   255
2002  1.060945  0.953562  1.165773   255
2001  1.117587  1.047669  1.192748   255
2000  1.085899  0.962649  1.211827   255
1999  0.939475  0.848176  0.998502   261

Exercise 1

The file "countries_population.csv" is a csv file, containing the population numbers of all countries (July 2014). The delimiter of the file is a space and commas are used to separate groups of thousands in the numbers. The method 'head(n)' of a DataFrame can be used to give out only the first n rows or lines. Read the file into a DataFrame.

Solution:

pop = pd.read_csv("../data1/countries_population.csv", 
                  header=None,
                  names=["Country", "Population"],
                  index_col=0,
                  quotechar="'", 
                  sep=" ", 
                  thousands=",")
print(pop.head(5))  

                Population
Country                   
China           1355692576
India           1236344631
European Union   511434812
United States    318892103
Indonesia        253609643

Writing csv Files

We can create csv (or dsv) files with the method "to_csv". Before we do this, we will prepare some data to output, which we will write to a file. We have two csv files with population data for various countries. countries_male_population.csv contains the figures of the male populations and countries_female_population.csv correspondingly the numbers for the female populations. We will create a new csv file with the sum:

column_names = ["Country"] + list(range(2002, 2013))
male_pop = pd.read_csv("../data1/countries_male_population.csv",
                  header=None,
                  index_col=0,
                  names=column_names)

female_pop = pd.read_csv("../data1/countries_female_population.csv",
                         header=None,
                         index_col=0,
                         names=column_names)


population = male_pop + female_pop

population

population.to_csv("../data1/countries_total_population.csv")

We want to create a new DataFrame with all the information, i.e. female, male and complete population. This means that we have to introduce an hierarchical index. Before we do it on our DataFrame, we will introduce this problem in a simple example:

import pandas as pd

shop1 = {"foo":{2010:23, 2011:25}, "bar":{2010:13, 2011:29}}
shop2 = {"foo":{2010:223, 2011:225}, "bar":{2010:213, 2011:229}}

shop1 = pd.DataFrame(shop1)
shop2 = pd.DataFrame(shop2)
both_shops = shop1 + shop2
print("Sales of shop1:\n", shop1)
print("\nSales of both shops\n", both_shops)

Sales of shop1:
       foo  bar
2010   23   13
2011   25   29

Sales of both shops
       foo  bar
2010  246  226
2011  250  258

shops = pd.concat([shop1, shop2], keys=["one", "two"])
shops

We want to swap the hierarchical indices. For this we will use 'swaplevel':

shops.swaplevel()
shops.sort_index(inplace=True)
shops

We will go back to our initial problem with the population figures. We will apply the same steps to those DataFrames:

pop_complete = pd.concat([population.T, 
                          male_pop.T,
                          female_pop.T], 
                          keys=["total", "male", "female"])
df = pop_complete.swaplevel()
df.sort_index(inplace=True)
df[["Austria", "Australia", "France"]]

df.to_csv("../data1/countries_total_population.csv")

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Upcoming online Courses

Python for Engineers and Scientists

Data Analysis With Python

Enrol here

Exercise 2

• Read in the dsv file (csv) bundeslaender.txt. Create a new file with the columns 'land', 'area', 'female', 'male', 'population' and 'density' (inhabitants per square kilometres.
• print out the rows where the area is greater than 30000 and the population is greater than 10000
• Print the rows where the density is greater than 300

lands = pd.read_csv('../data1/bundeslaender.txt', sep=" ")
print(lands.columns.values)

['land' 'area' 'male' 'female']

# swap the columns of our DataFrame:
lands = lands.reindex(columns=['land', 'area', 'female', 'male'])
lands[:2]

lands.insert(loc=len(lands.columns), 
             column='population', 
             value=lands['female'] + lands['male'])

lands[:3]

lands.insert(loc=len(lands.columns), 
             column='density', 
             value=(lands['population'] * 1000 / lands['area']).round(0))

lands[:4]

print(lands.loc[(lands.area>30000) & (lands.population>10000)])

                  land      area  female  male  population  density
0    Baden-Württemberg  35751.65    5465  5271       10736    300.0
1               Bayern  70551.57    6366  6103       12469    177.0
9  Nordrhein-Westfalen  34085.29    9261  8797       18058    530.0

Reading and Writing Excel Files

It is also possible to read and write Microsoft Excel files. The Pandas functionalities to read and write Excel files use the modules 'xlrd' and 'openpyxl'. These modules are not automatically installed by Pandas, so you may have to install them manually!

We will use a simple Excel document to demonstrate the reading capabilities of Pandas. The document sales.xls contains two sheets, one called 'week1' and the other one 'week2'.

An Excel file can be read in with the Pandas function "read_excel". This is demonstrated in the following example Python code:

with pd.ExcelFile("../data1/sales.xls") as excel_file:
    sheet = pd.read_excel(excel_file)
sheet

The document "sales.xls" contains two sheets, but we only have been able to read in the first one with "read_excel". A complete Excel document, which can consist of an arbitrary number of sheets, can be completely read in like this:

docu = {}
for sheet_name in excel_file.sheet_names:
    docu[sheet_name] = excel_file.parse(sheet_name)

for sheet_name in docu:
    print("\n" + sheet_name + ":\n", docu[sheet_name])

week1:
      Weekday          Sales
0     Monday  123432.980000
1    Tuesday  122198.650200
2  Wednesday  134418.515220
3   Thursday  131730.144916
4     Friday  128173.431003

week2:
      Weekday          Sales
0     Monday  223277.980000
1    Tuesday  234441.879000
2  Wednesday  246163.972950
3   Thursday  241240.693491
4     Friday  230143.621590

We will calculate now the avarage sales numbers of the two weeks:

average = docu["week1"].copy()
average["Sales"] = (docu["week1"]["Sales"] + docu["week2"]["Sales"]) / 2
print(average)

     Weekday          Sales
0     Monday  173355.480000
1    Tuesday  178320.264600
2  Wednesday  190291.244085
3   Thursday  186485.419203
4     Friday  179158.526297

We will save the DataFrame 'average' in a new document with 'week1' and 'week2' as additional sheets as well:

with pd.ExcelWriter('../data1/sales_average.xlsx') as writer:
    docu['week1'].to_excel(writer,'week1')
    docu['week2'].to_excel(writer,'week2')
    average.to_excel(writer,'average')
    writer.save()

Live Python training

Enjoying this page? We offer live Python training courses covering the content of this site.

See: Live Python courses overview

Upcoming online Courses

Python for Engineers and Scientists

Data Analysis With Python

Enrol here