Python Forum
Reading and manipulating csv
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading and manipulating csv
#1
hi,


I have a csv which has two columns and on the other hand i have multiple lists in my code.

I am looping through second column of each data in csv and checking if it exists in the already defined lists.

if it exists create a new csv and add them. But what has happened till now it has added data but it is separating them into different columns and i want it data of each category into one column but comma separated.

below is my code:
import csv
from itertools import groupby,chain
from operator import itemgetter

Facility1 = ["club_house", "gymnasium", "children’s_play_area", "24/7_water_supply", "power_back-up", "inter-com","lift"]
Facility2 = ["jogging_track", "swimming_pool", "aerobics_center", "badminton_court", "basketball_court", "beach_volleyball_court", "football_court", "golf_course", "lawn_tennis_court","yoga/meditation_center"]
Facility3 = ["crèche", "ATM", "car_wash_area", "community_hall", "banquet_hall", "garbage_disposal", "grocery_shop","library"]
Facility4 = ["fountains","landscape_gardens"]
Facility5 = ["gated_society"]
Facility6 = ["car_parking"]
Facility7 = ["CCTV_cameras"]
Facility8 = ["security_guards"]
Facility9 = ["pool_table", "carrom_room", "chess_room", "dart_board", "squash_court", "table_tennis_room", "skating_rink"]


def main():
    prj_structure = {}
    f = open("./facility_data.csv","r",encoding="utf-8")
    data = f.read()
    f.close()
    lst = data.split("\n")
    prj = ""
    for i in range(1, len(lst)):
        val = lst[i].split(",")
        if len(val)>0:
            prj = val[0]
        if prj!="":
            if prj not in prj_structure.keys():
                prj_structure[prj] = []
            prj_structure[prj].append(val[1])
    return prj_structure

final_data = []

def readingfiles():
    global final_data
    data = main()
    for k,v in data.items():
        for i in v:
            sublist = []
            sublist.append(k)
            if i in Facility1:
                sublist.append(i+"-facility1")
            if i in Facility2:
                sublist.append(i+"-facility2")
            if i in Facility3:
                sublist.append(i+"-facility3")
            if i in Facility4:
                sublist.append(i+"-facility4")
            if i in Facility5:
                sublist.append(i+"-facility5")
            if i in Facility6:
                sublist.append(i+"-facility6")
            if i in Facility7:
                sublist.append(i+"-facility7")
            if i in Facility8:
                sublist.append(i+"-facility8")
            if i in Facility9:
                sublist.append(i+"-facility9")
            final_data.append(sublist)
    return final_data


def writefile(filename, alldata):
    global final_data
    with open(filename,"w", encoding="utf-8")as csvfile:
        csvfile = csv.writer(csvfile, delimiter=",")
        csvfile.writerow("")
        for i in range(0, len(alldata)):
            csvfile.writerow(alldata[i])

def get_new():
    readingfiles()
    writefile("new_facilities.csv",[[k, *chain.from_iterable(r for _, *r in g)] for k, g in groupby(final_data, key=itemgetter(0))])
get_new()
attached is the sample csv that i am using to create a new csv which is by name facility_data.csv

and attached is the output csv how i want data to be which is by name "new_facilities.csv"

Please help! Wall Wall Wall Wall

Attached Files

.csv   facility_data.csv (Size: 22.72 KB / Downloads: 315)
.csv   new_facilities.csv (Size: 601 bytes / Downloads: 293)
Reply
#2
The Facility_data.csv file has non standard encoding what is the codec?
Reply
#3
Codec is UTF-8 only
Reply
#4
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 in position 58: invalid start byte
Reply
#5
i made a change in the code , please use that
Reply
#6
I already tried that, the error I show is from modified code.
I've seen this before, are you on MS windows?
I believe the character in question is a '
Reply
#7
0x92 problem will 'go away' if instead of utf-8 to use either ISO-8859-1 or windows-1252 encoding.
I'm not 'in'-sane. Indeed, I am so far 'out' of sane that you appear a tiny blip on the distant coast of sanity. Bucky Katt, Get Fuzzy

Da Bishop: There's a dead bishop on the landing. I don't know who keeps bringing them in here. ....but society is to blame.
Reply
#8
perfringo's codec is correct.
this is much easier done by using a dictionary:
example:
import csv
from itertools import groupby,chain
from operator import itemgetter
import os

facilities = {
    'Facility1': ["club_house", "gymnasium", "children’s_play_area", "24/7_water_supply", "power_back-up", "inter-com","lift"],
    'Facility2': ["jogging_track", "swimming_pool", "aerobics_center", "badminton_court", "basketball_court", "beach_volleyball_court", "football_court", "golf_course", "lawn_tennis_court","yoga/meditation_center"],
    'Facility3': ["crèche", "ATM", "car_wash_area", "community_hall", "banquet_hall", "garbage_disposal", "grocery_shop","library"],
    'Facility4': ["fountains","landscape_gardens"],
    'Facility5': ["gated_society"],
    'Facility6': ["car_parking"],
    'Facility7': ["CCTV_cameras"],
    'Facility8': ["security_guards"],
    'Facility9': ["pool_table", "carrom_room", "chess_room", "dart_board", "squash_court", "table_tennis_room", "skating_rink"]
}

def main():
    # Make sure in src directory to start
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    with open("facility_data.csv", encoding="ISO-8859-1") as fp:
        crdr = csv.reader(fp, delimiter=',')
        for n, row in enumerate(crdr):
            for facid, values in facilities.items():
                if row[1] in values:
                    print(f'found: from row: {n}, {row[1]} ({row[0]}) in {facid}')

if __name__ == '__main__':
    main()
partial output:
Output:
found: from row: 1, swimming_pool (R3) in Facility2 found: from row: 2, gymnasium (R3) in Facility1 found: from row: 6, swimming_pool (R5) in Facility2 found: from row: 7, gymnasium (R5) in Facility1 found: from row: 11, swimming_pool (R6) in Facility2 found: from row: 12, gymnasium (R6) in Facility1 found: from row: 16, swimming_pool (R9) in Facility2 found: from row: 17, gymnasium (R9) in Facility1 found: from row: 20, swimming_pool (R10) in Facility2 found: from row: 21, gymnasium (R10) in Facility1 found: from row: 22, swimming_pool (R703) in Facility2 found: from row: 23, gymnasium (R703) in Facility1 found: from row: 26, swimming_pool (R17) in Facility2 found: from row: 27, gymnasium (R17) in Facility1 found: from row: 30, swimming_pool (R18) in Facility2
Also (which I haven't done), write youor output incrementally as you read input
Reply
#9
Yes i am using windows
Reply
#10
Please respond to post 8. Do you understand the concept here?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Manipulating data from a CSV EvanS1 5 2,675 Jun-12-2020, 05:59 PM
Last Post: perfringo
  manipulating two lists rancans 8 3,112 Apr-16-2020, 06:00 PM
Last Post: deanhystad
  Manipulating index value, what is wrong with this code? Emun 1 1,726 Feb-05-2020, 07:18 AM
Last Post: perfringo
  Manipulating the filename of an output script mckinneycm 4 11,826 Jan-15-2020, 07:29 PM
Last Post: mckinneycm
  Manipulating Excel with Python. Spacely 2 3,586 Jun-25-2019, 01:57 AM
Last Post: Dequanharrison
  Manipulating CSV Prince_Bhatia 1 1,921 Apr-25-2019, 11:55 AM
Last Post: Gribouillis
  Manipulating an Excel Workbook Stanimal 4 2,949 Jan-18-2019, 11:03 PM
Last Post: Stanimal
  Manipulating Binary Data arsenal88 10 8,495 Apr-25-2017, 02:30 PM
Last Post: snippsat
  Manipulating files Python 2.7 hugobaur 6 8,347 Nov-01-2016, 12:28 PM
Last Post: hugobaur

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020