Difference between revisions of "Clean up excel data"

From rbachwiki
Jump to navigation Jump to search
Line 15: Line 15:
</pre>
</pre>
==[[#top|Back To Top]] - [[Python|Category]]==
==[[#top|Back To Top]] - [[Python|Category]]==
[[Category:Python]]

Revision as of 16:21, 1 September 2020

import pandas as pd
excel_file = 'docs/baddata.xlsx'
df = pd.read_excel(excel_file)
#print(df.head(2))

#test on one column
#df['Name'] = df['Name'].str.replace(r'\W',"")
# r means regular expression \w (opposite of w) selects everything that is not a number and not a letter, replace with blank
#apply to entire sheet
for column in df.columns:
    df[column] = df[column].str.replace(r'\W',"")
print(df)
df.to_excel("docs/cleaned.xlsx")

Back To Top - Category