Merging Top Two Rows of a dataframe and editing the result

October 9, 2023

I have a dataframe:

df = pd.DataFrame({ 
'0': ['FY18', 'Q1', 1500, 1200, 950, 2200], 
'1': ['FY18', 'Q2', 2340, 1234, 2000, 1230],
'2': ['FY18', 'Q3', 2130, 2200, 2190, 2210],   
'3': ['FY18','YearTotal', 1000, 1900, 1500, 1800], 
})

I wish to merge the top two rows of the dataframe and make it the index

I tried:

Merge the top two rows into a single row

merged_row = pd.concat([df.iloc[0], df.iloc[1]], axis=1)

Transpose the merged row to make it a single row with all columns

merged_row = merged_row.T

Replace the first row of the DataFrame with the merged row

df.iloc[0] = merged_row

But I get an error

ValueError: Incompatible indexer with DataFrame

Further I want to edit the header so it reverse the ‘Q1’ to ‘1Q’. Also deletes ‘ YearTotal’ and keeps just ‘FY18’ when the column says ‘YearTotal’.
The final output could look like this:

df = pd.DataFrame({ 
'0': ['1Q18', 1500, 1200, 950, 2200], 
'1': ['2Q18', 2340, 1234, 2000, 1230],
'2': ['3Q18', 2130, 2200, 2190, 2210],   
'3': ['FY18', 1000, 1900, 1500, 1800], 
})

>Solution :

No need for transposing the dataframe, in my opinion. You can just replace the 0 row with the needed values and drop the 1st row:

def get_unified_value(vals):
    if vals[1].startswith("Q"):
        return vals[1][1:] + "Q" + vals[0][2:]
    
    return vals[0]

df.loc[0, :] = list(map(get_unified_value, zip(df.loc[0, :], df.loc[1, :])))
print(
    df.drop(1).reset_index(drop=True)
)

Output:

      0     1     2     3
0  1Q18  2Q18  3Q18  FY18
1  1500  2340  2130  1000
2  1200  1234  2200  1900
3   950  2000  2190  1500
4  2200  1230  2210  1800