Skip to content

Instantly share code, notes, and snippets.

@timstaley

timstaley/pandas_overlap.ipynb

Created February 9, 2018 17:04
Show Gist options
  • Save timstaley/3bd72ce9c42d5b8ead4caabdbdb95104 to your computer and use it in GitHub Desktop.
Save timstaley/3bd72ce9c42d5b8ead4caabdbdb95104 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
pandas_overlap.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import numpy as np\n",
"import pandas as pd"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"import string"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"string.ascii_uppercase"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>A</th>\n",
" <th>B</th>\n",
" <th>C</th>\n",
" <th>D</th>\n",
" <th>E</th>\n",
" <th>F</th>\n",
" <th>G</th>\n",
" <th>H</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>4</td>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>8</td>\n",
" <td>9</td>\n",
" <td>10</td>\n",
" <td>11</td>\n",
" <td>12</td>\n",
" <td>13</td>\n",
" <td>14</td>\n",
" <td>15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>16</td>\n",
" <td>17</td>\n",
" <td>18</td>\n",
" <td>19</td>\n",
" <td>20</td>\n",
" <td>21</td>\n",
" <td>22</td>\n",
" <td>23</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>24</td>\n",
" <td>25</td>\n",
" <td>26</td>\n",
" <td>27</td>\n",
" <td>28</td>\n",
" <td>29</td>\n",
" <td>30</td>\n",
" <td>31</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>32</td>\n",
" <td>33</td>\n",
" <td>34</td>\n",
" <td>35</td>\n",
" <td>36</td>\n",
" <td>37</td>\n",
" <td>38</td>\n",
" <td>39</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>40</td>\n",
" <td>41</td>\n",
" <td>42</td>\n",
" <td>43</td>\n",
" <td>44</td>\n",
" <td>45</td>\n",
" <td>46</td>\n",
" <td>47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>48</td>\n",
" <td>49</td>\n",
" <td>50</td>\n",
" <td>51</td>\n",
" <td>52</td>\n",
" <td>53</td>\n",
" <td>54</td>\n",
" <td>55</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>56</td>\n",
" <td>57</td>\n",
" <td>58</td>\n",
" <td>59</td>\n",
" <td>60</td>\n",
" <td>61</td>\n",
" <td>62</td>\n",
" <td>63</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>64</td>\n",
" <td>65</td>\n",
" <td>66</td>\n",
" <td>67</td>\n",
" <td>68</td>\n",
" <td>69</td>\n",
" <td>70</td>\n",
" <td>71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>72</td>\n",
" <td>73</td>\n",
" <td>74</td>\n",
" <td>75</td>\n",
" <td>76</td>\n",
" <td>77</td>\n",
" <td>78</td>\n",
" <td>79</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" A B C D E F G H\n",
"0 0 1 2 3 4 5 6 7\n",
"1 8 9 10 11 12 13 14 15\n",
"2 16 17 18 19 20 21 22 23\n",
"3 24 25 26 27 28 29 30 31\n",
"4 32 33 34 35 36 37 38 39\n",
"5 40 41 42 43 44 45 46 47\n",
"6 48 49 50 51 52 53 54 55\n",
"7 56 57 58 59 60 61 62 63\n",
"8 64 65 66 67 68 69 70 71\n",
"9 72 73 74 75 76 77 78 79"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"nrows = 10\n",
"ncols = 8\n",
"superframe = pd.DataFrame(index=range(nrows), columns=list(string.ascii_uppercase[:ncols]),\n",
" data=np.arange(nrows*ncols).reshape(nrows,-1))\n",
"superframe"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>C</th>\n",
" <th>D</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>10</td>\n",
" <td>11</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" C D\n",
"0 2 3\n",
"1 10 11"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"subframe1 = superframe.iloc[0:2,2:4]\n",
"subframe1"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>D</th>\n",
" <th>E</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>35</td>\n",
" <td>36</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>43</td>\n",
" <td>44</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" D E\n",
"4 35 36\n",
"5 43 44"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"subframe2 = superframe.iloc[4:6,3:5]\n",
"subframe2"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>E</th>\n",
" <th>F</th>\n",
" <th>G</th>\n",
" <th>H</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>36.1</td>\n",
" <td>37.1</td>\n",
" <td>38.1</td>\n",
" <td>39.1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>44.1</td>\n",
" <td>45.1</td>\n",
" <td>46.1</td>\n",
" <td>47.1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" E F G H\n",
"4 36.1 37.1 38.1 39.1\n",
"5 44.1 45.1 46.1 47.1"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"subframe3 = superframe.iloc[4:6, 4:8]\n",
"subframe3 +=0.1\n",
"subframe3"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [],
"source": [
"subframes = [subframe1, subframe2, subframe3]"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>C</th>\n",
" <th>D</th>\n",
" <th>E</th>\n",
" <th>F</th>\n",
" <th>G</th>\n",
" <th>H</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2.0</td>\n",
" <td>3.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>10.0</td>\n",
" <td>11.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>NaN</td>\n",
" <td>35.0</td>\n",
" <td>36.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>NaN</td>\n",
" <td>43.0</td>\n",
" <td>44.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>36.1</td>\n",
" <td>37.1</td>\n",
" <td>38.1</td>\n",
" <td>39.1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>44.1</td>\n",
" <td>45.1</td>\n",
" <td>46.1</td>\n",
" <td>47.1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" C D E F G H\n",
"0 2.0 3.0 NaN NaN NaN NaN\n",
"1 10.0 11.0 NaN NaN NaN NaN\n",
"4 NaN 35.0 36.0 NaN NaN NaN\n",
"5 NaN 43.0 44.0 NaN NaN NaN\n",
"4 NaN NaN 36.1 37.1 38.1 39.1\n",
"5 NaN NaN 44.1 45.1 46.1 47.1"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"concat = pd.concat(subframes)\n",
"concat"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>C</th>\n",
" <th>D</th>\n",
" <th>E</th>\n",
" <th>F</th>\n",
" <th>G</th>\n",
" <th>H</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" C D E F G H\n",
"0 1 1 0 0 0 0\n",
"1 1 1 0 0 0 0\n",
"4 0 1 2 1 1 1\n",
"5 0 1 2 1 1 1"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"concat.groupby(concat.index).count()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now with disjoint subframes..."
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [],
"source": [
"upper_left = superframe.iloc[:5, :4]\n",
"upper_right = superframe.iloc[:5, 4:]\n",
"lower_left = superframe.iloc[5:, :4]"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [],
"source": [
"quads = [upper_left, upper_right, lower_left]\n",
"# for q in quads:\n",
"# print(q)"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [],
"source": [
"q_concat = pd.concat(quads)\n",
"# q_concat"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [],
"source": [
"cell_counts = q_concat.groupby(q_concat.index).count()\n",
"# cell_counts"
]
},
{
"cell_type": "code",
"execution_count": 36,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"False"
]
},
"execution_count": 36,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"np.any(cell_counts>1)"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>A</th>\n",
" <th>B</th>\n",
" <th>C</th>\n",
" <th>D</th>\n",
" <th>E</th>\n",
" <th>F</th>\n",
" <th>G</th>\n",
" <th>H</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0.0</td>\n",
" <td>1.0</td>\n",
" <td>2.0</td>\n",
" <td>3.0</td>\n",
" <td>4.0</td>\n",
" <td>5.0</td>\n",
" <td>6.0</td>\n",
" <td>7.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>8.0</td>\n",
" <td>9.0</td>\n",
" <td>10.0</td>\n",
" <td>11.0</td>\n",
" <td>12.0</td>\n",
" <td>13.0</td>\n",
" <td>14.0</td>\n",
" <td>15.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>16.0</td>\n",
" <td>17.0</td>\n",
" <td>18.0</td>\n",
" <td>19.0</td>\n",
" <td>20.0</td>\n",
" <td>21.0</td>\n",
" <td>22.0</td>\n",
" <td>23.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>24.0</td>\n",
" <td>25.0</td>\n",
" <td>26.0</td>\n",
" <td>27.0</td>\n",
" <td>28.0</td>\n",
" <td>29.0</td>\n",
" <td>30.0</td>\n",
" <td>31.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>32.0</td>\n",
" <td>33.0</td>\n",
" <td>34.0</td>\n",
" <td>35.0</td>\n",
" <td>36.0</td>\n",
" <td>37.0</td>\n",
" <td>38.0</td>\n",
" <td>39.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>40.0</td>\n",
" <td>41.0</td>\n",
" <td>42.0</td>\n",
" <td>43.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>48.0</td>\n",
" <td>49.0</td>\n",
" <td>50.0</td>\n",
" <td>51.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>56.0</td>\n",
" <td>57.0</td>\n",
" <td>58.0</td>\n",
" <td>59.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>64.0</td>\n",
" <td>65.0</td>\n",
" <td>66.0</td>\n",
" <td>67.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>72.0</td>\n",
" <td>73.0</td>\n",
" <td>74.0</td>\n",
" <td>75.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" A B C D E F G H\n",
"0 0.0 1.0 2.0 3.0 4.0 5.0 6.0 7.0\n",
"1 8.0 9.0 10.0 11.0 12.0 13.0 14.0 15.0\n",
"2 16.0 17.0 18.0 19.0 20.0 21.0 22.0 23.0\n",
"3 24.0 25.0 26.0 27.0 28.0 29.0 30.0 31.0\n",
"4 32.0 33.0 34.0 35.0 36.0 37.0 38.0 39.0\n",
"5 40.0 41.0 42.0 43.0 NaN NaN NaN NaN\n",
"6 48.0 49.0 50.0 51.0 NaN NaN NaN NaN\n",
"7 56.0 57.0 58.0 59.0 NaN NaN NaN NaN\n",
"8 64.0 65.0 66.0 67.0 NaN NaN NaN NaN\n",
"9 72.0 73.0 74.0 75.0 NaN NaN NaN NaN"
]
},
"execution_count": 41,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"q_combine = q_concat.groupby(q_concat.index).sum()\n",
"q_combine.mask(cell_counts==0)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment