Data Wrangling: explain the challenges posed by the inconsistent data set

[bdravec]

create DATA_WRANGLING.md file and insert the findings about the data set. is linked to isse #10 clean data set thoroughly.

Problem description

in the "NAME" column, of 102'349 entries, we only have 61'281 distinct values. this especially interesting, since it's a text field that describes the listing (1 br with a view OR skyscraper in unique building etc.) with several words. This bears the question: why do we have so many duplicate entries in a textfield with ONLY 250 MISSING VALUES but at least half of the descriptions are duplicates? We don't know at this point how to distinguish which entry is the correct one. there is no timestamp with an 'last updated' information.

Analysis

• the two distinctive id's, namely 'id' and 'host id' are unique, so with every "update" of an entry, those id's are newly created and not kept.
• the duplicate entries differ in one, two, sometimes three columns e.g. in one entry only the 'cancellation_policy' was updated from strict to moderate and house_rules were added.
• duplicate entries that differ *slightly but we have no way of figuring out which one is the latest
• since we are looking at NY there are a lot of skyscrapers with listings that display the same longituted/latitued
• looking at unique "NAME" which are basically listing descriptions, we only have ~ 61k unique listings
• it seems every time a listing is updated, a new entry is generated
• in addition, important information changes e.g. from "verified host identity" to "unconfirmed"

Proposed solution there is an unnamed column, that could be a database counter: whenever a new entry is created, the counter goes up. we cannot be sure however, that this is the case. if it was, the entry with the highest counter would be the most up-to-date entry.

[bdravec]

What is the column 'calculated host listings count'. it doesn't seem to be the number of listings each host has. I don't know what it could be. here a couple of tests that show it's not related to the number of listings: # Create a DataFrame where host name is Aldus aldus_df = df_data[df_data['host name'] == 'Aldus'] aldus_df --> output are 3 listings that have Aldus as a host, however, each of those have 'calculated host listings count' of 3, 2, and 1 respectively.

# Create a DataFrame where host name is Madaline madaline_df = df_data[df_data['host name'] == 'Madaline'] madaline_df --> output are 4 listings that have Madaline as a host, however the 'calculated host listings count' in Madalines row is 6

[bdravec]

@bdravec frage Riccardo, was wir tun sollen

[bdravec]

Email sent to Riccardo Dear Riccardo

I hope this finds you well. While wrangling the Airbnb data set we’ve encountered a disturbing inconsistency:

• When a user updates a listing, instead of ‘updating’ the current listing, a new listing is created with a new listing id and new host id.
• Since there’s no time stamp or unique id, we cannot know which listing is the latest or correct one.
• From the roughly 102k listings we estimate that approx. 35 - 39k could be updates and therefore do not hold relevant information for our users.

Is this a well-known inconsistency in the data set (new listing is created when user updates a listing)? Without knowing which listings are the ‘updated’ ones, we cannot clean the data very well.

• Do you have any hints / ideas how we should proceed?
• Do you have any preference how we should document theses findings (e.g. no unique identifier for a listing)? We suggest we provide the user with a discussion of the data (e.g. data_wrangling.md) and point to the problems, challenges and consequences of using the data set provided. What do you think?

Thanks for your help and insights!

Kindest regards, The AirBnB-Team Barbara, Lukas, Viola, Romano