Skip to main content
 首页 » 编程设计

python之为每个州获得 3 个最广阔的城市

2024年11月24日8Renyi-Fan

我想根据下面的数据框为每个州获得 3 个具有最大“提高”值的城市。

因此,每个城市都有许多事件。我想汇总每个城市的“筹集”金额,以获得每个城市的总筹集金额,然后根据每个州的“筹集”金额显示前三个城市。

     category                  city            created     goal                                               name   raised state                                                url 
0     Medical                OXNARD  December 30, 2018  15000.0                           Wayne's Cancer Care Fund     80.0    CA  https://www.gofundme.com/8qz8h6-waynes-cancer-... 
1     Medical           CHINO HILLS   December 4, 2018   2000.0            Mother of two has cancer and needs help    500.0    CA                   https://www.gofundme.com/3qi0rog 
2     Medical          BATTLE CREEK   December 6, 2018  10000.0                                     Hospital costs    570.0    MI                   https://www.gofundme.com/3sbwals 
3     Medical  FEASTERVILLE TREVOSE   December 3, 2018  10000.0                          Help raise Joey & Brianna   2200.0    MI         https://www.gofundme.com/get-away-from-him 
4     Medical       WEST PALM BEACH  December 12, 2018   6000.0                        Kelvin McCray Recovery Fund   2450.0    MI      https://www.gofundme.com/send-ricky-to-school 
5     Medical                 JONES  December 11, 2018  25000.0           Wheelchair Accessible Vehicle for Taelor   2270.0    OK       https://www.gofundme.com/HelpTaelorTransport 
6     Medical                CONROE  December 20, 2018  10000.0                       "A Good friend in dire need"   1250.0    OK                   https://www.gofundme.com/4dmeoko 

json 中的示例日期:

[{ 
    "category": "Medical", 
    "city": "OXNARD", 
    "created": "December 30, 2018", 
    "goal": 15000.0, 
    "name": "Wayne's Cancer Care Fund", 
    "raised": 80.0, 
    "state": "CA", 
    "url": "https://www.gofundme.com/8qz8h6-waynes-cancer-care-fund" 
}, 
{ 
    "category": "Medical", 
    "city": "CHINO HILLS", 
    "created": "December 4, 2018", 
    "goal": 2000.0, 
    "name": "Mother of two has cancer and needs help", 
    "raised": 500.0, 
    "state": "CA", 
    "url": "https://www.gofundme.com/3qi0rog" 
}, 
 
{ 
    "category": "Medical", 
    "city": "BATTLE CREEK", 
    "created": "December 6, 2018", 
    "goal": 10000.0, 
    "name": "Hospital costs", 
    "raised": 570.0, 
    "state": "MI", 
    "url": "https://www.gofundme.com/3sbwals" 
}, 
{ 
    "category": "Medical", 
    "city": "FEASTERVILLE TREVOSE", 
    "created": "December 3, 2018", 
    "goal": 10000.0, 
    "name": "Help raise Joey & Brianna", 
    "raised": 2200.0, 
    "state": "MI", 
    "url": "https://www.gofundme.com/get-away-from-him" 
}, 
{ 
    "category": "Medical", 
    "city": "WEST PALM BEACH", 
    "created": "December 12, 2018", 
    "goal": 6000.0, 
    "name": "Kelvin McCray Recovery Fund", 
    "raised": 2450.0, 
    "state": "MI", 
    "url": "https://www.gofundme.com/send-ricky-to-school" 
}, 
{ 
    "category": "Medical", 
    "city": "JONES", 
    "created": "December 11, 2018", 
    "goal": 25000.0, 
    "name": "Wheelchair Accessible Vehicle for Taelor", 
    "raised": 2270.0, 
    "state": "OK", 
    "url": "https://www.gofundme.com/HelpTaelorTransport" 
}, 
{ 
    "category": "Medical", 
    "city": "CONROE", 
    "created": "December 20, 2018", 
    "goal": 10000.0, 
    "name": "\"A Good friend in dire need\"", 
    "raised": 1250.0, 
    "state": "OK", 
    "url": "https://www.gofundme.com/4dmeoko" 
}] 

预期的结果应该是这样的:

123 State1 City1 100  
3          City2 99   
58         City3 98   
8   State2 City4 97   
12         City5 96   
1          City6 95   

这并没有什么帮助:

maxRaisedCityByState = a.df.groupby(['state','city'])['raised'].max() 

据称来自 duplicate question 的答案不帮忙:

请您参考如下方法:

我简化了城市和州的名称,以便于理解。

请允许我尝试一种新的表格格式化解决方案:)

<table><tbody><tr><th>category</th><th>city</th><th>created</th><th>goal</th><th>name</th><th>raised</th><th>state</th><th>url</th><th> </th></tr><tr><td>0</td><td>Medical</td><td>City1</td><td>December 30, 2018</td><td>15000.0</td><td>Wayne's Cancer Care Fund</td><td>80.0</td><td>State1</td><td>https://www.gofundme.com/8qz8h6-waynes-cancer-...</td></tr><tr><td>1</td><td>Medical</td><td>City1</td><td>December 4, 2018</td><td>2000.0</td><td>Mother of two has cancer and needs help</td><td>500.0</td><td>State1</td><td>https://www.gofundme.com/3qi0rog</td></tr><tr><td>2</td><td>Medical</td><td>City2</td><td>December 6, 2018</td><td>10000.0</td><td>Hospital costs</td><td>570.0</td><td>State1</td><td>https://www.gofundme.com/3sbwals</td></tr><tr><td>3</td><td>Medical</td><td>City3</td><td>December 3, 2018</td><td>10000.0</td><td>Help raise Joey &amp; Brianna</td><td>2200.0</td><td>State1</td><td>https://www.gofundme.com/get-away-from-him</td></tr><tr><td>4</td><td>Medical</td><td>City4</td><td>December 12, 2018</td><td>6000.0</td><td>Kelvin McCray Recovery Fund</td><td>2450.0</td><td>State2</td><td>https://www.gofundme.com/send-ricky-to-school</td></tr><tr><td>5</td><td>Medical</td><td>City5</td><td>December 11, 2018</td><td>25000.0</td><td>Wheelchair Accessible Vehicle for Taelor</td><td>2270.0</td><td>State2</td><td>https://www.gofundme.com/HelpTaelorTransport</td></tr><tr><td>6</td><td>Medical</td><td>City6</td><td>December 20, 2018</td><td>10000.0</td><td>"A Good friend in dire need"</td><td>1250.0</td><td>State2</td><td>https://www.gofundme.com/4dmeoko</td></tr></tbody></table>

第 1 步:删除不需要的列:

df.drop(['category', 'created', 'goal', 'name', 'url'], inplace=True, axis = 1) 

这给了我们:

    city    raised  state 
0   City1   80.0    State1 
1   City1   500.0   State1 
2   City2   570.0   State1 
3   City3   2200.0  State1 
4   City4   2450.0  State2 
5   City5   2270.0  State2 
6   City6   1250.0  State2 

第 2 步:按州和城市分组并汇总他们筹集的金额(本例中仅 city1):

df = df.groupby(['state', 'city']).sum() 

现在我们有:

                raised 
state   city     
State1  City1   580.0 
        City2   570.0 
        City3   2200.0 
State2  City4   2450.0 
        City5   2270.0 
        City6   1250.0 

请注意我们的索引从数字变为多级。 1 级是州,2 级是城市。

第 3 步:按募集金额排序。正如预期的那样,这忽略了多索引顺序,因此在排序后我们需要重新排序索引。我们只按状态 0 级排序。 :

df.sort_values('raised', ascending=False).sort_index(level=[0], sort_remaining=False).groupby('state').head(3) 

最后我们有:

                raised 
state   city     
State1  City3   2200.0 
        City1   580.0 
        City2   570.0 
State2  City4   2450.0 
        City5   2270.0 
        City6   1250.0