How do I pull specific data from one file and add it to another file in a specific spot?

Question

I am learning how to use python.

For the project I am working on, I have hundreds of datasheets containing a City, Species, and Time (speciesname.csv).
I also have a single datasheet that has all cities in the world with their latitude and longitude point (cities.csv).

My goal is to have 2 more columns for latitude and longitude (from cities.csv) in every (speciesname.csv) datasheet, corresponding to the location of each species. I am guessing my workflow will look something like this: Go into speciesname.csv file and find the location on each line Go into cities.csv and search for the location from speciesname.csv Copy the corresponding latitude and longitude into new columns in speciesname.csv.

I have been unsuccessful in my search for a blog post or someone else with a similar question. I don't know where to start so anyone with a starting point would be very helpful.

Thank you.

Answer 1

You can achieve it in many ways. The simplest way I can think of to approach this problem is:

1. collect all cities.csv data inside a dictionary {"cityname":(lat,lon), ...}
2. read line by line your speciesname.csv and for each line search by key (key == speciesname_cityname) in the dictionary.
3. when you find a correspondence add all data from the line and the lat & lon separated by comma to a buffer string that has to end with a "\n" char
4. when the foreach line is ended your buffer string will contains all the data and can be used as input to the write to file function

Answer 2

Here is a little program that should work if you put it in the same folder as your separate CSVs. I'm assuming you just have 2 sheets, one that is cities and another with the species. Your description saying the cities info is in hundreds of datasheets is confusing since then you say it's all in one csv.

This program turns the two separate CSV files into pandas dataframe format which can then be joined on the common city column. Then it creates a new CSV from the joined data frame.

In order for this program to work, you need to need to install pandas which is a library specifically for dealing with things in tabular (spreadsheet) format. I don't know what system you are on so you'll have to find your own instructions from here: https://pandas.pydata.org/pandas-docs/stable/getting_started/install.html

This is the version if your csv do not have a header, which is when the first row is just some data.

# necessary for the functions like pd.read_csv
import pandas as pd 

species_column_names = ['city','species','time']
speciesname = pd.read_csv('speciesname.csv', names=species_column_names, header=None)

cities_column_names = ['city','lat','long']
cities = pd.read_csv('cities.csv', names=cities_column_names, header=None)
# this joining function relies on both tables having a 'city' column 
combined = speciesname.join(cities.set_index('city'), on='city')

combined_csv = combined.to_csv()

If you already have headers for both files, use these two lines instead to ignore the first row since I don't know how they are spelled/capitalized/whatever and we are joining based on all lower case custom column names:

import pandas as pd 

species_column_names = ['city','species','time']
speciesname = pd.read_csv('speciesname.csv', names=species_column_names, skiprows = 0, header=None)

cities_column_names = ['city','lat','long']
cities = pd.read_csv('cities.csv', names=cities_column_names, skiprows = 0, header=None)
# this joining function relies on both tables having a 'city' column 
combined = speciesname.join(cities.set_index('city'), on='city')

combined_csv = combined.to_csv()