How to Manipulate Data in csv file columns using Pandas?

Question

My data contains three columns, with one of them being a city location. I want to add a new column to my .csv file that adds a zip code corresponding to the city in the city column. I don't know how to implement a conditional expression that achieves this. Currently, I am using Pandas to read in my csv file, and I understand how to add a column to my csv file, but I can't find any information on how to manipulate the data in the way described above.

Edit:

My data looks something like this (about a million rows down). I have another csv file with two columns, one containing a state and a corresponding zip code. I want to match the state in the below csv file to the zip code in another csv file. My dataset also does not contain that many states so I was thinking I could take advantage of a simple conditional expression or case statement that sets a new column equal to a certain zip code if a certain state is in that row.

Do you have the zip codes of the cities in the dataframe or a dictionary? — It_is_Chris
– It_is_Chris, Commented Nov 3, 2018 at 21:19
Could you provide some example data in your question? Also, do you currently have these zip codes, or will you have to get them (e.g., using a geocoding dataset or API)? — Peter Leimbigler
– Peter Leimbigler, Commented Nov 3, 2018 at 21:27

grge · Accepted Answer · 2018-11-03 21:56:13Z

2

I'm assuming that the other csv file has columns "STATE" and "ZIPCODE", and it contains exactly one row per state, and has state abbreviations in the same format as the large csv file.

You want to do a "merge" operation on the state column. Specifying the "left join" style merge will ensure that every row of your original dataset is preserved, even if you don't have the corresponding zipcode.

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

https://pandas.pydata.org/pandas-docs/stable/merging.html

This should do what you need:

big_df = pandas.read_csv("big_file.csv")
zipcodes_df = pandas.read_csv("zipcodes.csv")
merged_df = big_df.merge(zipcode_df, on='STATE', how='left')
merged_df.to_csv("merged_big_file.csv", index=False)

answered Nov 3, 2018 at 21:56

grge

14510 bronze badges

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

Collectives™ on Stack Overflow

How to Manipulate Data in csv file columns using Pandas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related