Python 3.x - 创建数据框并指定列名称

Question

我是 Pandas 新手。我必须创建一个数据框，其中一列是“来源”，第二列是“金额”。

创建了一个新的数据框

df=[]

现在我如何向此数据框添加“来源”和“金额”列。最终结果是

打印(df)

Source  Amount
S1      10
S2      12
S3       8
S4      5

数据将来自 for 循环。每次迭代都会生成源，然后生成金额。我想创建一个像 -

的数据框

df=[]

for str in some_variable: 
    df['Source'].append(str[0]) #str[0] will contain source elements 
    df['Amount'].append(str[1])  #str[1] will contain amount elements

代码 -

import requests
import pandas as pd
import bs4
import string
import matplotlib.pyplot as plt
url = "https://www.revisor.mn.gov/laws/?year=2014&type=0&doctype=Chapter&id=294"

result = requests.get(url)
soup = bs4.BeautifulSoup(result.content)
summary = soup.find("div", {"class":"bill_section","id": "laws.1.1.0"})
tables = summary.findAll('table')
data_table = tables[1]

df=pd.DataFrame(['Source','Amount'])  #Trying to incorrectly add columns to df
for row in data_table.findAll("tr"):
    cells = row.findAll("td")
    try :
        for char in cells[0].findAll("ins"):
             df['Source'] = df['Source'].append()   #This is where the issue is
        for char in cells[2].findAll("ins"):
             df['Amount'] = df['Amount'].append()   #And here
    except:
        pass

Answer 1

我认为你可以使用read_html与 replace , str.strip , str.replace最后to_numeric :

import pandas as pd
import matplotlib.pyplot as plt
url = "https://www.revisor.mn.gov/laws/?year=2014&type=0&doctype=Chapter&id=294"

#read second table in url
df = pd.read_html(url)[1]
#replace texts to empty string
df = df.replace('new text begin','', regex=True).replace('new text end','', regex=True)
#set new columns names
df.columns = ['Source','b','Amount']
#remove first row
df = df[1:]
#remove second column b
df = df.drop('b', axis=1)
#strip whitespaces
df.Source = df.Source.str.strip()
#strip whitespaces and remove (),
df.Amount = df.Amount.str.strip().str.replace(r'[(),]','')
#convert column Amount to numeric
df.Amount = pd.to_numeric(df.Amount)
#reset index
df = df.reset_index(drop=True)

print df
                                             Source     Amount
0                           University of Minnesota  119367000
1         Minnesota State Colleges and Universities  159812000
2                                         Education    7491000
3                         Minnesota State Academies   11354000
4                 Perpich Center for Arts Education    2000000
5                                 Natural Resources   63480000
6                          Pollution Control Agency    2625000
7                 Board of Water and Soil Resources    8000000
8                                       Agriculture     203000
9                                 Zoological Garden   12000000
10                                   Administration  127000000
11              Minnesota Amateur Sports Commission    7973000
12                                 Military Affairs    3244000
13                                    Public Safety    4030000
14                                   Transportation   57263000
15                             Metropolitan Council   45968000
16                                   Human Services   86387000
17                                 Veterans Affairs    2800000
18                                      Corrections   11881000
19              Employment and Economic Development   92130000
20                      Public Facilities Authority   45993000
21                           Housing Finance Agency   20000000
22                     Minnesota Historical Society   12002000
23                               Bond Sale Expenses     900000
24                                    Cancellations   10849000
25                                            TOTAL  893054000
26   Bond Proceeds Fund (General Fund Debt Service)  814745000
27  Bond Proceeds Fund (User Financed Debt Service)   39104000
28                        State Transportation Fund   36613000
29                  Maximum Effort School Loan Fund    5491000
30                               Trunk Highway Fund    7950000
31                      Bond Proceeds Cancellations   10849000

print df.dtypes

Source    object
Amount     int64
dtype: object

但是，如果您需要使用 BeautifulSoup 解析数据的解决方案，首先将 Source 和 Amount 添加数据，然后创建 DataFrame:

import requests
import pandas as pd
import bs4
import string
import matplotlib.pyplot as plt
url = "https://www.revisor.mn.gov/laws/?year=2014&type=0&doctype=Chapter&id=294"

result = requests.get(url)
soup = bs4.BeautifulSoup(result.content)
summary = soup.find("div", {"class":"bill_section","id": "laws.1.1.0"})
tables = summary.findAll('table')
data_table = tables[1]

Source, Amount = [], []
for row in data_table.findAll("tr"):
    cells = row.findAll("td")
    try :
        for char in cells[0].findAll("ins"):
             Source.append(char.text)   #This is where the issue is
        for char in cells[2].findAll("ins"):
             Amount.append(char.text)   #And here
    except:
        pass

print Source
[u'SUMMARY', u'University of Minnesota', u'Minnesota State Colleges and Universities', u'Education', u'Minnesota State Academies', u'Perpich Center for Arts Education', u'Natural Resources', u'Pollution Control Agency', u'Board of Water and Soil Resources', u'Agriculture', u'Zoological Garden', u'Administration', u'Minnesota Amateur Sports Commission', u'Military Affairs', u'Public Safety', u'Transportation', u'Metropolitan Council', u'Human Services', u'Veterans Affairs', u'Corrections', u'Employment and Economic Development', u'Public Facilities Authority', u'Housing Finance Agency', u'Minnesota Historical Society', u'Bond Sale Expenses', u'Cancellations', u'TOTAL', u'Bond Proceeds Fund (General Fund Debt Service)', u'Bond Proceeds Fund (User Financed Debt Service)', u'State Transportation Fund', u'Maximum Effort School Loan Fund', u'Trunk Highway Fund', u'Bond Proceeds Cancellations']
print Amount  
[u'119,367,000', u'159,812,000', u'7,491,000', u'11,354,000', u'2,000,000', u'63,480,000', u'2,625,000', u'8,000,000', u'203,000', u'12,000,000', u'127,000,000', u'7,973,000', u'3,244,000', u'4,030,000', u'57,263,000', u'45,968,000', u'86,387,000', u'2,800,000', u'11,881,000', u'92,130,000', u'45,993,000', u'20,000,000', u'12,002,000', u'900,000', u'(10,849,000)', u'893,054,000', u'814,745,000', u'39,104,000', u'36,613,000', u'5,491,000', u'7,950,000', u'(10,849,000)']

print len(Source)
#33
print len(Amount)
#32
#remove first element
Source = Source[1:]

df=pd.DataFrame({'Source':Source,'Amount':Amount}, columns=['Source','Amount'])    

print df
                                             Source        Amount
0                           University of Minnesota   119,367,000
1         Minnesota State Colleges and Universities   159,812,000
2                                         Education     7,491,000
3                         Minnesota State Academies    11,354,000
4                 Perpich Center for Arts Education     2,000,000
5                                 Natural Resources    63,480,000
6                          Pollution Control Agency     2,625,000
7                 Board of Water and Soil Resources     8,000,000
8                                       Agriculture       203,000
9                                 Zoological Garden    12,000,000
10                                   Administration   127,000,000
11              Minnesota Amateur Sports Commission     7,973,000
12                                 Military Affairs     3,244,000
13                                    Public Safety     4,030,000
14                                   Transportation    57,263,000
15                             Metropolitan Council    45,968,000
16                                   Human Services    86,387,000
17                                 Veterans Affairs     2,800,000
18                                      Corrections    11,881,000
19              Employment and Economic Development    92,130,000
20                      Public Facilities Authority    45,993,000
21                           Housing Finance Agency    20,000,000
22                     Minnesota Historical Society    12,002,000
23                               Bond Sale Expenses       900,000
24                                    Cancellations  (10,849,000)
25                                            TOTAL   893,054,000
26   Bond Proceeds Fund (General Fund Debt Service)   814,745,000
27  Bond Proceeds Fund (User Financed Debt Service)    39,104,000
28                        State Transportation Fund    36,613,000
29                  Maximum Effort School Loan Fund     5,491,000
30                               Trunk Highway Fund     7,950,000
31                      Bond Proceeds Cancellations  (10,849,000)