用Python和pandas编写大型数据集到Excel
在使用API从Zendesk产品检索大型数据集之后,您可能希望将数据集移动到Microsoft Excel工作表中,以亚博便更轻松地查看和分析数据。本教程教你如何munge获取API数据并将其写入Excel。数据修改是将数据从一种格式转换或映射到另一种格式的过程,以便能够在另一个工具中使用它。
本教程使用Python 3和熊猫这是一个Python数据分析工具包,广泛用于科学和商业社区。要安装pandas,请参见指令在熊猫网站上。
你还需要OpenPyXL这是一个第三方库,pandas使用它来读写Excel文件。要安装它,请参见指令在OpenPyXL网站上。
本教程涉及的部分:
关于代码示例的说明由于文章的页面宽度,示例中的某些代码行可能会换行到下一行。在复制本教程中的示例时,请忽略换行。在Python中换行很重要。
免责声明:亚博Zendesk提供本文仅用于指导目的。亚博Zendesk不支持或保证代码。亚博Zendesk也不能为Python和pandas等第三方技术提供支持。
从API获取数据
从你想要回答的问题开始。确定需要哪些数据来回答这个问题,然后使用API从Zendesk产品获取数据。亚博想知道怎么做,请看使用Zendesk API和Python获取大型数据集亚博.
对于可以从Zendesk产品检索到的所有可能的数据,请参阅亚博API参考.大多数api都有一个“List”端点来获取多个记录。
假设您检索了一个社区主题中的所有帖子,并加载了撰写这些帖子的用户。Python中生成的数据结构通常反映API返回的JSON数据。在本例中,它将由一个Python字典组成,其中包含一个帖子列表和一个用户列表。列表中的每一项都由一个属性字典组成。例子:
{
“文章”:(
{
“id”:123,
“标题”:“我的打印机着火了!”,
...
},
...
],
“用户”:(
{
“id”:321,
“名字”:“jbloe”,
...
},
...
]
}
还假设将数据结构序列化在名为my_serialized_data.序列化数据结构意味着将其转换为可以存储的格式,然后在同一环境中进行重构。序列化数据比每次需要数据时调用API要有效得多。获取大型数据集可能涉及数百甚至数千个API请求。
在Python中,您可以使用内置的pickle模块序列化和反序列化复杂的数据结构,例如帖子和用户的字典。看到序列化数据以重用它在上面提到的教程中。
在脚本中,第一步是获取API数据并将其分配给一个变量。创建一个名为write_posts.py并粘贴以下代码:
进口熊猫作为pd
主题=pd.read_pickle(“my_serialized_data”)
对象中读取序列化的数据my_serialized_data文件,重组为字典,并赋值给名为主题.
提示代码假定pickle文件与脚本在同一个文件夹中。如果它在另一个文件夹中,则导入内置的操作系统
并使用它来指定文件路径:
进口操作系统
进口熊猫作为pd
主题=pd.read_pickle(操作系统.路径.加入(“. .”,“pickle_jar”,“my_serialized_data”))
修改数据
Excel工作表由二维的行和列表组成。不幸的是,你的数据不是一个整洁的二维结构,可以很容易地写入Excel。它在一个由帖子列表和用户列表组成的字典中。列表中的每一项都由属性字典组成。看到从API获取数据以上为结构。
数据还包括许多您不希望在Excel文件中包含的额外信息。例如,每个记录包含列表中列出的所有属性发布API文档.您只需要每个帖子的以下数据:
- post id
- 职位名称
- 创建日期
- 创建它的人的名字
本节将教您如何将复杂的字典更改为具有4列的二维数据结构。它使用熊猫DataFrames完成工作。DataFrame是pandas中的基本二维数据结构。您可以将其视为电子表格或SQL表。
主题:
本节只触及如何使用pandas来修改数据的表面。要了解更多信息,请参阅熊猫文档.
创建数据框架
将以下行添加到脚本中,以转换您的主题字典变成2个dataframe:
posts_df=pd.DataFrame(主题(“文章”],
列=(“id”,“标题”,“created_at”,“author_id”])
users_df=pd.DataFrame(主题(“用户”],列=(“id”,“名字”])
的DataFrame ()
方法中的列表数据从主题作为它的第一个参数。的列
参数指定列表中要作为列包含在结果DataFrame中的字典的键。有关更多信息,请参见DataFrame在熊猫文档中。
您可以通过添加以下临时打印语句来查看在内存中创建的dataframe:
打印(posts_df)
打印(users_df)
保存文件。在命令行工具中,导航到脚本所在的文件夹,并运行以下命令:
python3 write_posts.py
您的数据应该写入控制台。如果不适合显示宽度,其他列将自动换行。如果您满意一切都按预期工作,则删除临时打印语句。
转换ISO 8601日期字符串
有时你必须清理数据以供分析。例如,API返回的'created_at'日期是ISO 8601格式字符串,如2015 - 08 - 13 t16:38:39z
.OpenPyXL是pandas用来处理Excel文件的库,它将这些日期作为字符串写入Excel。但是,一旦它们在Excel中,您就不能轻松地将字符串重新格式化为日期。
OpenPyXL编写Python日期对象作为Excel中的自定义格式化日期。因此,如果您需要在Excel中使用日期格式进行分析,您可以将'created_at'列中的每个字符串转换为Python日期对象。你可以使用应用()
方法来指定一个Pythonlambda表达式这将修改列的每行中的数据。lambda表达式是一个只有一行的迷你函数。
按照以下方式修改脚本以导入内置组件dateutil.parser
库,然后使用它来转换'created_at'字符串。忽略由右空格引起的换行。语句应该在一行上。
进口dateutil.解析器
...#休息的脚本
posts_df(“created_at”]=posts_df(“created_at”].应用(λx:dateutil.解析器.解析(x).日期())
转换语句的工作原理如下:
表达式
posts_df [' created_at ']
选择DataFrame中的列中的lambda表达式
应用()
方法将列中的每个ISO 8601字符串转换为Python日期
对象。它基本上是说,对于每一行的数据,我将调用x
,将其更改为x
”的dateutil解析器将ISO 8601日期字符串转换为
datetime
对象。接下来,日期()
方法将datetime
成一个日期
对象生成的列被分配回中的'created_at'列posts_df
合并数据框架
的posts_dfDataFrame包含你想写入Excel的大部分数据,包括'id', 'title'和'created_at'列。但是,'author_id'列只列出用户id,而不是实际的用户名。关联的用户名包含在users_df,这是通过使用API对用户进行侧加载而派生的。
在SQL中,您可以使用两个表中的公共键连接两个表,同样,您也可以使用两个dataframe中的公共键合并两个dataframe。DataFrames中的公共键是用户id,在中名为“author_id”posts_df还有'id' inusers_df.
添加以下语句来合并dataframe:
merged_df=pd.合并(posts_df,users_df,如何=“左”,left_on=“author_id”,
right_on=“id”)
的合并()
方法使用用户id作为公共键连接两个dataframe (left_on = ' author_id ' right_on =“id”
).
左合并(如何= '左'
)与SQL中的左连接相同。它返回左DataFrame中的所有行,posts_df,包括在中没有匹配键值的行users_df.
因为你在使用users_df作为一个查找表,确保它没有任何重复的记录。的users_dfDataFrame由来自API的侧载数据组成。侧载数据可能包含重复的记录,因为在分页期间同一记录可能被保存多次。若要删除重复记录,可以修改users_df变量声明如下(高亮显示):
users_df=pd.DataFrame(主题(“用户”],
列=(“id”,“名字”]).drop_duplicates(子集=(“id”])
的drop_duplicates ()
方法查看DataFrame的“id”列中的值,并删除具有重复id的任何行。
合并后进行清理
两个原始dataframe有一个名为“id”的列。合并的DataFrame中不能有两个具有相同名称的列,因此pandas在重叠的列名后添加了'_x'和'_y'后缀。例如,如果您在脚本中添加了以下临时语句:
打印(列表(merged_df))
然后运行脚本,将在控制台中打印以下列名列表:
[' id_x”、“标题”、“created_at ', ' author_id ', ' id_y ', '名字']
由于DataFrame列名称在Excel工作簿中用作列标题,因此您决定将'id_x'列重命名为'post_id',如下所示:
merged_df.重命名(列={“id_x”:“post_id”},原地=真正的)
合并的DataFrame还包括'id_y'和'author_id'列,你不想在你的Excel文件。您可以使用以下语句删除列:
merged_df.下降((“id_y”,“author_id”],轴=1,原地=真正的)
的轴= 1
参数指定您引用的是列,而不是行。
将数据写入Excel
修改完数据后,可以将数据写入Excel,如下所示:
merged_df.to_excel(“topic_posts.xlsx”,指数=假)
打印(“电子表格保存。”)
的指数= False
参数可以防止to_excel ()
方法来为Excel中的行创建标签。
保存文件。在命令行工具中,导航到脚本所在的文件夹,并运行以下命令:
python3 write_posts.py
检查一下topic_posts.xlsx在包含脚本的文件夹中创建文件,并在Excel中打开它。它应该包含您的帖子数据。
代码完成
进口dateutil.解析器
进口熊猫作为pd
主题=pd.read_pickle(“my_serialized_data”)
posts_df=pd.DataFrame(主题(“文章”],列=(“id”,“标题”,“created_at”,“author_id”])
users_df=pd.DataFrame(主题(“用户”],列=(“id”,“名字”]).drop_duplicates(子集=(“id”])
posts_df(“created_at”]=posts_df(“created_at”].应用(λx:dateutil.解析器.解析(x).日期())
merged_df=pd.合并(posts_df,users_df,如何=“左”,left_on=“author_id”,right_on=“id”)
merged_df.重命名(列={“id_x”:“post_id”},原地=真正的)
merged_df.下降((“id_y”,“author_id”],轴=1,原地=真正的)
merged_df.to_excel(“topic_posts.xlsx”,指数=假)
打印(“电子表格保存。”)