I have a .xlsx file I am creating two dataframes from and then trying to concat them so that the right columns append to the bottom of the left columns. How do I get the data in the 2nd dataframe to move directly below the 1st dataframe?

import pandas as pd

data = pd.read_excel('test.xlsx')
header = data[['col_1', 'col_2', 'col_3']]
detail = data[['col_4', 'col_5', 'col_6']]
combined = pd.concat([header, detail], ignore_index=True)
combined.to_csv('output.csv', header=None)

test.xlsx

Actual: output.csv

Expected: output.csv

CodePudding user response：

You could simply change the column names then combine:

header = data[['col_1', 'col_2', 'col_3']]
detail = data[['col_4', 'col_5', 'col_6']]
detail.columns = ['col_1', 'col_2', 'col_3']

combined = pd.concat([header, detail], axis = 0)

CodePudding user response：

try this:

cols = ['col_1', 'col_2', 'col_3']
header = data[cols]
detail = data[['col_4', 'col_5', 'col_6']].set_axis(cols, axis=1)
combined = pd.concat([header, detail], ignore_index=True).reindex(data.columns, axis=1)