How to output dataframe values to an Excel file? [Python]

Question

For the past few days I've been trying to do a relatively simple task but I'd always encounter some errors so I'd really appreciate some help on this. Here goes:

I have an Excel file which contains a specific column (Column F) that has a list of IDs.

What I want to do is for the program to read this excel file and allow the user to input any of the IDs they would like.

When the user types in one of the IDs, I would want the program to return a bunch IDs that contain the text that the user has inputted, and after that I'd like to export those 'bunch of IDs' to a new & separate Excel file where all the IDs would be displayed in one column but in separate rows.

Here's my code so far, I've tried using arrays and stuff but nothing seems to be working for me :/

import pandas as pd
import numpy as np
import re
import xlrd
import os.path
import xlsxwriter
import openpyxl as xl;
from pandas import ExcelWriter
from openpyxl import load_workbook
# LOAD EXCEL TO DATAFRAME
xls = pd.ExcelFile('N:/TEST/TEST UTILIZATION/IA 2020/Dev/SCS-FT-IE-Report.xlsm')
df = pd.read_excel(xls, 'FT')

# GET USER INPUT (USE AD1852 AS EXAMPLE)
value = input("Enter a Part ID:\n")
print(f'You entered {value}\n\n')
i = 0
x = df.loc[i, "MFG Device"]
df2 = np.array(['', 'MFG Device', 'Loadboard Group','Socket Group', 'ChangeKit Group'])

for i in range(17367):
#     x = df.loc[i, "MFG Device"]

    if value in x:
         df = np.array[x]
         df2.append(df)
         i += 1
print(df2)


# create excel writer object
writer = pd.ExcelWriter('N:/TEST/TEST UTILIZATION/IA 2020/Dev/output.xlsx')
# write dataframe to excel
df2.to_excel(writer)
# save the excel
writer.save()
print('DataFrame is written successfully to Excel File.')

Any help would be appreciated, thanks in advance! :)

Frank · Accepted Answer · 2020-06-15 05:01:23Z

1

It looks like you're doing much more than you need to do. Rather than monkeying around with xlsxwriter, pandas.DataFrame.to_excel is your friend.

Just do

df2.to_excel("output.xlsx")

answered Jun 15, 2020 at 5:01

Frank

5093 silver badges10 bronze badges

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

2 Comments

FreshATL Over a year ago

Yeah but i'm still getting an error that says "AttributeError: 'numpy.ndarray' object has no attribute 'to_excel'" :/

Frank Over a year ago

Ah, I wasn’t reading closely enough. Your df2 is an array, not a data frame, so you need to fix that.

Pooja Sonkar · Accepted Answer · 2020-06-15 06:41:52Z

0

You don't need xlsxwriter. Simply df.to_excel() would work. In your code df2 is a numpy array/ First convert it into a pandas DataFrame format a/c to the requirement (index and columns) before writing it to excel.

answered Jun 15, 2020 at 6:41

Pooja Sonkar

13210 bronze badges

Collectives™ on Stack Overflow

How to output dataframe values to an Excel file? [Python]

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related