CODEWITHSUNDEEP
pythonlistcsv
Wiz Ninja
Wiz Ninja

Reputation: 19

How do I convert items in a .csv to a list in python

I have a .csv file with a row and a column variable. The row is B1-K1 and the column is A2-A7. The actual data is from B2 to K7. I want to be able to put the values into a list and call them, if there is a way to number the data(B2-K7) in a pattern from 0,1,2,,3,4,5,0,1.... that would be good.

import csv
file = open(r'C:/cpps/python project/prodata.csv')
csvreader = csv.reader(file)
header = next(csvreader)
print(header)
rows = []
for row in csvreader:
    rows.append(row)
print(rows)
file.close()

Upvotes: 0

Views: 4731

Answers (2)

Grismar
Grismar

Reputation: 31329

There's many ways to skin a cat, but you don't need Excel-specific libraries if you're just reading a .csv (instead of an .xlsx):


# this code just (over)writes a .csv for demonstration purposes
from csv import writer
with open('block.csv', 'w', newline='') as f:
    data = [[(r+1)*100 + c+1 for c in range(15)] for r in range(9)]
    cw = writer(f)
    cw.writerows(data)


# this is how you can do it with pandas
import pandas as pd
df = pd.read_csv('block.csv', header=None)
# prints the entire dataframe
print(df)
# this selects the data you want (A2:A7), but it's still a dataframe
print(df.loc[1:6, 0:0])
# this selects the data, and the first columns from it, and then turns the values into a list
print(df.loc[1:6, 0:0][0].values.tolist())
# similarly, this selects B1:K1, turns the values into a list of lists and then selects the first
print(df.loc[0:0, 1:10].values.tolist()[0])


# you can also do it with the standard csv module
from csv import reader
# opening the file for reading
with open('block.csv', 'r') as f:
    cr = reader(f)
    # the reader will read the rows as a list of strings, we want numbers
    data = [[int(x) for x in row] for row in list(cr)]
    # prints the entire list of lists
    print(data)
    # this selects the first element from rows 1 through 6, so A2:A7
    print([x[0] for x in data[1:7]])
    # and from the first row, columns 2 through 11, so B1:K1 
    print(data[0][1:11])


# you don't need the csv module, although I would not recommend this, unless you must and you're
# certain your .csv doesn't have any problems
with open('block.csv', 'r') as f:
    # just reading and splitting the lines and converting the values to integer
    data = [[int(x) for x in line.strip().split(',')] for line in f]
    # the rest of the code is the same as for the csv.reader
    print(data)
    print([x[0] for x in data[1:7]])
    print(data[0][1:11])

As a bonus, here's a function that allows you to just use a range like in Excel, to avoid counting errors (like the one I made when posting the answer at first):

import re

def col_to_index(col):
    return sum((ord(c) - 64) * 26**i for i, c in enumerate(reversed(col))) - 1


def df_excel_range(df, excel_range):
    import re
    coords = [(col_to_index(cell[0]), int(cell[1])-1) for cell in [
        re.match('([A-Z]+)(\d+)', cell).groups() for cell in excel_range.upper().split(':')]]
    if len(coords) == 1:
        coords = [*coords[0], *coords[0]]
    elif len(coords) != 2:
        raise SyntaxError(f'not a valid range {excel_range}')
    return df.loc[coords[0][1]:coords[1][1], coords[0][0]:coords[1][0]]

You can use that to select a range from a dataframe like this:

print(df_excel_range(df, 'A2:A7')[0].values.tolist())

Upvotes: 1

Mohsen_Fatemi
Mohsen_Fatemi

Reputation: 3391

You can do it using pandas.

import pandas as pd
df = pd.read_csv('yourfilename.csv')
l = df.values.tolist() # this list will contain the whole csv file in a list.

Upvotes: 0

Related Questions