How to create a new dataframe using the another dataframe

Question

I have created and worked on a DataFrame for a project. It looks like the following:

Critics Items Ratings

a...........1..........5

b...........2..........3

b...........3..........2

c...........8..........1

a...........1..........5

b...........4..........4

My DataFrame has 1M+ rows and 8 columns.

I want to create a new DataFrame where the rows are the unique critics, the columns are the unique items, and the individual cells are the rating a critic has given for the particular item. If the critic has not reviewed the item then I want to add an NA over there.

I tried doing the following for the rows:

ratings = pd.DataFrame(f.review_profilename.unique())

For the columns, I saw a lot of answers involving people using

ratings.rename(<individual column names>, axis='columns')

But this doesn't help me since I can't list down all the unique item names.

Edit: I fixed the issues by using pivot tables. I am new to pandas and was not aware of something like this existing. The exact syntax I used was

ratings = f.pivot_table(index = 'critic',columns = 'item', values = 'ratings')

Answer 1

Not sure what you mean by "unique", I guess if there're n critics, m items, what you need is a dataframe with shape n*m rows and 3 columns, right? If so, try the demo:

#1. the original dataframe
df = pd.DataFrame([['a',1,5],['b',2,3],['b',3,2],['c',8,1],['a',1,5]],
     columns=['critic','item','rating'])

#2. create the first two columns(critic, item) by their permutation
from itertools import product
first_two_column = pd.DataFrame(list(product(set(df.critic),set(df.item))),
                   columns=['critic','item'])

#3. merge the first two column with ratings, using left join to add nan, drop duplicate first
first_two_column.merge(right=df.drop_duplicates(),on=['critic','item'],how='left')