基于不同的值创建新列并对其进行计数

对不起,标题不够清楚。让我解释一下我想要实现什么。

id | Area
A    one
A    two
A    one
B    one
B    one
C    one
C    two
D    one
D    one
D    two
D    three

我想基于现有数据框中的值创建一个新的数据框。首先,我想在df中找到不同id的总和。例如,id A有3个条目,B有2个条目,等等。然后用它创建一个新的数据帧。

对于我们的新数据帧,我们将其称为df_new

id | count 
 A    3
 B    2
 C    2
 D    4

df_新:

id | count | one | two | three
 A    3       2     1      0
 B    2       2     0      0
 C    2       1     1      0
 D    4       2     1      1

我已经开发了自己的代码,生成了新的df_,但是我相信熊猫有更好的功能来执行这种数据提取。这是我的密码。

#Read the data
df = pd.read_csv('test_data.csv', sep = ',')
df.columns = ['id', 'Area'] #Rename
# Count a total number of Area by Id
df_new = pd.DataFrame({'count' : df.groupby("id")["Area"].count()})
# Reset index
df_new = df_new.reset_index()
#For loop for counting and creating a new column for areas in df['Area']
for i in xrange(0, len(df)):
    #Get the id
    idx = df['id'][i]
    #Get the areaname
    area_name = str(df["Area"][i])
    #Retrieve the index of a particular id
    current_index = df_new.loc[df_new['id'] == idx, ].index[0]
    #If area name exists in a column
    if area_name in df_new.columns:
        #Then +1 at the Location of the idx (Index)
        df_new[area_name][current_index] += 1
    #If not exists in the columns
    elif area_name not in df_new.columns:
        #Create an empty one with zeros
        df_new[area_name] = 0
        #Then +1 at the location of the idx (Index)
        df_new[area_name][current_index] += 1

非常感谢。