-1

So I have a CSV file like this,

enter image description here

how can I separate them into different columns like this,

enter image description here

using python without using the pandas lib.

ForceBru
  • 43,482
  • 10
  • 63
  • 98
brt
  • 57
  • 5
  • I suggest taking look at [csv](https://docs.python.org/3/library/csv.html) built-in module – Daweo Apr 26 '21 at 14:50
  • 1
    They're already separated into different columns, you should import the file as CSV in your Excel and specify comma (`,`) as the separator – ForceBru Apr 26 '21 at 14:50
  • the problem is they are located in column A – brt Apr 26 '21 at 14:53
  • @bernardo, you can import the file as CSV and change the column delimiter to `,`: https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba – ForceBru Apr 26 '21 at 14:57

2 Answers2

0

Implementation that should work in python 3.6+.

import csv

with open("input.csv", newline="") as inputfile:
    with open("output.csv", "w", newline="") as outputfile:
        reader = csv.DictReader(inputfile)  # reader
        fieldnames = reader.fieldnames
        writer = csv.DictWriter(outputfile, fieldnames=fieldnames)  # writer
        # make header
        writer.writeheader()
        # loop over each row in input CSV
        for row in reader:
            # get first column
            column: str = str(row[fieldnames[0]])
            numbers: list = column.split(",")
            if len(numbers) != len(fieldnames):
                print("Error: Lengths not equal")

            # write row in output CSV
            writer.writerow({field: num for field, num in zip(fieldnames, numbers)})

Explanation of the code:

  • The above code takes two file names input.csv and output.csv. The names being verbose don't need any further explanation.
  • It reads each row from input.csv and writes corresponding row in output.csv.
  • The last line is a "dictionary comprehension" combined with zip (similar to "list comprehensions" for lists). It's a nice way to do a lot of stuff in a single line but same code in expanded form looks like:
row = {}
for field, num in zip(fieldnames, numbers):
   row[field] = num
writer.writerow(row)
eshaan7
  • 968
  • 2
  • 9
  • 17
0

It is already separated into different columns by , as separator, but the european version of excel usually uses ; as separator. You can specify the separator, when you import the csv: https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

If you really want to change the file content with python use the replace function and replace , with ;: How to search and replace text in a file?

jackattack
  • 75
  • 8