Read a Parquet File Using Pandas

Parquet is a columnar storage format that is optimized for distributed processing of large datasets. It is widely used in Big Data processing systems like Hadoop and Apache Spark. 

A partitioned parquet file is a parquet file that is partitioned into multiple smaller files based on the values of one or more columns. Partitioning can significantly improve query performance by allowing the processing system to read only the necessary files.

Concepts

• Parquet format: A columnar storage format that is optimized for distributed processing of large datasets.
• Partitioning: Dividing a dataset into smaller parts based on the values of one or more columns.
• Pandas DataFrame: A two-dimensional labeled data structure with columns of potentially different types.
• pyarrow: A Python package that provides a Python interface to the Arrow C++ library for working with columnar data.

Dataset link: [https://www.kaggle.com/datasets/pawankumargunjan/weather]

Example 1

Install the pyarrow package:

The pyarrow package provides a Python interface to the Arrow C++ library for working with columnar data. Install it using the following command

pip install pyarrow

Import the pandas or pyarrow packages:

Python3

import pyarrow.parquet as pa

Reading parquet data using pyarrow.parquet

Python

table = pa.read_table('weather.2016.parquet')
table

Output:

pyarrow.Table
ForecastSiteCode: int64
ObservationTime: int64
ObservationDate: timestamp[ms]
WindDirection: int64
WindSpeed: int64
WindGust: double
Visibility: double
ScreenTemperature: double
Pressure: double
SignificantWeatherCode: int64
SiteName: string
Latitude: double
Longitude: double
Region: string
Country: string
----
ForecastSiteCode: [[3002,3005,3008,3017,3023,...,3882,3002,3005,3008,3017],[3023,3026,3031,3034,3037,...,3797,3866,3872,3876,3882]]
ObservationTime: [[0,0,0,0,0,...,12,13,13,13,13],[13,13,13,13,13,...,23,23,23,23,23]]
ObservationDate: [[2016-02-01 00:00:00.000,2016-02-01 00:00:00.000,2016-02-01 00:00:00.000,2016-02-01 00:00:00.000,2016-02-01 00:00:00.000,...,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000],[2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,2016-03-12 00:00:00.000,...,2016-03-31 00:00:00.000,2016-03-31 00:00:00.000,2016-03-31 00:00:00.000,2016-03-31 00:00:00.000,2016-03-31 00:00:00.000]]
WindDirection: [[12,10,8,6,10,...,4,8,8,8,8],[9,9,10,8,8,...,1,0,1,1,1]]
WindSpeed: [[8,2,6,8,30,...,5,19,18,19,19],[25,26,24,23,23,...,5,10,2,3,2]]
WindGust: [[null,null,null,null,37,...,null,null,null,null,29],[36,41,37,34,37,...,null,null,null,null,null]]
Visibility: [[30000,35000,50000,40000,2600,...,4000,8000,3500,11000,28000],[4600,9000,30000,10000,2900,...,22000,null,50000,null,35000]]
ScreenTemperature: [[2.1,0.1,2.8,1.6,9.8,...,10,-99,7.4,8.1,9.2],[9.1,9.5,10.2,9.7,9.9,...,4.9,8.4,3.5,6.1,3.7]]
Pressure: [[997,997,997,996,991,...,1030,null,1019,1020,1019],[1019,1018,1020,1020,1021,...,1019,1018,1019,1019,1019]]
SignificantWeatherCode: [[8,7,-99,8,11,...,1,5,15,12,7],[15,12,12,12,15,...,0,-99,0,-99,0]]
...

Print the shape of the dataset

Python3

table.shape

Output:

(194697, 15)

Convert the pyarrow table dataset into a pandas dataframe.

Python3

df = table.to_pandas()
# Taking tanspose so the printing dataset will easy.
df.head().T

Output:

	0	1	2	3	4
ForecastSiteCode	3002	3005	3008	3017	3023
ObservationTime	0	0	0	0	0
ObservationDate	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00
WindDirection	12	10	8	6	10
WindSpeed	8	2	6	8	30
WindGust	NaN	NaN	NaN	NaN	37.0
Visibility	30000.0	35000.0	50000.0	40000.0	2600.0
ScreenTemperature	2.1	0.1	2.8	1.6	9.8
Pressure	997.0	997.0	997.0	996.0	991.0
SignificantWeatherCode	8	7	-99	8	11
SiteName	BALTASOUND (3002)	LERWICK (S. SCREEN) (3005)	FAIR ISLE (3008)	KIRKWALL (3017)	SOUTH UIST RANGE (3023)
Latitude	60.749	60.139	59.53	58.954	57.358
Longitude	-0.854	-1.183	-1.63	-2.9	-7.397
Region	Orkney & Shetland	Orkney & Shetland	Orkney & Shetland	Orkney & Shetland	Highland & Eilean Siar
Country	SCOTLAND	SCOTLAND	None	SCOTLAND	SCOTLAND

Example 2

Reading parquet data using pandas.read_parquet

Python

import pandas as pd

df = pd.read_parquet('weather.2016.parquet')
df.head()

Output:

	ForecastSiteCode	ObservationTime	ObservationDate	WindDirection	WindSpeed	WindGust	Visibility	ScreenTemperature	Pressure	SignificantWeatherCode	SiteName	Latitude	Longitude	Region	Country
0	3002	0	2016-02-01	12	8	NaN	30000.0	2.1	997.0	8	BALTASOUND (3002)	60.749	-0.854	Orkney & Shetland	SCOTLAND
1	3005	0	2016-02-01	10	2	NaN	35000.0	0.1	997.0	7	LERWICK (S. SCREEN) (3005)	60.139	-1.183	Orkney & Shetland	SCOTLAND
2	3008	0	2016-02-01	8	6	NaN	50000.0	2.8	997.0	-99	FAIR ISLE (3008)	59.530	-1.630	Orkney & Shetland	None
3	3017	0	2016-02-01	6	8	NaN	40000.0	1.6	996.0	8	KIRKWALL (3017)	58.954	-2.900	Orkney & Shetland	SCOTLAND
4	3023	0	2016-02-01	10	30	37.0	2600.0	9.8	991.0	11	SOUTH UIST RANGE (3023)	57.358	-7.397	Highland & Eilean Siar	SCOTLAND

Example 3

Filtering the parquet data

Python

import pandas as pd

df = pd.read_parquet('weather.2016.parquet', filters=[('Country', '=', 'ENGLAND')])
df.head()

Output

	ForecastSiteCode	ObservationTime	ObservationDate	WindDirection	WindSpeed	WindGust	Visibility	ScreenTemperature	Pressure	SignificantWeatherCode	SiteName	Latitude	Longitude	Region	Country
0	3134	0	2016-02-01	1	1	NaN	2100.0	4.3	999.0	15	GLASGOW/BISHOPTON (3134)	55.907	-4.533	Strathclyde	ENGLAND
1	3210	0	2016-02-01	8	11	33.0	3100.0	8.8	1005.0	7	ST. BEES HEAD (3210)	54.518	-3.615	North West England	ENGLAND
2	3212	0	2016-02-01	11	16	NaN	4800.0	11.6	1004.0	12	KESWICK (3212)	54.614	-3.157	North West England	ENGLAND
3	3214	0	2016-02-01	11	24	34.0	10000.0	10.0	1005.0	8	WALNEY ISLAND (3214)	54.125	-3.257	North West England	ENGLAND
4	3220	0	2016-02-01	16	-99	NaN	25000.0	11.1	1002.0	7	CARLISLE (3220)	54.933	-2.963	North West England	ENGLAND

This will filter the parquet data by Country=’ENGLAND‘ and print the first 5 rows of the DataFrame.

Aggregating the parquet data

Python

grouped = df.groupby(['Country']).mean(numeric_only=True)
# Taking tanspose so the printing dataset will easy.
print(grouped.T)

Output

Country                      ENGLAND
ForecastSiteCode         3560.622936
ObservationTime            11.517152
WindDirection               8.534412
WindSpeed                   7.770786
WindGust                   36.035424
Visibility              22431.530727
ScreenTemperature           5.336209
Pressure                 1011.335307
SignificantWeatherCode     -3.614757
Latitude                   52.354470
Longitude                  -1.586393

Group the data by Country, calculate the mean for each group, and print the resulting data frame.

Example 4

Multiple filters

Python

import pandas as pd
df = pd.read_parquet('weather.2016.parquet',
                     filters=[('Country', '=', 'ENGLAND'),
                              ('WindSpeed','<', 7)])
# Taking tanspose so the printing dataset will easy.
df.head().T

Output:

	0	1	2	3	4
ForecastSiteCode	3134	3220	3839	3220	3839
ObservationTime	0	0	0	1	1
ObservationDate	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00	2016-02-01 00:00:00
WindDirection	1	16	16	16	16
WindSpeed	1	-99	-99	-99	-99
WindGust	NaN	NaN	NaN	NaN	NaN
Visibility	2100.0	25000.0	NaN	15000.0	NaN
ScreenTemperature	4.3	11.1	12.1	11.2	12.3
Pressure	999.0	1002.0	NaN	1003.0	NaN
SignificantWeatherCode	15	7	-99	8	-99
SiteName	GLASGOW/BISHOPTON (3134)	CARLISLE (3220)	EXETER AIRPORT (3839)	CARLISLE (3220)	EXETER AIRPORT (3839)
Latitude	55.907	54.933	50.737	54.933	50.737
Longitude	-4.533	-2.963	-3.405	-2.963	-3.405
Region	Strathclyde	North West England	South West England	North West England	South West England
Country	ENGLAND	ENGLAND	ENGLAND	ENGLAND	ENGLAND

This will filter the parquet data by Country=’ENGLAND‘ and WindSpeed< 7 and print the first 5 rows of the DataFrame.

Conclusion

In conclusion, partitioning parquet files is a powerful way to optimize data storage and querying performance. By partitioning data based on one or more columns, you can easily filter, sort, and aggregate data within a subset of partitions, rather than having to scan the entire dataset.

In this article, we covered two methods for reading partitioned parquet files in Python: using pandas’ read_parquet() function and using pyarrow’s ParquetDataset class. We also provided several examples of how to read and filter partitioned parquet files using these methods with real-world weather data.

Overall, partitioning parquet files is an effective technique for optimizing data storage and retrieval. Whether you’re dealing with big data or just trying to improve query performance, partitioning can help you get the most out of your data.