I have a Pandas dataframe built like:
| Col1 | Col2 |
|---|---|
| 1 | A |
| 1 | B |
| 1 | B |
| 2 | A |
| 2 | A |
| 3 | A |
| 3 | Nan |
For every value of Col1, I want to count every value of Col2 ignoring the Nan values and put the sum in the associated column, obtaining something like:
| Col1 | A | B |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 2 | 0 |
| 3 | 1 | 0 |
How can I do that in Pandas? I have a lot of values in Col1 and lots of columns like Col2. Thank you very much!
You can try crosstab
out = pd.crosstab(df.Col1, df.Col2).reset_index()
Out[66]:
Col2 Col1 A B
0 1 1 2
1 2 2 0
2 3 1 0
simply do this !!works!!
df.groupby(list(df.columns)).size().unstack()
output:
Col2 A B
Col1
1 1.0 2.0
2 2.0 NaN
3 1.0 NaN
You can just add the columns and set the values to a new column.
sum_column = df["col1"] + df["col2"]
df["col3"] = sum_column
If you have several columns and want to count the values over all columns, here's a maybe not too elegant, but working solution:
import pandas as pd
import numpy as np
df = pd.DataFrame([[1, 1, 1, 2, 2, 2, 3, 3, 3],
["A", "B", "A", "C", "b", "A", "A", "A", np.nan],
["a", "a", "C", "b", "A", "b", "a", "b", np.nan]
]).T
df.columns = ["Col1", "Col2", "Col3"]
result = {}
for i, group in df.groupby("Col1"):
result[i] = group.drop(columns=["Col1"]).stack().value_counts()
result = pd.DataFrame(result).T.fillna(0)
There are a number of ways to achieve this(@BENY has already provided the crosstab option):
pd.get_dummies:
(pd
.get_dummies(df, columns=["Col2"], prefix="", prefix_sep="")
.groupby("Col1")
.sum()
)
A B
Col1
1 1 2
2 2 0
3 1 0
pivot_table:
(df.pivot_table(index="Col1", columns="Col2", aggfunc="size", fill_value=0))
Col2 A B
Col1
1 1 2
2 2 0
3 1 0
value_counts:
(df
.value_counts()
.unstack(fill_value=0)
.rename_axis(columns=None)
)
A B
Col1
1 1 2
2 2 0
3 1 0
Use pandas.pivot_tabel(). Link is available https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html.