Aggregate CSV file with python
Question: My cross-tabulated CSV file looks like this:

    
    
    Country,Age,All,M,F
    UK,Under65,30987,15000,15987
    UK,65andOver,12345,6345,6000
    Germany,Under65,32646,15642,17004
    Germany,65andOver,14747,7192,7555
    France,Under65,31587,16286,15301
    France,65andOver,13741,6187,7554
    

I would like to amend it so that it looks like this:

    
    
    Country,Under65_All,Under65_M,Under65_F,65andOver_All,65andOver_M,65andOver_F
    UK,30987,15000,15987,12345,6345,6000
    Germany,32646,15642,17004,14747,7192,7555
    France,31587,16286,15301,13741,6187,7554
    

Each country now sits on one row and the number of columns has been expanded
(no cross-tab).

I'm trying to do this in Python 3. Excel VBA is out because I was hitting the
row limit with some of the larger CSV files.

I suppose what I'm trying to do is an "aggregate" with an additional "group
by" step. I've got as far as reading in the CSV file and calculating various
values which may prove useful: number of unique countries(3), number of unique
age groups(2),names and number of columns required for final output file(7).

I'm looking to make the code as flexible as possible so that it can read in a
file with x number of unique countries and y number of unique age groupings
and z number of column variables. And the final file would contain a header
row with y*z+1 columns and below this x number of rows.

Hope this makes sense, any help/pointers would be appreciated.


Answer: I'm going to propose a [`pandas`](http://pandas.pydata.org) solution because
otherwise you're reinventing the wheel, but there's no way around the fact
that it takes a bit of getting used to. The upside is that once you've picked
it up operations like this become relatively straightforward.

    
    
    import pandas as pd
    
    df = pd.read_csv("c.dat")
    df = pd.melt(df, id_vars=["Country", "Age"], var_name="Other")
    df["Column"] = df.pop("Age") + "_" + df.pop("Other")
    df = df.pivot(index="Country", columns="Column")
    df.columns = df.columns.droplevel(0)
    df.to_csv("out.csv")
    

produces

    
    
    >>> !cat out.csv
    Country,65andOver_All,65andOver_F,65andOver_M,Under65_All,Under65_F,Under65_M
    France,13741,7554,6187,31587,15301,16286
    Germany,14747,7555,7192,32646,17004,15642
    UK,12345,6000,6345,30987,15987,15000
    

(where we could sort the columns if we really wanted to.)

* * *

There's no point in copying out an entire tutorial here -- although you can
read the reshaping tutorial [here](http://pandas.pydata.org/pandas-
docs/stable/reshaping.html) \-- but I can at least give an overview of how
this works.

Step by step. First, we read the csv file into a `DataFrame` (kind of like an
excel sheet):

    
    
    >>> df = pd.read_csv("c.dat")
    >>> df
       Country        Age    All      M      F
    0       UK    Under65  30987  15000  15987
    1       UK  65andOver  12345   6345   6000
    2  Germany    Under65  32646  15642  17004
    3  Germany  65andOver  14747   7192   7555
    4   France    Under65  31587  16286  15301
    5   France  65andOver  13741   6187   7554
    

where you can access the frame by rows, columns, etc. For your purposes we can
melt (unpivot) this data:

    
    
    >>> df = pd.melt(df, id_vars=["Country", "Age"], var_name="Other")
    >>> df
        Country        Age Other  value
    0        UK    Under65   All  30987
    1        UK  65andOver   All  12345
    2   Germany    Under65   All  32646
    3   Germany  65andOver   All  14747
    4    France    Under65   All  31587
    5    France  65andOver   All  13741
    6        UK    Under65     M  15000
    7        UK  65andOver     M   6345
    8   Germany    Under65     M  15642
    9   Germany  65andOver     M   7192
    10   France    Under65     M  16286
    11   France  65andOver     M   6187
    12       UK    Under65     F  15987
    13       UK  65andOver     F   6000
    14  Germany    Under65     F  17004
    15  Germany  65andOver     F   7555
    16   France    Under65     F  15301
    17   France  65andOver     F   7554
    

So now we have the row labels we want (the countries) and information about
the other columns, whatever they are, and the values. You wanted the "Age" and
whatever's in "Other" combined, so:

    
    
    >>> df["Column"] = df.pop("Age") + "_" + df.pop("Other")
    >>> df
        Country  value         Column
    0        UK  30987    Under65_All
    1        UK  12345  65andOver_All
    2   Germany  32646    Under65_All
    3   Germany  14747  65andOver_All
    4    France  31587    Under65_All
    5    France  13741  65andOver_All
    6        UK  15000      Under65_M
    7        UK   6345    65andOver_M
    8   Germany  15642      Under65_M
    9   Germany   7192    65andOver_M
    10   France  16286      Under65_M
    11   France   6187    65andOver_M
    12       UK  15987      Under65_F
    13       UK   6000    65andOver_F
    14  Germany  17004      Under65_F
    15  Germany   7555    65andOver_F
    16   France  15301      Under65_F
    17   France   7554    65andOver_F
    

and now all the hard work is done. We simply have to call `pivot` to turn it:

    
    
    >>> df = df.pivot(index="Country", columns="Column")
    >>> df
                     value                                                    \
    Column   65andOver_All  65andOver_F  65andOver_M  Under65_All  Under65_F   
    Country                                                                    
    France           13741         7554         6187        31587      15301   
    Germany          14747         7555         7192        32646      17004   
    UK               12345         6000         6345        30987      15987   
    
    
    Column   Under65_M  
    Country             
    France       16286  
    Germany      15642  
    UK           15000  
    

(Looks better on the screen.) It's given us the extra "value" level, which you
don't want, so let's drop that:

    
    
    >>> df.columns = df.columns.droplevel(0)
    >>> df
    Column   65andOver_All  65andOver_F  65andOver_M  Under65_All  Under65_F  \
    Country                                                                    
    France           13741         7554         6187        31587      15301   
    Germany          14747         7555         7192        32646      17004   
    UK               12345         6000         6345        30987      15987   
    
    Column   Under65_M  
    Country             
    France       16286  
    Germany      15642  
    UK           15000  
    

And then we write it to csv:

    
    
    >>> df.to_csv("out.csv")
    

