<jupyter_start><jupyter_text>Clean up data set 1. Load what libraries you need.<jupyter_code>import pandas as pd
import numpy as np
from datetime import datetime
import matplotlib.pyplot as plt
from matplotlib.ticker import FormatStrFormatter
plt.style.use('ggplot')<jupyter_output><empty_output><jupyter_text>2. Load the data from file. - index_col makes the date column an index. - header[1,2] defines the second and third row as multilevel columns (platform, the rest per platform).https://stackoverflow.com/questions/22137723/convert-number-strings-with-commas-in-pandas-dataframe-to-float<jupyter_code>fn='mock_data.csv'
df = pd.read_csv(fn,header=[1,2],index_col=0,parse_dates=True,thousands=',')
print(df.head() )#show top of table<jupyter_output>Google Play Unnamed: 2_level_0 Unnamed: 3_level_0  \
Day of Date ActiveUsers            Revenue              Day 1   
2019-04-01         3312                233             48.44%   
2019-04-02         3416                318             52.44%   
2019-04-03         3483                279             49.72%   
2019-04-04         3429                226             45.58%   
2019-04-05         3314                303             46.84%   

            Unnamed: 4_level_0 Unnamed: 5_level_0 Unnamed: 6_level_0  \
Day of Date             Day 10             Day 11             Day 12   
2019-04-01              11.33%              9.56%              7.78%   
2019-04-02              13.56%             11.57%             10.67%   
2019-04-03              12.29%             11.92%             11.36%   
2019-04-04              14.62%             12.69%             11.54%   
2019-04-05              15.25%             12.42%             10.89%   

            Unnamed: 7_level_0 Unnamed:[...]<jupyter_text>3. Deal with ugly confusing headers.Thank you, https://stackoverflow.com/questions/30322581/pandas-read-multiindexed-csv-with-blanks<jupyter_code>#pull the column level values into a DataFrame:
columns = pd.DataFrame(df.columns.tolist())

#then rename the Unnamed: columns to NaN:
columns.loc[columns[0].str.startswith('Unnamed:'), 0] = np.nan

#and then forward-fill the NaNs:
columns[0] = columns[0].fillna(method='ffill')

#Now we can find the remaining NaNs and fill them with empty strings:
mask = pd.isnull(columns[0])
columns[0] = columns[0].fillna('')

#To make the first two columns, A and B, indexable as df['A'] and df['B'] -- 
#as though they were single-leveled -- you could swap the values in the first and second columns:
columns.loc[mask, [0,1]] = columns.loc[mask, [1,0]].values

#Now you can build a new MultiIndex and assign it to df.columns:
df.columns = pd.MultiIndex.from_tuples(columns.to_records(index=False).tolist())
#df.columns.get_level_values(1)

print(df.dtypes)<jupyter_output>Google Play  ActiveUsers     int64
             Revenue         int64
             Day 1          object
             Day 10         object
             Day 11         object
                             ...  
iOS          Day 5          object
             Day 6          object
             Day 7          object
             Day 8          object
             Day 9          object
Length: 62, dtype: object<jupyter_text>4. It seems easier to deal with when stacking platforms on top of one another.Some columns aren't numerical. Convert.<jupyter_code># stack the platform tables
stacked = df.stack(level=0)

# We now have 2 row indices, give them a name
stacked.index.names = ['Date','Platform']

#stacked.reset_index(1,inplace=True) # this makes index a normal column
#print(stacked.index)
#dates=list(stacked.index.get_level_values(0))
# https://stackoverflow.com/questions/25669588/convert-percent-string-to-float-in-pandas-read-csv

day_col=[col for col in stacked.columns if col.startswith('Day')]

for col in day_col:
    if col.startswith('Day'):
        stacked[col]=stacked[col].str.rstrip('%').astype('float') / 100.0  
        
#                     ^ use str funcs to elim '%'     ^ divide by 100
# could also be:     .str[:-1].astype(...#<jupyter_output><empty_output><jupyter_text>5. We also want a month/year column<jupyter_code>stacked['month_year'] = pd.DatetimeIndex(stacked.index.get_level_values(0)).to_period('M').strftime('%b %y')
#print(stacked.dtypes)

stacked
#print(stacked.index)

#print(dir(stacked['month_year']))
type(stacked['month_year'][0])<jupyter_output><empty_output><jupyter_text>I'd really like to sort the columns by name.- find Day columns: https://stackoverflow.com/questions/21285380/find-column-whose-name-contains-a-specific-stringhttps://stackoverflow.com/questions/27275236/pandas-best-way-to-select-all-columns-whose-names-start-with-xhttps://stackoverflow.com/questions/11067027/re-ordering-columns-in-pandas-dataframe-based-on-column-name- parse each string - extract day- get a sort index- then somehow apply this to a subset of cols.  Calculations Ex. 1: Calculate the monthly averages of the daily active users (DAU) for all the months included in the dataset, for each platform and visualize it in a chart.<jupyter_code>https://www.earthdatascience.org/courses/intro-to-earth-data-science/scientific-data-structures-python/pandas-dataframes/run-calculations-summary-statistics-pandas-dataframes/
table = stacked.pivot_table(index=pd.to_datetime(stacked['month_year'], format='%b %y'),
                            columns='Platform',values='ActiveUsers')
table

list(stacked['month_year'].unique())
fig,ax=table.plot(figsize=(8,4), subplots=True, layout=(2, 1),title='Average Daily Active Users',
xlabel='Month',ylabel='DAU',xticks=table.index)

# must define both xtick and ticklabel!
# use axis object to change just one plot...
ax[-1].set_xticklabels(stacked['month_year'].unique());

# plt.sca(ax[0]) # set current axis<jupyter_output><empty_output><jupyter_text>Overall, many more Google Play users than iOS. Makes sense as many more would have android phone, so much cheaper. Decreasing trend, then something exciting seems to happen in August. Excitement is relatively greater and lasts longer in iOS world.  Ex. 2. Calculate the ARPDAU (Average Revenue per Daily Active User) per each day, separately for each platform. Visualize it in timeline in a chart.<jupyter_code>#print(stacked.groupby(['month_year','Platform'],sort=False).groups.keys())
stacked['ActiveUsers'].describe()
stacked.head()

stacked['AvgRevPerUser']=stacked['Revenue']/stacked['ActiveUsers']
table2 = stacked.pivot_table(index='Date',columns='Platform',values='AvgRevPerUser')
print(table2)
table2.plot(figsize=(8,4), subplots=False,title='Average Revenue per Daily Active User',
xlabel='Date',ylabel='ARPDAU (USD)')
ax=plt.gca()
ax.set_facecolor('white')
ax.yaxis.set_major_formatter(FormatStrFormatter('%.2f'))
plt.legend(facecolor='white')
# must define both xtick and ticklabel!
# use axis object to change just one plot...
#ax[-1].set_xticklabels(stacked['month_year'].unique());<jupyter_output><empty_output><jupyter_text>Ex.3: Assuming the 1st of April 2019 is the Launch Date for the game, calculate the cumulative revenue of the “average” user that installed the game on the 1st of April for the first week after launch. To do so, use Return Rate and ARPDAU. Split the analysis by platform. https://www.shanelynn.ie/summarising-aggregation-and-grouping-data-in-python-pandas/<jupyter_code># just extract the columns that contain day 1-7. What next? 
howmanydays = 7
day_cols=[col for col in stacked.columns if col.startswith('Day') and int(col[4:]) <= howmanydays]
# print(day_cols)
# for i in day_cols:
#     print(i[4:])
#     print(int(i[4:])<=7)
    
cumRev = stacked.reindex(sorted(day_cols,key=lambda x: int(x[4:])), axis=1)
cumRev['sumcumR']=0
for col in day_cols:
    print(col)
    cumRev[col+'_cumR']=cumRev[col]*stacked['AvgRevPerUser']
    cumRev['sumcumR']=cumRev['sumcumR']+(cumRev[col]*stacked['AvgRevPerUser'])
whichDate='2019-04-01'
day_str=datetime.strptime(whichDate, '%Y-%m-%d').strftime('%d %B %Y')
#cumRev['sumcumR'] = cumRev['sumcumR'].map('${:,.2f}'.format) # bad idea, returns strings
cumRev_on_day = cumRev.loc[whichDate].groupby('Platform')['sumcumR'].sum()

print(cumRev_on_day)
fig,ax=plt.subplots(1,1)
cumRev_on_day.plot.bar(figsize=(8,6), subplots=False,
     xlabel='Platform',ylabel='Cumulative Revenue (USD)',rot=0)

ax=plt.gca()

plt.suptitle('Cumulative revenue of the “average” user*', y=1, fontsize=18)
plt.title('*7 day sum from launch date on ' + day_str, fontsize=10)
ax.yaxis.set_major_formatter(FormatStrFormatter('%.2f'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12670427832628114, 'fraction_numerical': 0.04290079924776681, 'mean_word_length': 3.485503426462836, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 31, 'https://': 8, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '171988', 'license': 'CC0-1.0', 'n_tokens_mistral': 3036, 'n_tokens_neox': 2756, 'n_words': 677, 'path': 'pandas_example.ipynb', 'repo_name': '2051289v/pandas_example_notebook', 'size': '8508'}