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

Critics Items Ratings







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')
  • 1
    $\begingroup$ What do you want to do if a critic has rated an item multiple times? Like critic A has with item 1 in your example? (In any case, the term of art to google is 'pivot table') $\endgroup$ Commented Dec 19, 2019 at 1:53
  • $\begingroup$ The actual data I am using does not have overlaps. But that is an interesting scenario and honestly, I have no idea how I would fix that. I would assume pandas would give me an error since it would not be able to decide what value to put there. $\endgroup$ Commented Dec 19, 2019 at 2:15
  • $\begingroup$ Well, that depends on how you write the code. You could, for example, take the mean of all the values. $\endgroup$ Commented Dec 19, 2019 at 2:53

1 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]],

#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))),

#3. merge the first two column with ratings, using left join to add nan, drop duplicate first
  • $\begingroup$ Thank you for your help. I found an answer and updated my question with the same. $\endgroup$ Commented Dec 19, 2019 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.