python - 根据另一个数据框中匹配值的行数创建新列

Question

我想根据 df2 中每个水果的行数创建新列。

Expected Output of df1

No  | Fruit_Name | 2018 | 2019 | 2020 
1   | Apple      |  2   |   1  | 0
2   | Banana     |  0   |   0  | 1
3   | Cherries   |  0   |   0  | 1

     df1                                       df2
No | Fruit_Name |                year   | farmer | fruit_farmed
1  | Apple      |                2018   | John   |   Apple
2  | Banana     |                2019   | Timo   |   Apple
3  | Cherries   |                2020   | Eva    |   Cherries
                                 2020   | Frey   |   Banana
                                 2018   | Ali    |   Apple

不起作用的代码：

i=0
for i in range(3):
    df1['2018'] = len(df2.loc[df2['fruit_farmed'] == df1['Fruit_Name'][i]])
    df1['2019'] = len(df2.loc[df2['fruit_farmed'] == df1['Fruit_Name'][i]])
    df1['2020'] = len(df2.loc[df2['fruit_farmed'] == df1['Fruit_Name'][i]])
    i=i+1

Output:
    No  Fruit_Name  2018    2019    2020
0   1      Apple     1        1      1
1   2      Banana    1        1      1
2   3     Cherries   1        1      1

标签： pythonpython-3.xregexpandasdataframe

Answer 1

crosstab你可以试试join

s = pd.crosstab(df2.fruit_farmed, df2.year)
s = s.reindex(df1.Fruit_Name)
s.index=df1.index
df1 = df1.join(s)