Python df.to_csv only prints last line even if for loop is present

Question

I am writing a code where the final results(denoted by x) to be exported to csv file. I used a for loop to iterate over but stil it only exports the last line of the result. My full code below is:

import csv
import itertools
import requests
import json
import pandas as pd


domainfile=open('domainsinfo.csv',newline='',encoding='utf_8')
reader=csv.reader(domainfile)
w=[]
  for row in reader:
    w.extend(row)

domain = list(itertools.permutations(w,1))
print(domain)

 def url_report(domain):
    url = 'https://www.virustotal.com/vtapi/v2/url/report'
    params = {'apikey': '', 'resource':domain}
    response = requests.get(url, params=params)
    return response

def pp_json(json_thing, sort=True, indents=4):
        if type(json_thing) is str:
            print(json.dumps(json.loads(json_thing), sort_keys=sort, 
    indent=indents))
      else:
              print(json.dumps(json_thing, sort_keys=sort, 
 indent=indents))
      return None


for d in domain:
    response = url_report(d)
    json_response = response.json()
    pretty_json = pp_json(json_response)

    response_list=[]
    for key in json_response['scans']:   

        if json_response['scans'][key] ['detected'] is True:      
            response_list.append(True)
        else:
            response_list.append(False)
    x=any(response_list)
    print(x)


    for d in domain:        
      final_list=[]

    final_list.append(x)
    result=(final_list)
    result_table = {'Domain': [d], 'Result':result}

    df=pd.DataFrame(data=result_table) 
    print(df)

    export_csv = df.to_csv (r'C:\csv', index=None, header=True)

print(pretty_json)


input()

Can someone explain why this does not work even if the for loop is present

I suggest, to get the indentation right; too many assumptions are required right now. — guidot
– guidot, Commented Sep 2, 2019 at 7:52

Ankur Sinha · Accepted Answer · 2019-09-02 09:14:48Z

2

Like liakoyras mentioned, you are writing your dataframe in the CSV. There are many ways to overcome your problem. While he suggested a merge, I am giving you another alternative here using append. Read the documentation for more information.

Here is how you need to modify your code block, explanation in comments inside the code:

######## Declare a dataframe outside the loop
final = pd.DataFrame()

for d in domain:
    ........................
       your other codes
    ........................
    result_table = {'Domain': [d], 'Result':result}

    df=pd.DataFrame(data=result_table) 

    ###### keep appending df to final 
    ###### the final df now gets updated in every loop
    final = final.append(df)

#### now outside the loop, write final dataframe to your csv
final.to_csv('your/path/file.csv')

answered Sep 2, 2019 at 9:14

Ankur Sinha

6,6648 gold badges44 silver badges74 bronze badges

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

3 Comments

Jacky Over a year ago

Thanks a lot Ankur Sinha & liakoyras

Jacky Over a year ago

This code does not seem to save data into the csv file. Could any of you tell as to the reason

Ankur Sinha Over a year ago

What do you see in the CSV file? This should be working, I use this extensively. May be check if you are getting your data into df first, and if you have final declared outside the loop.

liakoyras · Accepted Answer · 2019-09-02 07:47:08Z

2

This happens because in each loop, your .csv is overwritten by your next one.
The df.to_csv exports the df in a csv file.
You could try taking this code out of the loop, or changing the file name dynamically (depending on what you want.

edited Sep 2, 2019 at 7:47

answered Sep 2, 2019 at 7:45

liakoyras

1,20514 silver badges28 bronze badges

9 Comments

liakoyras Over a year ago

But in your code, you create a new df for each d in domain, so if you want all of them to be on the same csv, you need a slightly different approach.

Jacky Over a year ago

I want all of the domaiins in one csv. So how is the approach then?

liakoyras Over a year ago

Initialize df at the beginning of your code (df = pd.DataFrame) and when you calculate the result table, use df1=pd.DataFrame(data=result_table) and then use df.merge(df1) in order to concat the two dataframes.

Jacky Over a year ago

@ liakoyras if please could you explain this in detail please

liakoyras Over a year ago

In each loop, you create a new DataFrame in the line df=pd.DataFrame(data=result_table). So, the things you have found so far (stored in df variable) are overwritten each time. I suggested you change this by creating the df = pd.DataFrame() outside the for loop, and use df.merge in order to append your new data to df. Then, after the for loop ends, use df.to_csv in order to export.

|

Collectives™ on Stack Overflow

Python df.to_csv only prints last line even if for loop is present

2 Answers 2

3 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related