manipulating csv file python

i want to rearange a csv file :

from

actual format

SWA           hostname                    IP               MAC
SWA0    app1,app2,app3,app4,…etc ip1,ip2,ip3,ip4,,,,etc mac1,mac2,mac3,mac4,…etc
SWA1    app12,app13,app14,..etc ip12,ip13,ip14..etc mac12,mac13,mac14,…etc
SWA2    app18,app19,app20,..etc ip18,ip19,ip20..etc mac18,mac19,mac20,…etc

to

format desired

hostname IP     MAC     SWA
app1    ip1     mac1    SW0
app2    ip2     mac2    SW0
app3    ip3     mac3    SW0
app4    ip4     mac4    SW0
app11   ip11    mac11   SW1
app12   ip12    mac12   SW1
app13   ip13    mac13   SW1
app14   ip14    mac14   SW1
app18   ip18    mac18   SW2
app19   ip19    mac19   SW2
app20   ip20    mac20   SW2

i do generate this csv file by searching in a another csv file using:

def search(csv_file,word):
return [i for i in open(csv_file,'r') if i.find(word) != -1]

and

if s:
resu = search('result/result.csv',search_word)
str(resu)
#print(resu)
lenght = len(resu)
filenam = 'result/result_' + str(datetime.datetime.now()) + '.csv'
with open(filenam,'w') as export:
    writer = csv.writer(export,delimiter=";")
    writer.writerow(headerList)
    for line in resu:
        line = line.replace('[','').replace(']','').replace("'",'')
        export.write('{}'.format(line))

i want the result diffrently as showen above.

Thanks.

got a partiel answer :

import pandas as pd
df = pd.read_csv('/path/to/sample.csv')
df_reorder = df[['A', 'B', 'C', 'D', 'E']] # rearrange column here
df_reorder.to_csv('/path/to/sample_reorder.csv', index=False)

and now the result is :

hostname             ip          mac        SWA
app1,app2,app3  ip1,ip2,ip3 mac1,mac2,mac3  SW0
app4,app5,app6  ip4,ip5,ip6 mac4,mac5,mac6  SW1
app7,app8,app9  ip7,ip8,ip9 mac7,mac8,mac9  SW2

i need to dispatch them on every line

Answer

I don’t know Pandas, but I do know Python’s CSV module:

import csv

First, you’ll create output_rows, where you’ll store your new “sub rows” as you make them:

output_rows = []

You’ll read the input CSV and capture the header:

f_in = open("input.csv", newline="")
reader = csv.reader(f_in)
header = next(reader)

Then it’s on to iterating the input rows:

for row in reader:
    ...

Each row will look something like this:

["swa0", "app1,app2,app3", "ip1,ip2,ip3", "mac1,mac2,mac3"]

and you need to be able to expand the columns values, then group the expanded values: all the first items from each column, then all the second items from each column, and so on.

You want to get that row to look something like this:

[
  ["app1","ip1","mac1"],
  ["app2","ip2","mac2"],
  ["app3","ip3","mac3"]
]

The way to do that is to split each column, then use Python’s zip() function to interleave the items:

hostnames = row[1].split(",")
ips = row[2].split(",")
macs = row[3].split(",")

sub_rows = zip(hostnames, ips, macs)

sub_rows now looks like that desired list of lists of strings, from just above.

Now for each sub row, you add the SWA in to create a new output row:

swa = row[0]
for sub_row in sub_rows:
    output_row = [swa] + list(sub_row)  # sub_row is technically a tuple, so make it a list to append it
    output_rows.append(output_row)

Finally, you write the output rows to a new CSV:

f_out = open("output.csv", "w", newline="")
writer = csv.writer(f_out)
writer.writerow(header)
writer.writerows(output_rows)

When I put that all together and run it against this input.csv:

SWA,hostname,IP,MAC
SWA0,"app1,app2,app3,app4","ip1,ip2,ip3,ip4","mac1,mac2,mac3,app4"
SWA1,"app12,app13,app14","ip12,ip13,ip14","mac12,mac13,mac14"
SWA2,"app18,app19,app20","ip18,ip19,ip20","mac18,mac19,mac20"

I get this output.csv:

SWA,hostname,IP,MAC
SWA0,app1,ip1,mac1
SWA0,app2,ip2,mac2
SWA0,app3,ip3,mac3
SWA0,app4,ip4,app4
SWA1,app12,ip12,mac12
SWA1,app13,ip13,mac13
SWA1,app14,ip14,mac14
SWA2,app18,ip18,mac18
SWA2,app19,ip19,mac19
SWA2,app20,ip20,mac20

If you want to do this in Pandas, take a look at some of the Pandas solutions for this similar problem: How to split a row into two rows in python based on delimiter in Python.