apply result is a dataframe, how can I store it?

Question

I have a dataframe containing a path to an excel file, a sheet name and an id, each in one column:

df = pd.DataFrame([['00100', 'one.xlsx', 'sheet1'],
                   ['00100', 'two.xlsx', 'sheet2'],
                   ['05300', 'thr.xlsx', 'sheet3'],
                   ['95687', 'fou.xlsx', 'sheet4'],
                   ['05300', 'fiv.xlsx', 'sheet5']],
                  columns=['id', 'file', 'sheet'])

This dataframe looks like:

      id         file   sheet
0  00100  c:\one.xlsx  sheet1
1  00100  c:\two.xlsx  sheet2
2  05300  c:\thr.xlsx  sheet3
3  95687  c:\fou.xlsx  sheet4
4  05300  c:\fiv.xlsx  sheet5

I made a function to use with apply, which will read each file and return a dataframe.

 def getdata(row):
    file = row['file']
    sheet = row['sheet']
    id = row['id']
    tempdf = pd.ExcelFile(file)     # Used on purpose
    tempdf = tempdf.parse(sheet)    # Used on purpose
    tempdf['ID'] = id
    return tempdf

I then use apply over the initial dataframe so it will return a dataset for each row. The problem is, I don't know how to store the dataframes created in this way.

I tried to store the dataframes in a new column, but the column stores None:

df['data'] = df.apply(getdata, axis=1)

I tried to create a dictionary but the ways that came to my mind were plain useless:

results = {df.apply(getdata, axis=1)}  # for this one, in the function I tried to return id, tempdf

In the end, I ended converting the 'id' column to an index to iterate over it in the following way:

for id in df.index:
    df[id] = getdata(df.loc[id], id)

But I want to know if there was a way to store the resulting dataframes without using an iterator.

Thanks for your feedback.

In the line `tempdf = temp.parse(sheet)`, what is `temp`? Did you mean `tempdf`? — Peter Leimbigler, Mar 27 '18 at 03:05
You are storing each file in a column? Or you want to create a new data frame of the files you read? — YOLO, Mar 27 '18 at 03:30
Manish, I tried to create a new dataframe for each file read, the problem arised when I tried to store them, as I can not store a dataframe in a cell and I can't find a way to store the results of an apply function to a dictionary. — Caranarq Aramat, Mar 27 '18 at 15:18
Do note: [`DataFrame.apply` is a loop](https://stackoverflow.com/a/38938507/1422451) under the hood! — Parfait, Mar 27 '18 at 17:25

apply result is a dataframe, how can I store it?

0 Answers0