<jupyter_start><jupyter_text>Data Project: Pharmaceutical Prices and Competition  Introduction This data project examines the effect of competition--measured in number of substitutes--on the price development of pharmaceuticals. The idea has been adapted from the kindly suggested topic. The idea being that the number of substitutes to act as a strong indicator of the level of competition on a market and that competition generally dampens price increases. Practically, only products which have been on the market during the entire examined period (March 2015 to March 2020) and with rapported AUP pr. DDD (consumers' price per day) have been considered. Due to differences in typical market strategies across different pharmaceutical categories, some bias may occur from this method. It is not possible to predict the direction of the bias without thorough research of pharmaceutical products, which is deemed outside of the scope of this project. Firstly, median period to period price changes are considered for each level of competition. ALthough differences are apparent, no trends are dicernable. Secondly, the median price changes from the entire period is inspected, so as to paint a more uniform piture. Although it is clear that the variance of the median price changes increase in relation to number of substitutes, no definite answer to the question is found here either. Imports and set magics:<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import ipywidgets as widgets
import numpy as np
from datetime import datetime

# autoreload modules when code is run
%load_ext autoreload
%autoreload 2<jupyter_output>The autoreload extension is already loaded. To reload it, use:
  %reload_ext autoreload<jupyter_text>Read and clean data  Medicinal price data<jupyter_code># load data
prices = pd.read_excel('lmpriser_eSundhed_200323.xlsx', sheet_name = 'DATA')

# drop columns
drop_these = ['Lægemiddel', 'Varenummer',  'Pakning', 'Styrke', 'Form', 'Firma', 'Indikator']
prices.drop(drop_these, axis=1, inplace=True)

# drop all but AUP pr. DDD rows seeing as this is the pertinent price metric
prices = prices[3::4]

# drop all rows lacking AUP pr. DDD data
prices = prices.dropna()

# reset index
prices = prices.reset_index(drop=True)

# show
display(prices)
# find lowest price grouped by ATC for each period 
grouped_prices = prices.groupby('ATC')
grouped_prices = grouped_prices.agg(np.min)

# show
display(grouped_prices)
# Find changes in lowest price (AUP pr. DDD) for each ATC in each period 
pct_change = grouped_prices.pct_change(axis='columns', fill_method='ffill')

# removing missing values from first period
pct_change.drop(20150202, axis=1, inplace=True)

# show
display(pct_change)<jupyter_output><empty_output><jupyter_text>**Count ATC duplicates:**<jupyter_code>dups_ATC = prices.pivot_table(index=['ATC'], aggfunc='size')

#Change type of dups_ATC from series to dataframe
dups_ATC = dups_ATC.to_frame()

# rename column
dups_ATC= dups_ATC.rename(columns = {0:'dublicates'})

# show
display(dups_ATC)<jupyter_output><empty_output><jupyter_text>**Merge dataframe with price change data and dataframe with dublicate data:**<jupyter_code>merged = pd.merge(pct_change, dups_ATC, on='ATC', how='left')

# show
display(merged)
#Calculate mean price change for each ATC group in all periods. Also reset index
merged_mean = merged.groupby('dublicates').agg('mean').round(2).reset_index()

# convert to long format
merged_mean_long = pd.melt(merged_mean ,id_vars=['dublicates'],var_name='date', value_name='price_change')

# convert date float to datetime format
merged_mean_long['date'] = pd.to_datetime(merged_mean_long['date'], format='%Y%m%d')

# show
display(merged_mean_long)<jupyter_output><empty_output><jupyter_text>**Visual analysis**<jupyter_code>merged_mean_long.groupby('dublicates').agg(['mean']).round(3)<jupyter_output><empty_output><jupyter_text>Analysis Thorugh tables and plots the price data is reviewed. Firstly interactive and very detailed, secondly a finishing overview.<jupyter_code>def plot_price_changes(merged_mean,dublicates): 
    
        df = merged_mean
        y = 'price_change'
        
        I = df['dublicates'] == dublicates
        ax = df.loc[I,:].plot(x='date', y=y, style='-o')
        
widgets.interact(plot_price_changes, 
    
    merged_mean = widgets.fixed(merged_mean_long),
    dublicates = widgets.Dropdown(description='dublicates', 
                                    options=merged_mean_long.dublicates.unique())
                 
);<jupyter_output><empty_output><jupyter_text>The above interactive plot gives an impression of price changes in cheapest medicinal substitute thorughout the last five years in differing market conditions--ranging from monopoly to something akin to perfect competition. A definitive correlation between price changes throughout the period and the competitive environment is not identified. **To get a different view of the data total price changes are considered:**<jupyter_code># an additional dataframe is produced including absolute prices instead of period by period changes
# this provides summary data from start till end of data
# index is reset
merged_abs = pd.merge(grouped_prices, dups_ATC, on='ATC', how='left').reset_index()

# removing all columns except number of dublicates, ATC, and data for the first and last price report
merged_abs = merged_abs.filter(['ATC','dublicates', 20150202, 20200323])

# rename columns
merged_abs= merged_abs.rename(columns = {20150202:'first_price', 20200323: 'last_price'})

# calculate percentage change from first price (20150202) to last price (20200323)
merged_abs['total_pct_change'] = merged_abs.apply(lambda row: ((row.last_price-row.first_price)/row.last_price)*100, axis=1)

# find median of price changes for each ATC-group
merged_abs_grouped = merged_abs.groupby('dublicates').agg(['median']).round(2).reset_index()

# show in both summary table as well as a scatter plot
display(merged_abs_grouped)
plt.scatter(merged_abs_grouped['dublicates'], merged_abs_grouped['total_pct_change'])
plt.title('Median total price changes(AUP pr. DDD) in medicinal products, March 2015-2020')
plt.xlabel('Number of substitutes')
plt.ylabel('Median total price change, pct.')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0865828425911189, 'fraction_numerical': 0.01448352697755849, 'mean_word_length': 5.756989247311828, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '226131', 'license': 'MIT', 'n_tokens_mistral': 1972, 'n_tokens_neox': 1831, 'n_words': 644, 'path': 'dataproject/Data Project Pharmaceutical Prices and Competition.ipynb', 'repo_name': 'NumEconCopenhagen/projects-2020-jrf330', 'size': '6283'}