1

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

Improve this question
4
  • What is the code that you have written to try to solve this? Commented Jul 5, 2022 at 13:44
  • @user42 i added more details about my code Commented Jul 5, 2022 at 14:26
  • I do not understand if you want to transform A to B, or just skip A and get B directly. StackOverflow aims to help people with errors or unexpected behaviors in their code. What specific problems are you having in trying to get your intended result? Please edit your question to show us the problems that we can help you solve. Commented Jul 5, 2022 at 16:08
  • @ZachYoung thanks for your comment , i would rather transform A to B , i'll need A later . Commented Jul 6, 2022 at 8:59

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your answer , your code works , but the output file contains only the 10 last elements ! , i have above 400 elements in my input file ? you know why ? i supose that zip() function takes only 10 elements .
Actually, it takes only the last element of my input file, for the output file i get 10 elements because the last item contains 10 ips 10 hostnames and 10 macs. so i think i should put the whole code inside a loop and append the result to the same csv file every iteration of the loop. @Zach Young
Did you run the program on my sample input CSV and get the correct output? Make sure that works before you try anything else. And... I didn't show the code top-to-bottom, but make sure the blocks hostnames = row[1].split(",")..., and swa = row[0]... are indented under for row in reader:.
Indeed, the second loop was outside the first one , Thanks @Zach Young
I’m glad you got it sorted and that it worked for you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.