164

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. ...


28

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


24

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....


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 ...


16

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()


13

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.


11

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....


10

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'])


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 ...


9

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


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

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(...


9

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


8

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


8

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(...


8

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 0.35 5 """ #create 2 df for ...


7

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


7

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

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']...


6

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


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.


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....


5

I would import the datasets in pandas separately, mold them as you please, and then you can use the pd.concat function. This will assume that the instances are aligned by the automatically assigned index in pandas. If there is more data in one list than the other, the missing values will be NaN. df1 = pd.DataFrame(data=[1,2,3]) df2 = pd.DataFrame(data=['a','...


5

Those symbols you are talking about are hex values. They could actually be useful to keep in the dataset, as the tcpdump tool outputs them if the right options are set. Despite that, you can replace occurrences of them using pandas and a simple regular expression. A regular expression that matches hex values is: 0[xX][0-9a-fA-F]+ - see a live demo of that ...


5

From the docs: Warning: Starting in 0.20.0, the .ix indexer is deprecated, in favor of the more strict .iloc and .loc indexers. Just use .iloc instead (for positional indexing) or .loc (if using the values of the index). To read more about loc/ilic/iax/iat, please visit this question on Stack Overflow. To quote the top answer there: loc: only work on ...


4

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, ...


4

Assuming that the rows are in same order that you wish to merge all of the dataframes, you can use the concat command specificying axis=1. new_df = pd.concat([df1, df2, df3], axis=1) If the row index for each of the data frames are different and you want to merge them in the current order, you can also apply ignore_index: new_df = pd.concat([df1, df2, df3]...


4

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


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