Weird Data manipulation in Pandas

Question

I'm reading Python for Data Analysis by Wes Mckinney, but I was surprised by this data manipulation. You can see all the procedure here but I will try to summarize it here. Assume you have something like this:

In [133]: agg_counts = by_tz_os.size().unstack().fillna(0)
    Out[133]:
    a                 Not Windows   Windows
    tz                  245          276
    Africa/Cairo         0            3
    Africa/Casablanca    0            1
    Africa/Ceuta         0            2
    Africa/Johannesburg  0            1
    Africa/Lusaka        0            1
    America/Anchorage    4            1
    ...

tz means time zone and Not Windows and Windows are categories extracted from the User Agent in the original data, so we can see that there are 3 Windows users and 0 Non-windows users in Africa/Cairo from the data collected.

Then in order to get "the top overall time zones" we have:

In [134]: indexer = agg_counts.sum(1).argsort()
Out[134]:
tz
                                  24
Africa/Cairo                      20
Africa/Casablanca                 21
Africa/Ceuta                      92
Africa/Johannesburg               87
Africa/Lusaka                     53
America/Anchorage                 54
America/Argentina/Buenos_Aires    57
America/Argentina/Cordoba         26
America/Argentina/Mendoza         55
America/Bogota                    62
...

So at that point, I would have thought that according to the documentation I was summing over columns (in sum(1)) and then sorting according to the result showing arguments (as usual in argsort). First of all, I'm not sure what does it mean "columns" in the context of this series because sum(1) is actually summing Not Windows and Windows users keeping that value in the same row as its time zone. Furthermore, I can't see a correlation between argsort values and agg_counts. For example, Pacific/Auckland has an "argsort value" (in In[134]) of 0 and it only has a sum of 11 Windows and Not Windows users. Asia/Harbin has an argsort value of 1 and appears with a sum of 3 Windows and Not Windows users.

Can someone explain to me what is going on there? Obviously I'm misunderstanding something.

Answer 1

sum(1) means sum over axis = 1. The terminology comes from numpy.

For a 2+ dimensional object, the 0-axis refers to the rows. Summing over the 0-axis means summing over the rows, which amounts to summing "vertically" (when looking at the table).

The 1-axis refers to the columns. Summing over the 1-axis means summing over the columns, which amounts to summing "horizontally".

---

numpy.argsort returns an array of indices which tell you how to sort an array. For example:

In [72]: import numpy as np

In [73]: x = np.array([521, 3, 1, 2, 1, 1, 5])

In [74]: np.argsort(x)
Out[74]: array([2, 4, 5, 3, 1, 6, 0])

The 2 in the array returned by np.argsort means the smallest value in x is x[2], which equals 1. The next smallest is x[4] which is also 1. And so on.

If we define

totals = df.sum(1)
print(totals)
# tz                     521
# Africa/Cairo             3
# Africa/Casablanca        1
# Africa/Ceuta             2
# Africa/Johannesburg      1
# Africa/Lusaka            1
# America/Anchorage        5

then totals.argsort() is argsorting the values [521, 3, 1, 2, 1, 1, 5]. We've seen the result; it is the same as numpy.argsort:

[2, 4, 5, 3, 1, 6, 0]

These values are simply made into a Series, with the same index as totals:

print(totals.argsort())
# tz                     2
# Africa/Cairo           4
# Africa/Casablanca      5
# Africa/Ceuta           3
# Africa/Johannesburg    1
# Africa/Lusaka          6
# America/Anchorage      0

Associating the totals.index with this argsort indices does not appear have intrinsic meaning, but if you compute totals[totals.argsort()] you see the rows of totals in sorted order:

print(totals[totals.argsort()])
# Africa/Casablanca        1
# Africa/Johannesburg      1
# Africa/Lusaka            1
# Africa/Ceuta             2
# Africa/Cairo             3
# America/Anchorage        5
# tz                     521