Stripping and dropping columns from pandas dataframe

user_15777187 0
Viewed 41

I have the following columns in a pandas df:

Index(['Commodity Derivative Name\n(including associated contracts)',
       'Venue MIC ', 'Name of Trading Venue ', 'Venue Product Codes ',
       'Principal Venue Product Code', 'Spot month single limit#',
       'Other month limit#', 'Conversion Factor', 'Unit of measurement',
       'Definition of spot month', 'Unnamed: 10', 'Unnamed: 11', 'Unnamed: 12',
       'Unnamed: 13', 'Unnamed: 14', 'Unnamed: 15'],
      dtype='object')

I have looked at a few solutions for this, and I am not sure if it is because I am tired, but I cannot get this to work at all.

I guess I could hardcode in the columns but the file could change in the future and thought this would be better to do. I think that maybe after it strips the column in the temp column, it is maybe looking for the unstripped column which is no longer there, so it bugs out - not completely sure.

I have the following code to clean the columns of a df:

f = pd.read_excel(r"fca_position_limits.xlsx")

# unwanted spaces need to be removed from headers
f.columns = f.columns.strip() # --> this did not work

temp_f = f.copy()

for column in f.columns:
    temp_f = temp_f[column].str.strip()
    if column[0:7] == "Unnamed":
        temp_f.drop(column, inplace=True)
pandas dataframe python-3.x
1 Answers

To remove the trailing spaces:

df.columns = [c.strip() for c in df.columns]

and to drop the "Unnamed" columns:

df.drop(columns=df.filter(like='Unnamed').columns)

Here is an example for the drop part:

input:

>>> df = pd.DataFrame([], columns=['A', 'B', 'Unnamed 1', 'Unnamed 2', 'C'])
>>> df.columns
['A', 'B', 'Unnamed 1', 'Unnamed 2', 'C']

output:

>>> df2 = df.drop(columns=df.filter(like='Unnamed').columns)
>>> df2.columns
['A', 'B', 'C']
user_163434640
Related