Json to Pandas Table in Python

Question

Below is json from a web API. I wish to load it into a python pandas tabular dataframe so that I can pivot in python.

I am stuck reading the json. I am not been able to translate json format to tabular format.

Please assist me with a starting point. I have looked into many json to tabular videos with no success in my Python 3.6 Shell.

How do I load json into a pandas.DataFrame?

Below is the json format from web API:

{"success": true,"SalesData" :[ { "InvoiceNo":"S42694","ReceiptNo":"SR39258","GuestName":"ABC Ed","GuestCode":"","SoldOn":"2014-04-07 12:17:48","ServicedOn":"2014-04-07 10:00:00","ItemType":"0","ItemCode":"HS00018","ServiceName":"Wash & Blow - Dry Long Hair","Quantity":"1","UnitPrice":"90","SalePrice":"90","Discount":"0","ServiceTax":"11.24","TotalTax":"11.24","TaxCode":"abc_Service Tax_svc","LoyaltyPointRedemption":"0","MembershipRedemption":"0","PrepaidCardRedemption":"0","CashbackRedemption":"0","PackageRedemption":"0","Cash":"0","Card":"1012","Check":"0","Custom":"0","Points":"0","MembershipPaid":"0","PrepaidCard":"0","CenterCode":"","LastPaymentDate":"2014-04-07 12:20:28","InvoiceItemId":"1d41a266-e2a2-4c40-89c0-35d0e57cd83d","RoundingAdjustment":"0.76","Tips":"0","EmployeeName":"ABC FG","EmployeeCode":"7","EmployeeJobCode":"J0001","Tags":"","GuestId":"7d11090e-c330-4ec3-937c-98fe5139062b","FinalSalePrice":"101.24","Promotion":"","CouponPrinted":"","FirstVisit":"No","Package":"","PackageInvoice":"","CenterName":"ABC","Due":"0","PaymentType":"Visa","BUName":"brick"}, { "InvoiceNo":"S42695","ReceiptNo":"SR39264","GuestName":"ABC XYZ","GuestCode":"","SoldOn":"2014-04-07 12:30:30","ServicedOn":"2014-04-07 10:00:00","ItemType":"0","ItemCode":"SS00162","ServiceName":"Mica","Quantity":"1","UnitPrice":"50","SalePrice":"50","Discount":"100","ServiceTax":"4.44","TotalTax":"4.44","TaxCode":"Gurgaon_Service Tax_svc","LoyaltyPointRedemption":"0","MembershipRedemption":"0","PrepaidCardRedemption":"0","CashbackRedemption":"0","PackageRedemption":"0","Cash":"0","Card":"449.4741","Check":"0","Custom":"0","Points":"0","MembershipPaid":"0","PrepaidCard":"0","CenterCode":"","LastPaymentDate":"2014-04-07 14:08:26","InvoiceItemId":"4f501646-9619-4677-86d6-44bf376e3959","RoundingAdjustment":"0.0341840592193034","Tips":"0","EmployeeName":"ABC","EmployeeCode":"12345","EmployeeJobCode":"J0009","Tags":"","GuestId":"abd3695f-efd0-4a9e-9692-91eb6ce84c3b","FinalSalePrice":"449.44","Promotion":"Campaign: Campaign: ABC connection - non facial service (20% off)","CouponPrinted":"","FirstVisit":"No","Package":"","PackageInvoice":"","CenterName":"ABC","Due":"0","PaymentType":"Visa","BUName":"Brick"}

elelias · Accepted Answer · 2017-03-04 14:18:25Z

your issue is that your tabular data is inside a a key. I presume you want this:

sales_df = pd.DataFrame(your_json_dict['SalesData'])

That parses the json correctly and places the keys as column names and the values as column values.

This works:

j = {"success": "true","SalesData" :[ { "InvoiceNo":"S42694","ReceiptNo":"SR39258","GuestName":"ABC Ed","GuestCode":"","SoldOn":"2014-04-07 12:17:48","ServicedOn":"2014-04-07 10:00:00","ItemType":"0","ItemCode":"HS00018","ServiceName":"Wash & Blow - Dry Long Hair","Quantity":"1","UnitPrice":"90","SalePrice":"90","Discount":"0","ServiceTax":"11.24","TotalTax":"11.24","TaxCode":"abc_Service Tax_svc","LoyaltyPointRedemption":"0","MembershipRedemption":"0","PrepaidCardRedemption":"0","CashbackRedemption":"0","PackageRedemption":"0","Cash":"0","Card":"1012","Check":"0","Custom":"0","Points":"0","MembershipPaid":"0","PrepaidCard":"0","CenterCode":"","LastPaymentDate":"2014-04-07 12:20:28","InvoiceItemId":"1d41a266-e2a2-4c40-89c0-35d0e57cd83d","RoundingAdjustment":"0.76","Tips":"0","EmployeeName":"ABC FG","EmployeeCode":"7","EmployeeJobCode":"J0001","Tags":"","GuestId":"7d11090e-c330-4ec3-937c-98fe5139062b","FinalSalePrice":"101.24","Promotion":"","CouponPrinted":"","FirstVisit":"No","Package":"","PackageInvoice":"","CenterName":"ABC","Due":"0","PaymentType":"Visa","BUName":"brick"}, { "InvoiceNo":"S42695","ReceiptNo":"SR39264","GuestName":"ABC XYZ","GuestCode":"","SoldOn":"2014-04-07 12:30:30","ServicedOn":"2014-04-07 10:00:00","ItemType":"0","ItemCode":"SS00162","ServiceName":"Mica","Quantity":"1","UnitPrice":"50","SalePrice":"50","Discount":"100","ServiceTax":"4.44","TotalTax":"4.44","TaxCode":"Gurgaon_Service Tax_svc","LoyaltyPointRedemption":"0","MembershipRedemption":"0","PrepaidCardRedemption":"0","CashbackRedemption":"0","PackageRedemption":"0","Cash":"0","Card":"449.4741","Check":"0","Custom":"0","Points":"0","MembershipPaid":"0","PrepaidCard":"0","CenterCode":"","LastPaymentDate":"2014-04-07 14:08:26","InvoiceItemId":"4f501646-9619-4677-86d6-44bf376e3959","RoundingAdjustment":"0.0341840592193034","Tips":"0","EmployeeName":"ABC","EmployeeCode":"12345","EmployeeJobCode":"J0009","Tags":"","GuestId":"abd3695f-efd0-4a9e-9692-91eb6ce84c3b","FinalSalePrice":"449.44","Promotion":"Campaign: Campaign: ABC connection - non facial service (20% off)","CouponPrinted":"","FirstVisit":"No","Package":"","PackageInvoice":"","CenterName":"ABC","Due":"0","PaymentType":"Visa","BUName":"Brick"}]}

df = pd.DataFrame(j['SalesData'])

Hi Elelias, Thank you for your response, but i get "TypeError: string indices must be integers" with following code:sales_df = pd.DataFrame(url['SalesData'])
I tested the code with the example above and it works for me. See my edit.

Collectives™ on Stack Overflow

Json to Pandas Table in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related