Crosstab join in Python

Question

I am trying to combine multiple crosstabs into one. Please note that the data provided is obviously for testing purposes only. The evidence is much larger, so efficiency is very important to me.

Crosstabs are generated, listed, and then combined with the lambda function in the column word. However, the result of this merger is not what I expect. I think the problem is that columns with only N values ​​of the crosstab are discarded even when used dropna = False, which will lead to a function error merge. First I will show the code and after that I will get intermediate data and errors.

import pandas as pd
import numpy as np
import functools as ft

def main():
    # Create dataframe
    df = pd.DataFrame(data=np.zeros((0, 3)), columns=['word','det','source'])
    df["word"] = ('banana', 'banana', 'elephant', 'mouse', 'mouse', 'elephant', 'banana', 'mouse', 'mouse', 'elephant', 'ostrich', 'ostrich')
    df["det"] = ('a', 'the', 'the', 'a', 'the', 'the', 'a', 'the', 'a', 'a', 'a', 'the')
    df["source"] = ('BE', 'BE', 'BE', 'NL', 'NL', 'NL', 'FR', 'FR', 'FR', 'FR', 'FR', 'FR')

    create_frequency_list(df)

def create_frequency_list(df):
    # Create a crosstab of ALL values
    # NOTE that dropna = False does not seem to work as expected
    total = pd.crosstab(df.word, df.det, dropna = False)
    total.fillna(0)
    total.reset_index(inplace=True)
    total.columns = ['word', 'a', 'the']

    crosstabs = [total]

    # For the column headers, multi-level
    first_index = [('total','total')]
    second_index = [('a','the')]

    # Create crosstabs per source (one for BE, one for NL, one for FR)
    # NOTE that dropna = False does not seem to work as expected
    for source, tempDf in df.groupby('source'):
        crosstab = pd.crosstab(tempDf.word, tempDf.det, dropna = False)
        crosstab.fillna(0)
        crosstab.reset_index(inplace=True)
        crosstab.columns = ['word', 'a', 'the']
        crosstabs.append(crosstab)

        first_index.extend((source,source))
        second_index.extend(('a','the'))

    # Just for debugging: result as expected
    for tab in crosstabs:
        print(tab)

    merged = ft.reduce(lambda left,right: pd.merge(left,right, on='word'), crosstabs).set_index('word')

    # UNEXPECTED RESULT
    print(merged)    

    arrays = [first_index, second_index]

    # Throws error: NotImplementedError: > 1 ndim Categorical are not supported at this time
    columns = pd.MultiIndex.from_arrays(arrays)

    df_freq = pd.DataFrame(data=merged.as_matrix(),
                      columns=columns,
                      index = crosstabs[0]['word'])
    print(df_freq)

main()

Individual crosstabs : not as expected. NA columns are discarded

       word  a  the
0    banana  2    1
1  elephant  1    2
2     mouse  2    2
3   ostrich  1    1

       word  a  the
0    banana  1    1
1  elephant  0    1

       word  a  the
0    banana  1    0
1  elephant  1    0
2     mouse  1    1
3   ostrich  1    1

       word  a  the
0  elephant  0    1
1     mouse  1    1

, dataframes , , , .

: , ,

          a_x  the_x  a_y  the_y  a_x  the_x  a_y  the_y
word                                                    
elephant    1      2    0      1    1      0    0      1

:

# NotImplementedError: > 1 ndim Categorical are not supported at this time
columns = pd.MultiIndex.from_arrays(arrays)

, , , . , Python, .

, :

    source       total        BE          FR          NL
    det         a   the     a   the     a   the     a   the
    word
0   banana      2   1       1   1       1   0       0   0
1   elephant    1   2       0   1       1   0       0   1
2   mouse       2   2       0   0       1   1       1   1
3   ostrich     1   1       0   0       1   1       0   0

Answer 1

, :

df.groupby([col1, col2]).size().unstack() - pd.crosstab . - source. df.groupby([col1, col2, col3]).size().unstack([2, 1])

sort_index(1).fillna(0).astype(int) - .

. , :

• df.groupby(['word', 'gender']).size()
• df.groupby(['word', 'gender', 'source']).size()

unstack stack - , , . unstack([2, 1]) , .

, xtabs stack unstack prep pd.concat. Voilà!

xtabs = df.groupby(df.columns.tolist()).size() \
          .unstack([2, 1]).sort_index(1).fillna(0).astype(int)

pd.concat([xtabs.stack().sum(1).rename('total').to_frame().unstack(), xtabs], axis=1)

:

import pandas as pd
import numpy as np
import functools as ft

def main():
    # Create dataframe
    df = pd.DataFrame(data=np.zeros((0, 3)), columns=['word','gender','source'])
    df["word"] = ('banana', 'banana', 'elephant', 'mouse', 'mouse', 'elephant', 'banana', 'mouse', 'mouse', 'elephant', 'ostrich', 'ostrich')
    df["gender"] = ('a', 'the', 'the', 'a', 'the', 'the', 'a', 'the', 'a', 'a', 'a', 'the')
    df["source"] = ('BE', 'BE', 'BE', 'NL', 'NL', 'NL', 'FR', 'FR', 'FR', 'FR', 'FR', 'FR')

    return create_frequency_list(df)

def create_frequency_list(df):
    xtabs = df.groupby(df.columns.tolist()).size() \
              .unstack([2, 1]).sort_index(1).fillna(0).astype(int)

    total = xtabs.stack().sum(1)
    total.name = 'total'
    total = total.to_frame().unstack()

    return pd.concat([total, xtabs], axis=1)

main()