Section 1.1: Importing Data from the Clipboard

If you're dealing with small datasets sourced from the web or spreadsheets, the pd.read_clipboard() function is incredibly useful. First, copy the following text to your clipboard:

Name Age Score

0 Evan 33 85

1 Kate 34 90

2 Nik 32 85

3 Kyra 35 95

Now, execute the commands below to generate a structured DataFrame:

import pandas as pd

df = pd.read_clipboard()

print(df)

That's pretty cool, right? Next, we will look at how to read all sheets from an Excel file at once.

Section 1.2: Loading All Excel Sheets Simultaneously

Pandas offers a convenient way to load all worksheets from an Excel workbook using the pd.read_excel() function with the argument sheet_name=None. Interested in following along? You can download the fictional dataset here.

When you set sheet_name to None, Pandas returns a dictionary of DataFrames, where the sheet names are the keys and the corresponding DataFrames are the values. We can then use pd.concat() to combine these DataFrames. Here's how:

all_sheets = pd.read_excel("your_file.xlsx", sheet_name=None)

combined_df = pd.concat(all_sheets.values())

print(combined_df)

Section 1.3: Loading Multiple Workbooks from a Directory

Now, let's discuss how to load several Excel files from a directory at once. Download the sample files here and place them in a folder.

In the following code, we will:

• Import the Pandas and os libraries
• Define the directory path
• Use list comprehension to create a list of file paths
• Finally, append all DataFrames using pd.concat()

import pandas as pd

import os

file_path = "path_to_your_directory"

files = [os.path.join(file_path, f) for f in os.listdir(file_path) if f.endswith('.xlsx')]

dfs = [pd.read_excel(file) for file in files]

combined_df = pd.concat(dfs)

print(combined_df)

Section 1.4: Combining Date Columns into One

Pandas simplifies the parsing of specific columns as dates. You can even combine date components from different columns into a single datetime column.

Suppose your data has separate columns for Month, Day, and Year; you can merge them into one datetime column using the code below:

df['Date'] = pd.to_datetime(df[['Month', 'Day', 'Year']])

By providing a dictionary, you can assign a name to the resulting column, effectively reducing the DataFrame's size by excluding the individual columns.