<jupyter_start><jupyter_text>Get data from a spreadsheetIn this exercise, you'll create a data frame from a "base case" Excel file: one with a single sheet of tabular data. The `fcc_survey.xlsx` file here has a sample of responses from FreeCodeCamp's annual New Developer Survey. This survey asks participants about their demographics, education, work and home life, plus questions about how they're learning to code. Let's load all of it.`pandas` has _not_ been pre-loaded in this exercise, so you'll need to import it yourself before using `read_excel()` to load the spreadsheet.Instructions1. Load the `pandas` library as `pd`.2. Read in `fcc_survey.xlsx` and assign it to the variable `survey_responses`.3. Print the first few records of `survey_responses`.<jupyter_code># Load pandas as pd
import pandas as pd

# Read spreadsheet and assign it to survey_responses
survey_responses = pd.read_excel('fcc_survey.xlsx', skiprows=2)

# View the head of the data frame
survey_responses.head()<jupyter_output><empty_output><jupyter_text>Load a portion of a spreadsheetSpreadsheets meant to be read by people often have multiple tables, e.g., a small business might keep an inventory workbook with tables for different product types on a single sheet. Even tabular data may have header rows of metadata, like the New Developer Survey data here. While the metadata is useful, we don't want it in a data frame. You'll use `read_excel()`'s `skiprows` keyword to get just the data. You'll also create a string to pass to `usecols` to get only columns AD and AW through BA, about future job goals.`pandas` has been imported as `pd`.Instructions1. Create a single string, `col_string`, specifying that `pandas` should load column `AD` _and_ the range `AW` through `BA`.2. Load `fcc_survey_headers.xlsx`, setting `skiprows` and `usecols` to skip the first two rows of metadata and get only the columns in `col_string`.3. View the selected column names in the resulting data frame.<jupyter_code># Create string of lettered columns to load
col_string = 'AD, AW:BA'

# Load data with skiprows and usecols set
survey_responses = pd.read_excel('fcc_survey.xlsx',
                                 skiprows=2,
                                 usecols=col_string)

# View the names of the columns selected
survey_responses.columns<jupyter_output><empty_output><jupyter_text>Select a single sheetAn Excel workbook may contain multiple sheets of related data. The New Developer Survey response workbook has sheets for different years. Because `read_excel()` loads only the first sheet by default, you've already gotten survey responses for 2016. Now, you'll create a data frame of 2017 responses using `read_excel()`'s `sheet_name` argument in a couple different ways.`pandas` has been imported as `pd`.Instructions1. Create a data frame from the second workbook sheet by passing the sheet's position to `sheet_name`.2. Create a data frame from the `2017` sheet by providing the sheet's name to `read_excel()`.<jupyter_code># Create df from second worksheet by referencing its position
responses_2017 = pd.read_excel('fcc_survey.xlsx',
                               sheet_name=1,
                               skiprows=2)

# Graph where people would like to get a developer job
job_prefs = responses_2017.groupby('JobPref').JobPref.count()
job_prefs.plot.barh()
plt.show()
# Create df from second worksheet by referencing its name
responses_2017 = pd.read_excel('fcc_survey.xlsx',
                               sheet_name='2017',
                               skiprows=2)

# Graph where people would like to get a developer job
job_prefs = responses_2017.groupby('JobPref').JobPref.count()
job_prefs.plot.barh()
plt.show()<jupyter_output><empty_output><jupyter_text>Select multiple sheetsSo far, you've read Excel files one sheet at a time, which lets you customize import arguments for each sheet. But if an Excel file has some sheets that you want loaded with the same parameters, you can get them in one go by passing a list of their names or indices to `read_excel()`'s `sheet_name` keyword. To get them all, pass `None`. You'll practice both methods to get data from `fcc_survey.xlsx`, which has multiple sheets of similarly-formatted data.`pandas` has been loaded as `pd`.Instructions1. Load both the `2016` and `2017` sheets by name with a list and one call to `read_excel()`.2. Load the `2016` sheet by its position (`0`) and `2017` by name. Note the sheet names in the result.3. Load all sheets in the Excel file without listing them all.<jupyter_code># Load both the 2016 and 2017 sheets by name
all_survey_data = pd.read_excel('fcc_survey.xlsx',
                                sheet_name=['2016', '2017'])

# View the data type of all_survey_data
print(type(all_survey_data))
# Load all sheets in the Excel file
all_survey_data = pd.read_excel('fcc_survey.xlsx',
                                sheet_name=[0, '2017'])

# View the sheet names in all_survey_data
print(all_survey_data.keys())
# Load all sheets in the Excel file
all_survey_data = pd.read_excel('fcc_survey.xlsx',
                                sheet_name=None)

# View the sheet names in all_survey_data
print(all_survey_data.keys())<jupyter_output>dict_keys(['2016', '2017'])<jupyter_text>Work with multiple spreadsheetsWorkbooks meant primarily for human readers, not machines, may store data about a single subject across multiple sheets. For example, a file may have a different sheet of transactions for each region or year in which a business operated.The FreeCodeCamp New Developer Survey file is set up similarly, with samples of responses from different years in different sheets. Your task here is to compile them in one data frame for analysis.`pandas` has been imported as `pd`. All sheets have been read into the ordered dictionary `responses`, where sheet names are keys and data frames are values, so you can get data frames with the `values()` method.Instructions1. Create an empty data frame, `all_responses`.2. Set up a `for` loop to iterate through the values in the `responses` dictionary.3. Append each data frame to `all_responses` and reassign the result to the same variable name.<jupyter_code># Create an empty data frame
all_responses = pd.DataFrame()

# Set up for loop to iterate through values in responses
### Change 'responses' to 'all_survey_data'
for df in all_survey_data.values():
    # Print the number of rows being added
    print('Adding {} rows'.format(df.shape[0]))
    # Append df to all_responses, assign result
    all_responses = all_responses.append(df)

# Graph employment statuses in sample
counts = all_responses.groupby('EmploymentStatus').EmploymentStatus.count()
counts.plot.barh()
plt.show()<jupyter_output>Adding 1002 rows
Adding 1002 rows<jupyter_text>Set Boolean columnsDatasets may have columns that are most accurately modeled as Boolean values. However, `pandas` usually loads these as floats by default, since defaulting to Booleans may have undesired effects like turning NA values into Trues.`fcc_survey_subset.xlsx` contains a string ID column and several True/False columns indicating financial stressors. You'll evaluate which non-ID columns have no NA values and therefore can be set as Boolean, then tell `read_excel()` to load them as such with the `dtype` argument.`pandas` is loaded as `pd`.Instructions1. Count NA values in each column of `survey_data` with `isna()` and `sum()`. Note which columns besides `ID.x`, if any, have zero NAs.2. Set `read_excel()`'s `dtype` argument to load the `HasDebt` column as Boolean data.3. Supply the Boolean column name to the print statement to view financial burdens by group.<jupyter_code># Load the data
survey_data = pd.read_excel('fcc_survey_subset.xlsx')

# Count NA values in each column
survey_data.isna().sum()
# Set dtype to load appropriate column(s) as Boolean data
survey_data = pd.read_excel('fcc_survey_subset.xlsx',
                            dtype={'HasDebt': bool})

# View financial burdens by Boolean group
survey_data.groupby('HasDebt').sum()<jupyter_output><empty_output><jupyter_text>Set custom true/false valuesIn Boolean columns, `pandas` automatically recognizes certain values, like "TRUE" and 1, as `True`, and others, like "FALSE" and 0, as `False`. Some datasets, like survey data, can use unrecognized values, such as "Yes" and "No".For practice purposes, some Boolean columns in the New Developer Survey have been coded this way. You'll make sure they're properly interpreted with the help of the `true_values` and `false_values` arguments.`pandas` is loaded as `pd`. You can assume the columns you are working with have no missing values.Instructions1. Load the Excel file, specifying `"Yes"` as a true value and `"No"` as a false value.<jupyter_code># Load file with Yes as a True value and No as a False value
survey_subset_yn = pd.read_excel('fcc_survey_yn_data.xlsx',
                                 dtype={'HasDebt': bool,
                                 'AttendedBootCampYesNo': bool},
                                 true_values=['Yes'],
                                 false_values=['No'])

# View the data
survey_subset_yn.head()<jupyter_output><empty_output><jupyter_text>Parse simple dates`pandas` does not infer that columns contain datetime data; it interprets them as object or string data unless told otherwise. Correctly modeling datetimes is easy when they are in a standard format -- we can use the `parse_dates` argument to tell `read_excel()` to read columns as datetime data.The New Developer Survey responses contain some columns with easy-to-parse timestamps. In this exercise, you'll make sure they're the right data type.`pandas` has been loaded as `pd`.Instructions1. Load `fcc_survey.xlsx`, making sure that the `Part1StartTime` column is parsed as datetime data.2. View the first few values of the `survey_data.Part1StartTime` to make sure it contains datetimes.<jupyter_code># Load file, with Part1StartTime parsed as datetime data
survey_data = pd.read_excel('fcc_survey.xlsx',
                            parse_dates=['Part1StartTime'],
                            skiprows=2)

# Print first few values of Part1StartTime
survey_data['Part1StartTime'].head()<jupyter_output><empty_output><jupyter_text>Get datetimes from multiple columnsSometimes, datetime data is split across columns. A dataset might have a date and a time column, or a date may be split into year, month, and day columns.A column in this version of the survey data has been split so that dates are in one column, `Part2StartDate`, and times are in another, `Part2StartTime`. Your task is to use `read_excel()`'s `parse_dates` argument to combine them into one datetime column with a new name.`pandas` has been imported as `pd`.Instructions1. Create a dictionary, `datetime_cols` indicating that the new column `Part2Start` should consist of `Part2StartDate` and `Part2StartTime`.2. Load the survey response file, supplying the dictionary to the `parse_dates` argument to create a new `Part2Start` column.3. View summary statistics about the new `Part2Start` column with the `describe()` method.<jupyter_code># Create dict of columns to combine into new datetime column
datetime_cols = {'Part2Start': ['Part2StartDate', 
                                'Part2StartTime']}


# Load file, supplying the dict to parse_dates
survey_data = pd.read_excel('fcc_survey_dts.xlsx',
                            parse_dates=datetime_cols,
                            skiprows=2)

# View summary statistics about Part2Start
survey_data['Part2Start'].describe()<jupyter_output><ipython-input-43-068a9544f9df>:12: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.
  survey_data['Part2Start'].describe()<jupyter_text>Parse non-standard date formatsSo far, you've parsed dates that `pandas` could interpret automatically. But if a date is in a non-standard format, like 19991231 for December 31, 1999, it can't be parsed at the import stage. Instead, use `pd.to_datetime()` to convert strings to dates after import.The New Developer Survey data has been loaded as `survey_data` but contains an unparsed datetime field. We'll use `to_datetime()` to convert it, passing in the column to convert and a string representing the date format used.For more on date format codes, see [this reference](http://strftime.org/). Some common codes are year (`%Y`), month (`%m`), day (`%d`), hour (`%H`), minute (`%M`), and second (`%S`).`pandas` has been imported as `pd`.Instructions1. In the console, examine `survey_data`'s `Part2EndTime` column to see the data type and date format. Wich code describes the date format in `Part2EndTime`?2. Parse `Part2EndTime` using `pd.to_datetime()`, the `format` keyword argument, and the format string you just identified. Assign the result back to the `Part2EndTime` column.3. Print the head of `Part2EndTime` to confirm the column now contains datetime values. `%m%d%Y %H:%M:%S`<jupyter_code># Import dataset
survey_data = pd.read_excel('fcc_survey.xlsx', skiprows=2)

# Parse datetimes and assign result back to Part2EndTime
survey_data['Part2EndTime'] = pd.to_datetime(survey_data['Part2EndTime'],
                                             format='%m%d%Y %H:%M:%S')

# Print first few values of Part2EndTime
survey_data['Part2EndTime'].head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07765839199703593, 'fraction_numerical': 0.013190070396443127, 'mean_word_length': 4.243201243201243, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 15, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '205771', 'license': 'MIT', 'n_tokens_mistral': 3935, 'n_tokens_neox': 3741, 'n_words': 1644, 'path': 'streamlined_data_ingestion_with_pandas/2_importing_data_from_excel_files.ipynb', 'repo_name': 'vhsenna/datacamp-courses', 'size': '13495'}