Dataframe not outputting to Excel

Question

I am posting a snippet of my code to try to debug. I am trying to output df4 to Excel but it keeps coming up blank in the output. I have tried on my own to trouble shoot for a couple hours but can't figure out the issue.

from bs4 import BeautifulSoup
import os
import glob
import pandas as pd

os.chdir('C:/Users/PCTR261010/Desktop/Attribute Convert')
FileList = glob.glob('*.txt')

for fname in FileList:
    soup = BeautifulSoup(open(fname), 'html.parser')

    data = soup.find('data').text

    for partno in data.split('\n'):
        df4 = pd.DataFrame([partno.split('\t')])

        sheet_name = ('MPCC@' + soup.find('leafname').text)
        writer = pd.ExcelWriter(os.path.join(fname + '.xlsx'), engine='xlsxwriter')
        df4.to_excel(writer, sheet_name=sheet_name, startrow=0, startcol=0, index=False, header=False)

        writer.save()

SAMPLE DATA of the sudo XML File

<DATA>
PF0W20DEXQT     Yes NAPA    Proformer       1 qt    0W20    Synthetic   SN  ILSAC GF-5  Dexos 1 Gen 2   NAPA PROFORMER 0W20 SYNTHETIC DEXOS® MOTOR OIL is a superiorquality synthetic motor oil specially formulated from 100% pure virgin base oil and highperformance additives.  Bottle                                              
PF0W20SYQT      Yes NAPA    Proformer       1 qt    0W20    Synthetic   SN  ILSAC GF-5      NAPA PROFORMER 0W20 SYNTHETIC MOTOR OIL is a superior?quality synthetic motor oil specially formulated from 100% pure virgin base oil and highperformance additives.    Bottle                                              
PF10W30COQT         NAPA    Proformer       1 qt    10W30   Conventional    SN  ILSAC GF-5      NAPA PROFORMER 10W30 MOTOR OIL is a premiumquality conventional motor oil specially formulated from 100% pure virgin base oil and highperformance additives.    Bottle                                              
PF10W30HDBQT        Yes NAPA    Proformer       1 qt    10W30   Synthetic Blend CK-4            NAPANAPA PROFORMER 10W30 SYNTHETIC BLEND DIESEL MOTOR OIL is a superiorquality synthetic blend diesel motor oil specially formulated from 100% pure virgin base oil and highperformance additives.  Bottle                                              
PF10W30HDQT         NAPA    Proformer       1 qt    10W30   Conventional    CJ-4 / SN           NAPA PROFORMER 10W30 DIESEL MOTOR OIL is a premiumquality conventional diesel motor oil specially formulated from 100% pure virgin base oil and highperformance additives.  Bottle                                              
PF15W40HDQT         NAPA    Proformer       1 qt    15W40   Conventional    CK-4 / SN           NAPA PROFORMER 15W40 DIESEL MOTOR OIL is a premiumquality conventional diesel motor oil specially formulated from 100% pure virgin base oil and highperformance additives.  Bottle                                              
PF5W20COQT          NAPA    Proformer       1 qt    5W20    Conventional    SN  ILSAC GF-5      NAPA PROFORMER 5W20 MOTOR OIL is a premiumquality conventional motor oil specially formulated from 100% pure virgin base oil and highperformance additives. Bottle                                              
PF5W20SYQT      Yes NAPA    Proformer       1 qt    5W20    Synthetic   SN  ILSAC GF-5      NAPA PROFORMER 5W20 SYNTHETIC MOTOR OIL is a superior?quality synthetic motor oil specially formulated from 100% pure virgin base oil and highperformance additives.    Bottle                                              
PF5W30COQT          NAPA    Proformer       1 qt    5W30    Conventional    SN  ILSAC GF-5      NAPA PROFORMER 5W30 MOTOR OIL is a premiumquality conventional motor oil specially formulated from 100% pure virgin base oil and highperformance additives. Bottle                                              
PF5W30DEXQT     Yes NAPA    Proformer       1 qt    5W30    Synthetic   SN  ILSAC GF-5  Dexos 1 Gen 2   NAPA PROFORMER 5W30 SYNTHETIC DEXOS® MOTOR OIL is a superiorquality synthetic motor oil specially formulated from 100% pure virgin base oil and highperformance additives.  Bottle                                              
PF5W30SYQT      Yes NAPA    Proformer       1 qt    5W30    Synthetic   SN  ILSAC GF-5      NAPA PROFORMER 5W30 SYNTHETIC MOTOR OIL is a superior?quality synthetic motor oil specially formulated from 100% pure virgin base oil and highperformance additives.    Bottle                                              
</DATA>

OUPUT NEEDED

Output Image

please post some sample data (of your data variable) in text/CSV/Python code format (NOT as a picture) — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Apr 11, 2018 at 10:23
I know it has something to do with how I am having to split the data into rows and then separate by tabs. I can get it to output in one, but not with both constraints. — Sam Palmer
– Sam Palmer, Commented Apr 11, 2018 at 10:48
Actually you are overwriting your excel file in each iteration... What is your desired data set? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Apr 11, 2018 at 10:52
Updated with image of out put needed. But I basically need the data broke out by tab deliminator. — Sam Palmer
– Sam Palmer, Commented Apr 11, 2018 at 11:02

MaxU - stand with Ukraine · Accepted Answer · 2018-04-11 11:10:45Z

1

Try this:

def f(fname):
    soup = BeautifulSoup(open(fname), 'html.parser')
    data = soup.find('data').text
    sheet_name = ('MPCC@' + soup.find('leafname').text)

    df = pd.read_csv(io.StringIO(data), sep='\t', header=None)
    df.to_excel((os.path.join(fname + '.xlsx'), sheet_name=sheet_name, index=False, header=False)

# process all files
for fname in FileList:
    f(fname)

answered Apr 11, 2018 at 11:10

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

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

1 Comment

Sam Palmer Over a year ago

Thank you, but that didn't work for me. I'm not sure why the pd.read_csv is in there

Collectives™ on Stack Overflow

Dataframe not outputting to Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related