Difference between revisions of "Clean up excel data"
Jump to navigation
Jump to search
(Created page with "<pre> 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',""...") |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 14: | Line 14: | ||
df.to_excel("docs/cleaned.xlsx") | df.to_excel("docs/cleaned.xlsx") | ||
</pre> | </pre> | ||
==[[#top|Back To Top]] - [[Python|Main Category]]/[[Python_Excel_Related| Excel Category]]== | |||
[[Category:Python]] | |||
Latest revision as of 16:25, 2 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")