<commit_before>import pandas as pd

## READ & WRITE
    # CSV
df = pd.read_csv('shenzhen_processed.csv', low_memory=False)
df = pd.read_csv('olympics.csv', index_col=0, skiprows=1)   #take 1st col as index, and remove 1st row
df.to_csv('shenzhen_processed.csv', index=False)
df = pd.read_csv(file, usecols=[0,2])   #use only specific columns
    # EXCEL
df = pd.read_excel('shenzhen_processed.xlsx', sheetname=1) #sheetname starts from 0
df = pd.read_csv("P00000001-ALL.csv", nrows=20) # limit to only 20 rows
df.to_excel('output.xlsx', index=False)
    # multiple df in different Excel sheets
from pandas import ExcelWriter
writer = ExcelWriter(xls_path)
for n, df in enumerate(list_dfs):
    df1.to_excel(writer,'sheet%s' % n)
writer.save()
    # TXT
utown=pd.read_table('university_towns.txt', sep=',', header=None)
df1 = pd.read_table('training_text', sep='\|\|', engine='python', skiprows=1, names=["ID","Text"]) #note that any delimiter more than 1 is a reg, have to use \ to override
    # convert a clip board into dataframe!!!
pd.read_clipboard()
    # JSON
df=pd.read_json(path)
df.to_json('/Users/xxx/Desktop/d.json')
    # Sample the data to speed up computation
df = df.sample(frac=0.1, random_state=10)

# set column as string
df = pd.read_csv('sample.csv', dtype={'ID': object})

# encoding error, eg: 'utf-8' codec can't decode byte 0x92 in position 763: invalid start byte
# use below to decode
df = pd.read_csv(email, encoding = "ISO-8859-1")


#--------------------------------------------------------
## SETTINGS
pd.set_option('display.max_columns',1) # expand column height
pd.set_option('display.max_rows', None) # show all rows
pd.set_option('display.max_colwidth', -1) # no limit to column width
pd.reset_option('all') # reset set options



#--------------------------------------------------------
## PERFORMANCE
df.info(memory_usage="deep") # display memory usage
df.memory_usage(deep=True) # display memory usage for each column

# filter dataframe to select certain dtype
df_obj = df.select_dtypes(include=['object'])

# determining optimal dtype so that memory usage is minimised
df['columnNm'] = pd.to_numeric(df['columnNm'], downcast='integer')
df['columnNm'].dtype

# convert objects into category to minimise memory usage as its converted to int backend
# only use this when unique values <50% of rows & that there is no need for numeric calculations
df['columnNm'] = df['columnNm'].astype('category')


#--------------------------------------------------------
# display html
from IPython.core.display import display, HTML
html = 'https://github.com/PAIR-code/facets/blob/master/facets_dive/Dive_demo.ipynb'
display(HTML(html), width=400)


#--------------------------------------------------------
# jupyter notebook magic functions
%lsmagic #list all magic
%%timeit -n 100 #time script to complete, -n 100 means only loop 100 times; default is 100
%time #time just once with CPU time outputs
%%bash #run bash commands in mac
%%cmd #run command prompt in windows


#--------------------------------------------------------
## BUILDING A DATAFRAME
    #build dataframe from a for loop
x = 0
list = []
for i in df.columns: # how many nan in each column? Value
    list.append({'column':x, 'nan_count':df[i].isnull().values.sum(), 'variable':i})
    x+=1
df_nan = pd.DataFrame(list)

    #create random dataframe
df1 = pd.DataFrame(np.random.randint(1, 5, (10,2)), columns=['a','b']) #10 rows, 2 columns, with numbers 1 to 5

    #from a dictionary
df = pd.DataFrame()
newdf['Date'] = x.keys()    #where x is a dictionary
df['DateValue'] = x.values()


#--------------------------------------------------------
## EXPLORATORY
df.shape #total number of rows by columns
df.size #total number of rows
len(df) #total number of rows too
len(df.columns) #total number of columns
df.head(2) #top 2 rows
df.dtypes #format
df.describe #mean, std, count, etc. only numeric formated columns


#--------------------------------------------------------
## FORMAT
df.dtypes
df['hour'] = df['hour'].astype('int64')
df['text'] = df['text'].astype('str')
df['col3'] = df['col2'].astype('category') # category type has int code in the backend
    #coerce, any errors will be converted to NaN
df['price'] = pd.to_numeric(df['price'], errors='coerce')
df['Time'] = pd.to_datetime(df['Time'], errors='coerce')


#--------------------------------------------------------
## Using SQL
    
# Connection to database
    # sqlite connection
conn = sqlite3.connect(sqlitePath) 
df= pd.read_sql_query("SELECT * FROM table", conn)
    # postgres connection
import psycopg2 
conn = psycopg2.connect(database="postgres", user="postgres", password="***", host="127.0.0.1", port="5432")
    # OR use sqlalchemy, which supports most databases
    # database engine + database connector package://username:password @ host ip / database? client encoding
    # latin1 or utf8 depending on client encoding
from sqlalchemy import create_engine
import psycopg2
conn = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/postgres?client_encoding=latin1') # postgres
conn = create_engine('mysql+pymysal://{}:{}@{}:{}/{}'.format(username, password, address, port, db_name)) # mysql 
query = ''' SELECT * FROM customer  '''

# reading from sql
df = pd.read_sql(query, conn)
df = pd.read_sql_query(query, conn)

# upload dataframe to database as new table by default (use if_exist for appending), only available using sqlalchemy as engine
df.to_sql(name='wsg_ap_list3', con=conn, index=False, if_exists='append')


#--------------------------------------------------------
## INDEX NAMES
df.index
df['country'] = df.index #transfer index to a column
df = df.set_index('Gold') #set index from a column

df = df.set_index(['STNAME', 'CTYNAME']) #can set hierachical index
df.loc['Michigan', 'Washtenaw County'] #querying from index

df = df.reset_index(drop=True) #reset index; drop=True to remove original index as a column

df.loc['Animal'] #index name
df.iloc[5:10] #index location; row number

    #iloc can also detect by both index & column
df.iloc[1,0] #index 1, column 0
df.iloc[:,0] #all index, column 0


# ix indexing works just the same as .loc when passed strings
df.ix[['Andrade']] == df.loc[['Andrade']]
# ix indexing works the same as .iloc when passed integers.
df.ix[[33]] == df.iloc[[33]]


#--------------------------------------------------------
## COLUMNS NAMES
    ## identify column names
df.columns
df.columns[:2] ## first 3 columns
    ## show column names and position
x = 0
for i in df.columns:
    print x, i
    x += 1
    #renaming columns
df2 = df.rename(columns={'diam_circle_image':'diameter','depth_rimfloor_topog':'depth', 'number_layers':'layers'})
hdata2.rename(columns=dict(zip(hdata2.columns,date_change.tolist())), inplace=True) #change two lists into dictionary
df.columns = map(str.lower, df.columns) # change to lower case
    #drop columns
df.drop(df.columns[[0, 1, 3]], axis=1)
df.drop('column_name', axis=1, inplace=True) #note that if inplace value is not set to true, need to reassign a new df
del df['column_name']
    #choose column names by condition
col = [i for i in df.columns if i[-4:]=='2012']

    #concat two lists of columns together
df[df.columns[1:11] | df.columns[12:14]]

    #ordering columns in a df
df[sorted(df.columns.tolist())].head(3)
    #specific ordering of columns
df = df[['a', 'b', 'd', 'c']]

#--------------------------------------------------------
## SET VALUES PER CELL
df.set_value(i, 'Y_svy', svy[1]) # index, column name, value


#--------------------------------------------------------
## COUNTING
df['EVENT_TYPE'].value_counts()
df['number_layers'].value_counts(normalize=True)*100 # by percentage
df.describe()

<commit_msg>new way to rename columns<commit_after>import pandas as pd

## READ & WRITE
    # CSV
df = pd.read_csv('shenzhen_processed.csv', low_memory=False)
df = pd.read_csv('olympics.csv', index_col=0, skiprows=1)   #take 1st col as index, and remove 1st row
df.to_csv('shenzhen_processed.csv', index=False)
df = pd.read_csv(file, usecols=[0,2])   #use only specific columns
    # EXCEL
df = pd.read_excel('shenzhen_processed.xlsx', sheetname=1) #sheetname starts from 0
df = pd.read_csv("P00000001-ALL.csv", nrows=20) # limit to only 20 rows
df.to_excel('output.xlsx', index=False)
    # multiple df in different Excel sheets
from pandas import ExcelWriter
writer = ExcelWriter(xls_path)
for n, df in enumerate(list_dfs):
    df1.to_excel(writer,'sheet%s' % n)
writer.save()
    # TXT
utown=pd.read_table('university_towns.txt', sep=',', header=None)
df1 = pd.read_table('training_text', sep='\|\|', engine='python', skiprows=1, names=["ID","Text"]) #note that any delimiter more than 1 is a reg, have to use \ to override
    # convert a clip board into dataframe!!!
pd.read_clipboard()
    # JSON
df=pd.read_json(path)
df.to_json('/Users/xxx/Desktop/d.json')
    # Sample the data to speed up computation
df = df.sample(frac=0.1, random_state=10)

# set column as string
df = pd.read_csv('sample.csv', dtype={'ID': object})

# encoding error, eg: 'utf-8' codec can't decode byte 0x92 in position 763: invalid start byte
# use below to decode
df = pd.read_csv(email, encoding = "ISO-8859-1")


#--------------------------------------------------------
## SETTINGS
pd.set_option('display.max_columns',1) # expand column height
pd.set_option('display.max_rows', None) # show all rows
pd.set_option('display.max_colwidth', -1) # no limit to column width
pd.reset_option('all') # reset set options



#--------------------------------------------------------
## PERFORMANCE
df.info(memory_usage="deep") # display memory usage
df.memory_usage(deep=True) # display memory usage for each column

# filter dataframe to select certain dtype
df_obj = df.select_dtypes(include=['object'])

# determining optimal dtype so that memory usage is minimised
df['columnNm'] = pd.to_numeric(df['columnNm'], downcast='integer')
df['columnNm'].dtype

# convert objects into category to minimise memory usage as its converted to int backend
# only use this when unique values <50% of rows & that there is no need for numeric calculations
df['columnNm'] = df['columnNm'].astype('category')


#--------------------------------------------------------
# display html
from IPython.core.display import display, HTML
html = 'https://github.com/PAIR-code/facets/blob/master/facets_dive/Dive_demo.ipynb'
display(HTML(html), width=400)


#--------------------------------------------------------
# jupyter notebook magic functions
%lsmagic #list all magic
%%timeit -n 100 #time script to complete, -n 100 means only loop 100 times; default is 100
%time #time just once with CPU time outputs
%%bash #run bash commands in mac
%%cmd #run command prompt in windows


#--------------------------------------------------------
## BUILDING A DATAFRAME
    #build dataframe from a for loop
x = 0
list = []
for i in df.columns: # how many nan in each column? Value
    list.append({'column':x, 'nan_count':df[i].isnull().values.sum(), 'variable':i})
    x+=1
df_nan = pd.DataFrame(list)

    #create random dataframe
df1 = pd.DataFrame(np.random.randint(1, 5, (10,2)), columns=['a','b']) #10 rows, 2 columns, with numbers 1 to 5

    #from a dictionary
df = pd.DataFrame()
newdf['Date'] = x.keys()    #where x is a dictionary
df['DateValue'] = x.values()


#--------------------------------------------------------
## EXPLORATORY
df.shape #total number of rows by columns
df.size #total number of rows
len(df) #total number of rows too
len(df.columns) #total number of columns
df.head(2) #top 2 rows
df.dtypes #format
df.describe #mean, std, count, etc. only numeric formated columns


#--------------------------------------------------------
## FORMAT
df.dtypes
df['hour'] = df['hour'].astype('int64')
df['text'] = df['text'].astype('str')
df['col3'] = df['col2'].astype('category') # category type has int code in the backend
    #coerce, any errors will be converted to NaN
df['price'] = pd.to_numeric(df['price'], errors='coerce')
df['Time'] = pd.to_datetime(df['Time'], errors='coerce')


#--------------------------------------------------------
## Using SQL
    
# Connection to database
    # sqlite connection
conn = sqlite3.connect(sqlitePath) 
df= pd.read_sql_query("SELECT * FROM table", conn)
    # postgres connection
import psycopg2 
conn = psycopg2.connect(database="postgres", user="postgres", password="***", host="127.0.0.1", port="5432")
    # OR use sqlalchemy, which supports most databases
    # database engine + database connector package://username:password @ host ip / database? client encoding
    # latin1 or utf8 depending on client encoding
from sqlalchemy import create_engine
import psycopg2
conn = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/postgres?client_encoding=latin1') # postgres
conn = create_engine('mysql+pymysal://{}:{}@{}:{}/{}'.format(username, password, address, port, db_name)) # mysql 
query = ''' SELECT * FROM customer  '''

# reading from sql
df = pd.read_sql(query, conn)
df = pd.read_sql_query(query, conn)

# upload dataframe to database as new table by default (use if_exist for appending), only available using sqlalchemy as engine
df.to_sql(name='wsg_ap_list3', con=conn, index=False, if_exists='append')


#--------------------------------------------------------
## INDEX NAMES
df.index
df['country'] = df.index #transfer index to a column
df = df.set_index('Gold') #set index from a column

df = df.set_index(['STNAME', 'CTYNAME']) #can set hierachical index
df.loc['Michigan', 'Washtenaw County'] #querying from index

df = df.reset_index(drop=True) #reset index; drop=True to remove original index as a column

df.loc['Animal'] #index name
df.iloc[5:10] #index location; row number

    #iloc can also detect by both index & column
df.iloc[1,0] #index 1, column 0
df.iloc[:,0] #all index, column 0


# ix indexing works just the same as .loc when passed strings
df.ix[['Andrade']] == df.loc[['Andrade']]
# ix indexing works the same as .iloc when passed integers.
df.ix[[33]] == df.iloc[[33]]


#--------------------------------------------------------
## COLUMNS NAMES
    ## identify column names
df.columns
df.columns[:2] ## first 3 columns
    ## show column names and position
x = 0
for i in df.columns:
    print x, i
    x += 1
    #renaming columns
df.columns = ['newcolumn1', 'newcolumn2', 'newcolumn3'] #easiest way to change, but error if total columns does not match
df2 = df.rename(columns={'diam_circle_image':'diameter','depth_rimfloor_topog':'depth', 'number_layers':'layers'})
hdata2.rename(columns=dict(zip(hdata2.columns,date_change.tolist())), inplace=True) #change two lists into dictionary
df.columns = map(str.lower, df.columns) # change to lower case
    #drop columns
df.drop(df.columns[[0, 1, 3]], axis=1)
df.drop('column_name', axis=1, inplace=True) #note that if inplace value is not set to true, need to reassign a new df
del df['column_name']
    #choose column names by condition
col = [i for i in df.columns if i[-4:]=='2012']

    #concat two lists of columns together
df[df.columns[1:11] | df.columns[12:14]]

    #ordering columns in a df
df[sorted(df.columns.tolist())].head(3)
    #specific ordering of columns
df = df[['a', 'b', 'd', 'c']]

#--------------------------------------------------------
## SET VALUES PER CELL
df.set_value(i, 'Y_svy', svy[1]) # index, column name, value


#--------------------------------------------------------
## COUNTING
df['EVENT_TYPE'].value_counts()
df['number_layers'].value_counts(normalize=True)*100 # by percentage
df.describe()


{'fraction_non_alphanumeric': 0.21435910608785, 'fraction_numerical': 0.018558951965065504, 'mean_word_length': 3.149480415667466, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 3, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5127400', 'n_tokens_mistral': 5050, 'n_tokens_neox': 4779, 'n_words': 1492}