My DataFrame is layed out incorrectly

[dbyers]

Hi,

I am creating a dataframe from some records in a database, but the data is nested incorrectly. When I export to Excel, the data is shown as [‘data1’],[‘data2’] etc.

I’m relatively new to Python, but I’m not sure what I’ve done wrong here.

The code is as follows:

        iid=[]
        iSeries=[]
        iCountry=[]
        iNumber=[]
        iName=[]
        iAttribute=[]
        iUserswithitemcount=[]
        iUserswithitem=[]

        
        for section in sections:
            needed_items = run_query("SELECT `CardID` from `" + st.session_state.username + "_cards` WHERE Section = '" + section[0] + "' AND Need = 'True';")
            
            for needed_item in needed_items:
                needed_item_details=run_query("SELECT `ItemID`,`Series`,`Country`,`Number`,`Name`,`attribute` from `" + section[0] + "` WHERE ItemID = '" + needed_item[0] + "';")
                iid.append([i[0] for i in needed_item_details])
                iSeries.append([i[1] for i in needed_item_details])
                iCountry.append([i[2] for i in needed_item_details])
                iNumber.append([i[3] for i in needed_item_details])
                iName.append([i[4] for i in needed_item_details])
                iAttribute.append([i[5] for i in needed_item_details])
                iUserswithitemcount.append(len(get_users_with_item(needed_item[0])))

        def load_details_data():
            return pd.DataFrame(
                {
                    "ID": iid,
                    "Series": iSeries,
                    "Country": iCountry,
                    "Attribute" : iAttribute,
                    "Number": iNumber,
                    "Name": iName,
                    "Other Users With Spares": iUserswithitemcount,
                }
            )
        st.write(load_details_data())

The dataframe looks like this:

And the Excel sheet looks like this:

[Goyo]

Both look the same to me. The ovals in the dataframe are used to show elements of a sequence.

[dbyers]

Ok, but I don’t want the [‘’] surrounding each piece of data in each column in the Excel sheet.

[mathcatsand]

Are you maybe not intending each cell to contain a list? Are you instead wanting each cell to be populated from a single string?

[dbyers]

Yes that’s exactly right, a single string, not a list.

[mathcatsand]

@dbyers

iSeries.append([i[1] for i in needed_item_details])

Each of the lines like this is adding a list to a list.

I’m guessing that needed_item_details is predictably returning a single row of data, so maybe you want:

iSeries.append(needed_item_details[0][1])

(Syntax may vary depending on the exact data type that run_query outputs, but basically you don’t want a for each loop in needed_item_details since you only expect one.)

[dbyers]

Thank you so much, I feel a bit dumb that the answer was so simple.