How to append to duplicate column headers based on non-duplicate column headers?

user_17420813 0
Viewed 79

I have a pandas dataframe where I have a a number of repeated column names that are preceded by some subcategory header. Here is an example: Dataframe with repeating headers.

What I would like to do, is to append the subcategory (e.g. Occupancy (%), ADR, RevPAR, etc.) to the repeated headers (This Year, Last Year, Percent Change). So that I achieve something of the sort:

"Occupancy (%): This Year, Occupancy (%): Last Year, ..., ADR: This Year, ADR: Last Year, ..."

I would also like to do this generally so that I am not explicitly specifying the header I'd like to append, but instructing to "return to the last non-repeating/non-duplicated header and append this to all following headers until the next non-repeating/non-duplicated header is reached and repeat"

Is this possible? Is there a simpler logic I could be using?

pandas python
1 Answers

An way to do this is to find any repeated column and append the previous non-unique column to the name. This code example does this:

First we generate a test dataframe

import pandas as pd

df = pd.DataFrame(data=[[1,2,3,4,5,6,7,8]], 
                  columns=['Occupancy', 'This year', 'Last year', 'Percent Change', 'ADR', 'This year', 'Last year', 'Percent Change'])

And this renames the columns as per your request

import collections

# Find the columns that are repeated so we know which ones we need to append to 
# the non-repeated columns
repeated_columns = [item for item, count in 
collections.Counter(list(df.columns)).items() if count > 1]

new_columns = list()
uniq_column = None

for column in list(df.columns):
    # For every column check if it is a repeated column
    # or a non-repeated/unique column
    if column not in repeated_columns:
        # If this column is a non-repeated/unique column
        # store it and append it to the new columns list so we can
        # have a 1:1 match with the old columns. This column will not 
        # change it's name
        uniq_column = column
        new_columns.append(column)
    else:
        # In case this is a repeated column add the unique column
        # we have identified earlier and append it to the new columns list
        new_columns.append(uniq_column + ':' + column)
    
df.columns = new_columns
user_81214360
Related