We have a special method called read_excel() for reading Excel files. It’s similar to read_csv(), the difference being that read_excel() needs two arguments: the string with the name of the file or the file path in addition to sheet_name. If there’s no sheet_name argument, the method reads the first sheet by default.
import pandas as pd
df = pd.read_excel('file.xlsx', sheet_name='List1')
We use a standard pandas method to convert string values into numbers: to_numeric(). It turns column values into the float64 (floating-point numbers) or int64 (integers) type depending on the input value.
The to_numeric() method has an errors parameter. The errors values determine what to_numeric will do when it hits a problem with an incorrect value:
errors='raise': Default behavior, where an exception raised when incorrect value is encountered, stopping the conversion to numbers.errors='coerce': Incorrect values are forcibly replaced with NaN.errors='ignore': Incorrect values are ignored, leaving them where they are.In order to convert data into the type we’re looking for, we use the astype() method. A string with the name of the type is passed as the argument.
Python has a special data type we use to work with dates and times: datetime.
In order to convert strings into dates and times, we use the to_datetime() method. The method’s parameters are the column containing strings and the date format in a string.
We set the date format using a special designation system: