Write a Python program to read an Excel data from file and read all rows of first and last columns

Reading specific columns from an Excel file is a common data analysis task. Python's pandas library provides the iloc method to select rows and columns by their position, making it easy to extract the first and last columns from any dataset.

Understanding Column Selection with iloc

The iloc method uses integer-based indexing where:

• df.iloc[:,0] selects all rows of the first column (index 0)

• df.iloc[:,-1] selects all rows of the last column (index -1)

• The colon (:) means "all rows"

Creating Sample Data

First, let's create a sample Excel file to demonstrate the concept ?

import pandas as pd

# Create sample data
data = {
    'id': [1, 2, 3, 4, 5],
    'product_name': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Webcam'],
    'price': [50000, 500, 1500, 15000, 2000],
    'production_year': [2019, 2020, 2018, 2018, 2018]
}

df_sample = pd.DataFrame(data)
print("Sample Excel data:")
print(df_sample)

Sample Excel data:
   id product_name  price  production_year
0   1       Laptop  50000             2019
1   2        Mouse    500             2020
2   3     Keyboard   1500             2018
3   4      Monitor  15000             2018
4   5       Webcam   2000             2018

Reading First and Last Columns

Now let's extract the first and last columns from our data ?

import pandas as pd

# Create sample data (simulating reading from Excel)
data = {
    'id': [1, 2, 3, 4, 5],
    'product_name': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Webcam'],
    'price': [50000, 500, 1500, 15000, 2000],
    'production_year': [2019, 2020, 2018, 2018, 2018]
}

df = pd.DataFrame(data)

print("All rows of first column:")
print(df.iloc[:, 0])
print("\nAll rows of last column:")
print(df.iloc[:, -1])

All rows of first column:
0    1
1    2
2    3
3    4
4    5
Name: id, dtype: int64

All rows of last column:
0    2019
1    2020
2    2018
3    2018
4    2018
Name: production_year, dtype: int64

Reading from Actual Excel File

To read from an actual Excel file, use pd.read_excel() method ?

import pandas as pd

# Read data from Excel file
df = pd.read_excel('/path/to/your/file.xlsx')

# Extract first and last columns
first_column = df.iloc[:, 0]
last_column = df.iloc[:, -1]

print("First column data:")
print(first_column)
print("\nLast column data:")
print(last_column)

Key Points

• iloc[:, 0] always selects the first column regardless of column name

• iloc[:, -1] always selects the last column regardless of column name

• The result is a pandas Series object containing all row values

• Use pd.read_excel() for Excel files and pd.read_csv() for CSV files

Conclusion

Using df.iloc[:,0] and df.iloc[:,-1] provides a reliable way to extract first and last columns from Excel data. This approach works regardless of column names and is essential for data preprocessing tasks.