Count table or frequency table in python

user_13412850 0
Viewed 278

I have below dataframe

import pandas as pd
import numpy as np

df = pd.DataFrame({
                   'val0': [-8.93, 1.68, 1.58, -1.2, 3.43, 4.25, 3.96, -0.52, -8.12, -5.05, -7.12, 11.99, 16.98, 17.15, 0.11, -7.32, -11.61, -12.22, -11.99, -8.37, -5.84, 1.09, -3.21, -1.42, -4.31, -1.18, 5.39, -7.01, -3.99, -9.08, 3.32, -1.48, -0.64, -1.09, -2.18, 0.32, -2.31, 1.32, -4.02, -4.11],
                    'val1' : [-1.0, 4.0, -193.0, -22.0, -193.0, 200.0, 63.0, 43.0, 5.0, 35.0, 40.0, -7.0, -20.0, -43.0, -50.0, -43.0, -20.0, 20.0, 40.0, 48.0, 22.0, 9.0, 11.0, 6.0, 55.0, -200.0, -52.0, -10.0, -5.0, 60.0, 135.0, 85.0, 75.0, 75.0, 90.0, -122.0, -62.0, -162.0, 122.0, 50.0],

})

how can I create a frequency table or a count table similar to the snap enclosed below.

I have shown some examples, for instance there is one entry that falls under 0-25 range for Val1 and 0 to -1.6 range for Val0. Likewise there are no entries that fall under 0-25 range for Val1 and -1.6 and -3.2 range for Val0. (I will of course need the entire table populated)

Also, as show below in another table I want to see the average value of Val0 for each category

enter image description here

average values for Val0

enter image description here

pandas frequency python
1 Answers

You can cut() the data into bins:

df['val0_bin'] = pd.cut(df.val0, -np.arange(0, 32, 1.6)[::-1]) # np.arange(-32, 0, 1.6) does not seem to work
df['val1_bin'] = pd.cut(df.val1, range(0, 300, 25))

Then crosstab() them:

table1 = pd.crosstab(df.val1_bin, df.val0_bin)

# sort column bins to match question prompt
columns = df.val0_bin.unique().dropna().sort_values(ascending=False)
table1 = table1[columns]

# flip display of category bounds
categories = [f'[{c.right}, {c.left})' for c in table1.columns.categories]
table1.columns = table1.columns.rename_categories(categories)

# val0_bin    [-0.0, -1.6)  [-1.6, -3.2)  [-3.2, -4.8)  [-4.8, -6.4)  [-6.4, -8.0)  [-8.0, -9.6)  [-11.2, -12.8)
# val1_bin
# (0, 25]                1             0             1             1             0             1               1
# (25, 50]               1             0             1             1             1             1               1
# (50, 75]               2             0             1             0             0             1               0
# (75, 100]              1             1             0             0             0             0               0
# (100, 125]             0             0             1             0             0             0               0

For the average val0 crosstab, specify aggfunc='mean' with values=df.val0:

table2 = pd.crosstab(df.val1_bin, df.val0_bin, aggfunc='mean', values=df.val0).fillna(0)
table2 = table2[columns]
table2.columns = table2.columns.rename_categories(categories)

# val0_bin    [-0.0, -1.6)  [-1.6, -3.2)  [-3.2, -4.8)  [-4.8, -6.4)  [-6.4, -8.0)  [-8.0, -9.6)  [-11.2, -12.8)
# val1_bin
# (0, 25]           -1.420          0.00         -3.21         -5.84          0.00         -8.12          -12.22
# (25, 50]          -0.520          0.00         -4.11         -5.05         -7.12         -8.37          -11.99
# (50, 75]          -0.865          0.00         -4.31          0.00          0.00         -9.08            0.00
# (75, 100]         -1.480         -2.18          0.00          0.00          0.00          0.00            0.00
# (100, 125]         0.000          0.00         -4.02          0.00          0.00          0.00            0.00
user_131383640
Related