Python, iterating through an excel spreadsheet

Question

I need to write the program that loops through the columns of data. Resetting the variable based on the cell value and each column representing a variable.

The variables in the exercise are dependent on these values that are being looped through.

How can I loop through the rows with each iteration of the loop increasing the value by 1?

df=pd.DataFrame(r'C:/Users/user.name/Desktop/P_list.xlsx',sheet_name = 'Sheet1')
for i in range(0,5000):

    df2 = pd.read_excel(r'C:/Users/user.name/Desktop/P_list.xlsx',sheet_name = 'Sheet1'), index = list(range(i,5000,1), columns=list(range(0)))
    df3 = pd.read_excel(r'C:/Users/user.name/Desktop/P_list.xlsx',sheet_name = 'Sheet1'), index = list(range(i,5000,1), columns=list(range(1)))
    df4 = pd.read_excel(r'C:/Users/user.name/Desktop/P_list.xlsx',sheet_name = 'Sheet1'), index = list(range(i,5000,1), columns=list(range(2)))
    df5 = pd.read_excel(r'C:/Users/user.name/Desktop/P_list.xlsx',sheet_name = 'Sheet1'), index = list(range(i,5000,1), columns=list(range(3)))
    firstname = df2
    lastname = df3
    address = df4
    number= df5

#performed exercise

Idea is not cleat to me. What do you mean by saying program needs to loop through the sheets of column's data? — mrconcerned
– mrconcerned, Commented Nov 18, 2019 at 16:40
It needs to loop through the columns of data. Resetting the variable based on the cell value. Each column representing a variable. — SublimizeD
– SublimizeD, Commented Nov 18, 2019 at 16:42

Vityata · Accepted Answer · 2019-11-18 18:57:24Z

5

I have tried this on Jupyter. This is needed to load the Excel to df:

import numpy as np
import pandas as pd
import xlrd
df = pd.read_excel('Sample.xlsx', sheet_name = 0)

Then looping towards the column names is like this:

for col in df.columns:
    print(col)

And looping towards the data is this:

for col in df.columns:
    print("NEW ROW ----------")
    for val in df[col]:
        print (val)

This is the printed data:

Another way to do it is to loop through the columns and the rows:

columns = len(df.columns)
rows = len(df)

for column in range(columns):
    print("-----------")
    for row in range(rows):
        print(df.loc[row][column])

edited Nov 18, 2019 at 18:57

answered Nov 18, 2019 at 16:51

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SublimizeD Over a year ago

This got me on track, I was able to apply this and manipulate it to fit what I was working on! Thank you!

Vityata Over a year ago

@SublimizeD - welcome :) I am also studying pandas and numpy now :)

Collectives™ on Stack Overflow

Python, iterating through an excel spreadsheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related