pandas 处理excel、csv甚至数据库报表超👍

修改单个元素数据：

for report_index,report_row  in  all_report.iterrows():
    for index,row in pd_frame.iterrows():
        #方式1
        all_report.ix[report_index,'Owner'] = row['实际归属人']#混合方式来修改数据 
        #方式2
        all_report.iloc[index,list(all_report.columns).index('Owner')] = row['实际归属人']#通过第几行，第几列来访问
        #方式1和2两者效果一样

pandas tosql bug

#allindb  is  a DataFrame
pd.io.sql.to_sql(allindb, name='new_vulns_all', con=db, if_exists='append', index=False, chunksize=10000)
异常：如果数据表为空，则会报错。
allindb.to_sql(name='new_vulns_all', con=db, if_exists='append', index=False, chunksize=10000)
在数据表中没有数据存在，也不会报异常。

to_json

df = pd.DataFrame([['a', 'b'], ['c', 'd']],
  index=['row 1', 'row 2'],
  columns=['col 1', 'col 2'])
###########
split
###########
df.to_json(orient='split')
>'{"columns":["col 1","col 2"],
 "index":["row 1","row 2"],
 "data":[["a","b"],["c","d"]]}'
###########
index
###########
df.to_json(orient='index')
>'{"row 1":{"col 1":"a","col 2":"b"},"row 2":{"col 1":"c","col 2":"d"}}'
###########
records
###########
df.to_json(orient='records')
>'[{"col 1":"a","col 2":"b"},{"col 1":"c","col 2":"d"}]'
###########
table
###########
df.to_json(orient='table')
>'{"schema": {"fields": [{"name": "index", "type": "string"},
  {"name": "col 1", "type": "string"},
  {"name": "col 2", "type": "string"}],
 "primaryKey": "index",
 "pandas_version": "0.20.0"},
 "data": [{"index": "row 1", "col 1": "a", "col 2": "b"},
 {"index": "row 2", "col 1": "c", "col 2": "d"}]}'

dataframe常见方法

  • isin
  • str.contains
  • Series.tolist()
  • df.groupby("Family",as_index=False).apply(regexversion) 参考地址
  • df[‘A’].str.extract() # 第一个匹配的字符
  • df[‘A’].str.extractall() # 第一个匹配的字符
  • df.values() #展示df的数据
  • df = pd.read_json(json.dumps(data), orient='records') #[{},{}]格式
  • df.drop('Country', axis=1).join(df['Country'].str.split('/', expand=True).stack().reset_index(level=1, drop=True).rename('Country'))