You can use the following basic syntax to specify the dtype of each column in a DataFrame when importing an Excel file into pandas:
df = pd.read_excel('my_data.xlsx', dtype = {'col1': str, 'col2': float, 'col3': int})
The dtype argument specifies the data type that each column should have when importing the Excel file into a pandas DataFrame.
The following example shows how to use this syntax in practice.
Example: Specify dtypes when Importing Excel File into Pandas
Suppose we have the following Excel file called player_data.xlsx:
If we import the Excel file using the read_excel() function, pandas will attempt to identify the data type for each column automatically:
import pandas as pd #import Excel file df = pd.read_excel('player_data.xlsx') #view resulting DataFrame print(df) team points rebounds assists 0 A 24 8 5 1 B 20 12 3 2 C 15 4 7 3 D 19 4 8 4 E 32 6 8 5 F 13 7 9 #view data type of each column print(df.dtypes) team object points int64 rebounds int64 assists int64 dtype: object
From the output we can see that the columns in the DataFrame have the following data types:
- team: object
- points: int64
- rebounds: int64
- assists: int64
However, we can use the dtype argument within the read_excel() function to specify the data types that each column should have:
import pandas as pd #import Excel file and specify dtypes of columns df = pd.read_excel('player_data.xlsx', dtype = {'team': str, 'points': float, 'rebounds': int, 'assists': float}) #view resulting DataFrame print(df) team points rebounds assists 0 A 24.0 8 5.0 1 B 20.0 12 3.0 2 C 15.0 4 7.0 3 D 19.0 4 8.0 4 E 32.0 6 8.0 5 F 13.0 7 9.0 #view data type of each column print(df.dtypes) team object points float64 rebounds int32 assists float64 dtype: object
From the output we can see that the columns in the DataFrame have the following data types:
- team: object
- points: float64
- rebounds: int32
- assists: float64
These data types match the ones that we specified using the dtype argument.
Note that in this example, we specified the dtype for each column in the DataFrame.
However, you can choose to specify the dtype for only specific columns and let pandas infer the dtype for the remaining columns.
Note: You can find the complete documentation for the pandas read_excel() function here.
Additional Resources
The following tutorials explain how to perform other common tasks in pandas:
Pandas: How to Skip Rows when Reading Excel File
Pandas: How to Combine Multiple Excel Sheets
Pandas: How to Write DataFrames to Multiple Excel Sheets