CODEWITHSUNDEEP
pythonpandasloopsdataframe
asp
asp

Reputation: 835

Python Pandas looping through Dataframe not working properly

Trying to write a pandas/python program to do a api call and extract status of hosts and iterate through one of the column of data frame and add status column to the output

What I am trying:

import requests
import json
import pandas as pd

df = pd.read_table('c:\csv\input1.csv', engine='python', sep="\s*,\s*", skipinitialspace=True, usecols=[0, 1])
for v in df['Private Ip']:

    headers = {
        'X-Requested-By': 'cli',
        'Accept': 'application/json',
        'Content-Type': 'application/json',
    }
    params = (
        ('query', v),
        ('range', '86400'),
        ('limit', '100'),
        ('sort', 'timestamp:desc'),
        ('pretty', 'true'),
    )
    response = requests.get('http://xxx.xx.xxx.xxx:9000/api/search/universal/relative', headers=headers, params=params,
                            auth=('xxxxxxx', 'xxxxxxxx'))

    text = response.text    

    pretty_json = json.loads(text)
    col1 = df['Account Name'] = df['Account Name'].astype(str)
    col2 = df['Private Ip'] = df['Private Ip'].astype(str)

    if pretty_json.get('messages'):
        print(col1 + ',' + col2 + ',' + 'Yes')

    else:
      print(col1 + ',' + col2 + ',' + 'No')

Inputs:

My CSV:

Account Name,Hosts 
ABCD,XX.XXX.XX.XX           
ABCDE,XX.XX.XXX.XX
ABCDEF,XX.XX.XXX.XXX
ABCDEFG,XX.XXX.XX.XX

The problem: The loop is going 4 times (actually It should run for just 4 times as i am trying to loop row by row, that means It need to run 4 times

output i am getting: ( Its looping for 4 times, i guess pandas is considering entire df set as one iteration not an indidual row of data frame)

0 ABCD,XX.XXX.XX.XX,Yes         
1 ABCDE,XX.XX.XXX.XX,Yes
2 ABCDEF,XX.XX.XXX.XXX,Yes
3 ABCDEFG,XX.XXX.XX.XX,Yes
dtype: object

0 ABCD,XX.XXX.XX.XX,Yes         
1 ABCDE,XX.XX.XXX.XX,Yes
2 ABCDEF,XX.XX.XXX.XXX,Yes
3 ABCDEFG,XX.XXX.XX.XX,Yes
dtype: object

0 ABCD,XX.XXX.XX.XX,Yes         
1 ABCDE,XX.XX.XXX.XX,Yes
2 ABCDEF,XX.XX.XXX.XXX,Yes
3 ABCDEFG,XX.XXX.XX.XX,Yes
dtype: object

0 ABCD,XX.XXX.XX.XX,Yes         
1 ABCDE,XX.XX.XXX.XX,Yes
2 ABCDEF,XX.XX.XXX.XXX,Yes
3 ABCDEFG,XX.XXX.XX.XX,Yes
dtype: object

(the below is wrong as all hostgot has messages in json returned, not sure why its below else logic is getting executed)
    0 ABCD,XX.XXX.XX.XX,No          
    1 ABCDE,XX.XX.XXX.XX,No
    2 ABCDEF,XX.XX.XXX.XXX,No
    3 ABCDEFG,XX.XXX.XX.XX,No
    dtype: object

can some one suggest. I guess i m missing looping indenting somewhere, not sure where..Please suggest

Upvotes: 0

Views: 506

Answers (2)

asp
asp

Reputation: 835

Fixed the issued with

for index, row in df.iterrows():

Upvotes: 0

Tom Ron
Tom Ron

Reputation: 6181

I believe the problem are those lines - 

col1 = df['Account Name'] = df['Account Name'].astype(str)
col2 = df['Private Ip'] = df['Private Ip'].astype(str)

It does multiple assignment from right to left and refers to the entire datagram. Consider the following code - 

 a = 3
 b = 4
 c = a = b

After this code is executed, a,b,c are all equal to 4.

I suggest using pandas apply method to iterate over the rows.

Upvotes: 1

Related Questions