python - How to pass elements of different tags in XML in one line in a Pandas DataFrame?

Question

This may be a noob question, but I have looked at several questions/articles about parsing XML files in Python, and the files used as examples never really look like mine, so I'm a bit stuck. Here's an example of what I have:

<item client="client_A" is_known="yes">
 <sales1 id="sales1_name" check="no"></sales1>
 <sales2 id="sales2_name" check="yes"></sales2>
</item>
<item client="client_B" is_known="no">
 <sales1 id="sales1_name" check="yes"></sales1>
 <sales2 id="sales2_name" check="yes"></sales2>
</item>
<item client="client_C" is_known="yes">
 <sales2 id="sales2_name" check="yes"></sales2>
 <sales3 id="sales3_name" check="no"></sales3>
</item>

The main part of the file is just a series of item tags, with attributes, and other tags nested in them. What I would like to do is transform it in a pandas DataFrame in Python, but I would like all of the info inside the item tags to be on line, e.g.:

client	is_known	sales_id	sales2_id
client_A	yes	sales1_name	sales2_name

question from:https://stackoverflow.com/questions/65928535/how-to-pass-elements-of-different-tags-in-xml-in-one-line-in-a-pandas-dataframe

Answer 1

You can try something along these lines:

items = root.findall('.//item')
rows = []
for item in items:
    row = []
    row.extend([item.attrib['client'],item.attrib['is_known']])
    for sale in item.findall('.//*'):
        row.append(sale.attrib['id'])
    rows.append(row)
pd.DataFrame(rows,columns=df_cols)

Output:

    client  is_known    sales1_id   sales2_id
0   client_A    yes     sales1_name     sales2_name
1   client_B    no  sales1_name     sales2_name
2   client_C    yes     sales2_name     sales3_name