204

Pandas isna() vs isnull(). I'm assuming you are referring to pandas.DataFrame.isna() vs pandas.DataFrame.isnull(). Not to confuse with pandas.isnull(), which in contrast to the two above isn't a method of the DataFrame class. These two DataFrame methods do exactly the same thing! Even their docs are identical. You can even confirm this in pandas' code. ...


34

If you want to check equals values on a certain column let's say Name you can merge both Dataframes to a new one: mergedStuff = pd.merge(df1, df2, on=['Name'], how='inner') mergedStuff.head() I think this is more efficient and faster then whereif you have a big data set


29

In case this can help anyone else. Here is a solution that is more computationally efficient. TL;DR version If each row already has a unique index, then do this: >>> df.loc[df.groupby('A')['C'].idxmin()] If you've already indexed by 'A', then convert 'A' back into a column first. >>> df2 = df.reset_index() >>> df2.loc[df2....


20

df1.where(df1.values==df2.values).notna() True entries show common elements. This also reveals the position of the common elements, unlike the solution with merge.


18

pivot_table was made for this: df.pivot_table(index='Date',columns='Groups',aggfunc=sum) results in data Groups one two Date 2017-1-1 3.0 NaN 2017-1-2 3.0 4.0 2017-1-3 NaN 5.0 Personally I find this approach much easier to understand, and certainly more pythonic than a convoluted groupby operation. Then if you want the format ...


17

Feeding your column names into the y values argument as a list works for me like so: total_year[-15:].plot(x='year', y=['action', 'comedy'], figsize=(10,5), grid=True) Using something like the answer at this link is better and gives you way more control over the labels and whatnot: adding lines with plt.plot()


16

You can double check the exact number of common and different positions between two df by using isin and value_counts() Like that: df['your_column_name'].isin(df2['your_column_name']).value_counts() Result: True = common False = different


14

an example to write in same sheet: import pandas as pd data1 = """ class precision recall <18 0.0125 12 18-24 0.0250 16 25-34 0.00350 4 """ data2 = """ class precision recall <18 0 0 18-24 0.25 6 25-34 ...


13

Comparing values in two different columns Using set, get unique values in each column. The intersection of these two sets will provide the unique values in both the columns. Example: df1 = pd.DataFrame({'c1': [1, 4, 7], 'c2': [2, 5, 1], 'c3': [3, 1, 1]}) df2 = pd.DataFrame({'c4': [1, 4, 7], 'c2': [3, 5, 2], 'c3': [3, 7, 5]}) set(df1['c2']).intersection(set(...


13

You may use df2 = df.groupby(['address']).sum() or df2 = df.groupby(['address']).agg('sum') If there are columns other than balances that you want to peak only the first or max value, or do mean instead of sum, you can go as follows: d = {'address': ["A", "A", "B"], 'balances': [30, 40, 50], 'sessions': [2, 3, 4]} df = pd.DataFrame(data=d) df2 = df....


12

You can do this. But I doubt the efficiency. >> import pandas as pd >> df = pd.DataFrame({'a':[1,1,3,3],'b':[4,5,6,3], 'c':[1,2,3,5]}) >> df a b c 0 1 4 1 1 1 5 2 2 3 6 3 3 3 3 5 >> df[df['c'].isin(df.groupby('a').min()['c'].values)] a b c 0 1 4 1 2 3 6 3


11

One option is to map rare values to 'other'. This is commonly done in e.g. natural language processing - the intuition being that very rare labels don't carry much statistical power. I have also seen people map 1-hot categorical values to lower-dimensional vectors, where each 1-hot vector is re-represented as a draw from a multivariate Gaussian. See e.g. ...


11

The best way would be to use drop_duplicates(). If you have a larger DataFrame and only want those two columns checked, set subset equal to the combined columns you want checked. df = df.drop_duplicates() or df = df.drop_duplicates(subset=['userid', 'itemid'])


11

You can use this: df.columns = ['Goods_1', 'Durable goods','Services','Exports', 'Goods_2', 'Services', 'Imports', 'Goods_3', 'Services'] or if you have too many columns: cols = [] count = 1 for column in df.columns: if column == 'Goods': cols.append(f'Goods_{count}') count+=1 continue cols.append(column) df.columns = cols


11

The function dataframe.columns.difference() gives you complement of the values that you provide as argument. It can be used to create a new dataframe from an existing dataframe with exclusion of some columns. Let us look through an example: In [2]: import pandas as pd In [3]: import numpy as np In [4]: df = pd.DataFrame(np.random.randn(5, 4), columns=list(...


10

Consider the following approach: def f(col, threshold=3): mask = col.groupby((col != col.shift()).cumsum()).transform('count').lt(threshold) mask &= col.eq(0) col.update(col.loc[mask].replace(0,1)) return col In [79]: df.apply(f, threshold=3) Out[79]: col1 col2 names A 1 0 B 1 0 C 1 0 D ...


10

You can try this: cond = df1['Email'].isin(df2['Email']) df1.drop(df1[cond].index, inplace = True) >>df1 First Last Email 2 Joe Max email3@email.com 3 Will Bill email4@email.com


10

cols = list(df.columns) cols = [cols[-1]] + cols[:-1] df = df[cols]


9

Pandas black magic: df = df.groupby(['Date', 'Groups']).sum().sum( level=['Date', 'Groups']).unstack('Groups').fillna(0).reset_index() # Fix the column names df.columns = ['Date', 'one', 'two'] Resulting df: Date one two 0 2017-1-1 3.0 0.0 1 2017-1-2 3.0 4.0 2 2017-1-3 0.0 5.0


9

Break col1 into sub-groups of consecutive strings. Extract first and last entry per sub-group. Something like this: df = pd.DataFrame({'col1':['A>G','C>T','C>T','G>T','C>T', 'A>G','A>G','A>G'],'col2':['TCT','ACA','TCA','TCA','GCT', 'ACT','CTG','ATG'], 'start':[1000,2000,3000,4000,5000,6000,10000,20000]}) df['subgroup'] = (df['col1']...


8

You simply need to do: df['NEWcolumn'] = df['COLUMN_to_Check'].str.contains(pattern) df['NEWcolumn'] = df['NEWcolumn'].map({True: 'Yes', False: 'No'})


8

You can convert df2 to a dictionary and use that to replace the values in df1 cat_1 = [10, 11, 12] cat_2 = [25, 22, 30] cat_3 = [12, 14, 15] df1 = pd.DataFrame({'cat1':cat_1, 'cat2':cat_2, 'cat3':cat_3}) all_cats = [10, 11, 12, 25, 22, 30, 15] cat_codes = ['A', 'B', 'C', 'D', 'E', 'F', 'G'] df2 = pd.DataFrame({'all_cats':all_cats, 'cat_codes':cat_codes}) ...


7

I found the issue, I need to return a pd.Series() return pd.Series([ out_cost_price_dist, out_cost_price_stock, out_cost_price_expected, out_cost_price_country, out_cost_price_real_dist, out_cost_price_real_stock, out_cost_price_real_expected, out_cost_price_real_country ])


6

As Emre already mentioned, you may use the groupby function. After that, you should apply reset_index to move the MultiIndex to the columns: import pandas as pd df = pd.DataFrame( [ ['Hospital 1', 'District 19', 5], ['Hospital 1', 'District 19', 10], ['Hospital 1', 'District 19', 6], ['Hospital ...


6

Based on the documentation 0.22 and 0.24.1, the flavor does not exist in the argument list of the to_sql method. You're probably running the 0.24.1 version which does not need flavor argument.


6

Using dict comprehension with nested groupby: d = {k: f.groupby('subgroup')['selectedCol'].apply(list).to_dict() for k, f in df.groupby('maingroup')} Output: {'A': {123: [1, 9], 321: [3]}, 'B': {345: [5], 768: [2, 3]}, 'C': {178: [6, 5], 712: [4]}}


5

First check your data. You can't get value 12.780517 for index 196341. This value will be for index 196641. Now, to get column 'b' in your result, use pd.merge. Don't make column 'a' as index. Leave it as it is. >>> df = pd.DataFrame({'a':[196512, 196512, 196512, 196512, 196795, 196795, 196795, 196795, 196341, 196341, 196641, 196641, 196346, ...


5

This sort of effect can be achieved with pandas.DataFrame.resample() combined with Resampler.aggregate() like: Code: df.resample("1Min").agg({'A': sum, 'B': np.mean}) Test code: df = pd.read_fwf(StringIO(u""" A B 2017-01-01T00:01:01 0 100 2017-01-01T00:01:10 1 200 2017-01-01T00:01:16 2 300 ...


5

You should use pandas.DataFrame.shift() to find the pattern you need. Code: def fill_zero_not_3(series): zeros = (True, True, True) runs = [tuple(x == 0 for x in r) for r in zip(*(series.shift(i) for i in (-2, -1, 0, 1, 2)))] need_fill = [(r[0:3] != zeros and r[1:4] != zeros and r[2:5] != zeros) ...


5

I found a package called pandasql, which is based on sqldf for R. It seems quite a bit slower than doing the transformations with the pandas package, but it gets the job done. Just put the SQL query into a string like this: query_string = """ select * from df """ Then use the string in the pandasql.sqldf package, as follows: new_dataframe = pandasql....


Only top voted, non community-wiki answers of a minimum length are eligible