Merging repeating data cells in csv

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 .

– Emre
Jul 28 '16 at 22:57

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['Total'] = df.groupby(['Name', 'Age'])['Salary'].transform('sum')
df.drop_duplicates(take_last=True)

• I'd say this is the most useful way because it requires little programming effort and it's practical. Aug 16 '15 at 21:37

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")

## create an empty dictionary
mydictionary = {}

rownum = 0

## 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)

• thank you Tasos , i'm still a newbie for python . if you can please support me with the code . Aug 8 '15 at 13:27
• @Miller Check my edited answer. Aug 8 '15 at 14:24

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.