Madno - 1 year ago 74
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.

``````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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download