Remove column index from dataframe

Question

I extracted multiple dataframes from excel sheet by passing cordinates (start & end) Now i used below funtion to extacr according to cordinates, but when i am trying to convert it into dataframe, no sure from where index are coming in df as columns I wanted to remove these index and make 2nd row as columns, this is my dataframe

         0     1     2    3     4    5     6
  Cols/Rows    A    A2    B    B2    C    C2
0         A   50    50   150    150  200   200
1         B  200    200  250    300  300   300
2         C  350    500  400    400  450   450

def extract_dataframes(sheet):                         
    ws = sheet['pivots']
    cordinates = [('A1', 'M8'), ('A10', 'Q17'), ('A19', 'M34'), ('A36', 'Q51')]
    multi_dfs_list = []
    for i in cordinates:
        data_rows = []
        for row in ws[i[0]:i[1]]:
            data_cols = []
            for cell in row:
                data_cols.append(cell.value)
            data_rows.append(data_cols)
        multi_dfs_list.append(data_rows)
    multi_dfs = {i: pd.DataFrame(df) for i, df in enumerate(multi_dfs_list)}
    return multi_dfs

I tried to delete index but not working. Note: when i say

>>> multi_dfs[0].columns # first dataframe
RangeIndex(start=0, stop=13, step=1)

rafaelc · Accepted Answer · 2018-07-02 13:43:01Z

2

Change

multi_dfs = {i: pd.DataFrame(df) for i, df in enumerate(multi_dfs_list)}

for

multi_dfs = {i: pd.DataFrame(df[1:], columns=df[0]) for i, df in enumerate(multi_dfs_list)}

From the Docs,

columns : Index or array-like Column labels to use for resulting frame. Will default to RangeIndex (0, 1, 2, …, n) if no column labels are provided

answered Jul 2, 2018 at 13:43

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

Comments

jezrael · Accepted Answer · 2018-07-02 13:43:23Z

1

I think need:

df = pd.read_excel(file, skiprows=1)

edited Jul 2, 2018 at 13:43

answered Jul 2, 2018 at 13:39

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

4 Comments

sudonym Over a year ago

@TarunK that doesn't matter In this context

Tarun K Over a year ago

previous_ = load_workbook(previous_file, read_only=True)

rafaelc Over a year ago

I think he has lots of tables in the same excel worksheet and extracts many different dfs from these tables. So he probably has to skip rows in between these tables too, I guess

jezrael Over a year ago

@TarunK - Then super.

Collectives™ on Stack Overflow

Remove column index from dataframe

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related