Pandas: Eliminating for loops

Question

I have two Pandas dataframes, namely: habitat_family and habitat_species. I want to populate habitat_species based on the taxonomical lookupMap and the values in habitat_family:

import pandas as pd
import numpy as np
species = ['tiger', 'lion', 'mosquito', 'ladybug', 'locust', 'seal', 'seabass', 'shark', 'dolphin']
families = ['mammal','fish','insect']
lookupMap = {'tiger':'mammal', 'lion':'mammal', 'mosquito':'insect', 'ladybug':'insect', 'locust':'insect',
            'seal':'mammal', 'seabass':'fish', 'shark':'fish', 'dolphin':'mammal' }

habitat_family = pd.DataFrame({'id': range(1,11),
                         'mammal': [101,123,523,562,546,213,562,234,987,901],
                         'fish' :  [625,254,929,827,102,295,174,777,123,763],
                         'insect': [345,928,183,645,113,942,689,539,789,814] 
                         }, index=range(1,11), columns=['id','mammal','fish','insect'])

habitat_species = pd.DataFrame(0.0, index=range(1,11), columns=species)

# My highly inefficient solution:
for id in habitat_family.index: # loop through habitat id's
   for spec in species: # loop through species
       corresp_family = lookupMap[spec]
       habitat_species.loc[id,spec] = habitat_family.loc[id,corresp_family]

The nested for loops above do the job. But in reality the sizes of my dataframes are massive and using for loops are not feasible.

Is there a more efficient method to achieve this using maybe dataframe.apply() or a similar function?

EDIT: The desired output habitat_species is:

habitat_species
    tiger  lion  mosquito  ladybug  locust  seal  seabass  shark  dolphin
1     101   101       345      345     345   101      625    625      101
2     123   123       928      928     928   123      254    254      123
3     523   523       183      183     183   523      929    929      523
4     562   562       645      645     645   562      827    827      562
5     546   546       113      113     113   546      102    102      546
6     213   213       942      942     942   213      295    295      213
7     562   562       689      689     689   562      174    174      562
8     234   234       539      539     539   234      777    777      234
9     987   987       789      789     789   987      123    123      987
10    901   901       814      814     814   901      763    763      901

Answer 1

You don't need any loops at all. Check it out:

In [12]: habitat_species = habitat_family[Series(species).replace(lookupMap)]

In [13]: habitat_species.columns = species

In [14]: habitat_species
Out[14]: 
    tiger  lion  mosquito  ladybug  locust  seal  seabass  shark  dolphin
1     101   101       345      345     345   101      625    625      101
2     123   123       928      928     928   123      254    254      123
3     523   523       183      183     183   523      929    929      523
4     562   562       645      645     645   562      827    827      562
5     546   546       113      113     113   546      102    102      546
6     213   213       942      942     942   213      295    295      213
7     562   562       689      689     689   562      174    174      562
8     234   234       539      539     539   234      777    777      234
9     987   987       789      789     789   987      123    123      987
10    901   901       814      814     814   901      763    763      901

[10 rows x 9 columns]

Answer 2

First of all, fantastically written question. Thanks.

I would suggest making a DataFrame for each family, and concatenating at the end: You'll need to reverse your lookupMap:

In [80]: d = {'mammal': ['dolphin', 'lion', 'seal', 'tiger'], 'insect': ['ladybug', 'locust', 'mosquito'], 'fish': 
['seabass', 'shark']}

So as an example:

In [83]: k, v = 'mammal', d['mammal']

In [86]: pd.DataFrame([habitat_family[k] for _ in v], index=v).T
Out[86]: 
    dolphin  lion  seal  tiger
1       101   101   101    101
2       123   123   123    123
3       523   523   523    523
4       562   562   562    562
5       546   546   546    546
6       213   213   213    213
7       562   562   562    562
8       234   234   234    234
9       987   987   987    987
10      901   901   901    901

[10 rows x 4 columns]

Now do that for each family:

In [88]: for k, v in d.iteritems():
   ....:     results.append(pd.DataFrame([habitat_family[k] for _ in v], index=v).T)

And concat:

In [89]: habitat_species = pd.concat(results, axis=1)

In [90]: habi
habitat_family   habitat_species  

In [90]: habitat_species
Out[90]: 
    dolphin  lion  seal  tiger  ladybug  locust  mosquito  seabass  shark
1       101   101   101    101      345     345       345      625    625
2       123   123   123    123      928     928       928      254    254
3       523   523   523    523      183     183       183      929    929
4       562   562   562    562      645     645       645      827    827
5       546   546   546    546      113     113       113      102    102
6       213   213   213    213      942     942       942      295    295
7       562   562   562    562      689     689       689      174    174
8       234   234   234    234      539     539       539      777    777
9       987   987   987    987      789     789       789      123    123
10      901   901   901    901      814     814       814      763    763

[10 rows x 9 columns]

You might consider passing the families as the key parameter to concat if you want a hierarchical index for the columns with (family, species) pairs.

Some profiling, since you said performance matters:

# Mine
In [97]: %%timeit
   ....: for k, v in d.iteritems():
   ....:     results.append(pd.DataFrame([habitat_family[k] for _ in v], index=v).T)
   ....: habitat_species = pd.concat(results, axis=1)
   ....: 
1 loops, best of 3: 296 ms per loop

# Your's
In [98]: %%timeit
   ....: for id in habitat_family.index: # loop through habitat id's
   ....:    for spec in species: # loop through species
   ....:        corresp_family = lookupMap[spec]
   ....:        habitat_species.loc[id,spec] = habitat_family.loc[id,corresp_family]
10 loops, best of 3: 21.5 ms per loop

# Dan's
In [102]: %%timeit
   .....: habitat_species = habitat_family[Series(species).replace(lookupMap)]
   .....: habitat_species.columns = species
   .....: 
100 loops, best of 3: 2.55 ms per loop

Looks like Dan wins by a longshot!