How to convert strings in for loop to Pandas DataFrame in Python?

Question

I'm collecting some data from a website to create a map. I have some strings (latitude and longitude) inside a for loop as follows. Once I get this figured out, I'll be adding other information to the DataFrame.

28.5455
66.91390228
59.01139831542969 44.451599
38.55390167
35.2804985046
34.2961998
39.1754

I'm trying to convert this to a Pandas DataFrame, but when I do that, all the indexes are 0 instead of 0,1,2...

for row in csvread:
icao = row[0]
name = row[1]
latitude = row[2]
longitude = row[3]
metar_link = 'https://www.aviationweather.gov/metar/data?ids=' + icao + '&format=decoded&date=&hours=0'
page = requests.get(metar_link)
soup = bs(page.content, 'html.parser')

if soup.select_one('td:contains("Text:") + td').text == 'No data found' or not 'SM' in (
        soup.select_one('td:contains("Text:") + td').text):
    #print(icao, 'no metars available for this airport!')
    continue

metar = soup.select_one('td:contains("Text:") + td').text
metar_data = func.fraction(metar)
visibility = float(func.convert_to_float(metar_data))
ceiling = func.convert_ceiling(metar)
flight_conditions = func.flight_conditions(ceiling, visibility)
color = func.colorize(flight_conditions)

data = pd.DataFrame({
    'lat': [latitude],
    'lon': [longitude]
})

print(data)

When I print data, I'm getting:

       lat         lon
0  28.5455  -81.332901
           lat          lon
0  66.91390228  -151.529007
           lat           lon
0  65.99279785  -153.7039948
           lat           lon
0  59.75439835  -154.9109955
                 lat                  lon
0  59.01139831542969  -161.82000732421875
         lat         lon
0  44.451599  -83.394096
           lat           lon
0  38.55390167  -121.2979965
             lat             lon
0  35.2804985046  -116.629997253
          lat           lon
0  34.2961998  -116.1620026
       lat         lon
0  39.1754  -76.668297

The problem is all the index are 0, they're supposed to increment by 1. How can I fix this?

I looked at some examples for Pandas DataFrame, but all the ones I could find, they have the values manually entered in. I'm trying to pass the values from the for loop into a DataFrame. I hope I'm making sense. — Matt Damoz
– Matt Damoz, Commented Oct 17, 2019 at 14:55
python-graph-gallery.com/312-add-markers-on-folium-map This is what I'm trying to achieve, but I need to fix the DatFrame first. — Matt Damoz
– Matt Damoz, Commented Oct 17, 2019 at 14:57
Please fix indentation as code is not compilable in Python as it stands. I would edit but do not want to make assumptions on syntax. — Parfait
– Parfait, Commented Oct 17, 2019 at 14:59
@ansev, sorry I don't think I fully understand. I'm new to Python and Pandas. This is what I have now data = pd.DataFrame({ 'lat': [latitude], 'lon': [longitude] }) #data = data.reindex(list(range(0, len(data), 1))) data.index = range(len(data)) print(data) — Matt Damoz
– Matt Damoz, Commented Oct 17, 2019 at 15:20
@MattDamoz ... before anything, please edit your post for incorrect identation. — Parfait
– Parfait, Commented Oct 17, 2019 at 15:22

Parfait · Accepted Answer · 2019-10-17 15:18:05Z

Refrain from building data frame inside a loop. Instead, collect all needed values in list or dictionary and then bind to a data frame once outside the loop. Right now, you appear to be printing one-row data frames inside the loop, so would only show 0-index.

Specifically, build a dictionary of all needed values inside loop and then bind in DataFrame constructor once outside loop. Below assumes all values you scrape from BeautifulSoup and process with func are scalars (not iterables of multiple values):

data_list = []

for row in csvread:
   icao, name, latitude, longitude = row[0:3]

   metar_link = ('https://www.aviationweather.gov/metar/data?ids=' + icao + 
                 '&format=decoded&date=&hours=0')

   page = requests.get(metar_link)
   soup = bs(page.content, 'html.parser')

   if soup.select_one('td:contains("Text:") + td').text == 'No data found' \
       or not 'SM' in (soup.select_one('td:contains("Text:") + td').text):
       #print(icao, 'no metars available for this airport!')
       continue

   # ASSUMED ALL VALUES BELOW ARE SCALARS
   metar = soup.select_one('td:contains("Text:") + td').text
   metar_data = func.fraction(metar)
   visibility = float(func.convert_to_float(metar_data))
   ceiling = func.convert_ceiling(metar)
   flight_conditions = func.flight_conditions(ceiling, visibility)
   color = func.colorize(flight_conditions)

   # APPEND DICTIONARY TO LIST (INSIDE LOOP)
   data_list.append({'icao': icao, 'name': name, 'latitude': latitude, 
                     'longitude': longitude, 'metar': metar, 
                     'metar_data': metar_data, 'visibility': visibility,
                     'flight_conditions': flight_conditions, 'color': color})

# BIND LIST OF DICTS (OUTSIDE LOOP)
final_df = pd.DataFrame(data_list)

thank you, that worked for me. I'm getting 0,1,2..... Now I just need to figure out the folium.CircleMarker() to add those to the map.

moys · Accepted Answer · 2019-10-17 15:04:14Z

0

You can use

data=data.reindex(list(range(0,len(df),1)))

answered Oct 17, 2019 at 15:04

moys

8,1173 gold badges19 silver badges51 bronze badges

1 Comment

Matt Damoz Over a year ago

data = pd.DataFrame({ 'lat': [latitude], 'lon': [longitude] }) data = data.reindex(list(range(0, len(data), 1))) print(data) I tried that and I'm getting the same results as above, all the indexes are 0

Collectives™ on Stack Overflow

How to convert strings in for loop to Pandas DataFrame in Python?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related