Search code examples
python-3.xpandaspandasql

Pandas dataframe transpose with column name instead of index

I can't seem to figure out how to show actual column name in json after dataframe has been transposed. Any thoughts please?

from pandasql import *
import pandas as pd

pysqldf = lambda q: sqldf(q, globals())

q1 = """
SELECT
 beef as beef, veal as veal, pork as pork, lamb_and_mutton as lamb
FROM
 meat m
LIMIT 3;
"""

meat = load_meat()

df = pysqldf(q1)

#df = df.reset_index(drop=True)
#print(df.T.to_json(orient='records'))

df1 = df.T.reset_index(drop=True)
df1.columns = range(len(df1.columns))
print(df.T.to_json(orient='records'))

Output

[{"0":751.0,"1":713.0,"2":741.0},{"0":85.0,"1":77.0,"2":90.0},{"0":1280.0,"1":1169.0,"2":1128.0},{"0":89.0,"1":72.0,"2":75.0}]

Expected Output

[ { "0": "beef", "1": 751, "2": 713, "3": 741},{"0": "veal", "1": 85, "2": 77, "3": 90 },{"0": "pork", "1": 1280, "2": 1169, "3": 1128},{ "0": "lamb", "1": 89, "2": 72, "3": 75    }]
Solution

  • Try this:

    Where df:

       beef  veal  pork  lamb
0     0     1     2     3
1     4     5     6     7
2     8     9    10    11

    Use T, reset_index, and set_axis:

    df.T.reset_index()\
  .set_axis(range(len(df.columns)), axis=1, inplace=False)\
  .to_json(orient='records')

    Output:

    '[{"0":"beef","1":0,"2":4,"3":8},{"0":"veal","1":1,"2":5,"3":9},{"0":"pork","1":2,"2":6,"3":10},{"0":"lamb","1":3,"2":7,"3":11}]'