1
$\begingroup$

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')
$\endgroup$
  • 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$ – Matthew Drury Dec 19 '19 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$ – hussain sagar Dec 19 '19 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$ – Matthew Drury Dec 19 '19 at 2:53
0
$\begingroup$

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')
$\endgroup$
  • $\begingroup$ Thank you for your help. I found an answer and updated my question with the same. $\endgroup$ – hussain sagar Dec 19 '19 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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