Clever Geek Handbook

How to read a text file with columns

I have a csv file:

Model   1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17
Acura   CSX/EL  MDX NSX RDX RSX/Integra Other
Alfa Romeo  145/146/147 156/159 166 4C  Brera   GIULIETTA   GTV/GT  Mito    Spider  Other
Aston Martin    DB7/DB9 DBS One-77  Rapide  Vanquish    Vantage Virage  Zagato  Other
Audi    A1  A3  A4  A5  A6  A7  A8  Q3  Q5  Q7  R8  S3/RS3  S4/RS4  S5/RS5  S6/RS6  S7/RS7  S8

In cases where the first column is a car model and the other columns are made from that car.

The number of markers is dynamic. I want to keep a brand for each car and tried to do it like this:

# -*- coding: utf-8 -*-
import csv
class CsvToJson:
    def __init__(self, csvFilePath):
        with open(csvFilePath, 'rU') as csvFile:
            spamreader = csv.reader(csvFile, delimiter= ';',
                                    quotechar = '|', dialect='excel')
            final = dict()
            for row in spamreader:
                makes = list()
                print ', '.join(row)

k = CsvToJson(csvFilePath = 'carsModelsMakes.csv')

But I am stuck on how to get the column.

after great answer i got these results

enter image description here

how can i remove these empty values ​​please

+3


source to share


2 answers


You don't need a class for this, just a function:

import csv
import json

def CsvToJson(csvFilePath):
    with open(csvFilePath, 'rU', newline='') as csvFile:
        final = {}
        reader = csv.reader(csvFile, delimiter='\t') # change delimiter if needed
        next(reader)  # skip header
        for row in reader:  # now removes "empty" row values
            final[row[0]] = [value for value in row[1:] if value]

    return json.dumps(final, indent=4)

k = CsvToJson('carsModelsMakes.csv')
print(k)


Output:

{
    "Acura": [
        "CSX/EL",
        "MDX",
        "NSX",
        "RDX",
        "RSX/Integra",
        "Other"
    ],
    "Aston Martin": [
        "DB7/DB9",
        "DBS",
        "One-77",
        "Rapide",
        "Vanquish",
        "Vantage",
        "Virage",
        "Zagato",
        "Other"
    ],
    "Audi": [
        "A1",
        "A3",
        "A4",
        "A5",
        "A6",
        "A7",
        "A8",
        "Q3",
        "Q5",
        "Q7",
        "R8",
        "S3/RS3",
        "S4/RS4",
        "S5/RS5",
        "S6/RS6",
        "S7/RS7",
        "S8"
    ],
    "Alfa Romeo": [
        "145/146/147",
        "156/159",
        "166",
        "4C",
        "Brera",
        "GIULIETTA",
        "GTV/GT",
        "Mito",
        "Spider",
        "Other"
    ]
}
+4


source


Try pandas 

>>> import pandas as pd
>>> myFile = 'test.txt'
>>> df = pd.read_csv(myFile, sep=",", header=0)
>>> df
    Model  A  B  C
0  Model1  a  b  c
1  Model2  a  b  c
2  Model3  a  b  c

Retrieving the Model Column 



>>> df['Model']
0    Model1
1    Model2
2    Model3
Name: Model, dtype: object

Reformatting to JSON

>>> df['Model'].to_json()
'{"0":"Model1","1":"Model2","2":"Model3"}'
+1


source






More articles:


All Articles