How to keep one column of information while feeding another column

Question

I have an input file with two columns:

county name	id
"York, SC"	GHCND:USW00053871

I use the id column to create a list. Then I have a loop for the id to make an online weather request.

input_file = "id_in_sample.csv"  
df = pd.read_csv(input_file)

list = df['id'].tolist()

Then I save the weather information along with the id as a csv file.

id	weather
GHCND:USW00053871	5

I am trying to collect the county name in my file as well. So that it looks like the following table.

county name	id	weather
"York, SC"	GHCND:USW00053871	5

Is there a way to do it within the loop when I make a request or should I try to merge/join once I have the csv file?

not really getting your question. you can access country by `df.loc[df["id"] == id, "country"] ` inside the loop — srinath, Aug 03 '22 at 17:28
can you add the expected output and rephrase your question a bit — srinath, Aug 03 '22 at 17:29
@srinath Thank you for the comment. I edited the question with my expected output! — Rebecca, Aug 03 '22 at 21:32
What is preventing you from having the 2 data sets in the form of dataframes and then merging them along the 'id' column? — Rip_027, Aug 03 '22 at 21:49
As @Rip_027 suggested, keep the id and county name in one df and id and weather in another df. And merge based on id. — srinath, Aug 04 '22 at 01:58

score 0 · Answer 1 · answered Aug 04 '22 at 02:06

Read the input file (id_in_sample.csv) using pandas

df = pd.DataFrame({
    "county name": ["York, SC"],
    "id": ["GHCND:USW00053871"]
})

As per your statement - Then I save the weather information along with the id as a csv file. the weather information is saved in another csv file. load the csv file using pandas

df2 = pd.DataFrame({
    "id": ["GHCND:USW00053871"],
    "weather": [5]
})

now merge both the dataframes based on id

df.merge(df2, on=["id"], how="inner")

Sample result

  county name                 id  weather
0    York, SC  GHCND:USW00053871        5

if needed you can save the merged result back to csv file.

Note: Instead of reading the second dataframe (weather info) from file, you can compute the weather values , merge and then write to a file. but it completely depends on the usecase.

https://stackoverflow.com/questions/53645882/pandas-merging-101 - Before raising the question, do enough search. the link has a complete explanation of joins. — srinath, Aug 04 '22 at 02:34

How to keep one column of information while feeding another column

1 Answers1