Sum values in column 3 related to unique values in column 2 and 1

Question

I'm working in Python and I have a Pandas DataFrame of Uber data from New York City. A part of the DataFrame looks like this:

    Year Week_Number    Total_Dispatched_Trips      
    2015    51          1,109
    2015    5           54,380
    2015    50          8,989
    2015    51          1,025
    2015    21          10,195
    2015    38          51,957
    2015    43          266,465
    2015    29          66,139
    2015    40          74,321
    2015    39          3
    2015    50          854

As it is right now, the same week appears multiple times for each year. I want to sum the values for "Total_Dispatched_Trips" for every week for each year. I want each week to appear only once per year. (So week 51 can't appear multiple times for year 2015 etc.). How do I do this? My dataset is over 3k rows, so I would prefer not to do this manually.

Thanks in advance.

ℕʘʘḆḽḘ · Accepted Answer · 2018-01-03T21:25:59.443

1

okidoki here is it, borrowing on Convert number strings with commas in pandas DataFrame to float

import locale
from locale import atof
locale.setlocale(locale.LC_NUMERIC, '')

df['numeric_trip'] = pd.to_numeric(df.Total_Dispatched_trips.apply(atof), errors = 'coerce')
df.groupby(['Year', 'Week_number']).numeric_trip.sum()

edited Jan 03 '18 at 21:25

answered Jan 03 '18 at 20:48

ℕʘʘḆḽḘ

18,566
34
128
235

1

Total_Dispatched_Trips , base on the data he provided , this column dtype should be str – BENY Jan 03 '18 at 20:51
1

Is `pd.to_numeric('1,109',errors='coerce')` supposed to return `1109`? From what I can see, it returns `nan`. – Acccumulation Jan 03 '18 at 21:05
1

@ℕʘʘḆḽḘ After running your code my a majority of the values for "numeric_trip" are "NaN". What's the reason for this? – P. Faske Jan 03 '18 at 21:12
try again now pls – ℕʘʘḆḽḘ Jan 03 '18 at 21:13
@ℕʘʘḆḽḘ I get the following error when running your updated code: AttributeError: 'Series' object has no attribute 'applymap' – P. Faske Jan 03 '18 at 21:18
change applymap with apply – ℕʘʘḆḽḘ Jan 03 '18 at 21:20
@ℕʘʘḆḽḘ it now gives me the error: ValueError: could not convert string to float: '1,109' I tried using: df.Total_Dispatched_Trips.str.replace(",",".") but that did not help – P. Faske Jan 03 '18 at 21:23
`pd.to_numeric(df.Total_Dispatched_Trips.str.replace(",","."), errors = 'coerce')` should work too – ℕʘʘḆḽḘ Jan 03 '18 at 21:27
@ℕʘʘḆḽḘ I still get "Could not convert string to float" errors for some reason. – P. Faske Jan 03 '18 at 21:45

Sum values in column 3 related to unique values in column 2 and 1

1 Answers1