Q:

create a new dataframe from an aggregated dataframe

I have a dataframe that has aggregated people by location like so
location_id | score | number_of_males | number_of_females
     1      |  20   |        2        |         1
     2      |  45   |        1        |         2

I want to create a new dataframe that unaggregated this one so I get something like   
location_id | score | number_of_males | number_of_females
     1      |  20   |        1        |         0
     1      |  20   |        1        |         0
     1      |  20   |        0        |         1
     2      |  45   |        1        |         0
     2      |  45   |        0        |         1
     2      |  45   |        0        |         0

Or even better
location_id | score |       sex 
     1      |  20   |       male       
     1      |  20   |       male    
     1      |  20   |       female
     2      |  45   |       male
     2      |  45   |       female
     2      |  45   |       female

I want to do something like
import pandas as pd
aggregated_df = pd.DataFrame.from_csv(SOME_PATH)
unaggregated_df = df = pd.DataFrame(columns=['location_id', 'score', 'sex'])

for row in aggregated_df:
  for column in ['number_of_males', 'number_of_females']:
    for number_of_people in range(0, row[column]):
      if column == 'number_of_males':
        sex = 'male'
      else:
        sex = 'female'
      unaggregated_df.append([{'location_id': row['location_id'],
                              'score': row['score'],
                              'sex': sex}],
                             ignore_index=True)

I am having trouble getting the dict to append even though this seems to be supported in pandas
Is there a more pandthonic (panda's version of pythonic) way to accomplish this?

A:

Here is a way to get your result using group_by:
ids = ['location_id','score']

def foo(d):
    return pd.Series(d['number_of_males'].values*['male'] + 
                     d['number_of_females'].values*['female'])

pd.melt(df.groupby(ids).apply(foo).reset_index(), id_vars=ids).drop('variable', 1)

#Out[13]:
#   location_id  score   value
#0            1     20    male
#1            2     45    male
#2            1     20    male
#3            2     45  female
#4            1     20  female
#5            2     45  female

