CODEWITHSUNDEEP
pythonexcelpython-2.7pandas
Gombo
Gombo

Reputation: 748

how to merge a multirows header of a pandas dataframe into a single cell header?

I have a pandas DataFrame from an excel file with the header split in multiple rows as the following example:

    0           1       2       3           4           5           6           7
5   NaN         NaN     NaN     NaN         NaN         NaN         NaN         Above
6   Planting    Harvest NaN     Flowering   Maturity    Maturity    Maturity    ground
7   date        date    Yield   date        date        date        date        biomass
8   YYYY.DDD    YYYY.DDD(kg/ha) YYYY.DDD    YYYY.DDD    YYYY.DDD    YYYY.DDD    (kg/ha)
9   NaN         NaN     NaN     NaN         NaN         NaN         NaN         NaN
10  1999.26     2000.21 5669.46 2000.14     2000.19     2000.19     2000.19     11626.7
11  2000.27     2001.22 10282.5 2001.15     2001.2      2001.2      2001.2      20565
12  2001.27     2002.22 8210.09 2002.15     2002.2      2002.2      2002.2      16509

I need to merge (that is join with a white space as glue) rows 5 to 9 (included) by column so to have just one header like this (I've formatted the table so to be easily read, so there are more tabs than actually should be)

Planting date YYYY.DDD   Harvest date YYYY.DDD    Yield (kg/ha)  Flowering date YYYY.DDD     Maturity date YYYY.DDD  Maturity date YYYY.DDD  Maturity date YYYY.DDD Above ground biomass (kg/ha)
1999.262                2000.206                5669.45623      2000.138                    2000.19                 2000.19                 2000.19                 11626.73122
2000.268                2001.216                10282.49713     2001.151                    2001.2                  2001.2                  2001.2                  20564.99427
2001.272                2002.217                8210.091653     2002.155                    2002.201                2002.201                2002.201                16509.03802

I guess it should be rather trivial, but I can't find my solution.

Any help will be appreciated

Upvotes: 4

Views: 6609

Answers (1)

jezrael
jezrael

Reputation: 862661

You can first select by loc, then replace NaN to empty string by fillna and apply join. If necessary remove first and last whitespaces by str.strip and then remove first rows by selecting df.loc[10:]:

df.columns = df.loc[5:9].fillna('').apply(' '.join).str.strip()

#if need monotonic index (0,1,2...) add reset index
print (df.loc[10:].reset_index(drop=True))
  Planting date YYYY.DDD Harvest date YYYY.DDD(kg/ha) Yield YYYY.DDD  \
0                1999.26                      2000.21        5669.46   
1                2000.27                      2001.22        10282.5   
2                2001.27                      2002.22        8210.09   

  Flowering date YYYY.DDD Maturity date YYYY.DDD Maturity date YYYY.DDD  \
0                 2000.14                2000.19                2000.19   
1                 2001.15                 2001.2                 2001.2   
2                 2002.15                 2002.2                 2002.2   

  Maturity date (kg/ha) Above ground biomass  
0               2000.19              11626.7  
1                2001.2                20565  
2                2002.2                16509

Upvotes: 6

Related Questions