0

I work in Freight shipping, and I recently built a scraper that scraped market rates of shipments based on 3 features: origin city, destination city, and time period.

Currently, I have these results stored in a csv/xlsx file that has this data outlined as follows:

Sample Data

My current project involves comparing what we actually paid for shipments versus the going market rate. From my scraped data, I need a way to rapidly access the:

AVERAGE MARKET RATE

based on: MONTH, ORIGIN CITY, and DESTINATION CITY.

Since I know what we paid for shipping on a particular day, if I can access the average market rate from that month, I can perform a simple subtraction to tell us how much we over or underpaid.

I am relatively proficient with using Pandas dataframes, and my first instincts were to try to combine a dataframe with a dictionary to call values based on those features, but I am unsure of how I can do that exactly.

Do you have any ideas?

Amar Srivastava
  • 121
  • 5
  • Please don't post images of code/data/Tracebacks. Just copy the text, paste it in your question and format it as code. Make it easy for us to copy and paste into our editors for testing. [You should not post code as an image because:](https://meta.stackoverflow.com/a/285557/2823755) – wwii Aug 10 '19 at 23:39
  • Welcome to SO. This isn't a discussion forum or tutorial. Please take the time to read [ask] and the other links found on that page. – wwii Aug 10 '19 at 23:41

1 Answers1

0

Using pandas, you could add your data as a new column in your csv. Then you could just subtract the two indexes, eg df['mean'] - df['paid']

You could do that in Excel too.

As a side note, you'll probably want to update your csv so that each row has the appropriate city - maybe it's harder to read, but it'll definitely be easier to work with in your code.

anderw
  • 103
  • 3