create csv from different files using python

Question

I have 2 csv files that I am generating via python. The records are below (a.csv and b.csv). b.csv has 2 rows and the values in second row can be duplicates. I want to have a result like final.csv. How can I do that?

I tried below code but that's not right. I am not doing the right comparison. Any help would be great.

a.csv

"all","1","1Gi","4","8Gi"
"als","0","0","100m","128Mi"
"awx","6","9Gi","20","32Gi"
"cho-1","9","9728Mi","15","20Gi"
"cho-2","12250m","15395Mi","20","24Gi"

b.csv

"all","ABC"
"als","ABC"
"awx","DPL"
"cho-1","ABC"
"cho-2","ABC"
"cho-3","ABC"

I want to create one file out of both like below

final.csv

"all","1","1Gi","4","8Gi","ABC"
"als","0","0","100m","128Mi","ABC"
"awx","6","9Gi","20","32Gi","DPL"
"cho-1","9","9728Mi","15","20Gi","ABC"
"cho-2","12250m","15395Mi","20","24Gi","ABC"

my code:

csv1 = csv.reader(open("reports/a.csv", "r"))
csv2 = csv.reader(open("reports/b.csv", "r"))
s=[]

while True:
    try:
        line1 = csv1.next()
        line2 = csv2.next()

        if (line1[0] == line2[0]):
            s.append([line1[1], line2[0], line2[1], line2[2], line2[3], line2[4]])
        else:
            s.append(["NA", line2[0], line2[1], line2[2], line2[3], line2[4]])
    except StopIteration:
        break

Heenashree Khandelwal · Accepted Answer · 2017-11-08 08:31:28Z

1

I took help of pandas in this scenario.

df0 = pd.read_csv("a.csv")
df1 = pd.read_csv("b.csv")
df1=df1.dropna(axis=1)
df1 = df1.merge(df0, on='Name', how='outer')
df1.to_csv("final.csv", index=True)

edited Nov 8, 2017 at 8:31

answered Nov 8, 2017 at 8:05

Heenashree Khandelwal

6992 gold badges13 silver badges30 bronze badges

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

Comments

Sumedh Junghare · Accepted Answer · 2017-11-07 09:49:54Z

0

from your expected output, I think that you should use set. Since the line1 and line2 variables contains comma separated values, you can make a list from these values. Like,

line1 = ["all","1","1Gi","4","8Gi"]
line2 = ["all","ABC"]

You can then merge these two lists to form a single list and make a set from it. So the set would look like,

set1 = set(line1.extend(line2))

making a set will remove the duplicates. Hope this helps.

answered Nov 7, 2017 at 9:49

Sumedh Junghare

3912 gold badges4 silver badges21 bronze badges

Comments

Serge Ballesta · Accepted Answer · 2017-11-08 08:54:18Z

0

You were not far from a solution, you just have to append data from line2 to line1 and use it:

...
csvout = csv.writer(open("final.csv", "wb"), quoting = csv.QUOTE_ALL)
while True:
    try:
        line1 = csv1.next()
        line2 = csv2.next()
        if line1[0] != line2[0]:    # control same first field
            raise Exception("Desynch", line1[0], '#', line2[0])
        line1.append(line2[1])      # append field from b.csv
        csvout.writerow(line1)      # and write it to final.csv
    except StopIteration:
        break

answered Nov 8, 2017 at 8:54

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Collectives™ on Stack Overflow

create csv from different files using python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related