Merging repeating data cells in csv

Question

I have a CSV file with around 1 Million rows. Let say its have details like

Name      |   Age   | Salary 
name 1      52       10000
name 2      55       10043 
name 3      50       100054
name 2      55       10023
name 1      52       100322...

and soon .

but i need to merge the redundant details . and need a output like

Name      |   Age   | Salary 
name 1      52       110322*
name 2      55       20066 *
name 3      50       100054 

you might notice that the repeating Name 1 and Name 2 details are merged and the Salary values are added .So i'm looking for a way to apply this change to my original data set. so i need a python script to fix my problem .

Answer 1

Pandas is a python library that you will find very useful for these types of tasks.

Here is a stack overflow post that tells you how to do what you want to accomplish.

It boils down to three very pythonic lines with a groupby and transformation followed by a drop_duplicates:

import pandas
df = pandas.read_csv('csvfile.csv', header = 0)
df['Total'] = df.groupby(['Name', 'Age'])['Salary'].transform('sum')
df.drop_duplicates(take_last=True)

Answer 2

My solution may not be the best, but I would opt for this one since it is the simplest.

• Create an empty dictionary
• Iterate on the CSV. If the name is already in the dictionary, sum up the salaries. If not, then create a new key with the salary.
• After that, iterate again on the dictionary to write a new CSV with the new values.

If you are not familiar with Python code, just ask it and I will write it for you :)

Edit with a code sample:

import csv

## open CSV file and rea it
myfile  = open('test.csv', "rb")
reader = csv.reader(myfile)

## create an empty dictionary
mydictionary = {}

rownum = 0

for row in reader:
    ## check if it is the header
    if rownum == 0:
        pass
    else:
        ## split the line of CSV in elements..Use the name for the key in dictionary and the other two in a list
        line = row.split(",")
        key = line[0]
        age = line[1]
        salary = line[2]

        if key in mydictionary:
            mydictionary[key][1] += salary
        else:
            mydictionary[key] = [age,salary]

    rownum += 1

ifile.close()

## create a new list of lists with the data from the dictionary
newcsvfile = ["name","age","salary"]

for i in mydictionary:
    newcsvfile.append(i,mydictionary[i][0],mydictionary[i][1])

## write the new list of lists in a new CSV file
with open("output.csv", "wb") as f:
    writer = csv.writer(f)
    writer.writerows(newcsvfile)

Answer 3

This isn't a python program so strictly it isn't a solution to your problem, technically it isn't even a program - just a command line, but it does illustrate that are many ways to skin a cat, some of them quite elegantly.

Dictionaries are a lovely feature of the modern language python but much earlier languages such as AWK, or it's linux equivalent GAWK, have had them for just short of 40 years, though in those days they were called associative arrays.

So here is a one line command using cat and gawk, with its ability to accept code as an argument, to accomplish your task, demonstrating that the design principle of linux to be a framework for collaborating utilities is still alive and well.

 $> cat in.dat | gawk '{if(NR==1)hdr=$0; else{emp[$1" "$2" "$3]=emp[$1" "$2" "$3]+$4}} END{print hdr; for(i in emp) print i,emp[i];}'  > out.dat

I've taken your file formats literally, if you do actually require true CSVs then a small mod will accomplish that.

cat in.csv | awk -F"," '{if(NR==1)hdr=$0; else{emp[$1","$2]=emp[$1","$2]+$3}} END{OFS=",";print hdr; for(i in emp) print i,emp[i];}' > out.csv

Name,Age,Salary

name 2,55,20066

name 1,52,110322

name 3,50,100054

Designed as a text manipulation language by some very clever computer scientists, including Brian Kernighan of C fame, AWK achieves this compactness by automatically parsing any input it is given into fields ( \$1, \$2, ... ) and keeping track of the number of lines processed. This allows a lot of the I/O management you might need to do in another language to be skipped over as it already exists.