Brief Description

I would like to propose a case_when function, similar to the same function in SQL and R, for conditions. It will be a wrapper around np.select.

Example API

       Type	Set
1	A	Z
2	B	Z
3	B	X
4	C	Y

# run checks based on conditions and create a new column, or modify existing column.
@pf.register_dataframe_method
def case_when(df, conditions,column_name, default = None, ) : 
    condlist, choicelist = zip(*[(value,key) for key, value in conditions.items()])
    df = df.assign(**{column_name : np.select(condlist, choicelist, default=default)})
    return df

cond = {"green" : df.Set=="Z", "yellow":df.Set=="X"}
df.case_when(conditions=cond, default=None, column_name='rag')

       Type	Set	rag
1	A	Z	green
2	B	Z	green
3	B	X	yellow
4	C	Y	None

PR Description

Please describe the changes proposed in the pull request:

• Single function to access pandas' string methods
• Small subset to see if the current method is acceptable

This PR resolves #360 .

PR Checklist

Please ensure that you have done the following:

1. [x] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.rst.

3. [ ] Add a line to CHANGELOG.rst under the latest version header (i.e. the one that is "on deck") describing the contribution.
   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Quick Check

To do a very quick check that everything is correct, follow these steps below:

• [x] Run the command make check from pyjanitor's top-level directory. This will automatically run:
  • black formatting
  • flake8 checking
  • running the test suite
  • docs build

Once done, please check off the check-box above.

If make check does not work for you, you can execute the commands listed in the Makefile individually.

Code Changes

If you are adding code changes, please ensure the following:

• [x] Ensure that you have added tests.
• [ ] Run all tests ($ pytest .) locally on your machine.
  • [ ] Check to ensure that test coverage covers the lines of code that you have added.
  • [ ] Ensure that all tests pass.

Documentation Changes

If you are adding documentation changes, please ensure the following:

• [ ] Build the docs locally.
• [ ] View the docs to check that it renders correctly.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

I see there is remove columns function. I think a select_columns function would work nice. It would be cleaner and easier to understand then df[['col1',col2','col3']].

Background

This thread is borne out of the discussion from #968 , in an effort to make documentation more beginner-friendly & more understandable. One of the subtasks mentioned in that thread was to go through the function docstrings and include a minimal working example to each of the public functions in pyjanitor.

Criteria reiterated here for the benefit of discussion:

It should fit with our existing choice to go with mkdocs, mkdocstrings, and mknotebooks.
The examples should be minimal and executable and complete execution within 5 seconds per function.
The examples should display in rich HTML on our docs page.
We should have an automatic way of identifying whether a function has an example provided or not so that every function has an example.

Sample of what MWE should look like is shown here.

I'm thinking we can create a task list so that 1. we can encourage more users to join in the effort, and 2. make sure we don't do duplicate work. A lot of the groundwork can be covered by selectively copying one or two examples over from the software test suite.

Then we can label this issue as a Help Wanted / Low-Hanging Fruit and get people to mention in this thread if they're intending to work on the files?

Task list

• [X] functions/add_columns.py
• [x] functions/also.py
• [x] functions/bin_numeric.py
• [x] functions/case_when.py
• [x] functions/change_type.py
• [x] functions/clean_names.py
• [x] functions/coalesce.py
• [x] functions/collapse_levels.py
• [x] functions/complete.py
• [x] functions/concatenate_columns.py
• [x] functions/conditional_join.py
• [x] functions/convert_date.py
• [x] functions/count_cumulative_unique.py
• [x] functions/currency_column_to_numeric.py
• [x] functions/deconcatenate_column.py
• [x] functions/drop_constant_columns.py
• [x] functions/drop_duplicate_columns.py
• [x] functions/dropnotnull.py
• [x] functions/encode_categorical.py
• [x] functions/expand_column.py
• [x] functions/expand_grid.py
• [x] functions/factorize_columns.py
• [x] functions/fill.py
• [x] functions/filter.py
• [x] functions/find_replace.py
• [x] functions/flag_nulls.py
• [x] functions/get_dupes.py
• [x] functions/groupby_agg.py
• [x] functions/groupby_topk.py
• [x] functions/impute.py
• [x] functions/jitter.py
• [x] functions/join_apply.py
• [x] functions/label_encode.py
• [x] functions/limit_column_characters.py
• [x] functions/min_max_scale.py
• [x] functions/move.py
• [x] functions/pivot.py
• [x] functions/process_text.py
• [x] functions/remove_columns.py
• [x] functions/remove_empty.py
• [x] functions/rename_columns.py
• [x] functions/reorder_columns.py
• [x] functions/round_to_fraction.py
• [x] functions/row_to_names.py
• [x] functions/select_columns.py
• [x] functions/shuffle.py
• [x] functions/sort_column_value_order.py
• [x] functions/sort_naturally.py
• [x] functions/take_first.py
• [x] functions/then.py
• [x] functions/to_datetime.py
• [x] functions/toset.py
• [x] functions/transform_columns.py
• [x] functions/truncate_datetime.py
• [x] functions/update_where.py
• [ ] spark/backend.py
• [ ] spark/functions.py
• [x] xarray/functions.py
• [x] biology.py
• [x] chemistry.py
• [x] engineering.py
• [ ] errors.py
• [x] finance.py
• [x] io.py
• [x] math.py
• [x] ml.py
• [x] timeseries.py B

Closes #705

PR Description

Please describe the changes proposed in the pull request:

• Introducing a "timeseries.py" module with data checking and data cleaning functions geared towards time series data
• Adding two functions (a utility function and an actual user-facing function) to fill_missing_timestamps in a timeseries data set

This PR resolves #705.

PR Checklist

Please ensure that you have done the following:

1. [x] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>. I think I am doing this, but I am really not sure, so please guide me through and I will be happy to resubmit.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.rst.

3. [x] Add a line to CHANGELOG.rst under the latest version header (i.e. the one that is "on deck") describing the contribution.

   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Quick Check

I have made code and documentation changes, however I don't have enough experience in adding unit tests. I just added one unit test using an existing example from the code base, but I need some help with pytest. I have used unittest very briefly in the past, but any guidance regarding the testing piece will be helpful for me to progress further.

To do a very quick check that everything is correct, follow these steps below:

• [x] Run the command make check from pyjanitor's top-level directory. This will automatically run:
  • black formatting
  • flake8 checking
  • running the test suite
  • docs build

tests\conftest.py:7: in <module>
    from janitor.testing_utils import date_data
E   ModuleNotFoundError: No module named 'janitor'

Code Changes

If you are adding code changes, please ensure the following:

• [ ] Ensure that you have added tests.
• [ ] Run all tests ($ pytest .) locally on your machine.
  • [ ] Check to ensure that test coverage covers the lines of code that you have added.
  • [ ] Ensure that all tests pass.

Documentation Changes

If you are adding documentation changes, please ensure the following:

• [ ] Build the docs locally.
• [ ] View the docs to check that it renders correctly.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

PR Description

Please describe the changes proposed in the pull request:

• We have added the enhancements listed in the above issues, as well as written tests and made sure the continuous integration passed and complied with all linters. Do note: there may be better solutions.

**This PR resolves #737 ** **This PR resolves #198 ** **This PR resolves #752 ** **This PR resolves #209 ** **This PR resolves #366 ** **This PR resolves #382 ** **This PR resolves #695 **

PR Checklist

Please ensure that you have done the following:

Automatic checks

There will be automatic checks run on the PR. These include:

• Building a preview of the docs on Netlify
• Automatically linting the code
• Making sure the code is documented
• Making sure that all tests are passed
• Making sure that code coverage doesn't go down.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

PR Description

Please describe the changes proposed in the pull request:

• Changed update_where conditions to query style mode
• Removed find_replace dependency on update_where function

This PR resolves #663 .

PR Checklist

Please ensure that you have done the following:

1. [x] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.rst.

3. [ ] Add a line to CHANGELOG.rst under the latest version header (i.e. the one that is "on deck") describing the contribution.
   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Quick Check

To do a very quick check that everything is correct, follow these steps below:

• [x] Run the command make check from pyjanitor's top-level directory. This will automatically run:
  • black formatting
  • flake8 checking
  • running the test suite
  • docs build

Once done, please check off the check-box above.

If make check does not work for you, you can execute the commands listed in the Makefile individually.

Code Changes

If you are adding code changes, please ensure the following:

• [x] Ensure that you have added tests.
• [x] Run all tests ($ pytest .) locally on your machine.
  • [ ] Check to ensure that test coverage covers the lines of code that you have added.
  • [ ] Ensure that all tests pass.

Documentation Changes

If you are adding documentation changes, please ensure the following:

• [ ] Build the docs locally.
• [ ] View the docs to check that it renders correctly.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

Brief Description

I would like to propose a function that counts the cumulative number of unique items in a column.

Example implementation

def cumulative_unique(df, column_name, new_column_name):
    """
    Compute the cumulative number of unique items in a column that have been seen.
    
    This function can be used to limit the number of source plates
    that we use to seed colonies.
    """
    df = df.copy()
    unique_elements = set()
    cumulative_unique = []
    for val in df[column_name].values:
        unique_elements.add(val)
        cumulative_unique.append(len(unique_elements))
    df[new_column_name] = cumulative_unique
    return df

I'm happy for anybody else to run with the implementation above and improve upon it.

Brief Description

I would like to know if there are any interest to create pyjanitor for pyspark? I'm using pyspark a lot and I would really like use custom method chaining to clean up my ETL code.

I'm not sure if it is doable or how easy it is but I would be open to explore.

Adding a case_when function

Please describe the changes proposed in the pull request:

• Effectively, this is a wrapper around pd.Series.mask but with an easier to manage mechanism for passing arguments using variable arguments.
• chose pd.Series.mask to allow support for pandas extension arrays/dtypes (np.select would convert them to numpy dtypes, which wont be beneficial for nullable Integer dtypes, or String dtypes, or other future pandas dtypes).
• The function does not mutate the original pd.DataFrame and is vectorized.
• Can be method chained

**This PR resolves #736 **

Speed comparison # ... with a pinch of salt ...

df = pd.DataFrame({'col1': list('ABBC'), 'col2': list('ZZXY')})
  col1 col2
0    A    Z
1    B    Z
2    B    X
3    C    Y

df = pd.concat([df]*100_000)

df.shape
(400000, 2)


%%timeit
conditions = [
    (df['col2'] == 'Z') & (df['col1'] == 'A'),
    (df['col2'] == 'Z') & (df['col1'] == 'B'),
    (df['col1'] == 'B')
]

choices = ['yellow', 'blue', 'purple']

df['color'] = np.select(conditions, choices, default='black')

167 ms ± 2.91 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)


df.case_when(
    df.col2.eq("Z") & df.col1.eq("A"), "yellow",  # first condition and value
    df.col2.eq("Z") & df.col1.eq("B"), "blue",    # second condition and value
    df.col1.eq("B"), "purple",                    # third condition and value 
    "black",                                      # default if no condition is True
    column_name = "color"
    )

125 ms ± 449 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)


# anonymous function
df.case_when(
    lambda df: df.col2.eq("Z") & df.col1.eq("A"), "yellow", 
    lambda df: df.col2.eq("Z") & df.col1.eq("B"), "blue",    
    lambda df: df.col1.eq("B"), "purple",                         
    "black",                                                     
    column_name = "color"
    )

134 ms ± 2.72 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)


# pd.eval
%%timeit
df.case_when(
   "col2 == 'Z' and col1 == 'A'", "yellow", 
  "col2 == 'Z' and col1 == 'B'", "blue",   
 "col1 == 'B'", "purple",                        
   "black",                                             
  column_name = "color"
  )
74.4 ms ± 4.46 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

PR Checklist

Please ensure that you have done the following:

1. [x] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.rst.

3. [x] Add a line to CHANGELOG.rst under the latest version header (i.e. the one that is "on deck") describing the contribution.
   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Quick Check

To do a very quick check that everything is correct, follow these steps below:

• [x] Run the command make check from pyjanitor's top-level directory. This will automatically run:
  • black formatting
  • flake8 checking
  • running the test suite
  • docs build

Once done, please check off the check-box above.

If make check does not work for you, you can execute the commands listed in the Makefile individually.

Code Changes

If you are adding code changes, please ensure the following:

• [x] Ensure that you have added tests.
• [x] Run all tests ($ pytest .) locally on your machine.
  • [x] Check to ensure that test coverage covers the lines of code that you have added.
  • [x] Ensure that all tests pass.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl
• @samukweku

PR Description

Please describe the changes proposed in the pull request:

• A method-chainable fill method for forward and backward fills on selected columns of a dataframe.

This PR resolves #700 .

Please ensure that you have done the following:

1. [x] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.rst.

3. [ ] Add a line to CHANGELOG.rst under the latest version header (i.e. the one that is "on deck") describing the contribution.
   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Quick Check

To do a very quick check that everything is correct, follow these steps below:

• [x] Run the command make check from pyjanitor's top-level directory. This will automatically run:
  • black formatting
  • flake8 checking
  • running the test suite
  • docs build

Once done, please check off the check-box above.

If make check does not work for you, you can execute the commands listed in the Makefile individually.

Code Changes

If you are adding code changes, please ensure the following:

• [x] Ensure that you have added tests.
• [x] Run all tests ($ pytest .) locally on your machine.
  • [x] Check to ensure that test coverage covers the lines of code that you have added.
  • [x] Ensure that all tests pass.

Documentation Changes

If you are adding documentation changes, please ensure the following:

• [ ] Build the docs locally.
• [ ] View the docs to check that it renders correctly.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

Brief Description

I would like to propose a mutate function, similar to pandas' assign function, but more flexible - it will also serve as replacement for the transform functions

Example API

df.mutate(y='sum',n=lambda df: df.nth(1))
df.mutate(y='sum',n=lambda df: df.nth(1), by='x')


# replicate dplyr's across
# https://stackoverflow.com/q/63200530/7175713
# select_columns syntax can fit in nicely here
mtcars.summarize(("*t", "mean"), ("*p", "sum"), {"cyl": lambda df: df + 1, "new_col": lambda df: df.select_columns("*t").sum(axis=1))

Brief Description

I would like to propose a summarize function, similar to dplyr's summarise function and pandas' agg function, but for grouping operations, and more flexible

Example API

df.summarize(y='sum',n=lambda df: df.nth(1), by='x')

# summarize on multiple columns
df.summarize((['a','b','c'], 'sum'), by = 'x')

# replicate dplyr's across
# https://stackoverflow.com/q/63200530/7175713
# select_columns syntax can fit in nicely here
mtcars.summarize(("*t", "mean"), ("*p", "sum"), by='cyl')

PR Description

Please describe the changes proposed in the pull request:

• Improve selection in conditional_join - either only df or right dataframe can be returned
• indicator parameter added - similar to the indicator parameter in pd.merge
• rewrite numba code, such that numba is called only once - numpy used as much as possible, where possible
• improve code for _range_indices algorithm, taking advantage of binary search for improved performance, for scenarios where both columns on the right dataframe are monotonically increasing

This PR resolves #1223 .

Speed test when both columns on the right are monotonically increasing

In [3]: import pandas as pd
   ...: import janitor
   ...: import numpy as np

# adapted from https://stackoverflow.com/a/41094529/7175713
In [4]: # Sample df1.
   ...: n1 = 50_000
   ...: df1 = pd.DataFrame({'date': pd.date_range('2016-11-24', freq='10T', periods=n1), 'value': np.random.random(n1)})
   ...: 
   ...: # Sample df2.
   ...: n2 = 4_00
   ...: df2 = pd.DataFrame({'start_date': pd.date_range('2016-11-24', freq='18H22T', periods=n2)})
   ...: 
   ...: # Randomly shift the start and end dates of the df2 intervals.
   ...: shift_start = pd.Series(np.random.randint(30, size=n2)).cumsum().apply(lambda s: pd.DateOffset(seconds=s))
   ...: shift_end1 = pd.Series(np.random.randint(30, size=n2)).apply(lambda s: pd.DateOffset(seconds=s))
   ...: shift_end2 = pd.Series(np.random.randint(5, 45, size=n2)).apply(lambda m: pd.DateOffset(minutes=m))
   ...: df2['start_date'] += shift_start
   ...: df2['end_date'] = df2['start_date'] + shift_end1 + shift_end2

# this PR 
# _range_indices performs well here - the numpy option sorts only once (if at all)
# and such does less work than the numba option - sorting is relatively expensive
# so if it can be avoided great
# numba shines for overlaps or scenarios where the columns are not both monotically increasing
# and for relatively large dataframes
In [5]: %timeit df1.assign(end = df1.date + pd.DateOffset(minutes=9, seconds=59)).conditional_join(df2, ('date', 'end_date', '<='),('end', 'start_date','>='), use_numb a = False)
5.8 ms ± 764 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [6]: %timeit df1.assign(end = df1.date + pd.DateOffset(minutes=9, seconds=59)).conditional_join(df2, ('date', 'end_date', '<='),('end', 'start_date','>='), use_numba = True)
10.6 ms ± 1.3 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)

# Dev
In [7]: %timeit df1.assign(end = df1.date + pd.DateOffset(minutes=9, seconds=59)).conditional_join(df2, ('date', 'end_date', '<='), ('end', 'start_date','>='), use_numba = False)
54 ms ± 8.6 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [8]: %timeit df1.assign(end = df1.date + pd.DateOffset(minutes=9, seconds=59)).conditional_join(df2, ('date', 'end_date', '<='), ('end', 'start_date','>='), use_numba = True)
12.4 ms ± 623 µs per loop (mean ± std. dev. of 7 runs, 1 loop each)

indicator parameter usage:

In [5]: df1 = pd.DataFrame({'id': [1,1,1,2,2,3],
                        'value_1': [2,5,7,1,3,4]})
   
   df2 = pd.DataFrame({'id': [1,1,1,1,2,2,2,3],
                        'value_2A': [0,3,7,12,0,2,3,1],
                       'value_2B': [1,5,9,15,1,4,6,3]})

In [6]: (df1
   .conditional_join(
        df2,
        ('id', 'id', '=='),
        ('value_1', 'value_2A', '>='),
       ('value_1', 'value_2B', '<='),
        how='left',
       sort_by_appearance=True,
        indicator=True,
       # this ensures only the `df` dataframe is returned
        right_columns=None
    ))
Out[6]: 
   id  value_1     _merge
0   1        2  left_only
1   1        5       both
2   1        7       both
3   2        1       both
4   2        3       both
5   2        3       both
6   3        4  left_only

PR Checklist

Please ensure that you have done the following:

1. [ ] PR in from a fork off your branch. Do not PR from <your_username>:dev, but rather from <your_username>:<feature-branch_name>.

2. [x] If you're not on the contributors list, add yourself to AUTHORS.md.

3. [x] Add a line to CHANGELOG.md under the latest version header (i.e. the one that is "on deck") describing the contribution.
   • Do use some discretion here; if there are multiple PRs that are related, keep them in a single line.

Automatic checks

There will be automatic checks run on the PR. These include:

• Building a preview of the docs on Netlify
• Automatically linting the code
• Making sure the code is documented
• Making sure that all tests are passed
• Making sure that code coverage doesn't go down.

Relevant Reviewers

Please tag maintainers to review.

• @ericmjl

• Improve selection in conditional_join, such that only a single dataframe (left/right) is returned
• simplify the numba implementation
• add indicator parameter , similar to the indicator parameter in pd.merge

PR Description

Please describe the changes proposed in the pull request:

• support for named groups in regex, making it easier to associate name with the sub regex
• support for dictionary in names_pattern

Example:

iris
   Sepal.Length  Sepal.Width  Petal.Length  Petal.Width    Species
0           5.1          3.5           1.4          0.2     setosa
1           5.9          3.0           5.1          1.8  virginica

In [6]: iris.pivot_longer(
   ...:         index = 'Species',
   ...:         names_to = None,
   ...:         names_pattern = r'(?P<part>.+)\.(?P<dimension>.+)'
   ...:         )
Out[6]: 
     Species   part dimension  value
0     setosa  Sepal    Length    5.1
1  virginica  Sepal    Length    5.9
2     setosa  Sepal     Width    3.5
3  virginica  Sepal     Width    3.0
4     setosa  Petal    Length    1.4
5  virginica  Petal    Length    5.1
6     setosa  Petal     Width    0.2
7  virginica  Petal     Width    1.8

# retain sub parts of the columns as new headers:
In [26]: iris.pivot_longer(
    ...:         index = 'Species',
    ...:         names_to = None,
    ...:         names_pattern = r'(?P<_>.+)\.(?P<dimension>.+)'
    ...:         )
Out[26]: 
     Species dimension  Sepal  Petal
0     setosa    Length    5.1    1.4
1  virginica    Length    5.9    5.1
2     setosa     Width    3.5    0.2
3  virginica     Width    3.0    1.8

# reshape with a dictionary: 

In [27]: iris.pivot_longer(
    ...:         index = 'Species',
    ...:         names_to = None,
    ...:         names_pattern = {'Sepal':'Sepal','Petal':'Petal'})
    ...: 
    ...: 
Out[27]: 
     Species  Sepal  Petal
0     setosa    5.1    1.4
1  virginica    5.9    5.1
2     setosa    3.5    0.2
3  virginica    3.0    1.8

This PR resolves #1209 .

• @ericmjl

PR Description

Please describe the changes proposed in the pull request:

• minor fixes for drop_constant_columns and get_dupes
• improve performance for select when all entries are scalars and are the same dtype
• move is more flexible with the select_columns syntax - multiple columns/rows can be moved at once
• avoid mutation in collapse_levels
• impute now supports multiple columns, making it easy to deprecate fill_empty
• fix deprecation warning for np.bool8

Please tag maintainers to review.

• @ericmjl