After pdf.read_csv('anyfile.txt') I need to sort and selct data. With search " pandas dataframe select data lines will value more than" google shows link Selecting Pandas DataFrame Rows Based On Conditions https://chrisalbon.com/python/pandas_selecting_rows_on_conditions.html

# Import modules
import pandas as pd
import numpy as np

# Create a dataframe

# Create a dataframe
raw_data = {'first_name': ['Jason', 'Molly', np.nan, np.nan, np.nan], 
        'nationality': ['USA', 'USA', 'France', 'UK', 'UK'], 
        'age': [42, 52, 36, 24, 70]}
df = pd.DataFrame(raw_data, columns = ['first_name', 'nationality', 'age'])
df

	first_name	nationality	age
0	Jason	USA	42
1	Molly	USA	52
2	NaN	France	36
3	NaN	UK	24
4	NaN	UK	70

Method 1: Using Boolean Variables

# Create variable with TRUE if nationality is USA
american = df['nationality'] == "USA"

# Create variable with TRUE if age is greater than 50
elderly = df['age'] > 50

# Select all casess where nationality is USA and age is greater than 50
df[american & elderly]

first_name	nationality	age
1	Molly	USA	52

Method 2: Using variable attributes

# Select all cases where the first name is not missing and nationality is USA 
df[df['first_name'].notnull() & (df['nationality'] == "USA")]

	first_name	nationality	age
0	Jason	USA	42
1	Molly	USA	52

And with my data set I code simple code with works and on cmd screen I see:

import pandas as pd

import numpy as np

dataset = pd.read_csv("C:\\Users\\ANTRAS\\Documents\\PANDASTEST.txt", delimiter="\t")

#print(dataset)
numerous = dataset['Values'] > 30000

dataset[numerous]

forprint = dataset[numerous]

print(forprint)

C:\Python27\python.exe C:/Users/ANTRAS/.PyCharmCE2017.2/config/scratches/scratch_11.py

Name ID Values Rules

1 FIRST 4152.0 50000.0 SELL

2 SECOND 4153.0 70000.0 BUY

3 THIRD 4154.0 40000.0 OFFER

Process finished with exit code 0

And with value less than 30000 I get

numerous = dataset['Values'] < 30000

C:\Python27\python.exe C:/Users/ANTRAS/.PyCharmCE2017.2/config/scratches/scratch_11.py
Name ID Values Rules
4 FOURTH 4155.0 1500.0 SWAP
5 FIFTH 4156.0 2000.0 FREE
6 SIXTH 4157.0 500.0 GET
7 SEVENTH 4158.0 1000.0 GO

Process finished with exit code 0

Another operations needed are to delete rows which names contain only underscores (since with regex I remove all non-alphanumeric characters) and values cell contain non-numerical values. Afterwards I will extract data using keywords list to new data frame and export it to csv file.

Here again I copy Chris Alon https://chrisalbon.com/

Dropping Rows And Columns In pandas Dataframe

Import modules

import pandas as pd

Create a dataframe

data = {'name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'], 
        'year': [2012, 2012, 2013, 2014, 2014], 
        'reports': [4, 24, 31, 2, 3]}
df = pd.DataFrame(data, index = ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'])
df

	name	reports	year
Cochice	Jason	4	2012
Pima	Molly	24	2012
Santa Cruz	Tina	31	2013
Maricopa	Jake	2	2014
Yuma	Amy	3	2014

Drop an observation (row)

df.drop(['Cochice', 'Pima'])

	name	reports	year
Santa Cruz	Tina	31	2013
Maricopa	Jake	2	2014
Yuma	Amy	3	2014

Drop a variable (column)

Note: axis=1 denotes that we are referring to a column, not a row

df.drop('reports', axis=1)

	name	year
Cochice	Jason	2012
Pima	Molly	2012
Santa Cruz	Tina	2013
Maricopa	Jake	2014
Yuma	Amy	2014

Drop a row if it contains a certain value (in this case, "Tina")

Specifically: Create a new dataframe called df that includes all rows where the value of a cell in the name column does not equal "Tina"

df[df.name != 'Tina']

	name	reports	year
Cochice	Jason	4	2012
Pima	Molly	24	2012
Maricopa	Jake	2	2014
Yuma	Amy	3	2014

To find wheather it is numeric or non-numeric value requires more sophisticated methods.

Finding non-numeric rows in dataframe in pandas?

On stackoverflow I found solutions how to find with numpy isreal method numeric and non-numeric values. Here we come with Biblical alpha numeric notions. But main method is Pandas applymap

DataFrame.applymap(func)[source]

Apply a function to a DataFrame that is intended to operate elementwise, i.e. like doing map(func, series) for each series in the DataFrame

I make google search "pandas data frame select rows containing keyword attributes":

pandas + dataframe - select by partial string

def.loc[:, df.columns.str.contains('a').tolist()]

https://stackoverflow.com/questions/11350770/pandas-dataframe-select-by-partial-string

Same search brings solution of list of arguments from stackoverflow.

Select rows containing certain values from pandas dataframe

For single search value

df[df.values  == "banana"]

 df[df.isin(['banana'])]

For multiple search terms:

  df[(df.values  == "banana")|(df.values  == "apple" ) ]

df[df.isin(['banana', "apple"])]

  #         A       B      C
  #  1   apple  banana    NaN
  #  2     NaN     NaN  apple
  #  3  banana     NaN    NaN
  #  4   apple   apple    NaN

From Divakar: lines with both are returned.

select_rows(df,['apple','banana'])

 #         A       B     C
 #   0  apple  banana  pear

To delete rows with specific columns not having only intengers seems complex tasks.

So I tested many codes for Boolean how determine "Values" column to find if values are intenger.

pandas.DataFrame.dropna

also

https://stackoverflow.com/questions/14991195/how-to-remove-rows-with-null-values-from-kth-column-onward-

df2.dropna(subset=['three', 'four', 'five'], how='all')

dropna drops rows if in specified column there is NA and NaN since it is default value for null generated by Pandas.

import pandas as pd
import numpy as np
dataset = pd.read_csv("C:\\Users\\ANTRAS\\Documents\\PANDASTEST.txt", delimiter="\t")
result3 = dataset.dropna(subset=['Values'], how='all') #OKprint(result3)

Pandas remove rows which any string. On stackoverflow great solutions to integrate regex library, ix method is not known to me,

import re

regex = re.compile("[a-zA-Z]+")

result3 = dataset.ix[dataset.Values.map(lambda x: regex.search(x) is None)]

print(result3)

Before:

Name ID Values Rules

FIRST 4152 50000 SELL

SECOND 4153 70000 BUY

second45 7896 sduisl zet

THIRD 4154 40000 OFFER

FOURTH1 4161 540 SELLING

inser 4786 htpp// deit

FOURTH 4155 1500 SWAP

FIFTH 4156 2000 FREE

SIXTH 4157 500 GET

SEVENTH 4158 1000 GO

sweasd 7896 rjasj REX

After:
C:\Python27\python.exe C:/Users/ANTRAS/.PyCharmCE2017.2/config/scratches/scratch_11.py
Name ID Values Rules
0 FIRST 4152 50000 SELL
1 SECOND 4153 70000 BUY
3 THIRD 4154 40000 OFFER
4 FOURTH1 4161 540 SELLING
6 FOURTH 4155 1500 SWAP
7 FIFTH 4156 2000 FREE
8 SIXTH 4157 500 GET
9 SEVENTH 4158 1000 GO

Process finished with exit code 0

And got but which I can not explain, after I cleaned with Pandas data frame I can not make selection with values in Values column. Though bug fix research was rewarding and I found Programming with Python Python lessons for CU libraries blog. Software Carpentry.

Indexing, slicing and subsetting DataFrames with pandas

The blog is solely for stepby step automation with Pandas.

The bug with lambda function appears when there is no intenger fields in "Values column", so you need to write condition boolean if field is just with numeric exit, i.e. do not execute ix method.

Remove non-numeric rows in one column with pandas

On stackoverflow I found other solution. To be tested. Use Pandas data frames and numpy methods.

I tested these code, they not make any change.

dataset[dataset['Values'].apply(lambda x: isinstance(x, (int, np.int64)))]dataset[dataset['Values'].apply(lambda x: type(x) in [int, np.int64, float, np.float64])]

#testui = senabaze = pd.read_table('test.txt', col_index='keys_ids', col=names) #testui.to_csv('testui.csv')

I was looking to pandas cheatsheet and made searches using cheatsheet terminology "boolean indexing", objects and etc.

So I test pandas methods.

pandas.DataFrame.select_dtypes¶

C:\Python27\python.exe C:/Users/ANTRAS/.PyCharmCE2017.2/config/scratches/scratch_11.py

Name object

ID int64

Values object

Rules object

dtype: object

I there strong or object in column cell value, you get object type.

The search for advance Pandas Data Manipulation methods was rewarding. I found Python Pandas tutorial for business analysis.

So my problem can be solved by addtional function to check fo column values and exit on float64 type, o investigate and test with numpy for variablve string (object values) in specific column values.

So the problem with dtypes is that for mathematical comparison you have to have float64 type. You can convert int64 type to float64 using astype(float64) method pd (your data name) column(name of colum) plus astype(float64), i.e. example.columninexample.astype(float64). And below working data frame method to concanate data frames, remove duplicates and select rows with values more than 10.000. Note that if you read text file having add header, the data frame digits will be object type and you will not be able to convert directly to float64 (not tested for int64) type/

import pandas as pd

user_cols = ['keys_ids', 'Name', 'Members', 'Rules']
senabaze = pd.read_table('test.txt', sep=',', header=None, index_col='keys_ids', names=user_cols)
senabaze.head()

#senabaze.dtypes
#reikia su dtypes tikrimti, skaiciai del header txt file buna object, be header int64 kuriuos per astype reikia padaryti float64
senabaze['Members'] = senabaze.Members.astype(float)
#senabaze.dtypes

naujiduomenys = pd.read_table('test1.txt', sep=',', header=None, index_col='keys_ids', names=user_cols)
naujiduomenys.head()

#reikia su dtypes tikrimti, skaiciai del header txt file buna object, be header int64 kuriuos per astype reikia padaryti float64
naujiduomenys['Members'] = naujiduomenys.Members.astype(float)
naujiduomenys.dtypes

atnaujinta = pd.concat([senabaze, naujiduomenys])
atnaujinta.drop_duplicates()
netoatnaujinta = atnaujinta.drop_duplicates()

#netoatnaujinta.Members
#SSS =  netoatnaujinta.Members
#print SSS

type(False)
bool
booleans = []
for svarbios in netoatnaujinta.Members:

    if svarbios >= 10000:
        booleans.append(True)
    else:
        booleans.append(False)

daugiauneidesimttukst = pd.Series(booleans)
daugiauneidesimttukst.head()

daugiauneidesimttukst = netoatnaujinta.Members >= 10000
daugiauneidesimttukst.head()

#netoatnaujinta[daugiauneidesimttukst]

netoatnaujinta.loc[netoatnaujinta.Members >= 10000, 'Name']
TTT = netoatnaujinta.loc[netoatnaujinta.Members >= 10000, 'Name']

TTT.to_csv('Morethan10000.csv')



#testui = senabaze = pd.read_table('test.txt', col_index='keys_ids', col=names)
#testui.to_csv('testui.csv')

Python Pandas tutorial for business analysis http://modeanalytics.com
Creating Pandas DataFrames & Selecting Data

I encountered problem with reading Adwords data csv file to Pandas Data Frame. Main problem I do not see numeric data when data import, only NaN and astype(float) neither convert(objects)work. The file also is corrupted to convert to text file, null value. I also had to to choose python=engine parameter. I need to import to data frame to match with pytrends Google Trends data. Google trends data bring most relevant to category keyword whether search, picture, youtube or shopper. 100 average most relevant keyword. Google Trends data from cmd has text markups, so to automate export of five keywords data is necessary to csv or text files for further merging with glob library.

The problem to read csv file downloaded from Adwords is UTF-16 coding. Therefore you need to recode csv file to UTF-8. With UTF-8 csv file decreases in size twice.

import codecs

import csv

#for Adwords files

with codecs.open('TOTAL.csv', 'rU', 'UTF-16') as infile:

with open('TOTAL8.csv', 'wb') as outfile:

for line in infile:

outfile.write(line.encode('utf8'))

https://stackoverflow.com/questions/13875566/python-csv-file-utf-16-to-utf-8-print-error

So this pandas match of Adwords, Google Trends category relevance for images and searches. The match suggest to use 5 main keywords. Note that Adwords keywords were scrapped from competing sites urls.




 
 
 
 
 
 
 
 
 
 
 
 
  

  Keyword
  AvgMonth
  Competition
  Suggestedbid
  Inplan
  Number
  Traffic_x
  Region_x
  Traffic_y
  Region_y
 

  0
  freesia
  550
  0.64
  0.7
  N
  48
  1
  England
  2
  England
 

  1
  plants
  550
  0.59
  0.96
  Y
  52
  100
   England
  100
   England
 

  2
  alstroemeria
  55
  0.58
  0.74
  Y
  53
  0
  England
  0
  England
 

  3
  roses
  550
  0.57
  0.78
  Y
  54
  95
   England
  95
   England
 

  4
  florist
  550
  0.23
  2.12
  Y
  4
  85
   England
  67
   London
 

  5
  carnation
  550
  0.16
  0.39
  N
  8
  2
  England
  3
  London
 

  6
  horseshoe
  55
  0.16
  0.05
  N
  9
  3
  England
  3
  England
 

  7
  weddings
  55
  0.14
  0.25
  Y
  10
  38
   England
  38
   England
 

  8
  gerberas
  55
  0.02
  

  Y
  17
  0
  England
  0
  England
 

  9
  butterfly
  550
  0.01
  

  Y
  19
  1
  England
  1
  England
 

  10
  congratulations
  550
  0
  0.08
  Y
  22
  8
   England
  8
   England
 

  11
  germini
  55
  0
  

  Y
  26
  13
  England
  13
  England
 

Run business automating tasks with python programming.

Search This Blog

Saturday, October 28, 2017

Pandas operations with data

Method 1: Using Boolean Variables

Dropping Rows And Columns In pandas Dataframe

Drop an observation (row)

Drop a variable (column)

Drop a row if it contains a certain value (in this case, "Tina")

No comments:

Post a Comment

Labels

Total Pageviews

	Keyword	AvgMonth	Competition	Suggestedbid	Inplan	Number	Traffic_x	Region_x	Traffic_y	Region_y
0	freesia	550	0.64	0.7	N	48	1	England	2	England
1	plants	550	0.59	0.96	Y	52	100	England	100	England
2	alstroemeria	55	0.58	0.74	Y	53	0	England	0	England
3	roses	550	0.57	0.78	Y	54	95	England	95	England
4	florist	550	0.23	2.12	Y	4	85	England	67	London
5	carnation	550	0.16	0.39	N	8	2	England	3	London
6	horseshoe	55	0.16	0.05	N	9	3	England	3	England
7	weddings	55	0.14	0.25	Y	10	38	England	38	England
8	gerberas	55	0.02		Y	17	0	England	0	England
9	butterfly	550	0.01		Y	19	1	England	1	England
10	congratulations	550	0	0.08	Y	22	8	England	8	England
11	germini	55	0		Y	26	13	England	13	England