Python Split data into multiple files with a rule
<p>I need some idea to solve my problem in python to split a file.</p>

<p>I more than 1.000.000 rows in a file with 2 columns: "accountid" and a "property". One "accountid" can have multiple properties, but each property is one row. Looks something like this:
<a href="http://i.stack.imgur.com/d2aVJ.png" rel="nofollow">Example</a></p>

<p>I need to split this data into 50.000 rows per file (which is not an issue). However, I one file also only allows 50 "properties" per "accountid". And a lot have more than 50 properties.</p>

<p>Do you have any <strong>idea</strong> how to technically solve this best? I do not require any code ;)</p>

<p>Thanks</p>

<p>Flo</p>

<p>Here is one solution that comes to my mind:</p>

<p>First you have to determine how many partitions you will need, based on two parameters X and Y. X is determined by the accountid with the maximum number of properties. Let's assume accountid=7 has the maximum number of properties equal to 270 properties. That means you will need at least 6 partitions to ensure a solution exists where none of the partitions will have more than 50 of the acountid=7 rows (based on pigeonhole principle). Y is determined by the total number of rows you have and how big each partition is (in your example Y is 1000,000/50,000 = 20). We take the maximum of X and Y as the number of partitions we need. In this scenario: Number of Partitions = max(6, 20) = 20.</p>

<p>Now, you sort the entire table using accountid. Let's assume we know the row number for each row. We will then define Partition k as:</p>

<pre><code>P_k = {row | row_number % 20 = k}
</code></pre>

<p>That should satisfy both of your requirements.</p>

<p>Note that if max(X, Y) = X, you will have to allow some partitions with less than 50K rows. Otherwise there is no solution to this problem.</p>

python split