<pre><code>import pandas as pd
# Setup the data
data = pd.DataFrame({'Date': {0: '2017-12-01',
1: '2017-12-01',
2: '2017-12-01',
3: '2017-12-01',
4: '2017-12-01',
5: '2017-12-01'},
'Newspaper': {0: 'NewsPaper1',
1: 'NewsPaper2',
2: 'NewsPaper3',
3: 'NewsPaper4',
4: 'NewsPaper5',
5: 'NewsPaper6'},
'City1': {0: 231563, 1: 173009, 2: 40511, 3: 37770, 4: 5176, 5: 137650},
'City2': {0: 8696, 1: 12180, 2: 4600, 3: 2980, 4: 900, 5: 8025},
'Region1Total': {0: 240259,
1: 185189,
2: 45111,
3: 40750,
4: 6076,
5: 145675},
'City3': {0: 21072, 1: 28910, 2: 5040, 3: 6520, 4: 1790, 5: 25300},
'City4': {0: 8998, 1: 5550, 2: 3330, 3: 1880, 4: 5000, 5: 11000},
'Region2Total': {0: 30070, 1: 34460, 2: 8370, 3: 8400, 4: 6790, 5: 36300}}
)
# Not all columns are required, only the Newspaper and any 'City' column
cleaned_data = data[[i for i in data.columns if 'City' in i] + ['Newspaper']]
# Change the structure
df = cleaned_data.set_index('Newspaper').unstack()
# Get the top 3 values for each city
df = df.groupby(level=0).apply(lambda df: df.sort_values(ascending=False)[:4])
df.index = df.index.droplevel(0)
df
Out[]:
Newspaper
City1 NewsPaper1 231563
NewsPaper2 173009
NewsPaper6 137650
NewsPaper3 40511
City2 NewsPaper2 12180
NewsPaper1 8696
NewsPaper6 8025
NewsPaper3 4600
City3 NewsPaper2 28910
NewsPaper6 25300
NewsPaper1 21072
NewsPaper4 6520
City4 NewsPaper6 11000
NewsPaper1 8998
NewsPaper2 5550
NewsPaper5 5000
</code></pre>