Stripping and dropping columns from pandas dataframe

Question

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)

mozway · Accepted Answer · 2021-08-24 08:33:34Z

2

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']

edited Aug 24, 2021 at 8:33

answered Aug 20, 2021 at 16:37

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bamir Over a year ago

Thanks, that worked. Would you mind explaining why mine didn't work? Would really help. I also thought that the strip method needs to be assigned to a variable, otherwise the effect goes, or am I wrong on that as well?

mozway Over a year ago

Well, df.columns has not strip method. The c.strip() is saved in the list comprehension (and then in the df.columns).

Bamir Over a year ago

btw, the dropping line of code didn't work for me. I used: for column in f.columns: if column[0:7] == "Unnamed": f.drop(column, inplace=True, axis=1 which ended up working for me.

mozway Over a year ago

@Bamir have you saved the output in df? or used df.drop(..., inplace=True)? If this worked with your loop this should work in one shot.

mozway Over a year ago

@Bamir I provided an example

|

Collectives™ on Stack Overflow

Stripping and dropping columns from pandas dataframe

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related