4

I need to convert a text file to CSV. Please see my code below. My text file contains 5 columns separated by unequal spaces. When I converted this to a CSV file, all these 5 columns are coming in a single column in the Excel file.

Code:

import pandas as pd   
 
dataframe1 = pd.read_csv("C:\HARI_BKUP\PYTHON_SELF_Learning\Funct_Noise_Corners_2p0_A.txt",) 
  
dataframe1.to_csv('FF.csv', index = None)

I need a CSV file with each separate column. May I know where I went wrong?

TEXT FILE

enter image description here

Output after running the code

dataframe1 = pd.read_csv("C:\HARI_BKUP\PYTHON _SELF Learning\Funct_Noise_Corners_2p0_A.txt", sep="\t")

enter image description here

Triet Doan
  • 11,455
  • 8
  • 36
  • 69
Hari
  • 333
  • 6
  • 18

2 Answers2

5

When you read the file, you didn't specify the separator. Therefore, Python thinks that the separators are commas and treats the input as a CSV file (you can check the docs.).

However, your input seems to be a space-separated file instead. Your code should be:

import pandas as pd

file_path = "C:\HARI_BKUP\PYTHON_SELF_Learning\Funct_Noise_Corners_2p0_A.txt"
dataframe1 = pd.read_csv(file_path, delim_whitespace=True)

dataframe1.to_csv('FF.csv', index = None)

Simply set the parameter delim_whitespace=True would make it work for you.

Triet Doan
  • 11,455
  • 8
  • 36
  • 69
  • ,I ran the code but still all are coming in the same column in excel file. – Hari Apr 27 '21 at 10:17
  • Could you open the file with Notepad and post here how it looks like? – Triet Doan Apr 27 '21 at 10:18
  • This one is working "delimiter=r"\s+"" .I am getting separated columns.But alingnment is not proper I want all to be alligned in middle – Hari Apr 27 '21 at 10:20
  • Ah, so your input is space-separated. I will update my answer. Regarding the alignment, Python has nothing to do with it. A CSV file does not contain information about alignment. – Triet Doan Apr 27 '21 at 10:25
  • I have posted the notepad file after running the code.Thank you. – Hari Apr 27 '21 at 10:26
  • It is working fine thank you. May I know is there any method to align the csv file.I would like all columns should be aligned center. – Hari Apr 27 '21 at 10:30
  • Actually no, CSV is only about data, not formatting. If you want to save your dataframe in a table format with alignment, I suggest [tabulate](https://pypi.org/project/tabulate/) package, which was used in this [answer](https://stackoverflow.com/a/48350780/1814420). – Triet Doan Apr 27 '21 at 10:36
2

I think this will solve it

dataframe1 = pd.read_csv('textfile.txt', delimiter = "\t")

or

dataframe1 = pd.read_csv('textfile.txt', sep=" ")
Rishids
  • 43
  • 5