Home > database >  Having trouble processing two csv files that has different id's in each file with json python
Having trouble processing two csv files that has different id's in each file with json python

Time:05-10

I have two csv files that I would like to open and get the unique id's from within both csv files. Each id has specific dates that I would like to get and subtract those dates. The problem is I need to get one date from one csv and the other date from the other csv. Both csv files has some data I don't need, so in my code I created new dictionaries and extracted those dates from the two csv files and dumped them into a new JSON file. I would like to note that in one csv file the unique ids are clumped towards the middle of the csv files and the other unique id's are clumped towards the end, so when running the code I noticed that the code keeps restarting back to the top of the csv and doesn't continue checking the unique id's within the csv files.

The code works at function make_json(), which gets the data I want and subtracts the two dates within the CSV, then dumps them into a JSON file. The other function called getMarginDict() is coming from the other CSV file, which has dates in this one as well. The only difference is the dates I need to subtract are in the other CSV file. I need to get dates from csv1 and subtract the dates from csv2, but as mentioned above the code keeps processing back at the top. I believe its because the file is opening back again. Is there a way for the code to continue processing the unique id's to where it left off. Lastly, the code works when checking the first unique id, but then restarts.

This is what the final JSON file looks like: As you can see the last three fields called New Date aren't being calculated correctly, but the first two are.

{"Name": "1", "Unique ID": "1357", "Date Finish": "06/19/2024", "Date Completed": "06/19/2024", "Date Left": 775, "Date Completed-Today's Date": 775, "New Date": 43}
{"Name": "2", "Unique ID": "1357", "Date Finish": "06/19/2024", "Date Completed": "06/19/2024", "Date Left": 775, "Date Completed-Today's Date": 775, "New Date": 43}
{"Name": "3", "Unique ID": "7531", "Date Finish": "12/25/2024", "Date Completed": "12/25/2024", "Date Left": 964, "Date Completed-Today's Date": 964, "New Date": -146}
{"Name": "4", "Unique ID": "7531", "Date Finish": "12/25/2024", "Date Completed": "12/25/2024", "Date Left": 964, "Date Completed-Today's Date": 964, "New Date": -146}
{"Name": "5", "Unique ID": "7531", "Date Finish": "12/25/2024", "Date Completed": "12/25/2024", "Date Left": 964, "Date Completed-Today's Date": 964, "New Date": -146}


data = {}
newData = []
marginData = {}

def incrementSV(data,x):
    data["Name"] = str(x)
    return x

    
def createNewDict(data, IDNum, endDate, latefinish, diffBaseline, diffLateFinish):
    return data, IDNum, endDate, latefinish, diffBaseline, diffLateFinish

def createNewDict(data, IDNum, endDate, latefinish, diffBaseline, diffLateFinish):
    data["Unique ID"] = IDNum
    data["Date Finish"] = endDate
    data["Date Completed"] = latefinish
    data["Days Left"] = diffBaseline.days
    data["Date Completed-Today's Date"] = diffLateFinish.days

def getMarginDict(endDateNew):
        with open(csvFilePathIMS, encoding='utf-8-sig') as csvf:
            csvReaderGR = csv.DictReader(csvf)
            for newsRows in csvReaderGR:
          
                uuid = newsRows["Unique ID"]
                laterDate= newsRows["Finish"]
                
                laterDateNew= datetime.datetime.strptime(laterDate, '%m/%d/%Y')
                dateNew= laterDateNewNew.date() - endDateNew9.date()

                if int(uuid) == 1234:
                    marginData["New Date"] = dateNew.days
                    return marginData
                
                elif int(uuid) == 4321:
                    marginData["New Date"] = dateNew.days
                    return marginData


def make_json(csvFilePath,jsonFilePath):
    es = Elasticsearch("localhost",) 
    # Open a csv reader called DictReader
    with open(csvFilePath, encoding='utf-8-sig') as csvf:
        csvReader = csv.DictReader(csvf)
        # Convert each row into a dictionary
        # and add it to data
        for rows in csvReader:
            IDNum = rows['Unique ID']
            endDate = rows['End Date']
            latefinish = rows['Date Completed']   
            
            endDateNew= datetime.datetime.strptime(endDate, '%m/%d/%Y')
            diffBaseline = endDateNew.date() - datetime.date.today()

            newLatefinishDate = datetime.datetime.strptime(latefinish, '%m/%d/%Y')
            diffLateFinish = newLatefinishDate.date() - datetime.date.today()
            
            imsData = partial(createNewDict, data, IDNum, endDate, latefinish, diffBaseline, diffLateFinish)
            
            if int(IDNum) == 1357:
                for x in range(1,3):
                    incrementSV(data,x)
                    imsData()
                    getMarginDict(endDateNew)
                    totalIMS = dict(data, **marginData)
                    newData.append(dict(totalIMS))
                    
            elif int(IDNum) == 7531:
                for x in range(3,6):
                    incrementSV(data,x)
                    imsData()
                    getMarginDict(endDateNew)
                    totalIMS = dict(data, **marginData)
                    newData.append(dict(totalIMS))
           

    with open(jsonFilePath, 'w', encoding='utf-8') as jsonf:
        for item in newData:
            x = json.dump(item,jsonf)
            jsonf.write('\n')

csvFilePath = "location of 1st csv file"
jsonFilePath = "location of JSON file"

csvFilePathIMS = 'location of 2nd csv file'

# Call the make_json function
make_json(csvFilePath,jsonFilePath)

CodePudding user response:

Every time you call getMarginDict you are using with open(csvFilePathIMS, encoding='utf-8-sig') as csvf:, to open and read the csv file. Since this is a context manager the file is automatically closed when you exit the scope of the "with open..." statement.

It sounds like what you intended to do is open the file once and read it line-by-line. To do this you can open the file using csvReaderGR = csv.DictReader(open(csvFilePathIMS, encoding='utf-8-sig')) and use this instance repeatedly.

  • Related