Stack / Unstack not preserving the data order in python

user_13521436 0
Viewed 96

I have json data below

[[{"value":"ZZ","formattedValue":"ZZ"},{"value":"In","formattedValue":"In"},{"value":"Amount1","formattedValue":"Amount1"},{"value":"100","formattedValue":"100"}],[{"value":"ZZ","formattedValue":"ZZ"},{"value":"In","formattedValue":"In"},{"value":"Amount2","formattedValue":"Amount2"},{"value":"200","formattedValue":"200"}],[{"value":"ZZ","formattedValue":"ZZ"},{"value":"Out","formattedValue":"Out"},{"value":"Amount1","formattedValue":"Amount1"},{"value":"30","formattedValue":"30"}],[{"value":"ZZ","formattedValue":"ZZ"},{"value":"Out","formattedValue":"Out"},{"value":"Amount2","formattedValue":"Amount2"},{"value":"4","formattedValue":"40"}],[{"value":"CC","formattedValue":"CC"},{"value":"In","formattedValue":"In"},{"value":"Amount1","formattedValue":"Amount1"},{"value":"100","formattedValue":"100"}],[{"value":"CC","formattedValue":"CC"},{"value":"In","formattedValue":"In"},{"value":"Amount2","formattedValue":"Amount2"},{"value":"200","formattedValue":"200"}],[{"value":"CC","formattedValue":"CC"},{"value":"Out","formattedValue":"Out"},{"value":"Amount1","formattedValue":"Amount1"},{"value":"30","formattedValue":"30"}],[{"value":"CC","formattedValue":"CC"},{"value":"Out","formattedValue":"Out"},{"value":"Amount2","formattedValue":"Amount2"},{"value":"4","formattedValue":"40"}]]

In the table format, it should look like below,

Dimension Type Amount1 Amount2
ZZ In 100 200
ZZ Out 30 40
CC In 100 200
CC Out 30 40

howerver, if I convert the json using python code below, the order in which the incoming data came is not preserved.

data  = 'jsondata'
data  = json.loads(data)
df = pd.DataFrame(data).stack().map(lambda x:x.get('formattedValue')).unstack()
df.columns =['Type','InOut','MeasureName','MeasureValue']
df = df.pivot_table(index=['Type','InOut'],columns=['MeasureName'],values="MeasureValue",aggfunc='sum').reset_index()

the output of this is below. If you look at it, the dimension is ordered. I dont want it to happen. I have to keep the order of the data same as it came in. How to achieve this please? Thanks.

Dimension Type Amount1 Amount2
CC In 100 200
CC Out 30 40
ZZ In 100 200
ZZ Out 30 40
pandas dataframe python
4 Answers

IIUC:

You can try:

before pivot_table create a variable:

uni=df['Type'].unique()

after pivoting use:

df=df.loc[df['Type'].map(dict(zip(uni,range(len(uni))))).sort_values().index]

output of df:

MeasureName     Type        InOut
  2             ZZ              In
  3             ZZ              Out
  0             CC              In
  1             CC              Out
user_142898920

Another solution:

df = pd.DataFrame(
    [
        {
            "Dimension": subl1[0]["formattedValue"],
            "Type": subl1[1]["formattedValue"],
            "Amount1": subl1[-1]["formattedValue"],
            "Amount2": subl2[-1]["formattedValue"],
        }
        for subl1, subl2 in zip(data[::2], data[1::2])
    ]
)
print(df)

Prints:

  Dimension Type Amount1 Amount2
0        ZZ   In     100     200
1        ZZ  Out      30      40
2        CC   In     100     200
3        CC  Out      30      40
user_100359850

Yet another solution: clean the data before putting it to pandas:

data = json.loads(s)
data = list(map(lambda row: [el.get('formattedValue') for el in row], data))
df = pd.DataFrame(data, columns=['Dimension', 'Type', 'Amount1', 'Amount2'])
user_131186320

Let's go back a few steps.

First start with pd.json_normalize to read in the json arrays.

Let's assume your json is called d you can replace it with your path:

df = pd.concat([pd.json_normalize(i).drop('value',1) for i in d])

Now let's create a new index so we can unstack without a duplication error:

df1 = df.set_index(df.groupby(level=0).cumcount(),append=True).unstack(0).droplevel(0,1)

print(df1)

    0    1        2    3
0  ZZ   In  Amount1  100
1  ZZ   In  Amount2  200
2  ZZ  Out  Amount1   30
3  ZZ  Out  Amount2   40
4  CC   In  Amount1  100
5  CC   In  Amount2  200
6  CC  Out  Amount1   30
7  CC  Out  Amount2   40

Okay we are close, let's create your pivot and assign your column names:

df2 = pd.pivot_table(df1,
                    index=[0,1], columns=[2], values=               
                    [3],aggfunc='first').reset_index().droplevel(0,1
                    )

df2.columns = ['Dimension','Type','Amount1','Amount2']

print(df2)

  Dimension Type Amount1 Amount2
0        CC   In     100     200
1        CC  Out      30      40
2        ZZ   In     100     200
3        ZZ  Out      30      40
user_93751020
Related