1

I have a directory with around 100,000 images sorted by the crew that took the photo and the time it was uploaded. I also have a master spreadsheet with the dates the images were taken, the camera's Media ID and the crew that took the photo.

The problem is the media ID resets every 10,000 photos and every crew has their own camera. I cannot use the match photo to time tool as some of the time stamps are too close (multiple photographs per location and I need to reference the unique photos)

EDIT: To add to the Match Photo to time problem, the camera's timestamps are all off by different amounts as well, and thousands of photos irrelevant to this project are also within these folders (Poor Planning)

The only unique combination of fields I have is Crew + MediaID + approximate time.

I cannot come up with any way to match crew, ID and a fuzzy time in two different tables (one with filepaths and both with dates, crew, approx time(but not matching), and mediaID) OR with one table and the directory.

The End result is returning an XY for each filepath to be used within arcmap. The below pictures are snips of some sample data. DigitalmediaID should be a RIMG + Four digit number. I fixed this within arcmap but the excel sheet I havent messed with. I want to spatially reference and catalog the photographs.

Table of XY to Match to Filepaths

Table of FilePaths

Improve this question
8
  • 3
    What is the relation to GIS with this question? What software are you working with?
    – Erik
    Commented Jul 23, 2021 at 14:30
  • I am using ArcMap 10.6.1 with standard license, but solving this in excel would work for me as well. I will also add some sample data as well.
    – Stuart Miller
    Commented Jul 23, 2021 at 15:25
  • 1
    There are no coordinates in the EXIF. XY was collected with a separate handheld GPS. The XY are matched to the original names in the spreadsheet but not matched with where they actually are in the directory.
    – Stuart Miller
    Commented Jul 23, 2021 at 15:55
  • 1
    I've added some bits of the data to two drive links @BERA
    – Stuart Miller
    Commented Jul 23, 2021 at 16:11
  • 1
    I ran a text to column in excel before uploading it. You can use DigitalMediaName in Table one and it matches the number in "aID" on table two. I ran it that way to solve the leading zeros issue.
    – Stuart Miller
    Commented Jul 23, 2021 at 16:25

1 Answer 1

Reset to default
1

I dont understand your data fully, but I would try something like this using python pandas library. Join based on the two columns crewid and medianame/id, sort by time difference, drop duplicates and keep the one with the shortest difference.

import pandas as pd

df1 = pd.read_csv(r'/home/bera/Downloads/table 1.csv - table 1.csv.csv')
df2 = pd.read_csv(r'/home/bera/Downloads/table 2.csv - table 2.csv.csv')
df3 = pd.merge(left=df1, right=df2, left_on=['CREWID','DigitalMediaName'], right_on=['CREWID','aID']) #Merge (join) the data based on crewid and mediaid

#Convert the two datetime columns from object/string to datetime
df3['DateTime'] = pd.to_datetime(df3['DateTime'])
df3['Time Uploaded'] = pd.to_datetime(df3['Time Uploaded'])


df3['timediff'] = df3['DateTime']-df3['Time Uploaded'] #Calculate a time difference column
df3['timediff'] = df3['timediff'].apply(lambda x: abs(x.seconds)) #Convert from timedelta to seconds

df3 = df3.sort_values(by=['CREWID','DigitalMediaName','timediff'], ascending=True) #Sort
df3 = df3.drop_duplicates(subset=['CREWID','DigitalMediaName'], keep='first') #Drop duplicates, keep the one with the shortest timediff

df3.to_csv(r'/home/bera/Downloads/Results.csv')

enter image description here

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.