Basics

The base interface you choose for your repository has two main purposes. First, you allow the Spring Data repository infrastructure to find your interface and trigger the proxy creation so that you inject instances of the interface into clients. The second purpose is to pull in as much functionality as needed into the interface without having to declare extra methods.

The common interfaces

The Spring Data core library ships with two base interfaces that expose a dedicated set of functionalities:

• CrudRepository – CRUD methods
• PagingAndSortingRepository – methods for pagination and sorting (extends CrudRepository)

Store-specific interfaces

The individual store modules (e.g. for JPA or MongoDB) expose store-specific extensions of these base interfaces to allow access to store-specific functionality like flushing or dedicated batching that take some store specifics into account. An example for this is deleteInBatch(…) of JpaRepository which is different from delete(…) as it uses a query to delete the given entities which is more performant but comes with the side effect of not triggering the JPA-defined cascades (as the spec defines it).

We generally recommend not to use these base interfaces as they expose the underlying persistence technology to the clients and thus tighten the coupling between them and the repository. Plus, you get a bit away from the original definition of a repository which is basically “a collection of entities”. So if you can, stay with PagingAndSortingRepository.

Custom repository base interfaces

The downside of directly depending on one of the provided base interfaces is two-fold. Both of them might be considered as theoretical but I think they’re important to be aware of:

1. Depending on a Spring Data repository interface couples your repository interface to the library. I don’t think this is a particular issue as you’ll probably use abstractions like Page or Pageable in your code anyway. Spring Data is not any different from any other general purpose library like commons-lang or Guava. As long as it provides reasonable benefit, it’s just fine.
2. By extending e.g. CrudRepository, you expose a complete set of persistence method at once. This is probably fine in most circumstances as well but you might run into situations where you’d like to gain more fine-grained control over the methods expose, e.g. to create a ReadOnlyRepository that doesn’t include the save(…) and delete(…) methods of CrudRepository.

The solution to both of these downsides is to craft your own base repository interface or even a set of them. In a lot of applications I have seen something like this:

interface ApplicationRepository<T> extends PagingAndSortingRepository<T, Long> { }

interface ReadOnlyRepository<T> extends Repository<T, Long> {

  // Al finder methods go here
}

The first repository interface is some general purpose base interface that actually only fixes point 1 but also ties the ID type to be Long for consistency. The second interface usually has all the find…(…) methods copied from CrudRepository and PagingAndSortingRepository but does not expose the manipulating ones. Read more on that approach in the reference documentation.

Here’s a generator that yields evenly-sized chunks:

def chunks(lst, n):
    """Yield successive n-sized chunks from lst."""
    for i in range(0, len(lst), n):
        yield lst[i:i + n]

import pprint
pprint.pprint(list(chunks(range(10, 75), 10)))
[[10, 11, 12, 13, 14, 15, 16, 17, 18, 19],
 [20, 21, 22, 23, 24, 25, 26, 27, 28, 29],
 [30, 31, 32, 33, 34, 35, 36, 37, 38, 39],
 [40, 41, 42, 43, 44, 45, 46, 47, 48, 49],
 [50, 51, 52, 53, 54, 55, 56, 57, 58, 59],
 [60, 61, 62, 63, 64, 65, 66, 67, 68, 69],
 [70, 71, 72, 73, 74]]

For Python 2, using xrange instead of range:

def chunks(lst, n):
    """Yield successive n-sized chunks from lst."""
    for i in xrange(0, len(lst), n):
        yield lst[i:i + n]

Below is a list comprehension one-liner. The method above is preferable, though, since using named functions makes code easier to understand. For Python 3:

[lst[i:i + n] for i in range(0, len(lst), n)]

For Python 2:

[lst[i:i + n] for i in xrange(0, len(lst), n)]

The Good:

• Do include a small example DataFrame, either as runnable code:

  In [1]: df = pd.DataFrame([[1, 2], [1, 3], [4, 6]], columns=['A', 'B'])
  

  or make it “copy and pasteable” using pd.read_clipboard(sep=r'\s\s+').

  In [2]: df
  Out[2]:
     A  B
  0  1  2
  1  1  3
  2  4  6
  

  Test it yourself to make sure it works and reproduces the issue.

  • You can format the text for Stack Overflow by highlighting and using Ctrl+K (or prepend four spaces to each line), or place three backticks (“`) above and below your code with your code unindented.
  • I really do mean small. The vast majority of example DataFrames could be fewer than 6 rows,^{[citation needed]} and I bet I can do it in 5. Can you reproduce the error with df = df.head()? If not, fiddle around to see if you can make up a small DataFrame which exhibits the issue you are facing.

    But every rule has an exception, the obvious one being for performance issues (in which case definitely use %timeit and possibly %prun to profile your code), where you should generate:

    df = pd.DataFrame(np.random.randn(100000000, 10))
    

    Consider using np.random.seed so we have the exact same frame. Having said that, “make this code fast for me” is not strictly on topic for the site.

  • For getting runnable code, df.to_dict is often useful, with the different orient options for different cases. In the example above, I could have grabbed the data and columns from df.to_dict('split').
• Write out the outcome you desire (similarly to above)

  In [3]: iwantthis
  Out[3]:
     A  B
  0  1  5
  1  4  6
  

  Explain where the numbers come from:

  The 5 is the sum of the B column for the rows where A is 1.

• Do show the code you’ve tried:

  In [4]: df.groupby('A').sum()
  Out[4]:
     B
  A
  1  5
  4  6
  

  But say what’s incorrect:

  The A column is in the index rather than a column.

• Do show you’ve done some research (search the documentation, search Stack Overflow), and give a summary:
  

  The docstring for sum simply states “Compute sum of group values”

  The groupby documentation doesn’t give any examples for this.

  Aside: the answer here is to use df.groupby('A', as_index=False).sum().

• If it’s relevant that you have Timestamp columns, e.g. you’re resampling or something, then be explicit and apply pd.to_datetime to them for good measure.

  df['date'] = pd.to_datetime(df['date']) # this column ought to be date.
  

  Sometimes this is the issue itself: they were strings.

The Bad:

• Don’t include a MultiIndex, which we can’t copy and paste (see above). This is kind of a grievance with Pandas’ default display, but nonetheless annoying:

  In [11]: df
  Out[11]:
       C
  A B
  1 2  3
    2  6
  

  The correct way is to include an ordinary DataFrame with a set_index call:

  In [12]: df = pd.DataFrame([[1, 2, 3], [1, 2, 6]], columns=['A', 'B', 'C'])
  
  In [13]: df = df.set_index(['A', 'B'])
  
  In [14]: df
  Out[14]:
       C
  A B
  1 2  3
    2  6
  

• Do provide insight to what it is when giving the outcome you want:

     B
  A
  1  1
  5  0
  

  Be specific about how you got the numbers (what are they)… double check they’re correct.

• If your code throws an error, do include the entire stack trace. This can be edited out later if it’s too noisy. Show the line number and the corresponding line of your code which it’s raising against.
• Pandas 2.0 introduced a number of changes, and Pandas 1.0 before that, so if you’re getting unexpected output, include the version:

  pd.__version__
  

  On that note, you might also want to include the version of Python, your OS, and any other libraries. You could use pd.show_versions() or the session_info package (which shows loaded libraries and Jupyter/IPython environment).

The Ugly:

• Don’t link to a CSV file we don’t have access to (and ideally don’t link to an external source at all).

  df = pd.read_csv('my_secret_file.csv') # ideally with lots of parsing options
  

  Most data is proprietary, we get that. Make up similar data and see if you can reproduce the problem (something small).

• Don’t explain the situation vaguely in words, like you have a DataFrame which is “large”, mention some of the column names in passing (be sure not to mention their dtypes). Try and go into lots of detail about something which is completely meaningless without seeing the actual context. Presumably no one is even going to read to the end of this paragraph.

  Essays are bad; it’s easier with small examples.

• Don’t include 10+ (100+??) lines of data munging before getting to your actual question.

  Please, we see enough of this in our day jobs. We want to help, but not like this…. Cut the intro, and just show the relevant DataFrames (or small versions of them) in the step which is causing you trouble.

Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.

But… this wasn’t the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.

The SQL way…

Given his table looks something like this:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Now look into his/her desired table:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use … GROUP BY.

The basic SQL providing this pivot can look something like this:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about this topic:

• http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
• http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
• http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
• https://codingsight.com/pivot-tables-in-mysql/