Condense similar instances in dataframe while adding the features of each

Question

I am working on a project using Pandas, and am running into trouble when trying to condense similar objects.

I have a dataframe with the columns Product ID, Currency, Price, and Book.

There are identical products in the Product ID category, such as X11, X23, X25, and so on. There are 3 instances of each, each with one of 3 currencies, with a price, and with one of 3 Books.

I want to have a dataframe where each instance is one product, with whatever books they appear in, and the 4 prices of the 4 currencies contained in each instance.

This is an example of what the Dataframe looks like now:

df = pd.DataFrame({'Product ID' : ['X11' ,'X11', 'X11', 'X23', 'X23', 'X23', 'X25', 'X25'], 
                    'Currency' : ['USD', 'EUR', 'GBP', 'USD', 'EUR', 'GBP', 'EUR', 'GBP'],
                    'Price' : [100, 90, 90, 200, 180, 180, 90, 90],
                    'Book' : ['America', 'Canada', 'Mexico', 'America', 'Canada', 'Mexico', 'Canada', 'Mexico']})


df

      Book Currency  Price Product ID
0  America      USD    100        X11
1   Canada      EUR     90        X11
2   Mexico      GBP     90        X11
3  America      USD    200        X23
4   Canada      EUR    180        X23
5   Mexico      GBP    180        X23
6   Canada      EUR     90        X25
7   Mexico      GBP     90        X25

Ultimately It will be converted to a JSON file that has all of that data in one instance, however before that happens I need to condense the identical products. See below for an example of what the final JSON converted object will look like.

What would be the best way of achieving this? I don't fully understand groupby, as some similar questions have suggested, and I haven't seen a question that answers how to do this. The actual conversion itself shouldn't be too hard once I have identical products only occurring in one instance, but with all of the price and book data.

Any help is much appreciated.

Answer 1

You can solve this in two stages.

The first stage requires a pivot on 3 columns -

i = df.pivot('Product ID', 'Currency', 'Price')\
      .add_suffix(' Price')\
      .reset_index()\
      .rename_axis(None, 1)

i
  Product ID  EUR Price  GBP Price  USD Price
0        X11       90.0       90.0      100.0
1        X23      180.0      180.0      200.0
2        X25       90.0       90.0        NaN

Next, perform a groupby on Product ID and aggregate the country names in Book -

j = df.groupby('Product ID').Book.agg(', '.join).reset_index()
j

  Product ID                     Book
0        X11  America, Canada, Mexico
1        X23  America, Canada, Mexico
2        X25           Canada, Mexico

Now, merge the two intermediate results -

df = i.merge(j)
df

  Product ID  EUR Price  GBP Price  USD Price                     Book
0        X11       90.0       90.0      100.0  America, Canada, Mexico
1        X23      180.0      180.0      200.0  America, Canada, Mexico
2        X25       90.0       90.0        NaN           Canada, Mexico

Now, convert df to JSON using .to_json in the records format -

df.to_json('file.json', orient='records')

---

file.json (output)

[
    {
        "GBP Price": 90.0,
        "Book": "America, Canada, Mexico",
        "Product ID": "X11",
        "EUR Price": 90.0,
        "USD Price": 100.0
    },
    {
        "GBP Price": 180.0,
        "Book": "America, Canada, Mexico",
        "Product ID": "X23",
        "EUR Price": 180.0,
        "USD Price": 200.0
    },
    {
        "GBP Price": 90.0,
        "Book": "Canada, Mexico",
        "Product ID": "X25",
        "EUR Price": 90.0,
        "USD Price": NaN
    }
]

Answer 2

Can also use for loop to create desired output:

outlist = []                            # empty output list to be filled
uid = pd.unique(df['Product ID'])       # get unique ID values
for id in uid:
    subdf = df[df['Product ID'] == id]  # get sub-dataframe for one ID
    entry = {}                          # empty dictionary to be filled, one for each ID
    entry["Product Code"] = id          # add ID to dictionary
    entry["Book"] = ", ".join(subdf['Book'].tolist())   # add Book list to dictionary
    for row in subdf.values:            # get subdf as a list of lists
        entry[row[1]+" Price"] = row[2] # add prices to dictionary
    outlist.append(entry)               # add dictionary to outlist

print(outlist)

Output:

[{'Product Code': 'X11', 'GBP Price': 90, 'EUR Price': 90, 'USD Price': 100, 'Book': 'America, Canada, Mexico'}, 
 {'Product Code': 'X23', 'GBP Price': 180, 'EUR Price': 180, 'USD Price': 200, 'Book': 'America, Canada, Mexico'}, 
 {'Product Code': 'X25', 'GBP Price': 90, 'EUR Price': 90, 'Book': 'Canada, Mexico'}]