how to get the average of dataframe column values





```
                    A        B
DATE                 
2013-05-01        473077    71333
2013-05-02         35131    62441
2013-05-03           727    27381
2013-05-04           481     1206
2013-05-05           226     1733
2013-05-06           NaN     4064
2013-05-07           NaN    41151
2013-05-08           NaN     8144
2013-05-09           NaN       23
2013-05-10           NaN       10

```

say i have the dataframe above. what is the easiest way to get a series with the same index which is the average of the columns A and B? the average needs to ignore NaN values. the twist is that this solution needs to be flexible to the addition of new columns to the dataframe.


the closest i have come was 



```
df.sum(axis=1) / len(df.columns)

```

however, this does not seem to ignore the NaN values


(note: i am still a bit new to the pandas library, so i'm guessing there's an obvious way to do this that my limited brain is simply not seeing)
Simply using `df.mean()` will Do The Right Thing(tm) with respect to NaNs:



```
>>> df
                 A      B
DATE                     
2013-05-01  473077  71333
2013-05-02   35131  62441
2013-05-03     727  27381
2013-05-04     481   1206
2013-05-05     226   1733
2013-05-06     NaN   4064
2013-05-07     NaN  41151
2013-05-08     NaN   8144
2013-05-09     NaN     23
2013-05-10     NaN     10
>>> df.mean(axis=1)
DATE
2013-05-01    272205.0
2013-05-02     48786.0
2013-05-03     14054.0
2013-05-04       843.5
2013-05-05       979.5
2013-05-06      4064.0
2013-05-07     41151.0
2013-05-08      8144.0
2013-05-09        23.0
2013-05-10        10.0
dtype: float64

```

You can use `df[["A", "B"]].mean(axis=1)` if there are other columns to ignore.
A couple of notes to get started:



> 
> .apply(literal\_eval)
> 
> 
> 


Wouldn't this be better as `map`?



> 
> I then re-create the Dask DF:
> 
> 
> ddf = dd.from\_pandas(ddf\_out, npartitions=nCores)
> 
> 
> 


`ddf_out` was already a dask dataframe, I don't know why you had to do this.



> 
> The columns in result seem to match those in metadir.
> 
> 
> 


The value of `result.columns` is taken from the meta you provided, no computing happens until you ask for it (dask is lazy in most operations). Does the ValueError exception not provide further information?


Here is a full example



```
x = ({'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'DEBRA MEALY',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'CHAIR PERSON',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'},
 {'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'HELEN GORDON',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'VICE CHAIR',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'})
df = pd.DataFrame({'a': x})
d = dd.from_pandas(df, 1)
meta = pd.DataFrame(columns=['F9_07_PZ_COMP_DIRECT', 
       'F9_07_PZ_DIRTRSTKEY_NAME',
       'F9_07_PZ_COMP_OTHER', 'F9_07_PZ_COMP_RELATED', 'F9_07_PZ_TITLE',
       'F9_07_PZ_AVE_HOURS_WEEK', 'F9_07_PC_TRUSTEE_INDIVIDUAL'], dtype="O")
d.map_partitions(lambda df: df.a.apply(pd.Series), meta=meta).compute()

```

How did I know what `meta` to use? I applied the function to the pandas dataframe - you might use a small piece of the dataframe to do this.


Some additional notes:


* it is an anti-pattern to load the data with pandas, pass to dask workers and then gather the whole result back to a pandas (in memory) dataframe, you are very unlikely to see a speedup this way and may incur a lot of overhead. You are better off loading with something like `dd.read_csv`, and aggregating or writing with dask functions too. Only `compute()` on something that will be small or not return anything (because it involves writing the output). The official examples do not use from\_pandas.
* string and dict processing are python methods, and so hold the interpreter lock (GIL) of any python function: threads will not actually run in parallel. To get parallelism, you need to run in processes, which is most easily achieved using <https://docs.dask.org/en/latest/setup/single-distributed.html>
* the distributed scheduler also gives you access to the dashboard, which has a lot of useful information to diagnose how your system is running. You can also configure a lot about its behaviour, in case you have firewall rules you need to obey. Hi JimCreech,


It is possible to achieve a similar result in Dataiku by going through the following steps:


* run a [Window](https://knowledge.dataiku.com/latest/courses/visual-recipes/window.html) recipe using id as partitioning column, event as ordering column, and compute the lagdiff values on days
* run a [Prepare](https://knowledge.dataiku.com/latest/courses/basics/prepare-data/concept-prepare-recipe.html) recipe which will:
	+ run the assertions on the specific conditions you mentioned and create a dummy\_tmp column we'll reuse at the final step
	+ filter out the records where event == 01 (since we won't need it for the aggregation)
* run a [Groupby](https://knowledge.dataiku.com/latest/courses/basics/group-data/group-summary.html) recipe to aggregate at the id level and get the minimum of the dummy\_tmp column (which should be 1 if all conditions you described are verified, 0 otherwise).


You will find attached a sample DSS project with an example of implementation of those steps using the small example data mentioned in your post. If you are working on a regular basis with time series data, you may find this [tutorial](https://knowledge.dataiku.com/latest/courses/visual-recipes/window/window.html) on the Window recipe capabilities useful.


Hope this helps !


Best,


Harizo




[View solution in original post](/t5/General-Discussion/Using-Lag-and-Lead-in-Dataiku/m-p/19308#M1146) for p = 1:10
 for q = 1:10
 if p ~= q
 ...
 end
 end
end
without a for loop:
x = rand(10, 10);
index = (1:10) ~= (1:10).';
mean(x(index)) % mean of elements, which are not on main diagonal Assuming you're joining on columns with no duplicates, which is a very common case:

* An inner join of A and B gives the result of A intersect B, i.e. the inner part of a [Venn diagram](http://en.wikipedia.org/wiki/Venn_diagram) intersection.
* An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

**Examples**

Suppose you have two tables, with a single column each, and data as follows:


```
A    B
-    -
1    3
2    4
3    5
4    6

```
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

**Inner join**

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.


```
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

```
**Left outer join**

A left outer join will give all rows in A, plus any common rows in B.


```
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+
1 | null
2 | null
3 |    3
4 |    4

```
**Right outer join**

A right outer join will give all rows in B, plus any common rows in A.


```
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
+
3    |  3
4    |  4
null |  5
null |  6

```
**Full outer join**

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.


```
select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
+
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

``` you seem to be missing some information about e.if e is the midpoint between d and f, thene is (1,1) take half of (4-2) = 1 and half of (5-3) = 1e alphabetically does come between d and f.just average the x values and average the y values of the two points d and for plot them graphically, carefully, maybe on some graph paper, and you'llvisually see the midpoint is at (1,1) :roll\_eyes:

[[image]](https://aws1.discourse-cdn.com/business6/uploads/dynamobim/original/3x/6/3/6393f7d9277c8a0ff2b48f07b1e2352f69f463b0.png "image")

t1 = [9, 9, 99, 9, 6, 7, 8];

t9 = dscore.list.chop(t1, 3);

a = t9;

t2 = get.valueatindex(a, 0);

t4 = [3, 2];

t10 = dscore.list.ofrepeateditem(t4, 3);

a1 = t10;

t3 = get.valueatindex(a1, 0);

t11 = t2<1l> + t3<1l>;

a2 = t9;

t5 = get.valueatindex(a2, 1);

a3 = t10;

t6 = get.valuea… thanks i was able to use something similar. i have just seen online that we’re not able to extract column values for calculated columns? is there any other way of extracting this data?

query = """ query { boards (ids: %s limit: 20, page: 1) { id name columns … [@najoua.abbaci](/u/najoua.abbaci)

mabye the flow can be adopted to following:

assign:

leftside: drresult (datatype: datarow)

right side:

dt\_datepv.asenumerable.orderby(function ( r ) datetime.parseexact(r(0).tostring.trim, “dd.mm.yyyy”, system.globalization.cultureinfo.currentuiculture.datetimeformat)).last()

so… you will need to manually link each battery record to all of its battery test records (using a linked record field).

then you can create a rollup field to give you the average of all its battery tests. hint: first of all, multiply and divide 9 in both the series after taking common 5 and 0.6 from them respectively. then make a g.p of the form \[10,{{10}^{2}},{{10}^{3}}....\] by writing 9 = 10 – 1, 99 = 100 – 1, and so on. finally, use the formula for the sum of n terms of g.p that is \[\dfrac{a\left( 1-{{r}^{n}} \right)}{\left( 1-r \right)}\].complete step-by-step solution -in this question, we have to find the sum of the n terms of the series.(i) 5 + 55 + 555 + ….(ii) .6 + .66 + .666 + ….let us consider the first seriess = 5 + 55 + 555 + ….. n termsby taking out 5 common from the above series, we get, s = 5 (1 + 11 + 111 + 1111 …. n terms)by multiplying 9 on both the sides of the above equation, we get, 9s = 5 [9 + 99 + 999 …… n times]\[\dfrac{9s}{5}=9+99+999.....\text{ n times}\]we can also write the above equation as, \[\dfrac{9s}{5}=\left( 10-1 \right)+\left( 100-1 \right)+\left( 1000-1 \right)....\text{ n times}\]by simplifying the above equation and taking the term 1 separately, we get, \[\dfrac{9s}{5}=\left( {{10}^{1}}+{{10}^{2}}+{{10}^{3}}.....\text{ n times} \right)-\left( 1+1+1......\text{ n times} \right)\]we know that 1 added n times is equal to n and the nth term of 1, 2, 3…. n. so, we get, \[\dfrac{9s}{5}=\left( {{10}^{1}}+{{10}^{2}}+{{10}^{3}}{{....10}^{n}} \right)-\left( n \right)\]we know that the series of the form \[a,ar,a{{r}^{2}},a{{r}^{3}}.....a{{r}^{n}}\] is in g.p and sum of the n terms of this g.p is given by \[\dfrac{a\left( 1-{{r}^{n}} \right)}{\left( 1-r \right)}\]in the above equation, we can see that \[10,{{10}^{2}},{{10}^{3}}....\] are in g.p with the first term and common ratio as 10. so, we get, \[\dfrac{9s}{5}=\dfrac{10\left( 1-{{\left( 10 \right)}^{n}} \right)}{1-10}-n\]\[\dfrac{9s}{5}=\dfrac{10\left( 1-{{\left( 10 \right)}^{n}} \right)}{-9}-n\]\[\dfrac{9s}{5}=\dfrac{10}{9}\left( {{10}^{n}}-1 \right)-n\]\[\dfrac{9s}{5}=\dfrac{10\left( {{10}^{n}}-1 \right)-9n}{9}\]by multiplying \[\dfrac{5}{9}\] on both the sides of the above equation, we get, \[s=\dfrac{5}{9}\left[ \dfrac{10\left( {{10}^{n}}-1 \right)-9n}{9} \right]\]\[s=\dfrac{50\left( {{10}^{n}}-1 \right)}{81}-\dfrac{5}{9}n\]hence, the sum of the sequence 5 + 55 + 555….. up to n terms is equal to \[\dfrac{50\left( {{10}^{n}}-1 \right)}{81}-\dfrac{5n}{9}\]let us consider the second set.s = 0.6 + 0.66 + 0.666 + ….. n times.by taking out 0.6 common, we can write the sequence as, s = 6 [0.1 + 0.11 + 0.111 ….. n terms]by multiplying and dividing rhs by 9, we get,\[s=\dfrac{6}{9}\left[ 0.9+0.99+0.999.....n\text{ terms} \right]\]\[s=\dfrac{2}{3}\left[ \dfrac{9}{10}+\dfrac{99}{100}+\dfrac{999}{1000}+\dfrac{9999}{10000}.....n\text{ terms} \right]\]we can write the above equation as, \[s=\dfrac{2}{3}\left[ \dfrac{\left( 10-1 \right)}{10}+\dfrac{\left( 100-1 \right)}{100}+\dfrac{\left( 1000-1 \right)}{1000}+\dfrac{\left( 10000-1 \right)}{10000}.....n\text{ terms} \right]\]\[s=\dfrac{2}{3}\left[ \left( 1-\dfrac{1}{10} \right)+\left( 1-\dfrac{1}{100} \right)+\left( 1-\dfrac{1}{1000} \right)+\left( 1-\dfrac{1}{10000} \right).....n\text{ terms} \right]\]\[s=\dfrac{2}{3}\left[ \left( 1+1+1......n\text{ terms} \right)-\left( \dfrac{1}{10}+\dfrac{1}{100}+\dfrac{1}{1000}.+...n\text{ terms} \right) \right]\]here, we can see that \[\dfrac{1}{10}+\dfrac{1}{{{\left( 10 \right)}^{2}}}+\dfrac{1}{{{\left( 10 \right)}^{3}}}+.....n\text{ terms}\] is in gp with \[a=\dfrac{1}{10}\] and \[r=\dfrac{1}{10}\].we know that the sum of n terms of the g.p is \[\dfrac{a\left( 1-{{r}^{n}} \right)}{\left( 1-r \right)}\]. so, we get, \[s=\dfrac{2}{3}\left[ \left( n \right)-\dfrac{\dfrac{1}{10}\left( 1-{{\left( \dfrac{1}{10} \right)}^{n}} \right)}{1-\dfrac{1}{10}} \right]\]\[s=\dfrac{2}{3}\left[ n-\dfrac{\dfrac{1}{10}\left( 1-\dfrac{1}{{{10}^{n}}} \right)}{\dfrac{9}{10}} \right]\]\[s=\dfrac{2}{3}\left[ n-\dfrac{1}{9}\left( 1-\dfrac{1}{{{10}^{n}}} \right) \right]\]we know that \[\dfrac{1}{{{a}^{n}}}={{a}^{-n}}\]. by using this, we get, \[s=\dfrac{2}{3}\left( n-\dfrac{1}{9}\left( 1-{{10}^{-n}} \right) \right)\]so, \[s=\dfrac{2}{3}n-\dfrac{2}{27}\left[ 1-{{\left( 10 \right)}^{-n}} \right]\]hence, we get the sum of the sequence 0.6 + 0.66 + 0.666 + ….. n terms as \[\dfrac{2}{3}n-\dfrac{2}{27}\left[ 1-{{\left( 10 \right)}^{-n}} \right]\].note: in this question, students must note that whenever we are in need to find the sum of series of the type a + aa + aaa + aaaa ….., we always have to make the series of 9 + 99 + 999 + ….. type by multiplying and dividing the whole series by 9 so that we can get a g.p of the type \[10,{{10}^{2}},{{10}^{3}}\]…. also in this question, some students make this mistake of considering 5 + 55 + 555 + …. or 0.6 + 0.66 + 0.666 + …. in g.p which is wrong because we can see that there is no common ratio in these sequences.