---
tags: reviews
---

# Gold standard dataset

### Final content of gold_standard.csv:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 57369 entries, 0 to 57368
Data columns (total 28 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 title_id 57369 non-null int64
1 year_published_first 57369 non-null int64
2 date_published_first 57290 non-null object
3 publication_count 57369 non-null int64
4 publishing_house_ids 57369 non-null object
5 publishing_houses 57369 non-null object
6 id 57369 non-null int64
7 created_by 57369 non-null int64
8 date 57369 non-null object
9 headline 57369 non-null object
10 url 46053 non-null object
11 quote 46193 non-null object
12 grades_transformed_6 57369 non-null float64
13 rev_id 57369 non-null int64
14 rev_gender_updated 45787 non-null object
15 media_importance 57369 non-null int64
16 media_name 57369 non-null object
17 media_type_id 57369 non-null int64
18 media_type_name 57369 non-null object
19 title_title 57369 non-null object
20 title_keywords 51965 non-null object
21 title_language_primary 57369 non-null object
22 author_entity_ids_c 57369 non-null int64
23 first_name 57369 non-null object
24 last_name 57095 non-null object
25 birth_year 23997 non-null float64
26 demean_grades 57369 non-null float64
27 author_gender 57369 non-null object
dtypes: float64(3), int64(9), object(16)

### Filters used

Columns in fiction-reviews.csv:

Index(['id', 'created_by', 'media_id', 'date', 'headline', 'quote', 'grade',
       'grade_normalized', 'url', 'referenced_title_ids', 'referenced_titles',
       'reviewer_id', 'underheading', 'grading_max', 'grading_min',
       'grading_precision', 'grading_symbol', 'media_importance',
       'media_initials', 'media_name', 'media_type_id', 'media_url',
       'media_type_name', 'clean_grades_scale6', 'clean_grading_max_scale6',
       'grades_transformed_6', 'clean_grades_scale5',
       'clean_grading_max_scale5', 'grades_transformed_5', 'rev_first_name',
       'rev_last_name', 'rev_gender', 'rev_id', 'title_id', 'title_title',
       'title_language_primary', 'title_language_translated_from',
       'title_is_translated', 'title_category_id', 'title_keywords',
       'category_id', 'category_name', 'title_ids', 'title_name', 'category_c',
       'author_entity_ids_c', 'multiple_titles', 'review_title_id_c',
       'multiauthors', rev_gender_updated],
      dtype='object')

### rev_gender_updated column:
* we have retrieved 16.309 gender of reviewers with help from Kirsten.
* for the ficiton dataset it gives us 14.760 review genders.

### From the fiction-review.csv dataset...
* filter away review on more than 1 book title and more and oen book author ==> removing 4.906 reviews out of 65.474 it total which is 7,49%
* get the subset of columns: ['id','created_by', 'date', 'headline', 'url', 'quote','grades_transformed_6','rev_id','rev_gender_updated','media_importance','media_name','media_type_id','media_type_name','title_title','title_keywords','title_language_primary','title_id','author_entity_ids_c']

* filter away reviews without id on book author ==> removing 147 reviews.
* filter away reviews without grade ==> removing 2957 reviews
* filter away reviews withaout media name ==> removing 1 review.

### From books:
* Get the follwoing information: ['year_published_first', year_published_first', 'date_published_first', 'publication_count', 'publishing_house_ids', 'publishing_houses', 'id'] from referenced_titles in reviews-graph.ndjson
* Remove all reviews without a book id ==> removing 74 reviews in total
* Column of book id is called ['title_id']

### Merge:
* Merge the two dataframes on 'title_id'
* remove all reviews wihtout id
* remove duplicated on 'id' and 'title_id'

Then we end up with: a (57463, 22) dataframe

#### Info:

<class 'pandas.core.frame.DataFrame'>
Int64Index: 57369 entries, 0 to 57462
Data columns (total 26 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 title_id 57369 non-null float64
1 year_published_first 57105 non-null float64
2 date_published_first 57290 non-null object
3 publication_count 57369 non-null float64
4 publishing_house_ids 57369 non-null object
5 publishing_houses 57369 non-null object
6 id 57369 non-null float64
7 created_by 57369 non-null float64
8 date 57369 non-null object
9 headline 57369 non-null object
10 quote 46193 non-null object
11 grades_transformed_6 57369 non-null float64
12 rev_id 34635 non-null float64
13 rev_gender_updated 45787 non-null object
14 media_importance 57369 non-null float64
15 media_name 57369 non-null object
16 media_type_id 57369 non-null float64
17 media_type_name 57369 non-null object
18 title_title 57369 non-null object
19 title_keywords 51965 non-null object
20 title_language_primary 57369 non-null object
21 author_entity_ids_c 57369 non-null int64
22 first_name 57369 non-null object
23 last_name 57095 non-null object
24 birth_year 23997 non-null float64
25 gender 57369 non-null object
dtypes: float64(10), int64(1), object(15)
memory usage: 11.8+ MB

### From author_gender.csv
* make dataframe with ['author_entity_ids_c', 'first_name', 'last_name', 'birth_year', 'gender'] where author_entity_ids_c is id column renamed.
* That gives us 12.822 unique author ids.
* In the big dataframe change the author_entity_ids_c to be int (123) instead of string with square brackets ('[123]').
* merge dataframe with author gender togehter with the big (result) dataframe with an outer join --> author_entity_ids_c = author_entity_ids_c
* drop all id which is nan. ==> author id for all 57.369 reviews.
* rename the column 'gender' to 'author_gender' so its not mistaken for 'rev_gender'

### Further additions:
* Some of the newspapers' website is listed as online media. This is now changed.
* I have added a column for demean of the grades.

### Questions
* If we need reviewer gender then we remove 11.587 reviews but we would still have 45.787 reviews.
* If we need reviewer id, then we need to remove 22.734 reviews ==> leading to 34.635 reviews.
* If we need both reviewer id and reviewer gender, then we will have 31.746 reviews.
* Do we need publishing_house_ids or publishing_houses ?
* example:
* publishing_house_ids: [6125, 7979]
* publishing_houses: [{'id': 6125, 'name_main': 'Forlaget Brændpunkt'}, {'id': 7979, 'name_main': 'Lindhardt og Ringhof', 'name_sub': 'SAGA Egmont'}]

# Goldstandard.csv: (57369, 27)

|Column name | Content | Gold standard Filter| Notes |
| ---------- | ------- | ------------------- | ----- |
| id | unique for each row| Should be there: filter out those without| |
| created_by | an integer, having values [2, 880, 906, 960, 200, 197,5,424,544]||
| date | when the review was published| |
| headline | review headline | |
| url | url to review | |
| quote | quote from the review (not the book) ||
| grades_transformed_6 | grade on the 6-point likert scale ||
| rev_id | Unique id for reviewer| |we only have 34706 reviewer ids|
| rev_gender_updated| gender of reviewer, updated with help from Kirsten ||
| media_importance |values: 100, 200, 300, nan ||
| media_name | 304 unique names ||
| media_type_id |values: 1,2,3,4,5,6, ,9, nan (7 and 8 missing?)||
| media_type_name | name of the media type , unique types in the data: Blog, Onlinemedie, Ugeblad, Avis, Fagblad, Onlinemedie uden citat, Regianl avis, nan | | |
| title_title | the title of the book| only reviews of one book ==> single title_title| |
| title_keywords| list of all keywords associated with the title||
| title_language_primary |||
| title_id | unique book id |Should be there: filter out those without| |
| year_published_first | ||
| date_published_first | | |
| publisher: publishing_house_ids OR publishing_house_ids | Publisher house|| see question above|
| publication_count | number of publications|||
| author_entity_ids_c | Unique id for author| Should be there: filter out those without| |
| first_name | first name of author | |
| last_name | last name of author | | |
| birth_year | birth_year of author ||
| author_gender | gender of author| | |
| demean_grades | demean value for the grades || |