0

I've been trying to concatenate two sheets while preserving the original indices of both dataframes. However, upon concatenation I can't seem to get the result to output the way I expect or want.

If I use ignore_index = True The old indices are replaced by an index that encompasses both sheets total rows. If I use ignore_index = False The indices are preserved, but there is a new empty column preceding it shifting the rest of columns over by one.

How can I concatenate my sheets without this excess column?

import pandas as pd
import easygui

path = easygui.fileopenbox("Select a file")
xls = pd.ExcelFile(path)

potential_names = [sheet for sheet in xls.sheet_names if sheet.startswith('Numbers_')]

df = pd.concat(pd.read_excel(xls, sheet_name = potential_names), ignore_index = True)

command_list = ["AA", "dd", "DD"]
warning_list = ["Dd", "dD"]
ingenium_list = ["CC", "BB"]

col_name_type = 'TYPE'
col_name_other = 'OTHER'
col_name_source = 'SOURCE'
df_filtered_command = df[df[col_name_type].isin(command_list)]
df_filtered_warnings = df[df[col_name_type].isin(warning_list)]
df_filtered_other = df[df[col_name_other].isin(ingenium_list)]

with pd.ExcelWriter(('(Processed) ' + os.path.basename(path))) as writer:
    #df_filtered_command.to_excel(writer, sheet_name="Command", index=True)
    df_final_command.to_excel(writer, sheet_name=("Command"), index=True)
    df_filtered_warnings.to_excel(writer, sheet_name="Warnings", index=True)
    df_filtered_other.to_excel(writer, sheet_name="Issues", index=True)

I suspect how the concat function is working, but I've not been able to figure out how to fix it.

Any help or direction would be amazing.

Edit: adding an example of my df after running.

Example output

I was mistaken before, it seems the first column is empty aside from the sheet names, but I'm still not able to find a way to prevent pandas from making that first column if I don't remake the index.

cVos
  • 27
  • 1
  • 5
  • Please provide a runnable, [reproducible example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) since we do not have access to your *path*. Please even show this very interesting *empty column*. – Parfait May 05 '22 at 00:18
  • @Parfait My mistake for not working it out. I've updated with an output example of the dataframe I write, and hopefully clarified what's happening vs what I expect. – cVos May 05 '22 at 03:13
  • As I thought, that is not an empty column but a MultiIndex! What is you desired result? Indexes are more reliable if all elements are unique. – Parfait May 05 '22 at 16:12
  • @Parfait Oh, I see! My end goal is to use the index as a reference of sorts to the original sheets rows. I previously only handled one worksheet in a workbook so there was no concatenating going on and I could just update the index to accurately reflect the master spread sheet (I would simply add 2 to the df's index), but now with the multiIndex I can't seem to figure out how to accomplish my goal. Thanks for the help, knowing it's a multi-index at least gives me more to look into! – cVos May 05 '22 at 18:41

1 Answers1

1

Since you passed in a dictionary (not a list) of data frames from using a list in sheet_names of pandas.read_excel, pandas.concat will preserve the dict keys, as specified for the first argument:

objs: a sequence or mapping of Series or DataFrame objects

If a mapping is passed, the sorted keys will be used as the keys argument, unless it is passed, in which case the values will be selected (see below).

Consequently, the sheet names (i.e., dict keys) and the index values of each data frame migrate as a MultIindex. Consider using the names argument to name the indexes:

names: list, default None

Names for the levels in the resulting hierarchical index.

sheets_df = pd.concat(
    pd.read_excel(xls, sheet_name = potential_names),
    names = ["sheet_name", "index"]
)

If you want to convert the dual index as new columns, simple run reset_index afterwards:

sheets_df = (
    pd.concat(
        pd.read_excel(xls, sheet_name = potential_names),
        names = ["sheet_name", "index"]
    ).reset_index()
)
Parfait
  • 104,375
  • 17
  • 94
  • 125