0

I have a weird array and I can't quite manage to transform it into datasets (a matrix) with the help of pandas

[{'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'red'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual, Ethnic, Formal, Party'}, {'Ideal For': 'Women'}, {'Weight': '200 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'dilli bazaaar Bellies, Corporate Casuals, Casuals', 'price': 349}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Ideal For': "Women's"}, {'Occasion': 'Casual, Party'}, {'Pattern': 'Self Design'}], 'product': "F&S Casual, Party Short Sleeve Self Design Women's Top", 'price': 421}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Ideal For': "Women's"}, {'Occasion': 'Casual, Party'}, {'Pattern': 'Self Design'}], 'product': "F&S Casual, Party Short Sleeve Self Design Women's Top", 'price': 421}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}]

I would like to group this object in a matrix and group by sessionId.

The other data in json, such as attributes or filters, I can leave them as json and calculate how many times they appear, but I would like to transform this large object

DaniD
  • 9
  • 2
  • Does this answer your question? [JSON to pandas DataFrame](https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe) – Arne Oct 08 '22 at 10:15
  • Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – Community Oct 08 '22 at 10:42

1 Answers1

0

This worked for me.

import pandas as pd

data = [{'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'red'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Heel Height': '0 inch'}], 'product': 'V4STYLES Loafers', 'price': 1049}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual, Ethnic, Formal, Party'}, {'Ideal For': 'Women'}, {'Weight': '200 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'dilli bazaaar Bellies, Corporate Casuals, Casuals', 'price': 349}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Ideal For': "Women's"}, {'Occasion': 'Casual, Party'}, {'Pattern': 'Self Design'}], 'product': "F&S Casual, Party Short Sleeve Self Design Women's Top", 'price': 421}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'blue'}, 'duration': 25, 'attributes': [{'Ideal For': "Women's"}, {'Occasion': 'Casual, Party'}, {'Pattern': 'Self Design'}], 'product': "F&S Casual, Party Short Sleeve Self Design Women's Top", 'price': 421}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Base Material': 'Brass'}, {'Brand': 'Vendee Fashion'}, {'Model Number': 'VD8636'}], 'product': 'Vendee Fashion Brass Jewel Set', 'price': 829}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Occasion': 'Casual'}, {'Ideal For': 'Women'}, {'Tip Shape': 'Round'}], 'product': 'Willy Winkies Black Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}, {'session_id': 'fc4867a5-cff2-4210-9ddf-9e5db35187ec', 'filters': {'color': 'green'}, 'duration': 25, 'attributes': [{'Ideal For': 'Women'}, {'Occasion': 'Casual'}, {'Weight': '500.00000000 g (per single Shoe) - Weight of the product may vary depending on size.'}], 'product': 'CatBird Boots', 'price': 999}]
df = pd.json_normalize(data)

Output:

                              session_id  duration  ... price filters.color
0   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...  1049           red
1   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...  1049          blue
2   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...  1049          blue
3   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...  1049          blue
4   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   349          blue
5   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   421          blue
6   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   421          blue
7   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   829         green
8   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   829         green
9   fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   829         green
10  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   829         green
11  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green
12  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green
13  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green
14  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green
15  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green
16  fc4867a5-cff2-4210-9ddf-9e5db35187ec        25  ...   999         green

Then you can group like this:

df.groupby('session_id')

Solution found here: JSON to pandas DataFrame

zhzh
  • 1
  • Thank you. And how could I calculate exactly how many times each value in the column appears? – DaniD Oct 08 '22 at 10:34
  • You can do `df.groupby('session_id').count()` for example, it groups by `session_id` and counts the number of rows. Not sure if this is exactly what you are looking for. – zhzh Oct 08 '22 at 11:06