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#
Import
pandas
andnumpy
and create aDataFrame
to work with
import pandas as pd
Read a
csv
file with theread_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 |
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 |
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 |