Search code examples
pythonpandasdataframeconcatenation

Merge and combine text of two first rows in specific columns in Dataframe

I have a DataFrame, df based on an imported csv-file, in order to be able to update data as new dataset become available.

   Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5
1         NO1        NaN        NO2        NaN
2          Up       Down         Up       Down
3           0          0          0          0
4           0          0          0          0

In my dataset, the "NO1" is two merged cells with two sub-categories "Up" & "Down" - I want to combine the "NO1" with both "Up" and "Down" into headers with the text "NO1 Up" & "NO1 Down", etc.

What I want to end up with is:

        NO1 Up     NO1 Down    NO2 Up    NO2 Down
0          0          0          0          0
1          0          0          0          0
2          0          0          0          0
3          0          0          0          0
Solution

  • You can TRY:

    df.columns = (df.iloc[0].ffill() + ' ' + df.iloc[1]).values
df = df[2:]