Filling a Pandas table with values

Wee 0
Viewed 25

First of all sorry for the kind-of misleading title but I didn't know how to word that properly. My problem is that basically I don't know how I can fill a Pandas table with the values below. I need a table with rows and columns going from 0 to X (in which X is the maximum value in the "[X,Y]" brackets below), which i managed to make with Numpy, but I don't know how to insert the corresponding value_counts() data.

import pandas as pd

det_vect1 = [[0, 1], [1, 1], [0, 1], [0, 1], [1, 0], [0, 0], [0, 0], [0, 0], [1, 1], [0, 1], [0, 0], [0, 0], [0, 0], [0, 1], [0, 1], [0, 1], [0, 1], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [1, 0], [0, 0], [0, 1], [0, 0], [3, 1], [0, 0], [0, 0], [0, 0], [2, 0], [1, 0], [3, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [1, 0], [0, 0], [0, 1], [0, 0], [1, 0], [0, 0], [2, 1], [0, 0], [0, 0], [1, 0], [0, 0], [0, 0], [0, 1], [1, 0], [0, 0], [0, 0], [0, 0], [2, 0], [0, 0], [0, 1], [0, 0], [0, 0], [6, 0], [0, 1], [0, 1], [2, 0], [0, 0], [0, 1], [0, 0], [0, 0], [0, 1], [1, 0], [2, 1], [0, 0], [0, 1], [0, 0], [0, 1], [1, 0], [0, 1], [0, 1], [0, 0], [0, 1], [0, 1], [1, 0], [0, 0], [1, 0], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [0, 0], [0, 0], [1, 0], [2, 0], [0, 0], [1, 0], [0, 1], [0, 0], [2, 1], [0, 1], [2, 0], [1, 0], [0, 1], [0, 0], [0, 0], [0, 1], [0, 0], [1, 0], [2, 0], [0, 2], [1, 0], [0, 1], [0, 1], [0, 0], [1, 0], [0, 0], [0, 0], [0, 1], [0, 0], [0, 1], [0, 1], [0, 0], [0, 0], [0, 1], [0, 0], [0, 1], [0, 1], [0, 0], [0, 1], [1, 0], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [0, 0], [0, 0], [0, 1], [0, 1], [0, 0], [0, 0], [0, 1], [0, 0], [0, 1], [0, 0], [0, 0], [0, 1], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [1, 0], [0, 0], [1, 0], [0, 1], [2, 1], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [1, 0], [0, 0], [0, 1], [1, 0], [0, 3], [0, 0], [0, 0], [1, 1], [0, 1], [0, 0], [0, 0], [1, 0], [0, 0], [1, 0], [0, 0], [0, 1], [1, 2], [0, 1], [1, 0], [1, 0], [1, 1], [0, 1], [1, 0], [0, 0], [0, 1], [0, 0], [0, 3], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [2, 1], [0, 1], [1, 1], [0, 0], [0, 0], [0, 1], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 0], [0, 1], [0, 1], [0, 0]]

print(pd.Series(det_vect1).value_counts())

which gives

[0, 0]    116
[0, 1]     64
[1, 0]     27
[2, 0]      6
[1, 1]      5
[2, 1]      5
[0, 3]      2
[3, 1]      1
[3, 0]      1
[6, 0]      1
[0, 2]      1
[1, 2]      1
dtype: int64

So i need something like (sorry for the scrappy Paint example with random values):

Final result example with random values

Of course the non-existing values can be easily filled with zeros, no problem.

Thank you in advance!

pandas arrays python
1 Answers

Use a DataFrame constructor, not Series, then unstack:

out = pd.DataFrame(det_vect1).value_counts().unstack(fill_value=0)

Or, with crosstab:

df_tmp = pd.DataFrame(det_vect1)
df = pd.crosstab(df_tmp[0], df_tmp[1])

output:

1    0   1  2  3
0               
0  116  64  1  2
1   27   5  1  0
2    6   5  0  0
3    1   1  0  0
6    1   0  0  0

For a complete index of values, reindex:

(pd.DataFrame(det_vect1)
   .value_counts().unstack(fill_value=0) # or crosstab alternative
   .pipe(lambda d: d.reindex(index=range(d.index.max()+1),
                             columns=range(d.columns.max()+1),
                             fill_value=0
                            )
        )
   .rename_axis(index=None, columns=None)
)

output:

     0   1  2  3
0  116  64  1  2
1   27   5  1  0
2    6   5  0  0
3    1   1  0  0
4    0   0  0  0
5    0   0  0  0
6    1   0  0  0
mozway0
Related