Madno Madno - 6 months ago 23
Python Question

Python Pandas Calculate Number of values in columns for each row and put it in columns

Let's say that I have a dataframe like:

x y
1 45 T
2 70 T
3 88 F
4 45 F
5 88 F
..


And I want to calculate the value_counts for both columns together, and put them in separated columns:

x y z
1 45 1 1
2 70 1 0
3 88 0 2
..


Where x is the plain number I have (integers), and y is the number of T values it got in the original dataframe, and z is the number of F values it got.

Answer Source

Use crosstab + reset_index + rename_axis:

df = pd.crosstab(df['x'], df['y']).reset_index().rename_axis(None, 1)
print (df)
    x  F  T
0  45  1  1
1  70  0  1
2  88  2  0

Alternative with groupby + size + unstack:

df = df.groupby(['x','y'])
       .size()
       .unstack(fill_value=0)
       .reset_index().rename_axis(None, 1)
print (df)
    x  F  T
0  45  1  1
1  70  0  1
2  88  2  0

But if need rename columns (but need only few unique values in y) need dict:

d = {'T':'y','F':'z'}
df = df.groupby(['x','y'])
       .size()
       .unstack(fill_value=0)
       .rename(columns=d)
       .rename_axis(None, 1)
       .reset_index()
print (df)
    x  z  y
0  45  1  1
1  70  0  1
2  88  2  0