CODEWITHSUNDEEP
pythonpandas
user3249433
user3249433

Reputation: 601

Transform Panda dataframe to this shape

I have a pandas dataframe like this

In [98]: myDict
Out[98]:
{'2015-08-07': [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}],
 '2015-08-06': [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}]}

In [99]: df = pandas.DataFrame(myDict)

In [100]: df
Out[100]:
              2015-08-06             2015-08-07
0    {'redoPerSec': 300}    {'redoPerSec': 300}
1  {'Transactions': 100}  {'Transactions': 100}
2          {'IOPS': 400}          {'IOPS': 400}

I would want to convert this to something below

Date            redoPerSec          Transactions    IOPS
2015-08-06          300                 100         400
2015-08-07          300                 100         400

Upvotes: 0

Views: 86

Answers (1)

yangjie
yangjie

Reputation: 6715

import pandas as pd

df = pd.DataFrame()
df['2015-08-06'] = [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}]
df['2015-08-07'] = [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}]

df = df.T

for i in range(3):
    df = df.join(df[i].apply(Series)).drop(i, 1)

df.index.name = 'Date'
print df

Output:

            redoPerSec  Transactions  IOPS
Date                                      
2015-08-06         300           100   400
2015-08-07         300           100   400

Edit

Since you create DataFrame from dict, you can directly use

df = pd.DataFrame.from_dict(myDict, 'index')

instead of creating a DataFrame and then transpose it through df = df.T.

A more element solution is first transforming your dict such that the value is a dict instead of a list of dict, although the nested dict comprehension is harder to understand. After that we can directly construct DataFrame as expected.

import pandas as pd

myDict = {'2015-08-07': [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}],
 '2015-08-06': [{'redoPerSec': 300}, {'Transactions': 100}, {'IOPS': 400}]}

myDict = { k0: { k: v for d in v0 for (k, v) in d.items() } for (k0, v0) in myDict.items()}
df = pd.DataFrame.from_dict(myDict, 'index')

Upvotes: 1

Related Questions