Python Forum
Add column headers to dataframe
Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Add column headers to dataframe
#1
Hey guys-I'm working on a script that grabs weather information from different locations. The only thing I'm missing within the dataframe is replacing "0_x" and "0_y" (below) with a reference to the location.

Ref 0_x 0_y
0 temp 83 71
1 humid 19% 45%
2 pressure 29 29
3 dew 37 49
4 wind_dir 157 227
5 wind_mph 6.5 1.2
6 wind_gust 9.8 3.7

Within the csv file that holds the location info, there is a 3rd column with specific location names. All I need to add is a line in the script that references the 3rd column and inserts it as a column header. Hopefully this all makes sense. Any help would be greatly appreciated.

import requests
import csv
import pandas as pd

stadiums = pd.read_csv('ParkLocations1.csv')
stadiums.to_dict('series')

all_loc_data = pd.DataFrame()
col_1 = ['temp','humid','pressure','dew','wind_dir','wind_mph','wind_gust']
all_loc_data.insert(loc=0, column='Ref', value=col_1)

url_template = "http://api.wunderground.com/api/88fb15b49e765a43/conditions/q/{state}/{city}.json"

for state,city in zip(stadiums.loc[ : ,"State"],stadiums.loc[ : ,"City"]):
    url = requests.get(url_template.format(state=state,city=city))
    parsed = url.json()

    temp = parsed['current_observation']['temp_f']
    humid = parsed['current_observation']['relative_humidity']
    pressure = parsed['current_observation']['pressure_in']
    dew = parsed['current_observation']['dewpoint_f']
    wind_dir = parsed['current_observation']['wind_degrees']
    wind_mph = parsed['current_observation']['wind_mph']
    wind_gust = parsed['current_observation']['wind_gust_mph']

    data_list = [temp,humid,pressure,dew,wind_dir,wind_mph,wind_gust]
    loc_data = pd.DataFrame(data_list)
    loc_data.insert(loc=0, column='Ref', value=col_1)
    
    all_loc_data = all_loc_data.merge(loc_data, on='Ref',how='outer')
    

all_loc_data.to_csv('WUEx.csv')
Reply
#2
Without examples of state/city pairs (as int your file) not much I can do, but you really have to learn to work with DataFrame.apply. This is extremely inefficient use of pandas
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#3
(May-15-2018, 09:59 AM)volcano63 Wrote: Without examples of state/city pairs (as int your file) not much I can do, but you really have to learn to work with DataFrame.apply. This is extremely inefficient use of pandas

Here is a sample of what the parklocations1.csv looks like:

Team State City
SFG CA San Francisco
NYY NY Bronx
Reply
#4
OK, here is a simple example how to use apply in your code

url_template = "http://api.wunderground.com/api/88fb15b49e765a43/conditions/q/{state}/{city}.json"
def get_weather(row, *fileds):
    url = requests.get(url_template.format(state=row['State'],city=row['City']))
    observation = url.json()['current_observation']
    
    return [observation[field] for field in fields]

fields = ['temp_f', 'relative_humidity']
stadiums = pd.DataFrame([['SFG', 'CA', 'San Francisco'], ['NYY', 'NY', 'Bronx']], 
                        columns=['Team', 'State', 'City'])
print(stadiums.apply(lambda r: get_weather(r, *fields), axis=1).values)
And the result is
Output:
[list([54.7, '82%']) list([63.0, '83%'])]
now,
stadium[fields] = pd.DataFrame(<result>)
will update your stadiums DataFrame with new fields - but you have to convert the result to a form accepted by pandas.DataFrame constructor. I will let you experiment with that on your own.

PS pandas is an amazing tool which I just started learning myself - but you have to understand, DataFrame and Series are objects intended for processing as a whole - otherwise, you abuse pandas. Learn it properly
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#5
(May-16-2018, 06:01 AM)volcano63 Wrote: PS pandas is an amazing tool which I just started learning myself - but you have to understand, DataFrame and Series are objects intended for processing as a whole - otherwise, you abuse pandas. Learn it properly

Thank you for the response and advice. I knew I was taking a round-a-bout approach but wasn't really sure how to improve on it. Your input made things a lot clearer for me.
Reply
#6
PS I just took a second look - I don't know why I did not suggest
url_template = http://api.wunderground.com/api/88fb15b49e765a43/conditions/q/{State}/{City}.json"
....
    url = requests.get(url_template.format(**row))
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  concat 3 columns of dataframe to one column flash77 2 776 Oct-03-2023, 09:29 PM
Last Post: flash77
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  attempt to split values from within a dataframe column mbrown009 8 2,218 Apr-10-2023, 02:06 AM
Last Post: mbrown009
  New Dataframe Column Based on Several Conditions nb1214 1 1,782 Nov-16-2021, 10:52 PM
Last Post: jefsummers
  Putting column name to dataframe, can't work. jonah88888 1 1,803 Sep-28-2021, 07:45 PM
Last Post: deanhystad
  Setting the x-axis to a specific column in a dataframe devansing 0 1,993 May-23-2021, 12:11 AM
Last Post: devansing
Question [Solved] How to refer to dataframe column name based on a list lorensa74 1 2,238 May-17-2021, 07:02 AM
Last Post: lorensa74
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 2,915 Jan-11-2021, 09:53 PM
Last Post: Azureaus
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 4,242 Jul-15-2020, 06:20 PM
Last Post: pawanmtm
  Pandas DataFrame and unmatched column sritsv19 0 2,989 Jul-07-2020, 12:52 PM
Last Post: sritsv19

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020