user6452366 user6452366 - 4 months ago 48
Python Question

Trying to remove top row from excel spreadsheet using Python

this is my first question here so if I don't follow perfect etiquette please forgive me. I have been searching for an answer for about 6 hours so I figured it was time to ask. I'm new to Python, and I'm trying to automate data entry with Selenium. I'm doing well, but stuck at the part where I pull data from Excel. The data pulling isn't the problem, but telling my excel to delete the top row, or move onto the next, is. Here is a sample of my script:

import pandas as pd
from pandas import ExcelWriter
import numpy as np

xl = pd.ExcelFile('C:\\Users\\Steph_000\\Desktop\\students2.xlsx')
xl

wr = pd.ExcelWriter('C:\\Users\\Steph_000\\Desktop\\students2.xlsx')

xl.sheet_names

['Sheet1']

df = xl.parse('Sheet1') #This reads the sheet into a dataframe
df

cp = pd.read_excel('C:\\Users\\Steph_000\\Desktop\\students2.xlsx')

IDNum = cp[:1] #This pulls the first row as a variable

print(IDNum) #This is where it prints to the search bar to look the student up

chart = cp[1:] #This pulls everything but the first row as a variable

print(chart)

df.to_excel(wr, 'Sheet1')
wr.save() #This is supposed to overwrite it, but just saves it as blank


No matter what I do, I can't get it to move up the rows, or anything like that. I'm really struggling and very frustrated. I'll use a different Python wrapper than Pandas if I have to, but I've already tried openpyxl, xlrd, and xlwr to no avail! Pulling my hair out, thanks in advance!

Answer

Look at example below:

  • missing engine='xlsxwriter'
  • missing sheet_name='Sheet1

Look at this:

            import pandas as pd
            #Create a Pandas dataframe from the data.
            df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

            # Create a Pandas Excel writer using XlsxWriter as the engine.
            writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

            # Convert the dataframe to an XlsxWriter Excel object.
            df.to_excel(writer, sheet_name='Sheet1')

            # Close the Pandas Excel writer and output the Excel file.
            writer.save()

http://xlsxwriter.readthedocs.io/working_with_pandas.html