Pandas: how to transform complex dataframe to simpler dataframe

Question

I have below raw data already in a dataframe

{'timePeriod': {'start': '2019-09-01', 'end': '2019-09-02'},
  [{'keys': ['173419835872', 'Software'],
    'metrics': {'Cost': {'amount': '1100.23', 'unit': 'USD'}}},
   {'keys': ['921747472697', 'Hardware'],
    'metrics': {'Cost': {'amount': '2300.11', 'unit': 'USD'}}},
   {'keys': ['921747472697', 'Tax'],
    'metrics': {'Cost': {'amount': '500.00', 'unit': 'USD'}}}]
  }

I want to end up with a dataframe with five columns labeled:

year month acct type amt
2019 Sep 173419835872 Software 1100.23
2019 Sep 921747472697 Hardware 2300.11
2019 Sep 921747472697 Tax 500.00

What df functions can I use to parse the raw data and create the new dataframe?

Thank you.

This question is not at all clear. _Can somebody guide me as to the most efficient way to transform df1 into df2 so I end up with something similar to below?_ what you have below looks ***nothing*** like your second column. It looks like you want to combine items from both columns to make a new dataframe? Please edit your question. — artemis, Nov 14 '19 at 03:53
[Please read this post on how to provide a great pandas example](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) and [refer to this one on how to provide a minimal, complete, and verifiable example](https://stackoverflow.com/help/minimal-reproducible-example) and revise your question accordingly so people in the community can easily help you. — Joe, Nov 15 '19 at 13:03

score 0 · Answer 1 · answered Nov 18 '19 at 00:35

It's not pretty but I ended up writing to a csv file and then reading the csv file directly into a dataframe.

    with open('ce.csv', 'w') as cecsv:
    cecsv.write("Year|Account|Service|Cost\n")
        for i in range(0,len(df)):
            for j in range(0,len(df['groups'][i])):
              cecsv.write(df['timePeriod'][i]['start'] + "|" + 
              df['groups'][i][j]['keys'][0] + "|" + 
              df['groups'][i][j]['keys'][1] + "|" + 
              df['groups'][i][j]['metrics']['UnblendedCost']['amount'] + "\n") 
        cecsv.close()```

Pandas: how to transform complex dataframe to simpler dataframe

1 Answers1