6. Reading Data from Excel#

Getting Ready#

Make sure you the library xlrd is available in your Python environment.

Install the library kaleido using

pip install -U xlrd

How to do it#

  1. Import pandas and numpy and create a DataFrame to work with

import pandas as pd

  1. Read a csv file with the read_csv method

pd.read_csv('data/cpi_rates_uk.csv', skiprows=4)
2023 May 129.1 7.9 0.6 131.3 8.7 0.7 117.5 4.2
0 NaN Jun 129.4 7.3 0.2 131.5 7.9 0.1 118.1 4.4
1 NaN Jul 129.0 6.4 -0.3 130.9 6.8 -0.4 118.6 4.5
2 NaN Aug 129.4 6.3 0.4 131.3 6.7 0.3 119.2 4.8
3 NaN Sep 130.1 6.3 0.5 132.0 6.7 0.5 119.8 5.0
4 NaN Oct 130.2 4.7 0.1 132.0 4.6 0.0 120.6 5.4
5 NaN Nov 130.0 4.2 -0.1 131.7 3.9 -0.2 121.1 5.3
6 NaN Dec 130.5 4.2 0.4 132.2 4.0 0.4 121.6 5.3
7 2024 Jan 130.0 4.2 -0.4 131.5 4.0 -0.6 122.1 5.4
8 NaN Feb 130.8 3.8 0.6 132.3 3.4 0.6 123.2 6.0
9 NaN Mar 131.6 3.8 0.6 133.0 3.2 0.6 123.9 6.3
10 NaN Apr 132.2 3.0 0.5 133.5 2.3 0.3 124.6 6.6
11 NaN May 132.7 2.8 0.4 133.9 2.0 0.3 125.3 6.7
12 Source: Consumer price inflation from the Office for N... NaN NaN NaN NaN NaN NaN NaN NaN

  1. Read an Excel file using the method read_excel

pd.read_excel('data/gdp_sectors.xls')
Figure 2: All three main sectors contributed positively to GDP growth in May 2024 Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4
0 Contributions to monthly GDP growth, May 2023 ... NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN
2 Notes 1. Sum of component contributions may not sum ... NaN NaN NaN
3 Unit Percentage points (%) NaN NaN NaN
4 NaN NaN NaN NaN NaN
5 Date GDP Services Production Construction
6 May 2023 -0.31 -0.12 -0.12 -0.07
7 Jun 2023 0.77 0.26 0.33 0.18
8 Jul 2023 -0.52 -0.35 -0.13 -0.03
9 Aug 2023 -0.01 0.1 -0.06 -0.06
10 Sep 2023 0.01 -0.01 -0.02 0.04
11 Oct 2023 -0.44 -0.23 -0.17 -0.03
12 Nov 2023 0.22 0.19 0.06 -0.04
13 Dec 2023 -0.07 -0.1 0.08 -0.04
14 Jan 2024 0.38 0.4 -0.09 0.06
15 Feb 2024 0.25 0.23 0.12 -0.1
16 Mar 2024 0.43 0.4 0.02 0.01
17 Apr 2024 0.03 0.22 -0.12 -0.07
18 May 2024 0.37 0.22 0.03 0.11

  1. Use the input skiprows

pd.read_excel('data/gdp_sectors.xls', skiprows=6)
Date GDP Services Production Construction
0 May 2023 -0.31 -0.12 -0.12 -0.07
1 Jun 2023 0.77 0.26 0.33 0.18
2 Jul 2023 -0.52 -0.35 -0.13 -0.03
3 Aug 2023 -0.01 0.10 -0.06 -0.06
4 Sep 2023 0.01 -0.01 -0.02 0.04
5 Oct 2023 -0.44 -0.23 -0.17 -0.03
6 Nov 2023 0.22 0.19 0.06 -0.04
7 Dec 2023 -0.07 -0.10 0.08 -0.04
8 Jan 2024 0.38 0.40 -0.09 0.06
9 Feb 2024 0.25 0.23 0.12 -0.10
10 Mar 2024 0.43 0.40 0.02 0.01
11 Apr 2024 0.03 0.22 -0.12 -0.07
12 May 2024 0.37 0.22 0.03 0.11