Writing values to excel in python using pandas

Question

I'm new to python and would like to pass the ZipCode in excel file to 'uszipcode' package and write the state for that particular zipcode to 'OriginalZipcode' column in the excel sheet. The reason for doing this is, I want to compare the existing states with the original states. I don't understand if the for loop is wrong in the code or something else is. Currently, I cannot write the states to OriginalZipcode column in excel. The code I've written is:

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import uszipcode as US
from uszipcode import ZipcodeSearchEngine
search = ZipcodeSearchEngine()
df = pd.read_excel("H:\excel\checking for zip and states\checkZipStates.xlsx", sheet_name='Sheet1')
#print(df.values)
for i, row in df.iterrows():
    zipcode = search.by_zipcode(row['ZipCode']) #for searching zipcode
    b = zipcode.State
    df.at['row','OriginalState'] = b
    df.to_excel("H:\\excel\\checking for zip and states\\new.xlsx", sheet_name = "compare", index = False)

The excel sheet is in this format:

| ZipCode   |CurrentState     | OriginalState |
|-----------|-----------------|---------------|
| 59714     | Montana         |               |
| 29620     | South Carolina  |               |
| 54405     | Wisconsin       |               |
|    .      | .               |               |
|    .      | .               |               |

you're saving your excel file with each iteration of the loop. you probably want to save outside of your loop. also, for all intents and purposes, your zip code question has nothing to do with your problem. so I would simplify to just include, e.g., looking up a key in a dictionary or something like that. — Paul H
– Paul H, Commented May 7, 2018 at 13:52
@PaulH don't I have to write the respective states to the excel file each iteration since I'm reading the zipcodes in a loop? — schikkamksu
– schikkamksu, Commented May 7, 2018 at 13:57
I'm able to print the states in the output console by reading zipcode from the excel. That means for-loop is working fine. But, I'm not sure if df.at['row', 'OriginalState'] = b is the right way to write into a cell in the excel. Any help would be much appreciated. Thanks! — schikkamksu
– schikkamksu, Commented May 7, 2018 at 14:18
print(df) outside of your loop will tell you if the values have been properly assigned — Paul H
– Paul H, Commented May 7, 2018 at 14:30
Simply do not quote row in df.at: df.at[row,'OriginalState'] = b — Parfait
– Parfait, Commented May 7, 2018 at 15:01

Leonardo Brugues · Accepted Answer · 2018-05-07 15:00:32Z

1

You can add the OriginalState column without iterating the df:

Define a function that returns the value you want for any given zip code:

def get_original_state(state):
    zipcode = search.by_zipcode(state) #for searching zipcode
    return zipcode.State

Then:

df['OriginalState'] = df.apply( lambda row: get_original_state(row['ZipCode']), axis=1)

Finally, export only once the df to excel.

This should do the trick.

answered May 7, 2018 at 15:00

Leonardo Brugues

745 bronze badges

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

1 Comment

schikkamksu Over a year ago

That works, thank you! Now, I'll have to dig more regarding the usage of df.apply()

Collectives™ on Stack Overflow

Writing values to excel in python using pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related