How to remove the entire blank row from the existing Excel Sheet using Python?
I need a solution which DOES NOT : Include reading the whole file and rewriting it without the deleted row.
IS THERE ANY DIRECT SOLUTION?
3
-
wow, all caps? what have you tried? Is it just one blank row or are there multiple blank rows?astrosyam– astrosyam2016-01-04 06:15:31 +00:00Commented Jan 4, 2016 at 6:15
-
I want to exactly perform the "Delete Row " operation of excel in which the row is deleted and other rows are shifted upJd16– Jd162016-01-04 10:21:37 +00:00Commented Jan 4, 2016 at 10:21
-
It will be Okay if I could delete a single row from an excel sheet.Jd16– Jd162016-01-04 10:23:09 +00:00Commented Jan 4, 2016 at 10:23
Add a comment
|
3 Answers
I achieved using Pandas package....
import pandas as pd
#Read from Excel
xl= pd.ExcelFile("test.xls")
#Parsing Excel Sheet to DataFrame
dfs = xl.parse(xl.sheet_names[0])
#Update DataFrame as per requirement
#(Here Removing the row from DataFrame having blank value in "Name" column)
dfs = dfs[dfs['Name'] != '']
#Updating the excel sheet with the updated DataFrame
dfs.to_excel("test.xls",sheet_name='Sheet1',index=False)
2 Comments
manty
Looks like this solution will work for only 1 column, but what if there are multiple columns ? Further this line
dfs = dfs[dfs['Name'] != ''] is giving a type error TypeError: invalid type comparison.
Jd16
Yes you are right. This statement can't be applied for multiple columns. If we want to delete a row which have Name as "abc" AND Salary as 2000 we can't do that with this statement. But if we want to delete a row which have Name as "abc" OR Salary as 2000 we can do that with this statement writing again and again and then ultimately converting the DataFrame to Excel.
If using memory is not an issue you can achieve this using an extra dataframe.
import pandas as pd
#Read from Excel
xl= pd.ExcelFile("test.xls")
dfs = xl.parse(xl.sheet_names[0])
df1 = dfs[dfs['Sal'] == 1000]
df1 = df1[df1['Message']=="abc"]
ph_no = df1['Number']
print df1
Comments
To delete an Excel row, say row 5, column does not matter so 1:
sh.Cells(5,1).EntireRow.Delete()
To delete a range of Excel rows, say row 5 to 20
sh.Range(sh.Cells(5,1),sh.Cells(20,1)).EntireRow.Delete()
