I have 2 dataframes that look as follows (first on is df):

The second one is df_2:

The system that I pulled the information out of split the data into 2 separate excel sheets/tabs. However, the information in df_2 was not given any column names as was the info in df. I need to concatenate them, however, if I insert an empty row of information in .loc[0] and then amend this empty info to the correct columns as in df, this will not work as the columns have already been set.

If I try to replace or rename the columns in df_2, this will force me to lose the information in that first column in df_2 which I need.

How would I go about keeping the first row in df_2 while giving it the same column names as those in df in order to concatenate the two dataframes? The result needs to look as so for df_2:

Thereafter, I can simply say:

pd.concat([df,df_2])

CodePudding user response：

You need to play with set_index/reset_index and transpose:

(df_2
 .drop(columns='Index')      # drop Index column
 .T.reset_index()            # reset column header
 .set_index(df.columns[1:])  # set names
 .T
 .reset_index(drop=True)     # drop old index
 .rename_axis('Index')       # set new index name 
 .reset_index()              # set new index as column
)

output:

   Index        Date  Detail  Amount
0      0  2001/01/24     DDD       4
1      1  2001/01/25     EEE       5
2      2  2001/01/26     FFF       6