0

I have a csv file I want to convert it to json. I tried my best but I could not get it. The csv is like this.

Income Statement,24/09/16,30/09/17,29/09/18,28/09/19,26/09/20,25/09/21,LTM
,,,,,,,
Revenues,,229234.00,265595.00,260174.00,274515.00,365817.00,378323.00
Total Revenues,,229234.00,265595.00,260174.00,274515.00,365817.00,378323.00
% Change YoY,,,15.9%,(2.0%),5.5%,33.3%,
Cost of Goods Sold,,"(141,048.00)","(163,756.00)","(161,782.00)","(169,559.00)","(212,981.00)","(215,572.00)"
Gross Profit,,88186.00,101839.00,98392.00,104956.00,152836.00,162751.00
% Change YoY,,,15.5%,(3.4%),6.7%,45.6%,
% Gross Margins,,38.5%,38.3%,37.8%,38.2%,41.8%,43.0%
Selling General & Admin Expenses,,"(15,261.00)","(16,705.00)","(18,245.00)","(19,916.00)","(21,973.00)","(22,791.00)"
R&D Expenses,,"(11,581.00)","(14,236.00)","(16,217.00)","(18,752.00)","(21,914.00)","(23,057.00)"
Other Operating Expenses,,"(26,842.00)","(30,941.00)","(34,462.00)","(38,668.00)","(43,887.00)","(45,848.00)"
Operating Income,,61344.00,70898.00,63930.00,66288.00,108949.00,116903.00
% Change YoY,,,15.6%,(9.8%),3.7%,64.4%,

What I want is a bit complicated

{"income_statement":
        {"24/9/16":
             {"Revenues(0)":"","Total Revenues(1)":"","   % Change YoY(2)":"","Cost of Goods Sold(3)":"","Gross Profit(4)":"","   % Change YoY(5)":"","   % Gross Margins(6)":"","Selling General & Admin Expenses(7)":"","R&D Expenses(8)":"","Other Operating Expenses(9)":"","Operating Income(10)":"","   % Change YoY(11)":"","   % Operating Margins(12)":"","Interest Expense(13)":"","Interest And Investment Income(14)":"","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"","EBT Excl. Unusual Items(17)":"","EBT Incl. Unusual Items(18)":"","Income Tax Expense(19)":"","Earnings From Continuing Operations(20)":"","Net Income to Company(21)":"","Net Income(22)":"","Net Income to Common Incl Extra Items(23)":"","   % Net Income to Common Incl Extra Items Margins(24)":"","Net Income to Common Excl. Extra Items(25)":"","   % Net Income to Common Excl. Extra Items Margins(26)":"","Diluted EPS Excl Extra Items(27)":"","   % Change YoY(28)":"","Weighted Average Diluted Shares Outstanding(29)":"","   % Change YoY(30)":"","Weighted Average Basic Shares Outstanding(31)":"","   % Change YoY(32)":"","Dividends Per Share(33)":"","   % Change YoY(34)":"","Payout Ratio %(35)":"","Basic EPS(36)":"","EBITDA(37)":"","   % Change YoY(38)":"","EBITDAR(39)":"","R&D Expense(40)":"","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":""},
        "30/9/17":
             {"Revenues(0)":"229.234,00","Total Revenues(1)":"229.234,00","   % Change YoY(2)":"","Cost of Goods Sold(3)":"(141.048,00)","Gross Profit(4)":"88.186,00","   % Change YoY(5)":"","   % Gross Margins(6)":"38,5 %","Selling General & Admin Expenses(7)":"(15.261,00)","R&D Expenses(8)":"(11.581,00)","Other Operating Expenses(9)":"(26.842,00)","Operating Income(10)":"61.344,00","   % Change YoY(11)":"","   % Operating Margins(12)":"26,8 %","Interest Expense(13)":"(2323,00)","Interest And Investment Income(14)":"5201,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"(133,00)","EBT Excl. Unusual Items(17)":"64.089,00","EBT Incl. Unusual Items(18)":"64.089,00","Income Tax Expense(19)":"(15.738,00)","Earnings From Continuing Operations(20)":"48.351,00","Net Income to Company(21)":"48.351,00","Net Income(22)":"48.351,00","Net Income to Common Incl Extra Items(23)":"48.351,00","   % Net Income to Common Incl Extra Items Margins(24)":"21,1 %","Net Income to Common Excl. Extra Items(25)":"48.351,00","   % Net Income to Common Excl. Extra Items Margins(26)":"21,1 %","Diluted EPS Excl Extra Items(27)":"2,30","   % Change YoY(28)":"","Weighted Average Diluted Shares Outstanding(29)":"21.006,77","   % Change YoY(30)":"","Weighted Average Basic Shares Outstanding(31)":"20.868,97","   % Change YoY(32)":"","Dividends Per Share(33)":"0,60","   % Change YoY(34)":"","Payout Ratio %(35)":"26,4 %","Basic EPS(36)":"2,32","EBITDA(37)":"71.501,00","   % Change YoY(38)":"","EBITDAR(39)":"72.601,00","R&D Expense(40)":"11.581,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"24,6 %"},
        "29/9/18":
             {"Revenues(0)":"265.595,00","Total Revenues(1)":"265.595,00","   % Change YoY(2)":"15,9 %","Cost of Goods Sold(3)":"(163.756,00)","Gross Profit(4)":"101.839,00","   % Change YoY(5)":"15,5 %","   % Gross Margins(6)":"38,3 %","Selling General & Admin Expenses(7)":"(16.705,00)","R&D Expenses(8)":"(14.236,00)","Other Operating Expenses(9)":"(30.941,00)","Operating Income(10)":"70.898,00","   % Change YoY(11)":"15,6 %","   % Operating Margins(12)":"26,7 %","Interest Expense(13)":"(3240,00)","Interest And Investment Income(14)":"5686,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"(441,00)","EBT Excl. Unusual Items(17)":"72.903,00","EBT Incl. Unusual Items(18)":"72.903,00","Income Tax Expense(19)":"(13.372,00)","Earnings From Continuing Operations(20)":"59.531,00","Net Income to Company(21)":"59.531,00","Net Income(22)":"59.531,00","Net Income to Common Incl Extra Items(23)":"59.531,00","   % Net Income to Common Incl Extra Items Margins(24)":"22,4 %","Net Income to Common Excl. Extra Items(25)":"59.531,00","   % Net Income to Common Excl. Extra Items Margins(26)":"22,4 %","Diluted EPS Excl Extra Items(27)":"2,98","   % Change YoY(28)":"29,4 %","Weighted Average Diluted Shares Outstanding(29)":"20.000,44","   % Change YoY(30)":"(4,8 %)","Weighted Average Basic Shares Outstanding(31)":"19.821,51","   % Change YoY(32)":"(5,0 %)","Dividends Per Share(33)":"0,68","   % Change YoY(34)":"13,3 %","Payout Ratio %(35)":"23,0 %","Basic EPS(36)":"3,00","EBITDA(37)":"81.801,00","   % Change YoY(38)":"14,4 %","EBITDAR(39)":"83.001,00","R&D Expense(40)":"14.236,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"18,3 %"},
        "28/9/19":
             {"Revenues(0)":"260.174,00","Total Revenues(1)":"260.174,00","   % Change YoY(2)":"(2,0 %)","Cost of Goods Sold(3)":"(161.782,00)","Gross Profit(4)":"98.392,00","   % Change YoY(5)":"(3,4 %)","   % Gross Margins(6)":"37,8 %","Selling General & Admin Expenses(7)":"(18.245,00)","R&D Expenses(8)":"(16.217,00)","Other Operating Expenses(9)":"(34.462,00)","Operating Income(10)":"63.930,00","   % Change YoY(11)":"(9,8 %)","   % Operating Margins(12)":"24,6 %","Interest Expense(13)":"(3576,00)","Interest And Investment Income(14)":"4961,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"422,00","EBT Excl. Unusual Items(17)":"65.737,00","EBT Incl. Unusual Items(18)":"65.737,00","Income Tax Expense(19)":"(10.481,00)","Earnings From Continuing Operations(20)":"55.256,00","Net Income to Company(21)":"55.256,00","Net Income(22)":"55.256,00","Net Income to Common Incl Extra Items(23)":"55.256,00","   % Net Income to Common Incl Extra Items Margins(24)":"21,2 %","Net Income to Common Excl. Extra Items(25)":"55.256,00","   % Net Income to Common Excl. Extra Items Margins(26)":"21,2 %","Diluted EPS Excl Extra Items(27)":"2,97","   % Change YoY(28)":"(0,3 %)","Weighted Average Diluted Shares Outstanding(29)":"18.595,65","   % Change YoY(30)":"(7,0 %)","Weighted Average Basic Shares Outstanding(31)":"18.471,34","   % Change YoY(32)":"(6,8 %)","Dividends Per Share(33)":"0,75","   % Change YoY(34)":"10,3 %","Payout Ratio %(35)":"25,6 %","Basic EPS(36)":"2,99","EBITDA(37)":"76.477,00","   % Change YoY(38)":"(6,5 %)","EBITDAR(39)":"77.777,00","R&D Expense(40)":"16.217,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"15,9 %"},
        "26/9/20":
             {"Revenues(0)":"274.515,00","Total Revenues(1)":"274.515,00","   % Change YoY(2)":"5,5 %","Cost of Goods Sold(3)":"(169.559,00)","Gross Profit(4)":"104.956,00","   % Change YoY(5)":"6,7 %","   % Gross Margins(6)":"38,2 %","Selling General & Admin Expenses(7)":"(19.916,00)","R&D Expenses(8)":"(18.752,00)","Other Operating Expenses(9)":"(38.668,00)","Operating Income(10)":"66.288,00","   % Change YoY(11)":"3,7 %","   % Operating Margins(12)":"24,1 %","Interest Expense(13)":"(2873,00)","Interest And Investment Income(14)":"3763,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"(87,00)","EBT Excl. Unusual Items(17)":"67.091,00","EBT Incl. Unusual Items(18)":"67.091,00","Income Tax Expense(19)":"(9680,00)","Earnings From Continuing Operations(20)":"57.411,00","Net Income to Company(21)":"57.411,00","Net Income(22)":"57.411,00","Net Income to Common Incl Extra Items(23)":"57.411,00","   % Net Income to Common Incl Extra Items Margins(24)":"20,9 %","Net Income to Common Excl. Extra Items(25)":"57.411,00","   % Net Income to Common Excl. Extra Items Margins(26)":"20,9 %","Diluted EPS Excl Extra Items(27)":"3,28","   % Change YoY(28)":"10,4 %","Weighted Average Diluted Shares Outstanding(29)":"17.528,21","   % Change YoY(30)":"(5,7 %)","Weighted Average Basic Shares Outstanding(31)":"17.352,12","   % Change YoY(32)":"(6,1 %)","Dividends Per Share(33)":"0,80","   % Change YoY(34)":"6,0 %","Payout Ratio %(35)":"24,5 %","Basic EPS(36)":"3,31","EBITDA(37)":"77.344,00","   % Change YoY(38)":"1,1 %","EBITDAR(39)":"88.144,00","R&D Expense(40)":"18.752,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"14,4 %"},
        "25/9/21":
            {"Revenues(0)":"365.817,00","Total Revenues(1)":"365.817,00","   % Change YoY(2)":"33,3 %","Cost of Goods Sold(3)":"(212.981,00)","Gross Profit(4)":"152.836,00","   % Change YoY(5)":"45,6 %","   % Gross Margins(6)":"41,8 %","Selling General & Admin Expenses(7)":"(21.973,00)","R&D Expenses(8)":"(21.914,00)","Other Operating Expenses(9)":"(43.887,00)","Operating Income(10)":"108.949,00","   % Change YoY(11)":"64,4 %","   % Operating Margins(12)":"29,8 %","Interest Expense(13)":"(2645,00)","Interest And Investment Income(14)":"2843,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"60,00","EBT Excl. Unusual Items(17)":"109.207,00","EBT Incl. Unusual Items(18)":"109.207,00","Income Tax Expense(19)":"(14.527,00)","Earnings From Continuing Operations(20)":"94.680,00","Net Income to Company(21)":"94.680,00","Net Income(22)":"94.680,00","Net Income to Common Incl Extra Items(23)":"94.680,00","   % Net Income to Common Incl Extra Items Margins(24)":"25,9 %","Net Income to Common Excl. Extra Items(25)":"94.680,00","   % Net Income to Common Excl. Extra Items Margins(26)":"25,9 %","Diluted EPS Excl Extra Items(27)":"5,61","   % Change YoY(28)":"71,0 %","Weighted Average Diluted Shares Outstanding(29)":"16.864,92","   % Change YoY(30)":"(3,8 %)","Weighted Average Basic Shares Outstanding(31)":"16.701,27","   % Change YoY(32)":"(3,8 %)","Dividends Per Share(33)":"0,85","   % Change YoY(34)":"6,9 %","Payout Ratio %(35)":"15,3 %","Basic EPS(36)":"5,67","EBITDA(37)":"120.233,00","   % Change YoY(38)":"55,5 %","EBITDAR(39)":"134.833,00","R&D Expense(40)":"21.914,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"13,3 %"},
        "LTM":
            {"Revenues(0)":"378.323,00","Total Revenues(1)":"378.323,00","   % Change YoY(2)":"","Cost of Goods Sold(3)":"(215.572,00)","Gross Profit(4)":"162.751,00","   % Change YoY(5)":"","   % Gross Margins(6)":"43,0 %","Selling General & Admin Expenses(7)":"(22.791,00)","R&D Expenses(8)":"(23.057,00)","Other Operating Expenses(9)":"(45.848,00)","Operating Income(10)":"116.903,00","   % Change YoY(11)":"","   % Operating Margins(12)":"30,9 %","Interest Expense(13)":"(2701,00)","Interest And Investment Income(14)":"2746,00","Currency Exchange Gains (Loss)(15)":"","Other Non Operating Income (Expenses)(16)":"(79,00)","EBT Excl. Unusual Items(17)":"116.869,00","EBT Incl. Unusual Items(18)":"116.869,00","Income Tax Expense(19)":"(16.314,00)","Earnings From Continuing Operations(20)":"100.555,00","Net Income to Company(21)":"100.555,00","Net Income(22)":"100.555,00","Net Income to Common Incl Extra Items(23)":"100.555,00","   % Net Income to Common Incl Extra Items Margins(24)":"26,6 %","Net Income to Common Excl. Extra Items(25)":"100.555,00","   % Net Income to Common Excl. Extra Items Margins(26)":"26,6 %","Diluted EPS Excl Extra Items(27)":"6,02","   % Change YoY(28)":"","Weighted Average Diluted Shares Outstanding(29)":"16.716,32","   % Change YoY(30)":"","Weighted Average Basic Shares Outstanding(31)":"16.565,42","   % Change YoY(32)":"","Dividends Per Share(33)":"0,87","   % Change YoY(34)":"","Payout Ratio %(35)":"14,5 %","Basic EPS(36)":"6,07","EBITDA(37)":"128.218,00","   % Change YoY(38)":"","EBITDAR(39)":"","R&D Expense(40)":"23.057,00","Selling and Marketing Expense(41)":"","Effective Tax Rate %(42)":"14,0 %"}}}

I dont know how to do it. I ave spent hours trying but all in vain. Here is what I have tried till now

dic_income = {}

with open('test.csv','r') as inc:
    lines = inc.readlines()
    in_income = {}
    col = []
    for line in lines:
        if line.startswith(',') and line.strip().endswith(','):
            continue
        ln = line.strip().split(',')

        for i in ln:
            if lines.index(line) == 0:
                if i == '':
                    pass
                else:
                    col.append(i)
                dic_income_key = ln[0]
                
                if ln.index(i) == 0:
                    pass
                else:
                    in_income[i] = {}
            else:
                pass
                

    dic_income[dic_income_key] = in_income
    print(dic_income)
    df = pd.read_csv("test.csv", usecols=col)
    print(df[col[0]])

It gives only output for one line and now I am stuck at this point.

Improve this question

1 Answer 1

Reset to default
2

Code below will give you what you want:

import pandas as pd
import json
df = pd.read_csv("test.csv")
dictionary = {}
columns = df.columns
for index, row in df.iterrows():
  for column in columns:
    if row["Income Statement"] != row["Income Statement"]:
      continue
    else:
      if column == "Income Statement":
        continue
      if "Income Statement" not in dictionary:
        dictionary["Income Statement"] = {}
      if column not in dictionary["Income Statement"]:
        dictionary["Income Statement"][column] = {}
      if row[column] != row[column]:
        toPut = ""
      else:
        toPut = row[column]
      dictionary["Income Statement"][column][row["Income Statement"]] = toPut
print(json.dumps(dictionary, indent=4))

Output

{
    "Income Statement": {
        "24/09/16": {
            "Revenues": "",
            "Total Revenues": "",
            "% Change YoY": "",
            "Cost of Goods Sold": "",
            "Gross Profit": "",
            "% Gross Margins": "",
            "Selling General & Admin Expenses": "",
            "R&D Expenses": "",
            "Other Operating Expenses": "",
            "Operating Income": ""
        },
        "30/09/17": {
            "Revenues": "229234.00",
            "Total Revenues": "229234.00",
            "% Change YoY": "",
            "Cost of Goods Sold": "(141,048.00)",
            "Gross Profit": "88186.00",
            "% Gross Margins": "38.5%",
            "Selling General & Admin Expenses": "(15,261.00)",
            "R&D Expenses": "(11,581.00)",
            "Other Operating Expenses": "(26,842.00)",
            "Operating Income": "61344.00"
        },
        "29/09/18": {
            "Revenues": "265595.00",
            "Total Revenues": "265595.00",
            "% Change YoY": "15.6%",
            "Cost of Goods Sold": "(163,756.00)",
            "Gross Profit": "101839.00",
            "% Gross Margins": "38.3%",
            "Selling General & Admin Expenses": "(16,705.00)",
            "R&D Expenses": "(14,236.00)",
            "Other Operating Expenses": "(30,941.00)",
            "Operating Income": "70898.00"
        },
        "28/09/19": {
            "Revenues": "260174.00",
            "Total Revenues": "260174.00",
            "% Change YoY": "(9.8%)",
            "Cost of Goods Sold": "(161,782.00)",
            "Gross Profit": "98392.00",
            "% Gross Margins": "37.8%",
            "Selling General & Admin Expenses": "(18,245.00)",
            "R&D Expenses": "(16,217.00)",
            "Other Operating Expenses": "(34,462.00)",
            "Operating Income": "63930.00"
        },
        "26/09/20": {
            "Revenues": "274515.00",
            "Total Revenues": "274515.00",
            "% Change YoY": "3.7%",
            "Cost of Goods Sold": "(169,559.00)",
            "Gross Profit": "104956.00",
            "% Gross Margins": "38.2%",
            "Selling General & Admin Expenses": "(19,916.00)",
            "R&D Expenses": "(18,752.00)",
            "Other Operating Expenses": "(38,668.00)",
            "Operating Income": "66288.00"
        },
        "25/09/21": {
            "Revenues": "365817.00",
            "Total Revenues": "365817.00",
            "% Change YoY": "64.4%",
            "Cost of Goods Sold": "(212,981.00)",
            "Gross Profit": "152836.00",
            "% Gross Margins": "41.8%",
            "Selling General & Admin Expenses": "(21,973.00)",
            "R&D Expenses": "(21,914.00)",
            "Other Operating Expenses": "(43,887.00)",
            "Operating Income": "108949.00"
        },
        "LTM": {
            "Revenues": "378323.00",
            "Total Revenues": "378323.00",
            "% Change YoY": "",
            "Cost of Goods Sold": "(215,572.00)",
            "Gross Profit": "162751.00",
            "% Gross Margins": "43.0%",
            "Selling General & Admin Expenses": "(22,791.00)",
            "R&D Expenses": "(23,057.00)",
            "Other Operating Expenses": "(45,848.00)",
            "Operating Income": "116903.00"
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

What does if ‘row[Income Statement] != row[Income Statement]’ mean?
@ZachYoung Interesting question, Zach. In the iterrows loop, I wanted to check if the value of the Income Statement column is nan. But how would you check if the value of a cell is equal to nan? By comparing it with nan itself, or "nan" as a string or None? None of them is working. You just need to check it with itself because nan values are not equal to themselves. For more information, please check this link out stackoverflow.com/questions/33704124/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.