<jupyter_start><jupyter_text>Recap: pandas BasicsInsert one-liners to solve all 10 tasks<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>read the CSV file `gapminder_total_fertility.csv`<jupyter_code>df=pd.read_csv('data/gapminder_total_fertility.csv',index_col=0)<jupyter_output><empty_output><jupyter_text>write the file to an Excel spreadsheet<jupyter_code>df.head()
df.to_excel('test excel.xlsx')<jupyter_output><empty_output><jupyter_text>read the spreadsheet again<jupyter_code>df=pd.read_excel('test excel.xlsx',index_col=0)<jupyter_output><empty_output><jupyter_text>select all data for the year 2010<jupyter_code>df['2010']
df.index<jupyter_output><empty_output><jupyter_text>select the 10 most fertile countries in 2010<jupyter_code>df.sort_values(by=['2010'], ascending=[False]).head(10)
df['2010'].sort_values(ascending=[False]).head(10)
df<jupyter_output><empty_output><jupyter_text>select countries below 2.0 in 2010<jupyter_code>df[(df['2010'] < 2.0)]
df[(df['2010'] < 2.0)]['2010']<jupyter_output><empty_output><jupyter_text>calculate the mean and standard deviation for each year<jupyter_code>df.agg(['count','mean', 'std','sem'])
df.describe()
df.iloc[:,0]
df.index # != df.iloc[:,0]
# df['C'] = df['A'].str.get(0)
# df['new_col'] = df['Total fertility rate'].str.get(0)
df['new_col'] = df.index.str.get(0)
df<jupyter_output><empty_output><jupyter_text>extract the first character as a separate column<jupyter_code>df['new_col'] = df.index.str[0]
df<jupyter_output><empty_output><jupyter_text>count the number of countries for each first character<jupyter_code>g = df.groupby(by='new_col')
g.count()
# or 
df.groupby('new_col').count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15897136177673876, 'fraction_numerical': 0.03389830508474576, 'mean_word_length': 10.806896551724138, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '246998', 'license': 'MIT', 'n_tokens_mistral': 721, 'n_tokens_neox': 664, 'n_words': 96, 'path': 'week_01/recap_pandas_basics.ipynb', 'repo_name': 'xhguo86/spiced_academy_backup-', 'size': '1711'}