<jupyter_start><jupyter_text>Use this notebook to calculate mean of pixels per field for   - Sentinel one data  - Sentinel 2 data with 25 periods/dates  - Sentinel 2 data with month start month end dates**All you have to change is the path to the files**<jupyter_code>!pip -qq install pyspark findspark
!apt-get install openjdk-8-jdk-headless -qq > /dev/null
!wget -q https://www-us.apache.org/dist/spark/spark-3.0.1/spark-3.0.1-bin-hadoop2.7.tgz
import pandas as pd
import numpy as np
import os
import glob
from tqdm.notebook import tqdm_notebook
import pyspark
from pyspark import SparkContext
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
from pyspark.sql import Row, DataFrame, Column
from pyspark.sql.functions import lit

from functools import reduce
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
spark = SparkSession.builder \
    .master('local[*]') \
    .config("spark.driver.memory", "15g") \
    .appName('my-cool-app') \
    .getOrCreate()
spark
train = pd.read_csv('/content/drive/MyDrive/CompeData/Radiant/train_file.csv')
field_ids = train.field.unique()
len(field_ids)
train.shape<jupyter_output><empty_output><jupyter_text>SAR<jupyter_code>%%time
dfs = []
for i in tqdm_notebook(glob.glob('/content/drive/MyDrive/CompeData/Radiant/s1_all_data/s1_train*.csv')):
  dfs.append(spark.read.csv(path = i, sep =',', encoding = 'UTF-8', comment = None, header = True))
dfs[0].show(5)
%%time
cols = set()
for j in tqdm_notebook(dfs):
    for x in j.columns:
        cols.add(x)
cols = sorted(cols)

# Create a dictionary with all the dataframes
all_data = {}
for i, d in enumerate(dfs):
    new_name = 'new_df' + str(i)  # New name for the key, the dataframe is the value
    all_data[new_name] = d
    # Loop through all column names. Add the missing columns to the dataframe (with value 0)
    for x in cols:
        if x not in d.columns:
            all_data[new_name] = all_data[new_name].withColumn(x, F.lit(0))
    all_data[new_name] = all_data[new_name].select(cols)  # Use 'select' to get the columns sorted
len(dfs)
%%time
all_data_dfs = [all_data['new_df'+str(i)] for i in range(10)]
final_data = reduce(DataFrame.unionAll, all_data_dfs)
final_data.show(5)
final_data = final_data.select(*(F.col(c).cast("float").alias(c) for c in final_data.columns))
final_data.printSchema()
%%time
final_data_mean = final_data.groupBy('fields').mean().toPandas()
final_data_mean.head()
final_data_mean.head()
sar_train = final_data_mean.iloc[:, 1:]
sar_train.columns = final_data.columns
sar_train.y = round(sar_train.y).astype(int)
sar_train = sar_train[sar_train.y != 0].reset_index(drop = True)
sar_train.head()
sar_train.y.unique()
sar_train.to_csv('/content/drive/MyDrive/CompeData/Radiant/s1_all_data/sar_train.csv', index = False)
%%time
dfs = []
for i in tqdm_notebook(glob.glob('/content/drive/MyDrive/CompeData/Radiant/s1_all_data/s1_test*.csv')):
  dfs.append(spark.read.csv(path = i, sep =',', encoding = 'UTF-8', comment = None, header = True))
%%time
cols = set()
for j in tqdm_notebook(dfs):
    for x in j.columns:
        cols.add(x)
cols = sorted(cols)

# Create a dictionary with all the dataframes
all_data = {}
for i, d in enumerate(dfs):
    new_name = 'new_df' + str(i)  # New name for the key, the dataframe is the value
    all_data[new_name] = d
    # Loop through all column names. Add the missing columns to the dataframe (with value 0)
    for x in cols:
        if x not in d.columns:
            all_data[new_name] = all_data[new_name].withColumn(x, F.lit(0))
    all_data[new_name] = all_data[new_name].select(cols)  # Use 'select' to get the columns sorted
len(dfs)
%%time
all_data_dfs = [all_data['new_df'+str(i)] for i in range(5)]
final_data = reduce(DataFrame.unionAll, all_data_dfs)
final_data.show(5)
final_data = final_data.select(*(F.col(c).cast("float").alias(c) for c in final_data.columns))
final_data.printSchema()
%%time
final_data_mean = final_data.groupBy('fields').mean().toPandas()
final_data_mean.head()
sar_test = final_data_mean.iloc[:, 1:]
sar_test.columns = final_data.columns
sar_test.head()
sar_test.to_csv('/content/drive/MyDrive/CompeData/Radiant/s1_all_data/sar_test.csv', index = False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13446969696969696, 'fraction_numerical': 0.00946969696969697, 'mean_word_length': 4.678763440860215, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 10, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '332586', 'license': 'Apache-2.0', 'n_tokens_mistral': 1618, 'n_tokens_neox': 1558, 'n_words': 369, 'path': '2nd place -  Ensemble/Brainiac_Field_Aggregation_Mean.ipynb', 'repo_name': 'RadiantMLHub/spot-the-crop-xl-challenge', 'size': '4224'}