CODEWITHSUNDEEP
pythonpython-3.xexcelpandas
JodeCharger100
JodeCharger100

Reputation: 1059

Reading each worksheet into a new dataframe

I have more than 50 worksheets in an Excel file. My primary objective is to import the different columns into different dataframes. I can import the files and read all the names of the worksheets.

import pandas as pd
df = pd.read_excel('Fileoffiles.xls')
df.sheet_names # will give me names of all my sheets

The idea is to read the sheets into df1, df2....., df50

I do not want it in a dictionary of dataframes, but just individual dataframes.

Upvotes: 1

Views: 444

Answers (3)

brady brady
brady brady

Reputation: 11

Another Great Example of Respondents on SO flatly Telling the Inquisitor that they are wrong to ask about the ability of a Program to Solve A Problem, instead of Simply Answering It if they could, or just not responding, since you have told the OP he doesn't want what he specifically asked for...

OP stated :

I do not want it in a dictionary of dataframes, but just individual dataframes.

Upvotes: -1

willwrighteng
willwrighteng

Reputation: 2994

Like Mayank mentioned if you want each dataframe named then a dictionary of dataframes will probably work best.

import pandas as pd
xl = pd.ExcelFile('Book1.xlsx')
sheets = xl.sheet_names 
    
dfs = {}
df_names = range(len(sheets))
df_names = ['df'+str(i) for i in df_names]
for sheet,name in zip(sheets,df_names):
    dfs[name] = xl.parse(sheet)
    
dfs

results in:

{'df0':     col_1  col_2  col_3  col_4  col_5
 0      12     18     24     30     36
 1      23     24     25     26     27
 2      34     30     26     22     18
 3      45     36     27     18      9
 4      56     42     28     14      0
 5      67     48     29     10     -9
 6      78     54     30      6    -18
 7      89     60     31      2    -27
 8     100     66     32     -2    -36
 9     111     72     33     -6    -45
 10    122     78     34    -10    -54
 11    133     84     35    -14    -63
 12    144     90     36    -18    -72
 13    155     96     37    -22    -81
 14    166    102     38    -26    -90
 15    177    108     39    -30    -99
 16    188    114     40    -34   -108
 17    199    120     41    -38   -117,
 'df1':          col_1       col_2  col_3      col_4      col_5
 0     1.000000   15.000000   29.0  43.000000  57.000000
 1     5.000000   20.000000   35.0  50.000000  65.000000
 2    19.000000   27.000000   35.0  43.000000  51.000000
 3    26.333333   32.666667   39.0  45.333333  51.666667
 4    35.333333   38.666667   42.0  45.333333  48.666667
 5    44.333333   44.666667   45.0  45.333333  45.666667
 6    53.333333   50.666667   48.0  45.333333  42.666667
 7    62.333333   56.666667   51.0  45.333333  39.666667
 8    71.333333   62.666667   54.0  45.333333  36.666667
 9    80.333333   68.666667   57.0  45.333333  33.666667
 10   89.333333   74.666667   60.0  45.333333  30.666667
 11   98.333333   80.666667   63.0  45.333333  27.666667
 12  107.333333   86.666667   66.0  45.333333  24.666667
 13  116.333333   92.666667   69.0  45.333333  21.666667
 14  125.333333   98.666667   72.0  45.333333  18.666667
 15  134.333333  104.666667   75.0  45.333333  15.666667
 16  143.333333  110.666667   78.0  45.333333  12.666667
 17  152.333333  116.666667   81.0  45.333333   9.666667
 18  161.333333  122.666667   84.0  45.333333   6.666667}

A list of dataframes is a little less complicated

dfs = []
for sheet in sheets:
    dfs.append(xl.parse(sheet))

reference

Upvotes: 1

Rajesh Bhat
Rajesh Bhat

Reputation: 1000

You can try using "eval" to assign the i-th sheet to the i-th dataframe. The code would be something like:

xls = pd.ExcelFile('Fileoffiles.xls')
for i in range(len(xls.sheet_names)):
    eval('df' + str(i) '= pd.read_excel("Fileoffiles.xls", sheetname="' + xls.sheet_names[i] +'")')

Upvotes: 1

Related Questions