<jupyter_start><jupyter_text>Columns containing values, not variables<jupyter_code>pew = pd.read_csv('../data/pew.csv')
pew.head()
pd.melt(pew, id_vars=['religion'], var_name='income', value_name='count').head()
billboard = pd.read_csv('../data/billboard.csv')
billboard.head()
(pd.melt(billboard,
        id_vars=['year', 'artist', 'track', 'time', 'date.entered'],
        value_name='rank',
        var_name='week'
       )
 .groupby('artist')['rank']
 .mean()
 .head())<jupyter_output><empty_output><jupyter_text>Columns containing multiple variables<jupyter_code>ebola = pd.read_csv('../data/country_timeseries.csv')
ebola.head()
ebola_long = ebola.melt(id_vars=['Date', 'Day'],
                       var_name='cd_country',
                       value_name='count')
ebola_long.head()
var_split = ebola_long['cd_country'].str.split('_')
var_split.head()
type(var_split)
type(var_split[0])
[1, 2, 3][0]
type(var_split[0][0])
status = var_split.str.get(0)
country = var_split.str.get(1)
ebola_long['status'] = status
ebola_long['country'] = country
ebola_long.head()
var_split_2 = ebola_long['cd_country'].str.split('_', expand=True)
var_split_2.head()
# don't need to use concat for this example

ebola_long[['status2', 'country2']] = var_split_2
ebola_long.head()<jupyter_output><empty_output><jupyter_text>Variables in both rows and columns<jupyter_code>weather = pd.read_csv('../data/weather.csv')
weather.head()
weather_melt = weather.melt(id_vars=['id', 'year', 'month', 'element'],
                            var_name='day', value_name='temp')
weather_melt.head()
weather_tidy = weather_melt.pivot_table(
    index=['id', 'year', 'month', 'day'],
    columns='element',
    values='temp'
)
weather_tidy.reset_index()
1_000_000
[1,
 2,
 3,
 #4
]<jupyter_output><empty_output><jupyter_text>Multiple observational units in a table (normalization)<jupyter_code>billboard_long = pd.melt(billboard,
        id_vars=['year', 'artist', 'track', 'time', 'date.entered'],
        value_name='rank',
        var_name='week'
       )
billboard_long.head()
billboard_long.loc[billboard_long.track == 'Loser']
billboard_songs = billboard_long[['year', 'artist', 'track', 'time']]
billboard_songs.head()
billboard_songs.shape
billboard_songs = billboard_songs.drop_duplicates()
billboard_songs.shape
billboard_songs.head()
billboard_songs['id'] = billboard_songs.index
billboard_songs.head()
billboard_ratings = billboard_long.merge(billboard_songs,
                                        on=['year', 'artist', 'track', 'time'])
billboard_ratings.head()
billboard_ratings = billboard_ratings[['id', 'date.entered', 'week', 'rank']]
billboard_ratings.head()
billboard_songs.to_csv('./billboard_songs.csv', index=False)
billboard_ratings.to_excel('billboard_ratings.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1679771265189421, 'fraction_numerical': 0.008934953538241601, 'mean_word_length': 5.332579185520362, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '626185', 'license': 'MIT', 'n_tokens_mistral': 1075, 'n_tokens_neox': 1020, 'n_words': 147, 'path': '.notes/03-tidy.ipynb', 'repo_name': 'iahmedani/pycon_2019-pandas_tutorial', 'size': '2798'}