<jupyter_start><jupyter_text>Working with tabular data using pandas[Pandas](https://pandas.pydata.org/) is a library for processing and analysing tabular data.  Loading data from diskLoad a csv file from disc and show it<jupyter_code>import pandas as pd

data_frame = pd.read_csv('../data/Measurements_blobs_ImageJ.csv', delimiter=',')

# show it
data_frame<jupyter_output><empty_output><jupyter_text>Data selectionSelect a column out of the table - or two!<jupyter_code>data_frame["Round"]<jupyter_output><empty_output><jupyter_text>Descriptive statisticsDetermine the mean of a column<jupyter_code>import numpy as np
np.mean(data_frame["Mean"])<jupyter_output><empty_output><jupyter_text>Read out one particular cell of the table<jupyter_code>data_frame["Mean"][0]<jupyter_output><empty_output><jupyter_text>Creating DataFramesMake a new [DataFrame](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html) from scratch:<jupyter_code>header = ['A', 'B', 'C']

data = [
    [1, 2, 3],  # this will later be colum A
    [4, 5, 6],  #                          B
    [7, 8, 9]   #                          C
]

# convert the data and header arrays in a pandas data frame
data_frame = pd.DataFrame(data, header)

# show it
data_frame
# rotate/flip it
data_frame = data_frame.transpose()

# show it
data_frame<jupyter_output><empty_output><jupyter_text>You can also create a DataFrame from a dictionary of arrays. Earlier, we called them tables.<jupyter_code># source: https://en.wikipedia.org/wiki/List_of_largest_cities
cities_table = {
    "City":       ['Tokyo',  'Delhi',  'Shanghai', 'Sao Paulo', 'Mexico City'],
    "Country": ['Japan',  'India',  'China',    'Brazil',    'Mexico'],
    "Population":    [13515271, 16753235, 24183000,   12252023,    9209944],
    "Area_km2":   [2191,     1484,     6341,       1521,        1485]
}

cities = pd.DataFrame(cities_table)
cities<jupyter_output><empty_output><jupyter_text>Selecting rows and columnsYou can filter rows and columns from pandas DataFrames quite intuitively, [see also](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html).Let's start by selecting two columns<jupyter_code>cities[['City', 'Country']]<jupyter_output><empty_output><jupyter_text>When analysing tabular data, it is often necessary to filter tables by selecting only rows which fulfill certain conditions. For example, we select large cities by area:<jupyter_code>cities[cities['Area_km2'] > 2000]<jupyter_output><empty_output><jupyter_text>How does this work? The inner square bracket returns a list of boolean `flags`, which select entries in the outer square brackets:<jupyter_code>cities['Area_km2']
flags = cities['Area_km2'] > 2000
flags
cities[flags]<jupyter_output><empty_output><jupyter_text>Adding columnsSometimes, you want to compute parameters from other parameters and add them to the table. You can also add columns like this:<jupyter_code>cities['Population per km2'] = (cities['Population'] / cities['Area_km2']).astype(int)
cities<jupyter_output><empty_output><jupyter_text>Removing columnsYou can use pythons [del](https://www.w3schools.com/python/ref_keyword_del.asp) cmmand to remove a column.<jupyter_code>del cities['Population per km2']
cities<jupyter_output><empty_output><jupyter_text>Combining tablesIn the mastery of data science you need to combine information from multiple tables. For example, to compute the relative population of a city with respect to the country it's in, you need to combine the `cities` DataFrame with the `Countries` DataFrame.<jupyter_code># source https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)
countries_table = {
    'Country':    ['Japan',   'India',    'China',    'Brazil',  'Mexico'],
    'Population': [127202192, 1352642280, 1427647786, 209469323, 126190788]
}
countries = pd.DataFrame(countries_table)
countries<jupyter_output><empty_output><jupyter_text>For combining tables, we use the [join](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html) function.<jupyter_code>countries.merge(cities, on='Country')<jupyter_output><empty_output><jupyter_text>The two population columns with _x and _y might be confusing. Thus, we can explicitly name them properly by providing a suffix.<jupyter_code>combined = countries.merge(cities, on='Country', suffixes=['_country', '_city'])
combined<jupyter_output><empty_output><jupyter_text>Just for fun, we compute the population ratio of the city in its country. For showing results nicely, remove all the other columns.<jupyter_code># compute ratio
combined['City_Country_population_ratio'] = combined['Population_city'] / combined['Population_country']

# only show selected columns
combined[['City', 'City_Country_population_ratio']]<jupyter_output><empty_output><jupyter_text>Saving tables to diskSaving tables to disk is trivial. Depending on the software you want to use for postprocessing, you nay want to specify separators (tabulator, comma, semicolon). See [documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html) for details.<jupyter_code># save a dataframe to a CSV
cities.to_csv("output.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11014827652609281, 'fraction_numerical': 0.025418833044482957, 'mean_word_length': 6.057065217391305, 'pattern_counts': {'":': 4, '<': 65, '<?xml version=': 0, '>': 67, 'https://': 8, 'lorem ipsum': 0, 'www.': 1, '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': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '435210', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1809, 'n_tokens_neox': 1670, 'n_words': 440, 'path': 'biostatistics/processing_tables_with_pandas.ipynb', 'repo_name': 'BiAPoL/Bio-image_Analysis_with_Python', 'size': '5193'}