Merging Top Two Rows of a dataframe and editing the result

Question

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], 
})

Answer 1

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