Sum values in specific columns in DataFrame and ignore None

user_15220414 0
Viewed 96

I want to calculate the distance that my cars have driven. I have all the coordinates that the cars need to go to. Some cars parks earlier then others, and this messes up my calculation. I have this:

cars= pd.DataFrame({'x': [3,3,3,3,3,3,3,3,3],
                        'y': [1,2,3,4,5,6,7,8,9],
                        'x_goal_1': [3,3,3,3,3,3,3,3,3],
                        'y_goal_1': [10,10,10,10,10,10,10,10,10],
                        'x_goal_2': [17,24,31,31,17,17,38,38,31],
                        'y_goal_2': [10,10,10,10,10,10,10,10,10],
                        'x_goal_3': [17,24,31,31,17,17,38,38,31],
                        'y_goal_3': [17, 3, 3, 3, 17, 17, 17, 17, 3],
                        'x_goal_4': [None,27,35,28,14,18,42,43,None],
                        'y_goal_4': [None, 3, 3, 3, 17, 17, 17, 17, None],
                        'z': [3,4,5,6,7,8,9,12,22]})
 
    cars['moved_tot'] = (
                        abs(cars['x']-cars['x_goal_1']) + abs(cars['y']-cars['y_goal_1']) +
                        abs(cars['x_goal_1']-cars['x_goal_2']) + abs(cars['y_goal_1']-cars['y_goal_2']) +
                        abs(cars['x_goal_2']-cars['x_goal_3']) + abs(cars['y_goal_2']-cars['y_goal_3']) +
                        abs(cars['x_goal_3']-cars['x_goal_4']) + abs(cars['y_goal_3']-cars['y_goal_4']) )

I then get:

   x  y  x_goal_1  y_goal_1  ...  x_goal_4  y_goal_4   z  moved_tot
0  3  1         3        10  ...       NaN       NaN   3        NaN
1  3  2         3        10  ...      27.0       3.0   4       39.0
2  3  3         3        10  ...      35.0       3.0   5       46.0
3  3  4         3        10  ...      28.0       3.0   6       44.0
4  3  5         3        10  ...      14.0      17.0   7       29.0
5  3  6         3        10  ...      18.0      17.0   8       26.0
6  3  7         3        10  ...      42.0      17.0   9       49.0
7  3  8         3        10  ...      43.0      17.0  12       49.0
8  3  9         3        10  ...       NaN       NaN  22        NaN

I want the first moved_tot I want 30, and in the last I want 36. I want the calculation to ignore if a value is None ( that is if this car has parked earlier ). How do I do this?

with help from David S ( thank you! ) I figured out how to do it.

bags['moved_tot'] = (
                    abs(bags['x']-bags['x_goal_1']).fillna(0) + abs(bags['y']-bags['y_goal_1']).fillna(0) +
                    abs(bags['x_goal_1']-bags['x_goal_2']).fillna(0) + abs(bags['y_goal_1']-bags['y_goal_2']).fillna(0) +
                    abs(bags['x_goal_2']-bags['x_goal_3']).fillna(0) + abs(bags['y_goal_2']-bags['y_goal_3']).fillna(0) +
                    abs(bags['x_goal_3']-bags['x_goal_4']).fillna(0) + abs(bags['y_goal_3']-bags['y_goal_4']).fillna(0) 
                    )
pandas python
2 Answers

You could just replace the NaN inplace with 0 to avoid getting NaN in the result column, like:

cars['moved_tot'] = (abs(cars['x']-cars['x_goal_1'].fillna(0)) + abs(cars['y']-cars['y_goal_1'].fillna(0)) +
                        abs(cars['x_goal_1'].fillna(0)-cars['x_goal_2'].fillna(0)) + abs(cars['y_goal_1'].fillna(0)-cars['y_goal_2'].fillna(0)) +
                        abs(cars['x_goal_2'].fillna(0)-cars['x_goal_3'].fillna(0)) + abs(cars['y_goal_2'].fillna(0)-cars['y_goal_3'].fillna(0)) +
                        abs(cars['x_goal_3'].fillna(0)-cars['x_goal_4'].fillna(0)) + abs(cars['y_goal_3'].fillna(0)-cars['y_goal_4'].fillna(0)) )

If you want to 0 the calculation result if NaN is present just move the .fillna(0) to outside the abs()

user_88906040

Just use cars = cars.fillna(0) or cars.fillna(0, inplace=True) to get rid of repeating .fillna(0) after each abs.

If you don't want to change the original dataframe, use cars_ = cars.fillna(0) then replace cars in cars['moved_tot'] to cars_.

Besides, you could make use of the feature of columns to get rid of writing repeating column names.

cars_ = cars.fillna(0)

cars_['moved_tot'] = 0
for i in range(len(cars.columns) - 3):
    print(cars_.columns[i], '-', cars_.columns[i+2])
    cars_['moved_tot'] += abs(cars_[cars_.columns[i]] - cars_[cars_.columns[i+2]])

# Output and print(cars_)

x - x_goal_1
y - y_goal_1
x_goal_1 - x_goal_2
y_goal_1 - y_goal_2
x_goal_2 - x_goal_3
y_goal_2 - y_goal_3
x_goal_3 - x_goal_4
y_goal_3 - y_goal_4

   x  y  x_goal_1  y_goal_1  x_goal_2  y_goal_2  x_goal_3  y_goal_3  x_goal_4  y_goal_4   z  moved_tot
0  3  1         3        10        17        10        17        17       0.0       0.0   3       64.0
1  3  2         3        10        24        10        24         3      27.0       3.0   4       39.0
2  3  3         3        10        31        10        31         3      35.0       3.0   5       46.0
3  3  4         3        10        31        10        31         3      28.0       3.0   6       44.0
4  3  5         3        10        17        10        17        17      14.0      17.0   7       29.0
5  3  6         3        10        17        10        17        17      18.0      17.0   8       26.0
6  3  7         3        10        38        10        38        17      42.0      17.0   9       49.0
7  3  8         3        10        38        10        38        17      43.0      17.0  12       49.0
8  3  9         3        10        31        10        31         3       0.0       0.0  22       70.0

You could even do the sum in one line

cars_['moved_tot'] = pd.concat([abs(cars_[cars_.columns[i]] - cars_[cars_.columns[i+2]]) for i in range(len(cars.columns) - 3)], axis=1).sum(1)
user_103151630
Related