How to append value_counts() values to new column in pandas

Question

How to count repeated values in a column(df1) and add it to a new dataframe(df2) with two modified columns. I tried with drop_duplicates(), value_counts() and assigned to new dataframe but value_counts() showing NaN values . How to convert dataframe from df1 to df2. Thank You.

df1:
    A       
0  Dell 
1  Lenovo
2  Acer
3  Apple
4  Lenovo
5  Dell


df2:
    A       B
0  Dell     2
1  Lenovo   2
2  Acer     1
3  Apple    1

score 1 · Answer 1 · answered Apr 26 '21 at 07:18

1

You can .reset_index() after .value_counts():

print(df.value_counts().reset_index().rename(columns={0: "B"}))

Prints:

        A  B
0    Dell  2
1  Lenovo  2
2    Acer  1
3   Apple  1

answered Apr 26 '21 at 07:18

Andrej Kesely

168,389
15
48
91

score 0 · Accepted Answer · answered Apr 26 '21 at 07:23

You can groupby on A column then apply count function on A column, name the count column as B.

df_ = df.groupby("A").agg(B=pd.NamedAgg(column="A", aggfunc="count")).reset_index()

print(df_)

        A  B
0    Acer  1
1   Apple  1
2    Dell  2
3  Lenovo  2

How to append value_counts() values to new column in pandas

2 Answers2