Free Company Housing Census - November 2021

[Big_Bap]

Wow, it's that time of the year (again)... xivhousing.com has been updated.

FC housing reports for November 2021.

Missing data: 0.003% (11/355,724)

Main report

Available Breakdowns:

• Free Company Houses
• Foundation (Year)
• Rank (Groups)
• Grand Company
• Members (Groups)
• Members (Total)
• Members (Average)

Available Views:

• Server
• Data Center
• Region

----------

FC Housing - Solo

Available Breakdowns:

• Free Company Houses (Solo)
• Foundation (Year)
• Rank (Groups)
• Grand Company

Available Views:

• Server
• Data Center
• Region

----------

FC Housing - ≤ 4 Members

Available Breakdowns:

• Free Company Houses (≤ 4 Members)
• Foundation (Year)
• Rank (Groups)
• Grand Company
• Members (Groups)

Available Views:

• Server
• Data Center
• Region

----------

FC Housing - Plot Distribution

Available Breakdowns:

• Region
• Data Center
• Sizes
• Area
• Foundation (Year)
• Rank (Groups)
• Grand Company
• Members (Groups)
• Members (Total)
• Members (Average)

Available Views:

• Default

----------

FC Housing - Ward Distribution

Available Breakdowns

• Region
• Data Center
• Sizes
• Area
• Foundation (Year)
• Rank (Groups)
• Grand Company
• Members (Groups)
• Members (Total)
• Members (Average)

Available Views:

• Default

---


- A complete Free Company census will be available in a few days. Currently dealing with database limitations.

- A Relocation report is available for July to November

- 3% of FCs with a house have blown up between July and November for those who may wonder. That percentage doesn't account for FCs that blew up between the two censuses.

- Japan is still leading the way when it comes to "shell FCs" so you are more than welcome to go tell them how terrible human beings they are if you think you're in a position to dictate how other players should play a video game.

- FC housing as a whole claimed 2.75% of personal housing since March. FC houses with less than 4 members account for .8%.

- "Roleplayers benefit the most from upgrading from a small house" Right...

- "Japanese players don't hoard houses" Right...

[ItMe]

This is interesting and helpful data. Thanks for sharing it.
Out of curiosity, how was it compiled?

[Knot_D]

Lmao this is interesting census, thank you for sharing!

[Big_Bap]

Here are two reports on Free Companies that had a house when the data was acquired from the Lodestone in July and no longer have a house as of this week of November.

Main report

Plots

[Big_Bap]

This is interesting and helpful data. Thanks for sharing it.
Out of curiosity, how was it compiled?

Didn't quite use this method this time around, but that's more or less how it's done + fixing bugs between Google Sheets, Google Data Studio, Excel and Octoparse.

Lmao this is interesting census, thank you for sharing!

Thank you

[ItMe]

Didn't quite use this method this time around, but that's more or less how it's done + fixing bugs between Google Sheets, Google Data Studio, Excel and Octoparse.

Oooooh, I see. That's pretty clever.
Yo, this is some real accurate data then~

There is one step I don't understand though.
#6 says "retrieve missing FCs (less than 1%)"
How were they missing and how do you find the missing ones?

[Toukichiful]

very nice information, it's interesting to see so many solo fc houses on spriggan.

[Big_Bap]

Oooooh, I see. That's pretty clever.
Yo, this is some real accurate data then~

There is one step I don't understand though.
#6 says "retrieve missing FCs (less than 1%)"
How were they missing and how do you find the missing ones?

Obtaining the data is a two-step process. The lodestone will only display up to 1,000 search results and some servers have upwards of 6,000 FCs. For nearly all servers, you only need 200 queries in order to have the Lodestone bring up all the FCs from that server on the search page. Bump it to 220/240 queries for Odin and Mateus.

So right off the bat, you know which search URLs you are going to use. If one of them does not return any FC information, it's either because there is nothing to display (ex: being on page 20 when there's only 15 pages worth of FCs that meet your criteria) or because the lodestone failed to complete your query.

If I have data from: Cerberus/no house/1-10 members/Maelstrom/recruitment closed/page 12
But somehow I have no results for page 11, I know I'm missing 50 FCs. Another way to check that is to build individual tasks for each server. If the total amount of FCs being retrieved from the search pages does not match the combination of FCs with a house, with a plot, and without a house, you are missing data. That's the 1% I was referring to.

The reason why you want to add those 3 together is that it's the only accurate way to know how many FCs are actually on a server. If you do a simple search on any given server and compare that number, you will see a pretty big difference, but that's another story.

Anyhow, once you have obtained all the FCs from the search pages, you then start extracting data from each individual URL you've obtained. Loading FC URLs will very rarely return any information. In the last run, I've had 150 URLs that brought up nothing. About 130 were FCs that had been deleted. Those are very easy to spot because all FCs have a name/server/tag/foundation/members (even 0, yes).

Because names/membership/estate ownership can change, I like to rely on the foundation/URL to compare if the data between the search results and the FC extraction match. I've had an instance where some data that belongs to certain FCs was given to the FC that was next in the queue. Typically speaking, if you manually compare data from the first and last URL you've extracted, that's a sign the software didn't mess up and everything is in order.

[Big_Bap]

Once you have all the data you need and that it's been verified, you have to clean it up.

- Some FCs have 0 members because the FC leader deleted his character. The FC still exists and sometimes even has a house standing, but I personally remove those.

- The lodestone will display the data center of a FC next to its server. For my purposes, that has to be changed. "Gilgamesh (Aether)" is simply changed to "Gilgamesh". I don't need to add another column in the database for the data center, because, within google data studio, I can create a calculated field for the same purpose. It sorta looks like: if SERVER ("Gilgamesh", "Faerie", "Jenova, etc) THEN "Aether".

- I add another calculated field for the region "if DATA CENTER ("Aether", "Crystal", "Primal") THEN "North America"

- Same thing for Grand Companies. I consolidate all the GC ranks into one with a calculated field.

- Extracting the address of a FC will return something like "Plot 1, Ward 1, Mist (Medium)". Within excel, I use a built-in function to break that up at every comma and (. Then it's just a matter of cleaning up the new columns: removing "Plot" and the space next to it to only keep the number, etc.

--

There is a lot left to cover, but the bulk of the actual work is setting up the database properly. Not because it's particularly time-consuming, but because you will run into problems you didn't expect (formatting, performance, space). You can spend a lot of time wondering why you can't get any data in your graph because the date format in your database is different than the one google data studio expects...

[Big_Bap]

Two additional reports on Free Companies that have lost their house between July and now. Doesn't include FCs that have purchased and lost their house between then and now.

Main report - FCs that blew up

Plots - FCs that blew up

Pour one out for those who lost a Lb41, a M35 and a S60 and got nothing out of it.

Also lol @that rank 30 FC on Louisoix that had 432 members and lost their Mist 14. Looks like it has been deleted