How to read a txt with pandas and correctly put it in a dataframe to be converted to excel

0 votes

I just started web scraping. My scraped data has to be transformed into an xlsx file. I changed the text file to look like a csv so that it could subsequently be changed in Excel. txt appears as follows: info about energy levels in Brazil, I generated the following data frame information: dataframe result  FURNAS row is set as an index row in the first line, but I want it to be a regular row.

I've tried using index = None and setting a row of white characters, but all I want is an excel table without any index rows or columns.

Mar 21, 2023 in Others by narikkadan
• 63,600 points
2,068 views

1 answer to this question.

0 votes

It appears that you wish to create an XLSX file without an index using the scraped data. You can try the following actions to do this:

  1. Make sure you have the required libraries installed. You will need pandas and openpyxl. If you don't have them installed, you can install them using:
pip install pandas openpyxl
  1. Assuming you have your data in a CSV-like txt file, first read the contents into a pandas DataFrame:
import pandas as pd

file_path = "your_file.txt"
df = pd.read_csv(file_path, delimiter=";")

# It's recommended to display the DataFrame to see its current structure
print(df.head())
  1. If the FURNAS row is set as the index row, reset the index to make it a regular row:
df.reset_index(inplace=True)
  1. Save the DataFrame to an Excel file without an index and header (if you don't want the column names as well):
output_file_path = "output.xlsx"
df.to_excel(output_file_path, index=False, header=None, engine='openpyxl')
  1. After running these steps, you should have an XLSX file without any index rows or columns. Be sure to replace your_file.txt with the correct filename and path of your txt file and output.xlsx with the desired output filename and path.
answered Mar 21, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,600 points
2,560 views
0 votes
1 answer

I want to make Excel read a value in Calc and copy it to my sheet in Excel

Here is the sample code that will allow ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,600 points
538 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,000 points
660 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
3,548 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,600 points
2,334 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,600 points
2,713 views
0 votes
1 answer

Export DataFrame timedelta column to timestamp Excel column

The reason that the column format isn't ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
1,581 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,000 points
1,784 views
0 votes
1 answer

Is it possible to create a universal hyperlink in excel for windows and mac without programming

You may learn whether it runs on ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,000 points
893 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP