The advantages of having a diverse workforce are now widely acknowledged. The Centre for Creative Leadership states companies with a higher proportion of women in their workforce have higher job satisfaction from all genders, are more likely to attract young talent and are more likely to have increased revenue.

“The gap between male and female employment is the smallest ever recorded”

In the last few decades, more and more women have been joining the workforce. According to the office of national statistics (ONS) the female employment rate is up 20% since 1970 and the gap between female and male employment is currently 6.4%, the smallest ever recorded.

Key pieces of government legislation such as the Equality Act of 2010 which protects workers from discrimination and the Shared Parental Leave Regulations 2014 which allows couples to share maternity leave, allowing mothers to spend less time away from work, have helped more women progress in their careers.

The Issue

There is, however, an elephant in the room. The gender pay gap. By analysing our hourly wage data, we can see the gap not only exists but becomes more evident around and after the age most couples have a baby. This may imply women are struggling to rejoin the labour market after taking time off to care for a child.

The governments latest scheme in tackling gender discrimination is gender pay reporting which came into force in April 2018. It requires large businesses to report the difference in wages between genders or the ‘Gender Pay Gap’ (GPG). According to a report by the Government Equality Office in 2019, over 80% of companies who reported their GPG before August 2018 had more women in the lowest-paid positions then the highest-paid positions. If you take the mean hourly pay for males and females, you can calculate the GPG for the UK to be around 17%.

Women in charge

By splitting newly appointed company directors by gender we can see how the number of women in top positions has changed over time.

The percentage of female directors has grown by over 10% since 1994. Whilst this is a move in the right direction there is still a huge gap between the two genders, with 75% of new directors being male in 2020. We can also observe a slump in progress during the last 10 years, despite the introduction of gender pay reporting in 2018.

Another way of observing the number of women in top positions is by splitting individuals with significant control of a company by gender. An individual will often be classed as having significant control if they hold a majority or large percentage of shares in a company.

This graph also shows the stagnation of progress as the percentage of women with significant control has not increased since 2016 and is still a long way off an equal playing field with men.

Acknowledging Barriers

Acknowledging the barriers to women reaching top positions is vital in closing these gaps. By using our demographics data we can see the average hours worked for males in 2020 was 36, for females this was 29. This difference, along with the key points from the 3 graphs above, may imply that women are more likely to take either time off or a part-time position when entering parenthood. This effectively puts a halt on career progression, preventing women from reaching top positions and hence increasing the GPG.

Breaking Barriers

The Government Equalities Office report ‘Women’s Progression in the Workplace‘ describes ways in which we can change dogmas around part-time and flexible working to allow career progression through parenthood and make women feel more comfortable balancing their work and personal life. These include:

• Training managers in reshaping workloads to match flexible and part-time hours
• Promote senior figures who work part-time
• Advertise all jobs as being available flexibly for everyone, not just new parents

More work to be done

The dogma around part-time working is of course just one problem in an extremely complex issue. Women also feel anxiety in the workplace from the prevalence of male-dominated upper management, sexual harassment and many other factors. These issues are equally complex to solve but with an ever-increasing awareness hopefully, we can overcome the stagnation in recent years and make the workplace somewhere where everyone can thrive.

Sources: Center for Creative Leadership, Office for National Statistics, Women’s Progression in the Workplace, BBC

Gender Equality in the workplace was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

The sudden onset of the pandemic has forced the government to act quickly on many issues. A special pandemic procurement policy means the government can award contracts that are urgently needed to reduce the risk to peoples health. However, there have been many critics of the way the department of health and social care have dished out these lucrative contracts. Analysis by the New York Times suggests around half of the companies receiving contracts have an unscrupulous past, no prior experience or political affiliation. This has led to many claims of ‘cronyism’ within the department. The government deny any wrongdoing and there is no evidence the law was broken.

We will use Doordas procurement data to see which companies are being awarded these emergency contracts. We can then join this information to our business data to dig deeper into the company and even find who the directors are. Here we will investigate 2 contract-winning companies.

P14 Medical

In total, P14 Medical won contracts worth over £270 million to provide P.P.E. to the NHS. None of these were open for competition.

Stephan Dechan is the owner of P14 Medical and a former conservative councillor on Stroud town council. He has denied claims that his political association gave him an advantage in securing these contracts and that they were given ‘on merit’, despite P14 reporting serious losses in 2019.

Meller Designs

Meller Designs is a home and beauty distributor who won 6 contracts worth over £160 million to provide P.P.E. to the NHS. All were awarded without a call for competition.

Their co-founder David Meller has donated £65,000 to the conservative party over the past decade, including Michael Gove’s leadership campaign in 2016, who is now head of the department responsible for PPE procurement.

Both the government and Meller Designs denied any wrongdoing. A spokesperson for Meller Designs said: “We are extremely proud of the role we played at the height of the crisis and managed to secure more than 150million items of PPE.”

Cause for concern?

During the peak of the pandemic in March 2020, the government were under huge amounts of pressure to deliver P.P.E. to protect frontline workers. Prime Minister Boris Johnson even said the country was on a “war footing”. The use of emergency contracts with no public competition was undoubtedly necessary to secure scarce stock as quickly as possible. But did the government put companies with political association above those with a proven track record? According to a New York Times analysis around half of the released contracts had government ties and with around half of these emergency contracts still not made publicly visible, there could be many more to follow.

Click here for the full report

Sources: National Audit Office, Guardian, New York Times, The Mirror

Connections mean contracts was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

This level of inflation, however, is not sustainable and there are plenty of causes for concern. The end of the stamp duty holiday on the 1st of April 2021 is expected to negatively affect the market. As well as this, faith in inner-city flat markets has plummeted following the fallout of the Grenfell tragedy. According to the Daily Mail, 1 in 7 flat sales are under threat as lenders and insurers grow increasingly concerned over the safety of exterior cladding.

Why the up swing?

In response to the crash in property sales during the first lockdown, the government suspended stamp duty for the first £500,000 of a property’s value in an attempt to boost the market and protect jobs. This was a substantial raise from £125,000 (or £300,000 for first-time buyers). According to a government study, a third of home buyers stated they would spend the money saved on home renovations, further boosting the economy. However, the scheme has come under criticism with some MP’s claiming it is merely a tax break for wealthy property owners and others pointing out its failure to increase house sales (see below). The argument is that without the tax break the fall in house sales would have been even worse.

The increase in prices is also likely due to people looking to move from cities to the countryside. In June and July, Rightmove reported a 126% increase in people considering homes in villages when compared with the same period in 2019.

The stamp duty holiday combined with people looking to move out of cities has resulted in huge demand. Buyers have reported a small supply of new houses to the market, powering the dramatic increase in property prices as buyers enter a bidding war to secure their dream home.

Winners and Losers

The benefits felt by this activity have not been universal. Owners of large detached homes in the country have been the biggest winners following huge demand from people looking for more space to work from home. Inner-city flats have felt the opposite effect as outdoor space has become a highly valuable asset. Some flat owners have been unable to sell entirely due to lenders confidence being shaken by the safety concerns of Grenfell-like cladding. The Times recently confirmed that over 200,000 flats across the country are wrapped in the same, or similar, cladding as Grenfell. New government proposals could force flat owners to pay £40,000 on average to replace unsafe cladding.

Our data displayed above shows sales in detached homes fell by 10% since the start of 2020, whereas flat sales dropped by over 40%.

Turbulent times

Multiple factors affecting housing prices simultaneously has created an extremely turbulent market. The gap between the winners and the losers is as big as ever. Everyone wants to know when this growth will subside. Many believe the end of the stamp duty holiday will reduce demand and with COVID-19 vaccines being rolled out across the country, lockdowns could become far less frequent, convincing homeowners to sell. This could flip the supply/demand balance on its head and put a halt on high growth rates. Another factor to consider is the boost to the economy coming out of lockdown will provide. This will likely have a positive impact on house prices.

In these turbulent times, it seems the only certainty is uncertainty. Overall the only advice we can give is, whether your buying or selling, make sure the timings right.

Sources:

Stamp duty holiday: Gov.uk1, Gov.uk2, BBC

Cladding: Daily Mail, The Times

City dwellers moving to the country: Guardian, Rightmove

All graphs were created using Doorda data. Doorda holds joined, up to date data on UK property, business, demographics, procurement and so much more. Find out more here.

Turbulence in the UK housing market was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Health campaigns have succeeded in reducing the number of smokers in the UK by 5.4% from 2011–2018. This has led to a huge reduction in smoking-related illnesses and millions saved for the NHS. Knowing which areas contain the most smokers is vital in targetting future campaigns and can lead to many other interesting findings. In this article, we use data from Doorda to uncover the areas with the highest and lowest number of smokers.

Background

In 2018, 14.7% of people aged 18 years and above smoked cigarettes. This equates to around 7.2 million of the UK population and represents a steep decline of more than 5% from the last survey in 2011.

Nationally more men (16.5%) smoked than women (13%).

The age group 25 to 34 years has the highest proportion of current smokers at 19.2%.

Around 1 in 4 (25.5%) of people in routine and manual occupations are smokers whereas this is only 10.2% for people in managerial and professional occupations (10.2%).

In Great Britain, 58.4% of people aged 16 years and above who currently smoked said they wanted to quit and 61.3% of those who have ever smoked said they had quit. This would indicate the downward trend should continue over the coming years.

However, it’s worth noting that 6.3% of people in 2018 said they currently used an e-cigarette, which equates to approximately 3.2 million adults in the population. More data will be required to see if this number increases over time.

Highest Smoking Areas

Knowing which areas contain the highest amount of smokers is important in targeting government funding to reduce smoking rates and therefore smoking-related diseases.

To see which area has the highest number of smokers lets first break it down by country. In England, approximately 17.8% of adults smoke, 19.4% in Wales, 19.5% in Scotland and Northern Ireland has the most smokers with 20.7%.

We can break down these results into the local authority level and visualise them (doorda.news/smokingpropensitymap) to narrow down which areas are smoking the most. The local authorities with the highest number of smokers are:

• Blaenau Gwent — South Wales (25%)
• Glasgow City (24.5%)
• Derry City and Strabane (24.4%)
• Knowsley — Liverpool (24.3%)
• Merthyr Tydfil — South Wales (24.2%)

With 2 of the top 5, it appears South Wales is the highest smoking area. We can dive deeper into South Wales and visualise it by ward to gain a better understanding (doorda.news/SouthWalesMap).

At a more granular level, we can see a small area around Merthyr Tydfil and Pontycymmer has exceptionally high smoking rates whereas the more rural surrounding areas have below average rates. These high smoking areas would make perfect targets for anti-smoking campaigns.

Lowest Smoking Areas

We can also use our map of local authorities to find the areas with the fewest smokers:

• City of London (11.3%)
• Richmond upon Thames (11.7%)
• Hart (11.9%)
• Wokingham (12.0%)
• Elmbridge (12.0%)

All of the lowest smoking areas are found either in London or the surrounding counties and highlights the correlation between wealth and the propensity to smoke. It also highlights the range in the number of smokers from region to region with the lowest areas having less than half the amount of smokers as the highest areas.

Conclusion

Moving from South Wales to London is unlikely to affect your smoking habits but it does show how the environment can affect peoples behaviour and eventually their health. This information is vital for many types of customer profiling whether it be targetted campaigning or advertising.

Where the heaviest smokers live was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Spring lockdown led to a huge drop in total crime but more Drug and Anti-Social offences.

The first national lockdown (March-May) set the scene for a huge sociological study. How would being locked at home affect peoples behaviour? The data provided by lockdowns will no doubt be studied for decades to come. In this article, we take a deeper look into how the lockdown affected the amount and type of crimes being reported.

A Decrease in Crime

Some of the most noticeable trends include a drop in burglaries (-40%) and shoplifting (-56%) when comparing April 2020 to April 2019. The same is true for violence and sexual offences, vehicle crime, theft from the person, robbery as well as criminal damage and arson. This is not a surprise as people staying at home and shops closing made it much more difficult to commit certain crimes.

A More Cautious Conclusion

At first glance, it seems the lockdown had a positive impact on the amount of crime committed. However, comparing to the previous year does not give the full story. When looking at the previous 4 years of violence and sexual crimes, a drop of this magnitude does not stand out significantly. It could even be viewed as standard deviation and hence not related to the lockdown. There was also a 35% increase in these types of crimes coming out of lockdown. This surge is most likely related to a lag in the reporting of domestic abuse cases during the lockdown.

Outliers

Interestingly, anti-social behaviour (+73%) and drug-related crime (+25%) saw a steep increase during the same timeframe. Antisocial behaviour surged due to higher numbers of reports of people breaking lockdown restrictions. The increase in drug-related crime is likely caused by a number of factors including an increase in recreational drug use due to stress and lack of access to support services.

As with violent and sexual offences, the cause of this trend is not so easy to distinguish. When looking at the last 3 years there is a steady upward trend in drug-related crime. The surge in cases during lockdown should therefore be taken with a pinch of salt. The upward trend may be due to increased police investigations in these areas and a crackdown on county line drug networks.

Summary

This data shows many interesting trends but some caution should be used when giving credit to the lockdown. Many other aspects affect the level of reported crime such as the way police categorise crimes, ever-changing government guidelines and the willingness of people to report crimes. This is a reminder that data can tell multiple stories and a responsible analysis is needed to show the full picture.

We will update our analysis as we exit Lockdown 3 to see if there is any further correlation with social restrictions and the overall reported crime trends.

Notes

• All data is provided by Doorda. A full report and interactive map can be seen here.
• All visuals exclude Greater Manchester Police as they have not released data since June 2019.
• Figures may differ from those reported by the office of national statistics (ONS). This is due to varying methods of analysis and timeframes used.

Sources: BBC, The Guardian, ITV

COVID andCrime was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

With the press reporting an ever increasing number of stabbing’s and violent assaults we thought we’d dig into the data and see what’s going on. Is this type of crime increasing? Or is it media hype?

Note you’ll find complete details on the data used along with how you can run your own analysis at the end of this article.

Crime Developments in the UK

Before any analysis, we must first generate a better understanding of crime patterns, specifically to answer the following question:

Are Crimes on the Rise? If so, which parts of the country are being affected the most?

To start with, we mapped the number of crimes in the most serious categories against the year of occurrence. An immediate finding is that reported UK crime data contains a lot of ‘Anti Social Behaviour’ related incidents, this is a large category as it contains everything from drinking alcohol in a park to kids on street corners causing a disturbance. As an over arching figure, this largely explains the decreasing trend over the years as due to it’s size it directly translates into an overall decrease in crime in UK. Whether this is due to the police not responding as often due to reduced resources, or there are less incidents is unknown, all we can say is that reported low level crime is decreasing and pulling the overall number down.

However, a decrease in the overall crime rate doesn’t necessarily mean our safety has improved. Many sources (such as the Independent) suggest that violent crimes in London are on a rapidly rising trend, as seen with the recent media frenzy over the keywords ‘Stabbings’, ‘Knife Crime’ and ‘Murder’.

Knife crime isn’t reported separately so we decided to look at changes in the possession of weapons to see if this has increased. The ‘Possession of Weapon’ category, can serve as an indication of the likelihood of an occurrence of a violent crime. The visualization on the left verifies that despite overall crime rates decreasing, the likelihood of being caught with a weapon has almost tripled since 2013. Our initial thought was that this was due to the police putting more focus on the possession of weapons, but stop and search figures show that there were an average of 18 stop and searches per 1,000 people in 2013/14, by 2016/17 this had fallen to 5. This would lead us to assume that there is less focus.

Before we can say knife crime is definitely on the increase we need to look at the distribution of crime by area.

The map visualizations below demonstrate the distribution of the 4 categories of crime we decided to look at across England and Wales.

Overall, a common trend is observed where the following cities tend to have high crime rates across all categories:

• London
• Newcastle
• Manchester
• Sheffield

These numbers are relative due to the differences in the population at each location. However even taking this into account London has a far higher proportion of reported weapon possession incidents compared to the others.

Investigating the Rise of Weapon Crime

With the ‘possession of weapon’ crime category identified as a rising threat in England and Wales, prominent speculations include demographics causes such as age and economic status, while others spectate gangs connected to crimes driven by drugs and anti-social behaviour activities. This section aims to understand the underlying motivators for occurrences of weapon crime by testing against the most prominent speculations.The visualisations below provide some initial insights:

Population Age: Occurrences of ‘possession of weapon’ crime peak at area with average population age between 25 to 50.

Anti-Social Behaviors: No observable trend

Drugs Related Crime: No observable trend

Household Deprivation: No observable trend

Note: Correlations between population qualification and weapon crimes were also run, but no insights were gained.

The results would indicate that the common assumptions are incorrect or the reported incidents are occurring away from home locations.

Which Location Are You Most likely To Be Assaulted With A Weapon?

Finally, to answer the core question of this article, we have mapped the number of reported ‘possession of weapon’ crimes to the property category of the location. This indicated that over 70% of the crimes were caught within the proximity of a ‘shop’, disproving popular beliefs that there is a higher risk of weapon crime in pubs, quiet car parks or housing estates.

Taking it further, to identify the specific type of shops within the category of ‘shop’, the data were also mapped to the commercial owner of the property. It was identified that gambling facilities (such as Betfred and William Hill) showed the highest count of reported weapon related crimes in their proximity. This is just indicative of the areas where these shops are based as a pose to the actual retailer being the focal point.

Conclusion

Our analysis shows:

• Overall reported crime is falling but mainly due to the decreases in Anti-Social Behaviour.
• The reported ‘Possession of Weapon’ category is rapidly increasing whilst the number of stop and searches and decreased.
• Weapons are not being found on deprived housing estates or areas with large number of youths.
• People are being caught in possession of weapons on the high street. Mainly near premises located in larger numbers in deprived areas such as betting shops

In conclusion our research shows the commonly held belief that people are more at risk in dark alleys or housings estates isn’t proven in the data. Surprising people are more likely to be caught carry a weapon on the high street.

Want to Create Your Own Analysis?

This articles utilized a range of different data to create interesting insights toward the more severe crimes in the UK, while diving into a deeper level on ‘possession of weapon’ related crimes by testing against its causes and location of occurrence. However, many more analysis can be made by diving into the 130 different datasets hosted in DoordaStats.

At Doorda we have mapped all our statistical data to a postcode level, which means you can easily add more granular insights to your geographical analysis. Our next article will be on imports and exports data, demonstrating the implication of the recent US trade tariffs toward UK companies.

Data Used

We’re happy to offer access to our data so just drop us an email and we’ll provide a login to our base datasets hosted on BigQuery. This allows you to connected any tool you like, Python, Tableau, Power BI up to you. We will NOT use your details for marketing purposes. But we’d love to know what you uncover!

DoordaStats

We used the spine in DoordaStats as a base starting point. This consists of basic details like postcode, output area, postcode introduction date, latitude, longitude along with population and household numbers.

Crime Data

DoordaStats consists of data on more than 20 different categories of incidence and crime, for the purpose of the analysis we will only be focusing on the occurrences of severe/ serious crimes in London. They are:

• Possession of Weapon: Found under the file name `r1_incident_possession_of_weapons`.
• Anti Social Behaviors: Found under the file name `r1_incident_anti_social_behaviour`.
• Criminal Damage and Arson: Found under the file name `r1_incident_criminal_damageand_arson`.
• Drugs: Found under the file name `r1_incident_drugs`.

Area Demographics Data

To identify the causes of serious crimes, we correlated the crime data with some area demographic datasets, including:

• Population Age: Found under the file name `r1_people_population_age_bands`.
• Population Education Level: Found under the file name `r1_people_highest_level_qualification`.
• Household Deprivation: Found under the file name `r1_household_deprivation_dimensions`.

Property Ownership Data

Finally, to identify the places in which weapon related crimes are likely to take place, the crime data was mapped to our occupancy dataset. This contains commercial occupant name and details on the type of of property . The data are found under the file name`business_rate`.

Connect to Doorda Host (BigQuery) and Merging with Stats Spine

Doorda’s data are hosted on Google’s BigQuery platform, it is easily accessible using a wide variety of tools including python, R and tableau.

The following documentations will provide an in-depth guide for connecting to Doorda’s database on BigQuery (Once yo have you r login details):

• Using python: https://www.doorda.com/kb/article/connecting-to-doorda-host-big-query-with-python.html
• Using R- Studio: https://www.doorda.com/kb/article/getting-started-with-doorda-host-and-r-studio.html
• Using tableau: https://www.doorda.com/kb/article/getting-started-with-doorda-host-using-tableau.html

All datasets within DoordaStats contain postcode information, which can be used as a key for merging your unique dataset for analysis.

Notes: it is recommended to export the final dataset onto your local machine to avoid repeating the same process of pulling and merging datasets should you restart the python script.

CRIME DATA IS THE ONLY TIP OF THE ICEBERG OF DOORDA’S STATISTICS DATA , AS DOORDASTATS ALSO ALLOWS YOU TO DIVE INTO HOUSEHOLD EXPENDITURE, LIVING ARRANGEMENTS AND ECONOMIC WELL-BEING DATA.

Which Location are you Most likely to be Assaulted with a Weapon? was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Welcome to the 3rd article of the series in which we help all you DataTrekers uncover new ways of gaining the edge over your competitors. Today we will be diving into the UK drugs prescription data.

Data Used

This article features the monthly prescription data as released by the NHS Prescription Services. The data contains information on drugs prescribed by local GP, with the corresponding number of prescriptions and associated cost. Specifically, we will be focusing on three types of drug:

1. Nasal Allergy Related Drugs: Found under the category`Ear, Nose and Oropharynx`, with the file name `bnf_paragraph_ear_nose_and_oropharynx_201601` in the Doorda database.
2. Hypertension and Heart Failure Related Drugs: Found under the category`Cardiovascular System`, with the file name `bnf_section_cardiovascular_system_201601` in the Doorda database.
3. Obesity and Depression Related Drugs: Found under the category`Central Nervous System`, with the file name `bnf_section_central_nervous_system_201601` in the Doorda database.

Connect to Doorda Host (BigQuery)

Before the analysis, we must first collect the data we need. As mentioned above, we will be pulling 3 dataset from Doorda’s database hosted on BigQuery.

# Pulling prescription data on cardiovascular related drugs
projectid= 'beta-203614'

query= 'SELECT * FROM Eval_DoordaHealth.bnf_section_cardiovascular_system_201601'

cardiovascular= pd.io.gbq.read_gbq(query, projectid)
 
# Pulling prescription data on central nervous system related drugs
projectid= 'beta-203614'

query= 'SELECT * FROM Eval_DoordaHealth.bnf_section_central_nervous_system_201601'

central_nervous= pd.io.gbq.read_gbq(query, projectid)

# Pulling prescription data on ear, nose and oropharynx related drugs
projectid= 'beta-203614'

query= 'SELECT * FROM Eval_DoordaHealth.bnf_paragraph_ear_nose_and_oropharynx_201601'

allergy= pd.io.gbq.read_gbq(query, projectid)

The following documentation provides an in-depth guide for connecting to Doorda’s database on BigQuery:
https://www.doorda.com/kb/article/connecting-to-doorda-host-with-python.html

Uncovering Hay Fever

Hay fever is one of the most common allergies found in the UK, approximately 20% of the UK population suffer from it. Additionally, our data shows that nasal allergy medications accounted for over 50% of ear, nose and oropharynx related drugs, amongst a total of 11 drugs within the category.

Therefore, our first analysis aims to identify:

The Severity of Hay Fever in Urban and Rural Areas

To do so, we will compare the differences between Urban (LS1, LS2) and Rural (LS8, LS9) in the Leeds area, demonstrating the average number of nasal allergy drugs prescribed (per person) in the respective postal areas.

Note: The number of drugs prescribed only provides an INDICATION to the number of people suffering from the said disease as prescription volume will vary from one person to the next.

Step 1: Geographical Segmentation

The first step of the analysis involves segmenting Leeds into urban and rural areas, which can be done using postcodes.

Urban: LS1 and LS2

Rural: LS8 and LS9

# Urban area (LS1, LS2) 
urban= ['LS1 ', 'LS2 ']

allergy_urban= allergy[allergy['postcode'].str.contains('|'.join(urban))]

# Rural area (LS8, LS9)
rural= ['LS8 ', 'LS9 ']

allergy_rural= allergy[allergy['postcode'].str.contains('|'.join(rural))]

Step 2: Calculate Average Prescriptions per Head

When comparing the number of drugs prescribed, we must also account for the population of the area. Therefore, we divide the number of drugs prescribed by the corresponding population for each postcode, then calculate the weighted average of drugs prescribed per person. Finally, we calculate the overall average for the urban and rural area.

# Divide number of drugs by population
allergy_per_population_urban= allergy_urban['drugs_used_in_nasal_allergy_items']/ allergy_urban['postcode_population']

allergy_per_population_rural= allergy_rural['drugs_used_in_nasal_allergy_items']/ allergy_rural['postcode_population']

# Calculate the overall average
avg_urban_allergy= allergy_per_population_urban.mean()
avg_rural_allergy= allergy_per_population_rural.mean()

Step 3: Results

The numbers below represent the average nose allergy drugs prescribed per person:

• Urban: 0.0039
• Rural: 0.0088

The results show that the number of allergy drugs prescribed in rural areas is double the number in urban, this would indicate that an average person in rural area is more likely to suffer from hay fever. Possible reasons include larger exposure to pollen due to the larger planted areas.

Culprit of Heart Disease- Depression or Obesity

Heart disease is one of the most common causes of death in the UK. Many studies have attempted to identify the causes of heart disease, among which smoking, obesity and mental well-being are considered the top causes. Notably, the following statement were recently made by the British Heart Foundation:

'If you're overweight or obese you are more likely to develop coronary heart disease'

'People with severe mental health problems are two to three times more likely to suffer from heart and circulatory disease'

Hence, the second part of our analysis will verify these claims in the context of Leeds. To do so, we ran correlation analysis to identify the relationship between the average number of heart failure drugs, to the average number of obesity and anti-depressants prescribed.

Note: The number of drugs prescribed will only provide an INDICATION as to the number of people suffering from said disease.

Step 1: Geographical Segmentation

Similar to the above, we first group the data by postcodes.

# Filter cardiovascular data by Postcode

cardiovascular_LS1= cardiovascular[cardiovascular['postcode'].str.match('LS1 ')

cardiovascular_LS9= cardiovascular[cardiovascular['postcode'].str.match('LS9 ')]

# Filter central_nervous data by Postcode

central_nervous_LS1= 
central_nervous[central_nervous['postcode'].str.match('LS1 ')]

central_nervous_LS9= central_nervous[central_nervous['postcode'].str.match('LS9 ')]

Step 2: Preparing Prescriptions per Head Data

In the 2nd step we calculate the average number of prescriptions, taking into account the population. We are also storing them in a list for correlation analysis in later sections.

# Calculate heart disease per population
heart_per_population_LS1= cardiovascular_LS1['hypertension_and_heart_failure_items'].sum()/cardiovascular_LS1['postcode_population'].sum()

heart_per_population_LS9= cardiovascular_LS9['hypertension_and_heart_failure_items'].sum()/cardiovascular_LS9['postcode_population'].sum()

# Store the data in a list
heart_per_population= [heart_per_population_LS1, heart_per_population_LS2, heart_per_population_LS3, heart_per_population_LS4, heart_per_population_LS5, heart_per_population_LS6, heart_per_population_LS7, heart_per_population_LS8, heart_per_population_LS9]

# Calculate obesity per population
obese_per_population_LS1= central_nervous_LS1['obesity_items'].sum()/central_nervous_LS1['postcode_population'].sum()

obese_per_population_LS9= central_nervous_LS9['obesity_items'].sum()/central_nervous_LS9['postcode_population'].sum()

# Store the data in a list
obese_per_population= [obese_per_population_LS1, obese_per_population_LS2, obese_per_population_LS3, obese_per_population_LS4, obese_per_population_LS5, obese_per_population_LS6, obese_per_population_LS7, obese_per_population_LS8, obese_per_population_LS9]

# Calculate depression per population
depress_per_population_LS1= central_nervous_LS1['antidepressant_drugs_items'].sum()/central_nervous_LS1['postcode_population'].sum()

depress_per_population_LS9= central_nervous_LS9['antidepressant_drugs_items'].sum()/central_nervous_LS9['postcode_population'].sum()

# Store the data in a list
depress_per_population= [depress_per_population_LS1, depress_per_population_LS2, depress_per_population_LS3, depress_per_population_LS4, depress_per_population_LS5, depress_per_population_LS6, depress_per_population_LS7, depress_per_population_LS8, depress_per_population_LS9]

Step 3: Correlation Analysis

Two correlation analysis are conducted to identify:

a) Relationship between heart disease and depression

import numpy as np
from scipy.stats import pearsonr
 
# Correlation Between heart disease and depression
r,p = pearsonr(heart_per_population, depress_per_population)
print (r,p)

Significant results (p= 0.0006) is found between heart disease and depression, in which a near perfect positive correlation (r= 0.91) is found.

Note: A positive correlation doesn’t imply that depression causes heart disease, it only means that occurrences of depression tend to be accompanied by heart disease

b) Relationship between heart disease and obesity

# Correlation Between heart disease and obesity
Correlation Between heart disease and obesity
r,p = pearsonr(heart_per_population,obese_per_population)
print (r,p)

An insignificant results (p= 0.08) is found between heart disease and obesity, despite a positive correlation (r= 0.61) being found.

Step 4: Visualisation

Finally, taking everything a step further we’ll visualise the results in a scatter plot.

import matplotlib.pyplot as plt

# Create scatter plot for heart disease and depression
plt.scatter(heart_per_population, depress_per_population)

plt.suptitle('Correlation between Heart Disease and Depression ', fontsize=13)
plt.xlabel('Heart Disease Drug Prescribed', fontsize=10)
plt.ylabel('Anti-depressants Prescribed', fontsize=10)

plt.show()

# Create scatter plot for heart disease and obesity
plt.scatter(heart_per_population, obese_per_population)
plt.ylim(0, 0.0008)

plt.suptitle('Correlation between Obesity and Heart Disease', fontsize=13)
plt.xlabel('Heart Disease Drug Prescribed', fontsize=10)
plt.ylabel('Obesity Drug Prescribed', fontsize=10)

plt.show()

Overall, significant correlations are found between heart disease and depression, but further research will be required to test their causation relationship.

Could heart disease affect your mental well-being? Or is mental well-being a determining factor for causing heart disease?

Conclusion

This articles utilised a range of different drug prescriptions data to create interesting insights on hay fever and heart disease. However, many more can be obtained by combining other data-sets such as patient demographics and behaviourial data, such as age profile and smoking habits.

At Doorda we have mapped all our statistical data to a postcode level, which means you can easily add more granular insights to your geographical analysis. Our next article will be on crime data, specifically featuring the occurrences of violent which had been dominating headlines.

DRUG PRESCRIPTION DATA IS THE ONLY TIP OF THE ICEBERG OF DOORDA’S HEALTH DATA, AS DOORDA HEALTH ALSO ALLOWS YOU TO DIVE INTO GP PATIENTS PROFILES, ILLNESS PREVALENCE AND GP SERVICE LEVELS

Visit Us at Doorda for more details!

Uncovering Prevalence of Hay Fever and Heart Disease in the UK was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Renting in the UK: Modelling the Market

Welcome to the 2nd article of the series in which we help all you DataTrekers to uncover new ways of gaining the edge over your competitors. Today we will be diving into the UK property rental market.

Data Used

For this project we used the Lookup Domestic dataset (part of our DoordaLookup range of products) on our data platform. The data features over 27 millions addresses in England and Wales, with up to 43 data point collected from each address and combined transaction value of properties over £2.8 trillion.

Lookup Domestic is an aggregated, refined and cleaned data-set which we collected from multiple sources, including Land Registry, Valuation Office Agency and Ministry of Housing, Communities and Local Government’s.

Tool Used

• Google BigQuery
• Google Data Studio

All visualisations and analysis were made using Google Data Studio, which pulls data directly from our data stored on BigQuery.

Who’s the Biggest Landlord in England and Wales?

Using the data on commercial owner’s name and type, we created a pie chart to helps us analyse the composition of commercial owners:

Currently, private companies makes up for the biggest group of commercial owner type, equivalent to 63.8% of total property, with usages ranging from investment, rental, to business facilities such as offices and factories. On the other hand, local authorities make up for 6.3% of total properties. Sequentially, more properties are rented from private landlord (15.81% of total properties) than councils (8.85% of total properties). It is also interesting to note that property rented from private landlord tend to be more expensive.

Taking it further, the 10 biggest properties owners in the UK consists of a mixture of council and private landlords. The biggest private owner in the UK is Wallace Estates Limited, a property investment company with over 20,000 properties. Its main lines of business includes renting, buying and selling of real estates. While the biggest council owner is the city council of Bristol, which owns 9715 properties.

Comparing the Most and Least Expensive Location to Rent

Using the data on rental prices, we have identified the most and least expensive location to rent in the United Kingdom.

Which of the 2 above locations are more expensive to rent? If your answer is the picture on the left, you are correct!

Picture on the left is taken from the City of Westminster, the most expensive location to rent (or possibly to buy) in England and Wales, with average rental price of nearly £5,000 per month, while its highest value can go up to over £13,000.

On the other hand, picture on the right is taken from Kingston upon Hull, the least expensive location to rent in England and Wales, with an average rental price of £425.83 per month, while the highest value in the area is only £434.

This supports our analysis in previous article: A New Way to Find High Net-Worth Individuals? where we found that Households in Westminster has among the highest expenditure in London.

Calculating Price/m²

It may seem that the above findings doesn't compare the true value between properties in Westminster and Hull, since it only looks at the rental price of the property on a whole, without taking into account the size of the property. What if the reason for a higher rental price is purely due to a larger property floor area? Therefore, to enable a fair comparison, we compared the price per square metre of properties.

To do so, we must introduce another metric from the dataset, Average Floor Area, which provides information regarding the size of property.

The above tables compare the highest average rental price per square metre, which is calculated by dividing average rental price by the floor area of the property. The finding demonstrates that properties in Westminster is OVERWHELMINGLY MORE EXPENSIVE than Hull!

NOTE: If you want to compare the individual properties between the area, make sure you also take into account the type of property! Otherwise your result can be erroneous if you compare the price of a flat to a house.

Conclusion

It is relatively simple to visualise rental data in a pie or bar chart, but the real power/insight can be obtained when combining property information and rental prices with other geographical data such as property transactions, households demographics or expenditure.

At Doorda we have mapped all our statistical data to a postcode level, which means that you can have more granular insights in your geographical analysis. Our next article will be on occupancy data, demonstrating how we can create value by understanding business occupancy.

DOORDA’S RENTAL DATA ALLOW YOU TO ANALYSIS AND SEGMENT OVER 27 MILLIONS PROPERTIES ON UP TO 43 FACTORS INCLUDING PROPERTY TYPE, TENURE, COUNCIL TAX BANDS AND MANY MORE.

Visit Us at Doorda for more details!

Renting in the UK: Modelling the Market was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Welcome to the first, in what I hope, will be a regular series helping all you DataTrekers out there uncover new ways of gaining the edge over your competitors, be they at college, or another company!

Like many of my fellow analysts, I have a deep interest in how data can be deployed to provide new insights. At Doorda, we’re always on the hunt for new sources we can add to our already extensive catalog.

However, we must first ask the question, is there any value in it? Generally speaking there is rarely any value in a single dataset, it’s only when you combine them you get to see the real value. Luckily working at Doorda I have access to a range of pre-processed data and advanced tools, so we can answer the question very quickly.

For the benefit of this article I’m going to work on the basis that you’re starting from scratch. To keep us focused I’m going to ask a simple question:

Where do the culture vultures live?

Why? It demands that we know household expenditure along with how much they typically spend. It’s also a very effective way of finding where the well educated, high net worth individuals tend to hang out.

Data Used

For this project we used the datasets listed below. Most of these we have access to in our database. However, for the benefit of this article I’ll take you through the process of acquiring, cleaning and mapping the data.

• Average weekly household expenditure by Output Area Classification(ONS)
• 2011 OAC Clusters and Names (ONS)
• Output Area Boundaries (ONS)

Tools Used

• R Studio
• My trusty Mac

I’ll mainly be using R as it’s my go to language for processing and analysing data.

When you extract data from public websites you also need to check the reuse licence, luckily for us most of the data we’re using is released under Open Government Licence, so we can fill our boots.

Acquiring Data

All 3 datasets can be acquired from the link above and can be imported to R as is, other than the Weekly Expenditure data, which will require conversion from xls to csv.

library("data.table")
library("rgdal")
library("plyr")
library("ggplot2")
library("ggmaps")
library("sp")

# Weekly Expenditure Data 
household_expenditure <- fread("/home/ubuntu/household_expenditure.csv")

# OAC Cluster and Names
oac_cluster = fread("/home/ubuntu/2011 OAC Clusters and Names csv v2.csv")

# OA Boundaries Polygon (SHP file)
oa_bd <- readOGR(“./oa_boundaries”, “Output_Area_December_2011_Full_Extent_Boundaries_in_England_and_Wales”)

The Weekly Expenditure with 16 different categories:

1. Food and Non-Alcoholic Drinks
2. Alcoholic Drink, Tobacco and Narcotics
3. Clothing and Footwear
4. Housing, Fuel and Power
5. Household Goods and Services
6. Health 
7. Transport
8. Communication
9. Recreation and Culture
10. Education
11. Restaurants and Hotels
12. Miscellaneous Goods and Services
13. All Expenditure Groups
14. Other Expenditure Items
15. Total Expenditure
16. Weekly Expenditure per Person

Data Process

Next step, we have to process the data in order for it to be usable. We first join the expenditure data with the Output Area Classification data. To do this, we need to replace spaces with underscores for the Group Name and then join it with the expenditure data.

1. Match weekly expenditure to OAC cluster names dataset by OAC group (i.e. Farming_Communities_1A, Rural_Tenants_1B)

# Create Group Name in OAC to match group name in expenditure data
oac_cluster$oac_group = paste(gsub(" ", "_", oac$`Group Name`), toupper(oac$`Group Code`), sep = "_")

# Left join on oac_cluster and household_expenditure
oac_cluster_expenditure = join(oac_cluster, household_expenditure, by = "oac_group", type = "left", match="first")

Once we have assigned the values in the expenditure data to each Output Areas, we will need to add that data to the Spatial Polygon dataframe we imported using the SHP file earlier. We also need to convert the Spatial data to a normal dataframe so that it can be used by ggmaps and ggplot2 library.

2. Add expenditure data to Spatial Data and convert to dataframe

# Standardise oa naming 
colnames(oac_cluster_expenditure)[2] = "oa11cd"

# Merge data with Spatial Polygon Dataframe
full_data = merge(oa_bd, oac_cluster_expenditure, by="oa11cd", sort=FALSE)

# Convert Easting Northing to Lat Lon 
full_data_transformed <- spTransform(full_data, CRS("+proj=longlat +datum=WGS84"))

# Converts Spatial data to dataframe that can be plot on a map
full_data_transformed@data$id = rownames(full_data_transformed@data)

full_data_transformed = fortify(full_data_transformed, region="id")

full_data.df = join(full_data_transformed.points, full_data_transformed@data, by="id")

Visualising

Now that we have the data cleaned and transformed our data, we can start visualising. We will be plotting the polygons we imported earlier from the SHP file using ggplot2 and ggmaps library. Let’s see how the spread of weekly expenditure per person looks like in North West and South East London. (Warning: the spatial data consists of 181408 polygons which will require a significant amount of memory to render, so create a subset of the data instead)

# Function to Round Up values
RoundUp <- function(from, to) ceiling(from / to) * to

# Round up weekly expenditure to the nearest £100 and convert to factor
full_data.df$weekly_expenditure_per_person = as.factor(RoundUp(as.integer(full_data.df$weekly_expenditure_per_person), 100))

# Get the map of the area using the lat lon of the data
guk <- get_map(location=c(lat=mean(full_data.df$lat), lon=mean(full_data.df$long)))

# Plot on Map 
ggmap(guk) + 
  geom_polygon(aes(lon=long, lat=lat, group=group, fill=weekly_expenditure_per_person), 
               data = full_data.df,
               alpha = 0.8, 
               color = "white",
               size = 0.2)+
  geom_path(color="white") +
  coord_equal() +
  scale_fill_brewer("Weekly Expenditure per Person (£)", palette="YlOrRd")

Results

With the data mapped, we are able to identify areas in NW London and SE London with high expenditure (specifically in areas like Kensington and Westminster). This process can also be used to identify the expenditure of various items in different areas.

Conclusion

It is relatively simple to plot the Census data on a map, but the real power/insight can be obtained when combining Census data with other geographical data like property prices, age groups, ethnicity etc. Also, since this example only maps the data to Output Area level (175, 434 OAs), at certain locations, we are not able to have more granularity in the insights.

At Doorda we have mapped all our statistical data to a postcode level, which means that you can have more granular insights in your geographical analysis.

Our next article will be on location analysis, how can you tell if an area is getting wealthier or poorer? You’ll be surprised what data we use to answer the question.

A new way to find high net worth individuals? was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.

Amid the rise in Business Rates and Wages, consumer confidence, taking a tumble and the growth in online retail the doom mongers are forecasting a ‘retail apocalypse’ for those who are slow to respond.

Among Marks & Spencer closing over 100 stores in the next four years and Mothercare closing 50 stores and many others following suit. The Financial Times highlights that some clothing retailers are bucking the trend, and many old department stores are transforming into new trendy retail shops. I’m going to stick my head over the parapet and say it’s more of a revolution than a apocalypse.

Regardless of our opinion I think we can all agree the commercial property sector is moving faster than any of us can keep track of. If you are a retailer next to a Marks & Spencer store which is closing your footfall, could decrease or switch to a completely different demographic.

Currently if you want to track commercial property activity, you need to pay for expensive market reports covering specific sectors or businesses. Being the datalutionaries we are, we know there must be a better way!

What if you could access this data on demand and create your analysis with automated updates?

What if you were also able to add 100’s of additional data points on individual companies to extract more profound insights with enhanced tracking capability?

We are pleased to announce that we are going switch the ‘What If?’ to ‘Now You Can’!

As one of the leading experts in the acquisition, joining and delivery of data, we are able to deploy our capabilities into the very fractured commercial occupancy space.

Over these past few months, we have researched every local authority to form a National Non-Domestic Rates database. This will include the company number, so you can easily cross-reference the data internally or supplement it to the additional data we have on risk, compliance and Companies House filings.

How can this benefit you?

• Follow a company’s movements over time from one address to another with occupancy dates.
• View if a company is growing or shrinking.
• Discover how a regional market is developing and the impact it may have on the local businesses.
• Track who’s occupying an address along with who currently owns it and when they bought it.

In summary, we will be providing you with access to intelligent, factual, insights ahead of any of your competitors, throw in the fastest updates on the market and what’s not to like.

Would you like to see a demo? Contact us to book an appointment.

The retail apocalypse is going to get you… was originally published in Doorda on Medium, where people are continuing the conversation by highlighting and responding to this story.