I think what you want is possible using `merge`. Pass in the keyword arguments for `left_on` and `right_on` to tell Pandas which column(s) from each DataFrame to use as keys:

    pandas.merge(df1, df2, how=&#39;left&#39;, left_on=[&#39;id_key&#39;], right_on=[&#39;fk_key&#39;])

The documentation describes this in more detail on [this page](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging).

df2[&#39;id_key&#39;] = df2[&#39;fk_key&#39;].str.lower()

df1[&#39;id_key&#39;] = df1[&#39;id_key&#39;].str.lower()

# Now try to merge the  dataframes

df3 = pd.merge(df2,df1,how=&#39;inner&#39;, on=&#39;id_key&#39;)

![enter image description here][1]


How can I change the colour of small triangle at the bottom right corner of spinner like shown in the image?
It is showing default grey colour right now. like this

![enter image description here][2]


  [1]: http://i.stack.imgur.com/5nzdR.jpg
  [2]: http://i.stack.imgur.com/ZJwWm.jpg

Change colour of small triangle on spinner in android

Using Xcode 6 GM seed my code completion has stopped working. It was working the other day. I was trying to get the unwind segue work around to work. I had made an Objc header file and assigned it as a header for a Swift class. 

At this point I get code completion with an Objective-c project. But, not with a Swift project. 

I have tried restarting Xcode, making a new empty project. 

Xcode 6 Swift code completion not working

According to [this documentation](http://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html#left-outer-join) I can only make a join between fields having the same name.

Do you know if it&#39;s possible to join two DataFrames on a field having different names?

The equivalent in SQL would be:

    SELECT *
    FROM df1
    LEFT OUTER JOIN df2
      ON df1.id_key = df2.fk_key

Pandas: join DataFrames on field with different names?

According to <a href="http://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html#left-outer-join" target="_blank" rel="noopener noreferrer">this documentation</a> I can only make a join between fields having the same name.
Do you know if it's possible to join two DataFrames on a field having different names?
The equivalent in SQL would be:
<pre><code class="hljs language-sql">SELECT *
FROM df1
LEFT OUTER JOIN df2
 ON df1.id_key = df2.fk_key
</code></pre>

For some reason, I can&#39;t use the `Tkinter` or `tkinter` module.
After running the following command in the python shell

    import Tkinter
or

    import tkinter

I got this error

&gt;    ModuleNotFoundError: No module named &#39;Tkinter&#39;

or 

&gt;    ModuleNotFoundError: No module named &#39;tkinter&#39;

What could be the reason for and how can we solve it?

 

ImportError: No module named &#39;Tkinter&#39;

I have a data frame df which looks like this. Date and Time are 2 multilevel index

                               observation1   observation2
    date          Time                             
    2012-11-02    9:15:00      79.373668      224
                  9:16:00      130.841316     477
    2012-11-03    9:15:00      45.312814      835
                  9:16:00      123.776946     623
                  9:17:00      153.76646      624
                  9:18:00      463.276946     626
                  9:19:00      663.176934     622
                  9:20:00      763.77333      621
    2012-11-04    9:15:00      115.449437     122
                  9:16:00      123.776946     555
                  9:17:00      153.76646      344
                  9:18:00      463.276946     212

I want to run some complex process over daily data block.

Pseudo code would look like
  

     for count in df(level 0 index) :
         new_df = get only chunk for count
         complex_process(new_df)

So, first of all, I could not find a way to access only blocks for a date

    2012-11-03    9:15:00      45.312814      835
                  9:16:00      123.776946     623
                  9:17:00      153.76646      624
                  9:18:00      463.276946     626
                  9:19:00      663.176934     622
                  9:20:00      763.77333      621

and then send it for processing. I am doing this in for loop as I am not sure if there is any way to do it without mentioning exact value of level 0 column. I did some basic search and able to get df.index.get_level_values(0), but it returns me all the values and that causes loop to run multiple times for a day. I want to create a dataframe per day and send it for processing.


     

How to iterate over pandas multiindex dataframe using index

I&#39;m building an app that has a separated front-end (Angular or some other JS library) and backend (Django). To ensure some security of requests being sent to the server, I want to append a url parameter say `server/someurl?unique_id=Something-unique`. 

I am storing this unique code on the machine&#39;s `localStorage` for a specific time. However, I want to set this code using some sort of function on the server end which will not only generate this random alphanumeric text but also validate it based on incoming requests. 

For example:

When a user opens the app, it&#39;ll send a `server/setCode` which will respond with this randomly generated string which I will store to `Local Storage` using JS. 

On an outgoing request, say `server/getdata?someparameter=some_data&amp;unique_id=string_from_local_storage` which the server can validate against the generating function and only then process the rest of the url. 

Is there a package or a module that could help me achieve the generation and validation? 
I hope I could convey what I want as I&#39;m not able to find any solution for this short of writing the function to generate and test myself. 

Django package to generate random alphanumeric string

I am trying to write a bulk upsert in python using the SQLAlchemy module (not in SQL!).

I am getting the following error on a SQLAlchemy add:

    sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint &quot;posts_pkey&quot;
    DETAIL:  Key (id)=(TEST1234) already exists.

I have a table called `posts` with a primary key on the `id` column.

In this example, I already have a row in the db with `id=TEST1234`. When I attempt to `db.session.add()` a new posts object with the `id` set to `TEST1234`, I get the error above. I was under the impression that if the primary key already exists, the record would get updated. 

**How can I upsert with Flask-SQLAlchemy based on primary key alone? Is there a simple solution?**

If there is not, I can always check for and delete any record with a matching id, and then insert the new record, but that seems expensive for my situation, where I do not expect many updates.




SQLAlchemy - performing a bulk upsert (if exists, update, else insert) in postgresql

I use Django1.7 with Mezzanine. I create simple profile (according to Mezzanine documentation) stored in separate app &quot;profiles&quot;:

    class RoadmapProfile(models.Model):
        user = models.OneToOneField(&quot;auth.User&quot;)
        fullname = models.CharField(max_length=100, verbose_name=&quot;Full name&quot;)

Creation of migrations returns:

      Migrations for &#39;profiles&#39;:
          0001_initial.py:
            - Create model RoadmapProfile

When I run &quot;migrate profiles&quot;:

    Operations to perform:
      Apply all migrations: profiles
    Running migrations:
      No migrations to apply.

The issue is, when I try to open any page related to mezzanine.accounts (for example update account), it crashes with:

    OperationalError at /accounts/update/

    no such column: profiles_roadmapprofile.fullname

What I have done wrong?

Django 1.7 - &quot;No migrations to apply&quot; when run migrate after makemigrations

I am trying to read a large csv file (aprox. 6 GB) in pandas and i am getting a memory error:

    MemoryError                               Traceback (most recent call last)
    &lt;ipython-input-58-67a72687871b&gt; in &lt;module&gt;()
    ----&gt; 1 data=pd.read_csv(&#39;aphro.csv&#39;,sep=&#39;;&#39;)

    ...
    
    MemoryError: 

Any help on this?



How do I read a large csv file with pandas?

    import pandas as pd
    dfs = pd.HDFStore(&#39;xxxxx.h5&#39;)

throws this error:

`&quot;ImportError: HDFStore requires PyTables, &quot;No module named tables&quot; problem importing&quot;`

I tried to install PyTables, which Requires Cython.
I have Cython 0.21 installed, but it is throwing an error stating that Cython should be greater than 0.13
 
Here is the log that I am getting:

    &quot;.. ERROR:: You need Cython 0.13 or greater to compile PyTables!
    
    ----------------------------------------
    Cleaning up...
    Command python setup.py egg_info failed with error code 1 in /private/tmp/pip_build_root/tables
    Storing debug log for failure in /Users/nikhilsahai/Library/Logs/pip.log
    Nikhils-MacBook-Pro:~ nikhilsahai$ sudo pip install cython
    Requirement already satisfied (use --upgrade to upgrade): cython in /Library/Python/2.7/site-packages/Cython-0.21-py2.7-macosx-10.9-intel.egg
    Cleaning up...&quot;

Please guide me how to do solve this issue.


ImportError HDFStore requires PyTables No module named tables

Why does pandas make a distinction between a ``Series`` and a single-column ``DataFrame``?  
In other words: what is the reason of existence of the ``Series`` class? 

I&#39;m mainly using time series with datetime index, maybe that helps to set the context. 



What is the difference between a pandas Series and a single-column DataFrame?

I have a python-pandas-DataFrame in which first column is `&quot;user_id&quot;` and rest of the columns are tags(`&quot;Tag_0&quot;` to `&quot;Tag_122&quot;`). 

I have the data in the following format:

    UserId	Tag_0	Tag_1
    7867688	0	5
    7867688	0	3
    7867688	3	0
    7867688	3.5	3.5
    7867688	4	4
    7867688	3.5	0

My aim is to achieve `Sum(Tag)/Count(NonZero(Tags))` for each user_id

`df.groupby(&#39;user_id&#39;).sum()`, gives me `sum(tag)`, however I am clueless about counting non zero values

Is it possible to achieve `Sum(Tag)/Count(NonZero(Tags))` in one command?

In MySQL I could achieve this as follows:-

    select user_id, sum(tag)/count(nullif(tag,0)) from table group by 1

Any help shall be appreciated.



Counting non zero values in each column of a DataFrame in python

Is it possible to do the following:

    IF [a] = 1234 THEN JOIN ON TableA 
    ELSE JOIN ON TableB

If so, what is the correct syntax?

Conditional JOIN Statement SQL Server

I have the following data frames:

    print(df_a)
         mukey  DI  PI
    0   100000  35  14
    1  1000005  44  14
    2  1000006  44  14
    3  1000007  43  13
    4  1000008  43  13

    print(df_b)
        mukey  niccdcd
    0  190236        4
    1  190237        6
    2  190238        7
    3  190239        4
    4  190240        7

When I try to join these data frames:

    join_df = df_a.join(df_b, on=&#39;mukey&#39;, how=&#39;left&#39;)

I get the error:

    *** ValueError: columns overlap but no suffix specified: Index([u&#39;mukey&#39;], dtype=&#39;object&#39;)

Why is this so? The data frames do have common `&#39;mukey&#39;` values.



Pandas join issue: columns overlap but no suffix specified

I&#39;m trying to inner join DataFrame A to DataFrame B and am running into an error.

Here&#39;s my join statement:

    merged = DataFrameA.join(DataFrameB, on=[&#39;Code&#39;,&#39;Date&#39;])

And here&#39;s the error:

    ValueError: len(left_on) must equal the number of levels in the index of &quot;right&quot;


I&#39;m not sure the column order matters (they aren&#39;t truly &quot;ordered&quot; are they?), but just in case, the DataFrames are organized like this:

    DataFrameA:  Code, Date, ColA, ColB, ColC, ..., ColG, ColH (shape: 80514, 8 - no index)
    DataFrameB:  Date, Code, Col1, Col2, Col3, ..., Col15, Col16 (shape: 859, 16 - no index)

Do I need to correct my join statement?  Or is there another, better way to get the intersection (or inner join) of these two DataFrames?


Why does Pandas inner join give ValueError: len(left_on) must equal the number of levels in the index of &quot;right&quot;?

I have below entities:

``` java
public class Category {
   private Integer id;

   @OneToMany(mappedBy = &quot;parent&quot;)
   private List&lt;Topic&gt; topics;
}

public class Topic {
   private Integer id;

   @OneToMany(mappedBy = &quot;parent&quot;)
   private List&lt;Posts&gt; posts;

   @ManyToOne
   @JoinColumn(name = &quot;id&quot;)
   private Category parent;
}

public class Post {
   private Integer id;

   @ManyToOne
   @JoinColumn(name = &quot;id&quot;)
   private Topic parent;
   /* Post fields */
}
```

and I want to fetch all categories with joined `topics` and joined `posts` using JPQL query. I wrote query like below:

```
SELECT c FROM Category c
JOIN FETCH c.topics t
JOIN FETCH t.posts p WHERE 
```

But I got the error
```
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
```

I found articles about this error, but these articles only describe situation where in one entity are two collections to join. My problem is a little different and I don&#39;t know how to solve it.

It is possible to do it in one query?



How to use multiple JOIN FETCH in one JPQL query

I need to merge two pandas dataframes on an identifier and a condition where a date in one dataframe is between two dates in the other dataframe.

Dataframe A has a date (&quot;fdate&quot;) and an ID (&quot;cusip&quot;):

![enter image description here][1]

I need to merge this with this dataframe B:

![enter image description here][2]

on `A.cusip==B.ncusip` and `A.fdate` is between `B.namedt` and `B.nameenddt`.

In SQL this would be trivial, but the only way I can see how to do this in pandas is to first merge unconditionally on the identifier, and then filter on the date condition:

    df = pd.merge(A, B, how=&#39;inner&#39;, left_on=&#39;cusip&#39;, right_on=&#39;ncusip&#39;)
    df = df[(df[&#39;fdate&#39;]&gt;=df[&#39;namedt&#39;]) &amp; (df[&#39;fdate&#39;]&lt;=df[&#39;nameenddt&#39;])]

Is this really the best way to do this? It seems that it would be much better if one could filter within the merge so as to avoid having a potentially very large dataframe after the merge but before the filter has completed.

  [1]: http://i.stack.imgur.com/qQ3UB.png
  [2]: http://i.stack.imgur.com/z6ESB.png
  [3]: http://stackoverflow.com/questions/29802022/pandas-dataframe-merge-between-two-values-instead-of-matching-one

Merge pandas dataframes where one value is between two others

I have a Pandas series sf:

    email
    email1@email.com    [1.0, 0.0, 0.0]
    email2@email.com    [2.0, 0.0, 0.0]
    email3@email.com    [1.0, 0.0, 0.0]
    email4@email.com    [4.0, 0.0, 0.0]
    email5@email.com    [1.0, 0.0, 3.0]
    email6@email.com    [1.0, 5.0, 0.0]

And I would like to transform it to the following DataFrame:

    index | email             | list
    _____________________________________________
    0     | email1@email.com  | [1.0, 0.0, 0.0]
    1     | email2@email.com  | [2.0, 0.0, 0.0]
    2     | email3@email.com  | [1.0, 0.0, 0.0]
    3     | email4@email.com  | [4.0, 0.0, 0.0]
    4     | email5@email.com  | [1.0, 0.0, 3.0]
    5     | email6@email.com  | [1.0, 5.0, 0.0]

I found a way to do it, but I doubt it&#39;s the more efficient one:

    df1 = pd.DataFrame(data=sf.index, columns=[&#39;email&#39;])
    df2 = pd.DataFrame(data=sf.values, columns=[&#39;list&#39;])
    df = pd.merge(df1, df2, left_index=True, right_index=True)

Convert pandas Series to DataFrame

The data I have to work with is a bit messy.. It has header names inside of its data. How can I choose a row from an existing pandas dataframe and make it (rename it to) a column header?

I want to do something like:

    header = df[df[&#39;old_header_name1&#39;] == &#39;new_header_name1&#39;]

    df.columns = header



Convert row to column header for Pandas DataFrame,

&gt;   
&gt; If you came here looking for information on _how to
&gt; merge a `DataFrame` and `Series` on the index_, please look at [this
&gt; answer](https://stackoverflow.com/a/40762674/4909087).
&gt; 
&gt; The OP&#39;s original intention was to ask _how to assign series elements
&gt; as columns to another DataFrame_. If you are interested in knowing the
&gt; answer to this, look at the [accepted answer](https://stackoverflow.com/a/26266031/4909087) by EdChum.

 

----

Best I can come up with is

    df = pd.DataFrame({&#39;a&#39;:[1, 2], &#39;b&#39;:[3, 4]})  # see EDIT below
    s = pd.Series({&#39;s1&#39;:5, &#39;s2&#39;:6})

    for name in s.index:
        df[name] = s[name]

       a  b  s1  s2
    0  1  3   5   6
    1  2  4   5   6

Can anybody suggest better syntax / faster method? 

My attempts:

    df.merge(s)
    AttributeError: &#39;Series&#39; object has no attribute &#39;columns&#39;

and

    df.join(s)
    ValueError: Other Series must have a name

*EDIT* The first two answers posted highlighted a problem with my question, so please use the following to construct `df`:

    df = pd.DataFrame({&#39;a&#39;:[np.nan, 2, 3], &#39;b&#39;:[4, 5, 6]}, index=[3, 5, 6])

with the final result

        a  b  s1  s2
    3 NaN  4   5   6
    5   2  5   5   6
    6   3  6   5   6


How to merge a Series and DataFrame

I want to find the number of `NaN` in each column of my data so that I can drop a column if it has fewer `NaN` than some threshold. I looked but wasn&#39;t able to find any function for this.  [`value_counts`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.value_counts.html) is too slow for me because most of the values are distinct and I&#39;m only interested in the `NaN` count.

How to count the NaN values in a column in pandas DataFrame

The data type of the field is String. I would like to fetch the data where character length of field name is greater than 40.

I tried these queries but returning error.
1. 

    db.usercollection.find(
    {$where: &quot;(this.name.length &gt; 40)&quot;}
    ).limit(2);

    output :error: {
    	&quot;$err&quot; : &quot;TypeError: Cannot read property &#39;length&#39; of undefined near &#39;40)&#39; &quot;,
    	&quot;code&quot; : 16722
    }

this is working in 2.4.9 But my version is 2.6.5



Content Type	Original Author	Original Content on Stackoverflow
Question	woshitom	View Question on Stackoverflow
Solution 1 - Python	Alex Riley	View Answer on Stackoverflow
Solution 2 - Python	Vipul Saxena	View Answer on Stackoverflow

Pandas: join DataFrames on field with different names?

Python Problem Overview

Python Solutions

Solution 1 - Python

Solution 2 - Python

Now try to merge the dataframes

Xcode 6 Swift code completion not working

Change colour of small triangle on spinner in android

Attributions