Convert Django QuerySet to Pandas Dataframe and Maintain Column Order

Question

Given a Django queryset like the following:

qs = A.objects.all().values_list('A', 'B', 'C', 'D', 'E', 'F')

I can convert my qs to a pandas dataframe easily:

df = pd.DataFrame.from_records(qs.values('A', 'B', 'C', 'D', 'E', 'F'))

However, the column order is not maintained. Immediately after conversion I need to specify the new order of columns and I'm not clear why:

df = df.columns['B', 'F', 'C', 'E', 'D', 'A']

Why is this happening and what can I do differently to avoid having to set the dataframe columns explicitly?

Answer 1

qs.values() converts the QuerySet into a dictionary, which is unordered. You are OK with qs.values_list(), which returns a list of tuples.

Try:

df = pd.DataFrame.from_records(
    A.objects.all().values_list('A', 'B', 'C', 'D', 'E', 'F')
)

check the docs about Django's QuerySets

Answer 2

try:

df = pd.DataFrame.from_records("DATA_GOES_HERE", columns=['A','B','C'.. etc.) 

I'm using the columns= parameter found here.

I believe you could also construct the DataFrame by just using pd.DataFrame and put your lists in there with the corresponding column names. This may be more manual work up-front, but if this is for an automated job it could work as well. (may have the ordering issue here again, but can easily be solved by rearranging the columns.. Again, may be more work upfront)