Python: Output Data to Excel Spreadsheet

Question

I need to do what is probably very simple for an experienced coder. My Python program successfully does the following:

1. Import an excel spreadsheet
2. Iterate through the rows and count repeating occurrences of a "Model", whether the model (Location) is consumed or not, and who "consumed" the model (and how many they've consumed). Anything not "Consumed" will be considered "Available".
3. Print the "Model", "Consumed", "Available", and "Requestor"(someone who's consumed the model).

Instead of just printing the values, I need them to be added to a sheet on a excel workbook with Model, Consumed, Avaible, and Requestors as the column headers.

Here's how I'm printing the values:

if not REQUESTORLIST:
    print(CURRENTMODEL, "Consumed:", CONSUMEDCOUNTER, "Available:", AVAILABLECOUNTER)
else:
    print(CURRENTMODEL,"Consumed:",CONSUMEDCOUNTER, "Available:",AVAILABLECOUNTER,REQUESTORS)

Here are the data types:

print(type(CURRENTMODEL))
print(type(CONSUMEDCOUNTER))
print(type(AVAILABLECOUNTER))
print(type(REQUESTORS))

Outputs:

<class 'str'>
<class 'int'>
<class 'int'>
<class 'collections.Counter'>

Finally, program outputs truncated:

Model    WS-SFP Consumed: 1 Available: 2 Counter({'Requester    Anthony House': 1})

I'm brand new to programming (this is the first program I've written) and am having trouble finding a way to get these values to write to an excel sheet with the four column headers I need. I've tried to convert them to strings and use .write but have been unsuccessful so far. Do you have any suggestions?

EDIT: Thanks for the quick responses. I'm thinking it may be helpful for me to just post my code. I'm open to feedback on how to optimize this thing as it's likely subpar. I've been experimenting with .write, which skips rows in the excel sheet output, doesn't fill column headers, etc. Probably not my best option.

import os
import openpyxl
import matplotlib
import numpy
import pandas as pd
import xlrd
import xlwt
from xlwt import Workbook
import xlsxwriter
from collections import Counter

#file to pull data from
excel_file = 'Customer_Inventory_Test.xlsx'
models = pd.read_excel(excel_file)
#file to export results
workbook = xlsxwriter.Workbook('Inventory Report.xlsx')
worksheet = workbook.add_worksheet()

row = 0
col = 0
ROWCOUNT = models.shape[0]

while True:
    CONSUMEDCOUNTER = 0
    AVAILABLECOUNTER = 0
    REQUESTORLIST = []
    #break when no more rows
    if row == ROWCOUNT:
        break
    MODEL = models.iloc[row, [0]]
    #convert to string for comparison
    MODEL = MODEL.to_string()
    CURRENTMODEL = MODEL
    LOCATION = models.iloc[row, [2]]
    LOCATION = LOCATION.to_string()

    while CURRENTMODEL == MODEL:
        if "Consumed" in LOCATION:
            CONSUMEDCOUNTER += 1
            REQUESTOR = models.iloc[row, [17]]
            # convert to string for comparison
            REQUESTOR = REQUESTOR.to_string()
            REQUESTORLIST.append(REQUESTOR)
        else:
            AVAILABLECOUNTER += 1
        row += 1
        if row == ROWCOUNT:
            break
        MODEL = models.iloc[row, [0]]
        MODEL = MODEL.to_string()
        LOCATION = models.iloc[row, [2]]
        LOCATION = LOCATION.to_string()

    REQUESTORS = Counter(REQUESTORLIST)

    if not REQUESTORLIST:
        worksheet.write(row, col, CURRENTMODEL)
        worksheet.write(row, col + 1, CONSUMEDCOUNTER)
        worksheet.write(row, col + 2, AVAILABLECOUNTER)
        print(CURRENTMODEL[9:], "Consumed:", CONSUMEDCOUNTER, "Available:", 
AVAILABLECOUNTER)
    else:
        worksheet.write(row, col, CURRENTMODEL)
        worksheet.write(row, col + 1, CONSUMEDCOUNTER)
        worksheet.write(row, col + 2, AVAILABLECOUNTER)
        #worksheet.write(row, col + 3, REQUESTORS) <- Doesn't like 
#requestors data structure
        print(CURRENTMODEL[9:],"Consumed:",CONSUMEDCOUNTER, 
"Available:",AVAILABLECOUNTER,REQUESTORS)

workbook.close()

Solution

Welcome to Stack Overflow. Check out this DataFrame method: .to_excel(). You need to call this method from your dataframe, providing a file path, which will be where the new Excel file will be located. An example: my_data_frame.to_excel('path/to/my/new_file.xlsx').

Comments

[chickity china chinese chicken]

Possibly helpful or related: Write headers to excel file in python

[smj]

Hey, can you provide some more information about the shape of the data you'd like to export? Is it actually in a dataframe? In my answer, I assumed that it was, which may have been a mistake...

[Anthony]

See edited post above. One of the problems is that the final data shapes are strings and ints, that were extrapolated from the original df. This may be easier to conver them back to df, but I'm having trouble with that.

[Anthony]

Thank you! I've experimented with that method a bit but the problem I have is that in my code I have to convert the values from df to strings so I can compare the strings to each other. I haven't been able to figure out how to convert the strings back into a data frame. I posted my entire code in the original question for more context.

[Anthony]

I will dig in to these. Thank you.