How can loop this excel datas in python?-CodePudding

data = pd.ExcelFile("DCB_200_new.xlsx")
x_dcb_200_9 = pd.read_excel(data, 'DCB_200_9',usecols=[2],skiprows=[0,1])
y_dcb_200_9 = pd.read_excel(data, 'DCB_200_9',usecols=[1],skiprows=[0,1])
x_dcb_200_15 = pd.read_excel(data, 'DCB_200_15',usecols=[2],skiprows=[0,1])
y_dcb_200_15 = pd.read_excel(data, 'DCB_200_15',usecols=[1],skiprows=[0,1])
x_dcb_200_23 = pd.read_excel(data, 'DCB_200_23',usecols=[2],skiprows=[0,1])
y_dcb_200_23 = pd.read_excel(data, 'DCB_200_23',usecols=[1],skiprows=[0,1])
x_dcb_200_26 = pd.read_excel(data, 'DCB_200_26',usecols=[2],skiprows=[0,1])
y_dcb_200_26 = pd.read_excel(data, 'DCB_200_26',usecols=[1],skiprows=[0,1])
x_dcb_200_28 = pd.read_excel(data, 'DCB_200_28',usecols=[2],skiprows=[0,1])
y_dcb_200_28 = pd.read_excel(data, 'DCB_200_28',usecols=[1],skiprows=[0,1])

x = [x_dcb_200_9, x_dcb_200_15, x_dcb_200_23,x_dcb_200_26,x_dcb_200_28]
y = [y_dcb_200_9,y_dcb_200_15,y_dcb_200_23,y_dcb_200_26,y_dcb_200_28]

I have an excel file which includes 5 sheet. I should create 5 graphs and plot them as x and y. but I should loop it. How can i do

CodePudding user response：

You can load all the sheets:

f = pd.ExcelFile('users.xlsx')

Then extract sheet names:

>>> f.sheet_names
['User_info', 'purchase', 'compound', 'header_row5']

Now, you can loop over the sheet names above. For example one sheet:

>>> f.parse(sheet_name = 'User_info')
      User Name Country      City Gender  Age
0  Forrest Gump     USA  New York      M   50
1     Mary Jane  CANADA   Tornoto      F   30
2  Harry Porter      UK    London      M   20
3     Jean Grey   CHINA  Shanghai      F   30

The loop looks like this:

for name in f.sheet_names:
    df = f.parse(sheet_name = name)
    # do something here

CodePudding user response：

No need to use variables, create the output lists and use this simple loop:

data = pd.ExcelFile("DCB_200_new.xlsx")

l = ['DCB_200_9', 'DCB_200_15', 'DCB_200_23', 'DCB_200_26', 'DCB_200_28']

x = []
y = []
for e in l:
    x.append(pd.read_excel(data, e, usecols=[2], skiprows=[0,1]))
    y.append(pd.read_excel(data, e, usecols=[1], skiprows=[0,1]))

But, ideally you should be able to load the data only once and loop over the sheets/columns. Please update your question with more info.