Automatically extracting data from csv file into specific matrix position

user_10046017 0
Viewed 47

I have a rather large csv file that I need the program to read, then input the data into the correct position of a zero matrix. Sample of csv block (also attached file):

 Sector,Service,Data_Point
Bio,Electricity NonEmitting,0
NEElectricity,Electricity NonEmitting,0.5
RE,Electricity NonEmitting,0
Electricity,Electricity NonEmitting,-1
Bio,Electricity Bio,0.8
NEElectricity,Electricity Bio,0
RE,Electricity Bio,0.04
Electricity,Electricity Bio,-2
Bio,Electricity BECCS,0.84
NEElectricity,Electricity BECCS,0
RE,Electricity BECCS,0.4
Electricity,Electricity BECCS,-1
Bio,Ammonia HB,0
Electricity,Ammonia HB,2.8
RE,Ammonia HB,0.06
Ammonia,Ammonia HB,-1
Bio,Biofuel TBD,0.30
Electricity,Biofuel TBD,0.02
RE,Biofuel TBD,0.012
Electricity,CarUse BEV,0.5
RE,CarUse BEV,0
CarUse,CarUse BEV,-1
Hydrogen,CarUse HFCEV,0.2
RE,CarUse HFCEV,0
CarUse,CarUse HFCEV,-1
Bio,NET DAC,0
NEElectricity,NET DAC,10.5
RE,NET DAC,-1

The problem is that I need it to be able to sort the data based on the Sector and Service columns. I.e. Sector = rows, Service = columns in the matrix. So if the program reads Sector as Bio: row = 1, and Service as Electricity NonEmitting: column 1, it inputs the corresponding number from Data_Point (in this case Data_Point is '0') into row 1 column 1 of the matrix. Or if it reads Sector as NEElectricity: row = 2, but service as Electricity NonEmitting again: column 1, the corresponding Data_Point '0.5' is inputted into row 2 column 1 of the matrix.

Below I have written code that automatically generates a zero matrix based on the number of unique elements in the Sector and Service columns. I just cannot figure out how to sort the values into the correct matrix position, so any help would be greatly appreciated.

import csv
import numpy as np
import pandas as pd

sector = pd.read_csv('Coeff_Sample.csv', usecols=["Sector"])
matrix_column = int(sector.nunique())

service = pd.read_csv('Coeff_Sample.csv', usecols=["Service"])
matrix_row = int(service.nunique())

coeff_matrix = np.zeros((matrix_row, matrix_column))

Best regards

pandas csv python-3.x numpy python
1 Answers

Matrix

Is that the kind of matrix u wanted to create?

I created this matrix without pandas with the following source code:

import csv
import numpy as np

rows = []
columns = []
all_rows = []

with open('test.csv', 'r') as read_obj:
    csv_dict_reader = csv.DictReader(read_obj)
    for row in csv_dict_reader:
        columns.append(row['Sector'])
        rows.append(row['Service'])
        all_rows.append(row)

rows_set = set(rows)
columns_set = set(columns)
coeff_matrix = np.full((len(rows_set)+1, len(columns_set)+1), 0).tolist()

row_list = list(rows_set)
columns_list = list(columns_set)

for idx, x in enumerate(columns_list):
    coeff_matrix[0][idx+1] = x

for idy, y in enumerate(row_list):
    coeff_matrix[idy+1][0] = y

for e in all_rows:
    sector = e['Sector']
    service = e['Service']
    value = e['Data_Point']
    for row_idx, row in enumerate(coeff_matrix):
        if row[0] == service:
            row_index = row_idx
    for column_idx, column in enumerate(coeff_matrix[0]):
        if column == sector:
            column_index = column_idx

    coeff_matrix[row_index][column_index] = value

np_coeff_matrix = np.asarray(coeff_matrix)

But it got a lot of loops inside. Maybe there are ways to be faster with that task using pandas or list/np.array functions.

user_115028440
Related