Indian Government Hospital EDA using MYSQL

The goal of this project was to use data collected from (https://pib.gov.in/PressReleasePage.aspx?PRID=1539877) to explore the key metric, Population per Bed. The files contained the details of the number of beds and hospitals in different states and union territories as of July 2018. The dataset is further classified into the ministries that maintain these hospitals and beds (Ministry of Defense, Railways, etc.). Data relating to the Population in India and Zonal Distribution of India was collected from the government website.

The data was cleaned and prepared in Excel then uploaded to MySQL Workbench and exploratory analysis was performed. Queries used explored: The beneficiaries per bed in Defense, ESIC, Railway hospitals, AYUSH hospital and their ownership stake in different states/UTs, Zones wise beds available for the population, beds ratio in PHC, CHC, SDH, DH in states/UTs, Data Updation, etc.

Some Important results have been displayed below and the remaining queries are attached as images.

ESIC Hospitals EDA

Comparing the below figure with the WHO recommendation of a minimum of 3 beds per 1000 population, the situation of ESI hospital is worst having 1 bed for approx. 7000 people making it 20 times more than recommended. So the following measures can be taken -

• In long run, there is a strong need for infrastructure planning and development in terms of new hospitals and manpower.
• In the short run, it can collaborate and have MOUs with Private hospitals where the beneficiaries can have cashless treatment.
• Since it has a huge beneficiary base, it can opt for mass education regarding healthy lifestyles and diets.

Defense Hospitals EDA

The Defense hospitals is having a good bed-to-population ratio but a little higher than the WHO recommendation. Still, to match it to the recommendation, it can upgrade existing facilities and increase the operational capacity.

AYUSH Hospitals EDA

The AYUSH (Ayurveda, Yoga & Naturopathy, Unani, Siddha, and Homeopathy) has been an integral part of the Indian healthcare delivery system since ancient times. With the emergence of Allopathy, private players have very less interest in setting up AYUSH Hospitals because of public acceptance and popularity, especially in some regions.

In most of the states/UTs, only Government is promoting and setting up AYUSH care. So, there is an urgent need to incentivize private players to set up AYUSH care and aggressively promote to the masses the benefits of this alternative therapy. It will ease the excessive burden on Allopathic facilities also AYUSH treatment is Pocket friendly and have fewer side effect.

With time, there has been PPP in most states for setting up AYUSH.

Railway Hospitals EDA

Railway has an impressive ratio among all the corporate government undertakings. It can focus on streaming it process and operation and make it more efficient and patient-centric.

Zone-wise Hospitals EDA

The zonal distribution of population and healthcare resources is non-equitable and is not evenly distributed. There has been a huge deviation between the regions for say South zone has the minimum number of people per bed having 752 whereas the east zone has the highest having 3516 people per bed.

So, to overcome these challenges and to make health services more accessible, available, and affordable the following actions can be taken -

• State government commitment towards policy formulation for healthcare infrastructure development especially in eastern and central zones having states like Bihar, Jharkhand, MP, and so on.
• Need of investing in Green Field projects in region where no existing healthcare facilities exist.
• Replicate the successful models like Mohalla clinics in Delhi and models that are present in southern zone states.
• Free flow of healthcare resources and facilitating transportation for Patients between states.
• Incentivizing and easing regulatory framework for private players to operate hospitals in regions where there is a high population per bed ratio.

State/UT Hospitals (PHC, CHC, the SDH, areDH) EDA

Small states/UTs have impressive ratio ranking top in the table whereas states topping from the top in the Population table are topping from the below in the Population per bed table. Bihar is having the worst condition among all followed by Jharkhand and UP.

So, it clearly shows the inverse relationship between the population and the healthcare ecosystem in States. A large pool of healthcare resources is needed in these states to serve the population better.