dictionary = {'Year': [1985, 1985, 1986, 1986, 1987, 1987, 1987]} pdf = pd.DataFrame(dictionary) Year 0 1985 1 1985 2 1986 3 1986 4 1987 5 1987 6 1987 I have a dataframe called pdf I need to form a new data frame in the following format:
Year count 1985 2 1986 2 1987 3 How can do this efficiently in pandas?
See .value_counts
pdf['Year'].value_counts() Here is the answer:
dictionary = {'Year': [1985, 1985, 1986, 1986, 1987, 1987, 1987]} pdf = pd.DataFrame(dictionary) gb = pdf.groupby('Year')['Year'].count() Counter is a counter tool provided to support convenient and rapid tallies of dictionaries and other hashable objects.
from collections import Counter df = pd.DataFrame(Counter(pd.DataFrame(dictionary).Year).items(), columns=['Year', 'Count']) >>> print df print(df) Year Count 0 1985 2 1 1986 2 2 1987 3 %timeit pd.DataFrame(dictionary).groupby('Year')['Year'].count() 1000 loops, best of 3: 777 µs per loop %timeit pd.DataFrame(Counter(pd.DataFrame(dictionary).Year).items(), columns=['Year', 'Count']) 1000 loops, best of 3: 672 µs per loop 