How can I convert WIQL query output into a structured data frame?

Question

I have managed to connect to Azure DevOps via their API (thanks to this question that was raised) , and managed to get the output required detailing all work items recorded. However, I would like to convert the output into a dataframe.

def print_work_items(work_items):
    for work_item in work_items:
        print(
            "{0} {1}: {2}".format(
                work_item.fields["System.WorkItemType"],
                work_item.id,
                work_item.fields["System.Title"],
            )
        )


wit_client = connection.clients.get_work_item_tracking_client()


def get_TC_from_query(query):
    query_wiql = Wiql(query=query)
    results = wit_client.query_by_wiql(query_wiql).work_items
    # WIQL query gives a WorkItemReference => we get the corresponding WorkItem from id
    work_items = (wit_client.get_work_item(int(result.id)) for result in results)
    #print_work_items(work_items)
   
    
dq_workitems = get_TC_from_query(
    """\
        SELECT
                [System.Id],
                [System.WorkItemType],
                [System.Title],
                [System.State],
                [System.AreaPath],
                [System.AssignedTo]
        FROM workitems
        WHERE
                [System.AreaPath] = 'Designated Area Path'
        ORDER BY [System.Id] DESC
        """
        )


df = pd.DataFrame(print_work_items(dq_workitems), columns=['Id','WorkItemType','Title',
                                         'State','AreaPath','AssignedTo']) 
print(df)

I am able to print out the work items onto the console but fail to populate the dataframe. Any thoughts/leads would be highly appreciated!

Hugh Lin · Answer 1 · 2020-11-17T01:43:06.100

We can try to write Wiql query output to a .csv file first, and then convert the csv file to data frame.

Write to .csv file:

# Create the csv file
with open('vehicle.csv', 'w', newline='') as f_handle:
    writer = csv.writer(f_handle)
    # Add the header/column names
    header = ['id', 'Title', 'State', 'WorkItemType']
    writer.writerow(header)
    # Iterate over `data`  and  write to the csv file
    for row in data:
        writer.writerow(row)

Convert csv file to dataframe:

Use pd.read_csv() to convert a .csv file to a pandas dataframe.

Call pd.read_csv(file) with the path name of a .csv file as file to return a DataFrame with the .csv data.

Here are some reference you can refer to:

score 1 · Accepted Answer · edited Dec 30 '20 at 19:56

The following steps worked for me:

I converted each row to a data frame.
concatenated the list of data frames.
exported the list as a CSV.

work_items = (
    wit_client.get_work_item(int(res.id)) for res in wiql_results
)
workitems_df_list: List[DataFrame] = []

for work_item in work_items:
    row = "{0},{1},{2},{3},{4},{5},{6}".format(
        work_item.id,
        work_item.fields["System.TeamProject"],
        work_item.fields["System.WorkItemType"],
        work_item.fields["System.Title"],
        work_item.fields["System.State"],
        work_item.fields["Microsoft.VSTS.Common.Priority"],
        work_item.fields["System.CreatedDate"]
    )
    workitems_df_list.append(pd.DataFrame(list(reader([row]))))

workitems_final_df = pd.concat(workitems_df_list)
workitems_final_df.to_csv(r'C:\test\workitems_test.csv', sep=',')

How can I convert WIQL query output into a structured data frame?

2 Answers2