How To Merge Several Csv Columns Into One Column Using Python 2.7?

I'm working with large set of csv data and I want to put several columns in different places into one column separated by semi-colon(;). So what I have now is.. a b c d 1 2

Solution 1:

Simple answer: don't bother deleting the row, but make a NEW row for insertion that only picks what you want.

It will look like this:

# leave row alone, don't bother deleting columns in it.
new_row = ["a=%s;b=%s;c=%s;d=%s"% (row[12], row[13], row[14])] 
# new_row has only one column, with a string constructed of what you need. 

writer.writerow(new_row)

And voila, that should do it for you. You can also copy any other columns you need to new_row, and append() whatever else you might desire.

Solution 2:

just read the file with a reader and write the data you read into another file. this example is assuming you want to still keep empty columns a, b and c

r = csv.readedr('filename.csv', 'r')
withopen('outfile.csv') as outfile:
    w = csv.writer(outfile, 'w')
    forrowin r:
        # joins the currentrowinto string seperated by spaces
        outdata = row.join(' ')
        # leaves first three columns empty, writes old content onto fourth column
        data_to_write = ('', '', '', outdata)
        w.writerow(data_to_write)

Solution 3:

Try pandas

import pandas as pd
df = pd.read_csv('1-0002.csv')
df['d_merged'] = df.apply(lambda row: 'a={0};b={1};c={2};d={3};'.format(row['a'],row['b'],row['c'],row['d']), axis=1)

This gives:

>>> df

   a  b  c  d          d_merged
01234  a=1;b=2;c=3;d=4;
11234  a=1;b=2;c=3;d=4;
21234  a=1;b=2;c=3;d=4;

Now delete the columns you don't want:

df = df.drop(['a','b','c','d'], axis=1)

>>> df

           d_merged
0  a=1;b=2;c=3;d=4;
1  a=1;b=2;c=3;d=4;
2  a=1;b=2;c=3;d=4;

Now rename d_merged if you wish:

df = df.rename(columns={'d_merged':'d'})

>>> df

                  d
0  a=1;b=2;c=3;d=4;
1  a=1;b=2;c=3;d=4;
2  a=1;b=2;c=3;d=4;

(Alternatively, combine the above two steps as:

df['d'] = df.apply(lambda row: 'a={0};b={1};c={2};d={3};'.format(row['a'],row['b'],row['c'],row['d']), axis=1)

)

Then write to CSV:

df.to_csv('csvout.csv', index=False)