2

I have a table with some info that are getting by a GPS, in my table I have this structure:

        Date    Time    Latitude    Longitude   Accuracy    Weather Visibility  ViewPoint   Species Bearing Distance    Count
29/12/2018  10:20:45    39.85528    -5.36466    1.500000001 Soleado Buena   Otero 4 Anser anser 210 600 70
29/12/2018  10:23:32    39.85528    -5.36466    1.500000001 Soleado Buena   Otero 4 Anser anser 260 900 20
29/12/2018  10:24:17    39.85528    -5.36466    1.500000001 Soleado Buena   Otero 4 Larus fuscus    230 500 2
29/12/2018  10:05:16    39.85528    -5.36466    1.299999952 Soleado Buena   Otero 4 Fulica atra 100 450 60
29/12/2018  10:09:47    39.85528    -5.36466    1.299999952 Soleado Buena   Otero 4 Anser anser 160 560 110
29/12/2018  10:12:41    39.85528    -5.36466    1.299999952 Soleado Buena   Otero 4 Anas penelope   160 560 200
29/12/2018  10:17:33    39.85528    -5.36466    1.299999952 Soleado Buena   Otero 4 Anser anser 194 560 45
29/12/2018  10:20:01    39.85528    -5.36466    1.299999952 Soleado Buena   Otero 4 Anser anser 200 560 60
29/12/2018  08:50:07    39.86689    -5.35504    2.000000001 Soleado Buena   Otero 5 Anser anser 0   0   
29/12/2018  08:55:49    39.866885   -5.355045   1.500000001 Soleado Buena   Otero 5 Anas penelope   200 550 60

I need to get the mean coordinates (to improve the GPS error) by the ViewPoint and Date. So, for the Date 1, I am going to have a mean coordinate for the ViewPoint1 (Otero1), and other mean coordinates for every ViewPoint. Later on, in the next Date, I am going to have the same structure, but the ViewPoint can be slightly displaced from one date to another, that's why I need group also by Date and not on ly by the ViewPoint.

I can apply the groupby and I get almost what I need:

df.groupby(['Date', 'ViewPoint'], as_index=True)['Latitude', 'Longitude'].mean()

Which throws this output:

                       Latitude Longitude
Date        ViewPoint
2016-12-29  Otero 0 39.859567   -5.399055
            Otero 1 39.871807   -5.385762
            Otero 3 39.853757   -5.381488
            Otero 4 39.855280   -5.364660
            Otero 5 39.866887   -5.354937
            Otero 9 39.830826   -5.356414
            OteroX  39.854657   -5.391452
2017-01-10  Otero 0 39.858376   -5.395767
            Otero 1 39.871402   -5.385639
            Otero 2 39.874892   -5.379403
            Otero 4 39.855324   -5.364828
            Otero 5 39.866883   -5.355035
            Otero 9 39.830876   -5.356457

So, what I am missing is how to replace the mean values in every row of the original table, with its mean values in the same combination "Date" and "ViewPoint"

1

I have no data to test on but this should work:

df = pd.merge(left=df, right=aa, how='left', left_on=['Date','Viewpoint'], right_on=['Date','Viewpoint'])

(After you create aa as you do in your answer:

dfmean = mean_coords.swaplevel()
aa = dfmean.reset_index()

)

Then drop the old lat long columns and rename the new ones:

df.drop(['Latitude', 'Longitude'], axis=1, inplace=True)
df.rename(columns = {'Latitude_y':'Latitude','Longitude_y':'Longitude'}, inplace=True)

(Or drop them before merging and you dont have to rename the new ones)

  • Thanks, that it works! The only thing if that I have now two "latitude" fields and two "longitude" fileds, but just need to remove the older ones and keep the meanings. It seems more elegant than iterate over all the DataFrame :) I am curious about what it would be faster, in my case the DataFrame only have around 1000 rows, so is pretty inmediate with both methods. Thanks again! – Digd Aug 12 '18 at 12:10
  • Nice! Yes just drop the old lat long and rename the new. I dont know but merge is very fast – BERA Aug 12 '18 at 12:34
0

I want to post mmy solution in case there were someone with my same problem some day.

I am sure that there must be a direct way to solve with a pandas order, but lacking that solution I managed to get mine:

After the groupby to get the mean coordinate fro ViewPoint and Date I did that to unsawp the index:

dfmean = mean_coords.swaplevel()
aa = dfmean.reset_index()

And I get this structure:

ViewPoint   Date        Latitude    Longitude
0   Otero 0 2016-12-29  39.859567   -5.399055
1   Otero 1 2016-12-29  39.871807   -5.385762
2   Otero 3 2016-12-29  39.853757   -5.381488
3   Otero 4 2016-12-29  39.855280   -5.364660
4   Otero 5 2016-12-29  39.866887   -5.354937
5   Otero 9 2016-12-29  39.830826   -5.356414

Then I run the DataFrame with iterrow and build a dict (I love dicts!) with the mean Lat and Lon for ViewPoint and Date:

coords = {}

for index, row in dfmean.iterrows():

    coords[index] =  {'Lat': row[0], 'Lon': row[1]}

Now, I can run the main table with iterrow again and build and index inside the loop based in the ViewPoint and the Date, these index are the keys if the dict that we build before, so we only have to reasign the Lat and Lon with the Lat and Lon values (the mean that we want) that we sotre in our dict:

for index, row in df.iterrows():
    ix = (index[1], index[0])
    df.at[index,'Latitude'] = coords[ix]['Lat']
    df.at[index,'Longitude'] = coords[ix]['Lon']

It works! Anyway, I'd aprecciate if some can show me a more elegant (and pythonic) way to do it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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