Python Pandas looping through Dataframe not working properly

Question

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

Tom Ron · Accepted Answer · 2020-06-09 06:01:59Z

1

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.

answered Jun 9, 2020 at 6:01

Tom Ron

6,2413 gold badges28 silver badges43 bronze badges

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

2 Comments

asp Over a year ago

after removing those col1 col2 assignment, its working same way. hmm.. all i trying to loop through all hostnames and add one more column as Yes (if json has value) or No (if json has no value), sorry i am bit new bie in programming, df.apply seems to complicated thing...all trying to to read one of the column of csv and loop through it

asp Over a year ago

Update: used for v in df.iterrows(): it working partically, now I dont have issue of else section executing , but still output repeating for all rows 5 times each...

asp · Accepted Answer · 2020-06-09 15:42:10Z

0

Fixed the issued with

for index, row in df.iterrows():

answered Jun 9, 2020 at 15:42

asp

8754 gold badges22 silver badges48 bronze badges

Collectives™ on Stack Overflow

Python Pandas looping through Dataframe not working properly

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related